View Full Version : Working with database

10-20-2010, 09:42 AM
1.Database administration
Ways of database administration:

phpMyAdmin (recommended)
mysql.exe in the Mysql packet

First way is recommended. You�ll have to learn such queries as ALTER TABLE, ADD COLUMN. Second way is so to speak roundabout technology which can be used if you don�t know about phpMyAdmin and mysqldump utility. Commands which delete database and create it again are written in the script. Sometimes it really helped, but it is still roundabout technology.
If you have few sites in the future it is better to create several databases. It�ll lighten server�s work and will help to escape table mess.
2.Connection with database server
Connection with database server is carried out with the help of mysql_connect: $connect = mysql_connect(«host», «login», «password») functions. By default on the MySQL server in the table of users there is user root that has access only to the localhost. Attention: localhost access means that your PHP script has an access, and you can call him from any computer.
What happens when we call mysql_connect function? When your script is called, PHP gives a space for information about the script and its variables. Information about script includes also information about connections with databases. Variable $connect indicates the place where that information is kept.
Why are variables used? They used on the case if you need to use several database servers (or for example, you use different logins with different privileges for providing security). In that cases in every query there should be some distinctness, by what channel command goes.
But if you use only one connection you don�t have to indicate it in the query function parameters. PHP finds first (in our case the only) connection and uses it.
3.Fetch-query and result processing.
Database query function mechanism of working is the same as connection function mechanism: function gets query parameters and if it is needed connection parameters. The result is written to the variable:

$result = mysql_db_query(string database, string query [, connection variable]); or

$result = mysql_query(string query [,connection variable]); Attention: if you want to use mysql_query function in which database isn�t mentioned, you should to select database you will use: functions mysql_fetch_row and mysql_fetch_array:

echo "<table>";
while ($row = mysql_fetch_array($result))
echo "<tr><td>", $row["field1"], "</td><td>", $row["field2"], "</td></tr>";
echo "</table>"; mysql_fetch_array function issues an array to the indicated variable (in our case $row). Array�s indexes are fields� name. mysql_fetch_row issues an array. Indexes of that array are numbers beginning from 0.
4.Queries of action
Queries of action are command DELETE and UPDATE. Such kinds of queries are similar to SELECT ones, that�s why command sending to the server is carried out by the same way: mysql_query (mysql_db_query). Bu here function doesn�t return the result:

$result = mysql_query("SELECT * FROM sometable");
mysql_query("DELETE FROM sometable WHERE id=..."); If we run fetch-query and don�t write the results to the variable, data won�t be kept anywhere.
5.Query errors processing
Message about the last error can be get by the means of the function mysql_error:
echo "Database error. MySQL writes:", mysql_error();
If the result is written to the variable we can check it:

$result = mysql_query($request);
if (!$result)
echo "Database error. MySQL writes:", mysql_error(), mysql_error();
else {
echo "<table>";
while ($row = mysql_fetch_array($result))
echo "<tr><td>", $row["field1"], "</td><td>", $row["field2"], "</td></tr>";
echo "</table>";
}; If the result isn�t written to the variable we write:

$request = "UPDATE (...)";
if (!mysql_error())
echo "Data update successful!";
else echo "Database error. MySQL writes:", mysql_error();