Working with databases.

All I can say as a recommendation for using databases is that life without them is just death! The database is a ray of exe-shine light in the dark realm of data processing by an interpreted program. The database is a bit of a headache, but it removes a lot more.
MySQL database server is taken as an example (I believe that once you have mastered it, you will easily master others. Someday I will write about them if I master it myself :).The first talk is about PHP functions used to work with MySQL. So let”s get started. 

1. Database Administration

Methods of database administration in descending order of convenience: 
phpMyAdmin (highly recommended!) Write a script that would distort the database (see example) in the mysql package

I especially recommend the first method. With it you don”t have to study ALTER TABLE, ADD COLUMN queries, etc. I still don”t know them. Moreover, “such questions, comrade ambassador, cannot be solved right away” – when will you need to automatically change the structure of the database or table? A few words about the second method. This is, so to speak, a workaround technology that I used without knowing about phpMyAdmin and the mysqldump utility. The script contains commands that delete the database and create it again. Once it helped, but in general, I will say it again, a workaround technology, a “prop”.
For the future: if you have several sites using a database, then at least within the home server, create several databases. This will make it easier for the server and eliminate the possibility of table confusion. In general, the rules for working with a database are the same as with a website – keep in a separate directory from others.

2. Database server connection

… is done with the mysql_connect function: $ connect = mysql_connect (& lt; host & gt ;, & lt; login & gt ;, & lt; password & gt;); By default, the mysql server has a root user in the user table, which can only be accessed from localhost, that is, from the same computer where the mysql server is located. ATTENTION! “Access from localhost” means your PHP script has access, and you can access it from any other computer.

What happens when we call the mysql_connect function? When your script starts executing, php allocates space in its memory for information about it and its variables. The information about the executable script stores, among other things, information about connections to databases. The variable $ connect is roughly a pointer to the place where this information is stored. This variable is exactly the same as the others – if you use functions, then you need to declare global variables to access it.

Why is a variable used at all? This is in case you need to use several database servers for work (or, for example, to ensure greater security, you use different logins, which may have different privileges). In such cases, in each request, certainty is needed, so to speak, through which channel the command goes. But if you use only one connection, you do not need to specify it in the parameters of the request functions (about them – below) – php finds the first (and in this case the only) connection established and uses it.

3. Query-selection and processing of results

The mechanism of operation of the database query functions is the same as that of the connection function: the request parameters and (if necessary) connection parameters are passed to the function, and the result is written to a variable:
$ result = mysql_db_query (string database, string query [, connection variable]);

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

ATTENTION! To use the mysql_query function, in which the database is not specified, you must first select the database to use:mysql_select_db (string database); 

We now have a $ result variable. This is a pointer to the result of the query. There are some rows of the table. You can get these lines through the mysql_fetch_row and mysql_fetch_array functions:

echo "<table>";
while ($row = mysql_fetch_array($result))
  echo "<tr><td>", $row["field1"], "</td><td>", $row["field2"], "</td></tr>";
echo "</table>";

The mysql_fetch_array function returns to the specified variable (in this case $ row) an array, the indices of which are the names of the fields (moreover, if you write table.field in the list of query fields, then the array index will be field). mysql_fetch_row returns an array whose indices are numbers starting from 0. 

Which function is better to use? If you ask for an asterisk, i.e. all fields of the table, and you need to display the fields in a certain sequence (when, for example, a header is drawn on the table), it is better to use mysql_fetch_array. If you are requesting one or two or three fields, clearly knowing their sequence, you can do mysql_fetch_row – this will reduce the amount of program code.

4. Action requests

These are DELETE and UPDATE commands. Similar queries – in “rights” are the same as SELECT, so the command is sent to the server in the same way – mysql_query (mysql_db_query). But in this case, the function does not return a result:

$result = mysql_query("SELECT * FROM sometable");
but
mysql_query("DELETE FROM sometable WHERE id=...");

Accordingly, if we execute a fetch query and do not write the result to a variable, the data will not be stored anywhere.

5. Handling request errors

The last error message can be obtained through the mysql_error function:

echo “Database error. MySQL writes:”, mysql_error ();

If the result of a function is written to a variable, you can check it:

$result = mysql_query($request);
if (!$result)
  echo "Database Error. MySQL writes:", 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 we don’t write to the variable, then like this:

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

If the request is generated automatically, you can display the request itself (it is useful to create a variable that would contain it and use it as a function parameter).

Leave a Reply

Your email address will not be published. Required fields are marked *

Previous article

Stealing sessions