PHP + SQL for beginners: We increase the level of programming.

This article is purely practical and touches upon only one aspect - how to increase the programming level in PHP when working with the SQL database (we will try to touch upon other aspects of programming in the future). Vysk level means achieving the goal with the minimum and quickly received code (the number of lines of code, characters in a line), even if this is to the detriment of the efficiency of the use of computing resources.

Currently, CPU time, memory and disk space are so cheap compared to the engineering staff working hours, that they can be neglected. The speed of developing an applied task is much more important than how effectively it works. If the developer, instead of 30 lines of code, writes only 10, then the error probability decreases three times. Further maintenance and modification of a shorter code also undoubtedly requires much less time and human resources. Do not forget that we do not consider system solutions here, when it is the code efficiency that is put at the forefront.

In this article, we will create a wrapper for the standard PHP library mysql. It will not be difficult to adapt it yourself for other databases. This will not be an attempt to create an ORM. The ORM has completely different tasks that go against our goal of improving the programming level and shortening the code.

As a working example, take an imaginary user data table of the following structure:
')

CREATE TABLE users (
id int auto_increment,
category tinyint,
name varchar (25),
password char (32),
email varchar (100)
);

Recall that the usual way to get data from a table would be something like this:

$ result = mysql_query ( "SELECT * FROM users" );
$ users = array ();
while ($ row = mysql_fetch_array ($ result))
{
$ users [] = $ row;
}

Something suggests that such a construction of 5-6 lines repeated from time to time can (and should!) Be replaced by a more compact one. It is clear that inside the loop we can actually perform some more useful actions than just accumulating an array. Here the paradigm of high-level programming comes into force, which says that you should not mix in one such system action, like fetching results from a database resource with its application usage. If you could even do without the $ users array, then now you will have to get it and continue to work with it in application mode.

We introduce a new function:

$users = sql_get_rows("SELECT * FROM users");


Well that's all. 5-6 lines of code, which still need to read and understand what they are doing, are replaced by one clear and understandable line. Of course, this example is purely academic, in practice it is often necessary to somehow refine the result. For example, I would like to directly access the string with a given id. Scouring the array in search of the desired $ users ['id'] is also not entirely correct. We considered this:

$users = sql_get_rows("SELECT * FROM users", 'id');


Now in our $ users array, the indices are not just in order of numbers, but correspond to the id of this user. We went further and provided for multidimensional or better said tree arrays. For example, you need to list all users, but grouping them into categories. You are welcome:

$users = sql_get_rows("SELECT * FROM users", 'category', 'id');


Now consider the special cases. For example, you know that you will always get one line as a result. Here we offer another function:

$user = sql_get_row("SELECT * FROM users WHERE id=$id");


Why another function. Well, firstly, if you can inadvertently increase the efficiency of the code, then why not. But this is not the point. The main thing is that the person who will read this code after you will clearly see that the result is one line. By the way, the function itself always adds “LIMIT 1” at the end.
Well, another degenerate function:

$qty = sql_get_value("SELECT count(*) FROM users");


As you understand, it returns a single scalar value.

For other queries that do not return a result, we have provided such a function:

$id = sql_query("INSERT INTO users ...");
$qty = sql_query("DELETE FROM users WHERE email='' ");


Notice that in fact they also return a result, and very useful. For INSERT, the auto_increment code is returned. For DELETE and UPDATE, the number of rows processed. It remains only to fill in the gaps at the dot in the example above. There, as you understand, there should be data that fill the fields.

Introduce the rule: the data must always come in the form of an associative array and nothing else. Believe me, it's always more convenient and more flexible. For example, you read a row from a table. It comes to you in the form of an associative array. You will replace individual values, perhaps delete some fields (for example, 'id') and give the array back to UPDATE or INSERT. Very effective. If the number of fields in the table has increased, then the code is almost nowhere to be corrected.

We introduce another auxiliary function:

$set = sql_set($fields);
$id = sql_query("INSERT INTO users $set");


It is not difficult to guess that the function sql_set () generates the SET option with a list of fields and data: SET name = 'Vasya', password = '56F54AC84', email = 'vasya @ pupkin.com'

Thus, having entered several high-level functions, we raised the level of the program. The length of those pieces of code that are responsible for working with the base has been reduced by about 5-7 times, but most importantly, the readability of the code and its semantic purity have increased. We guarantee that in a typical application (that is, in 99% of cases) you will no longer need to turn to low-level functions. These functions will be enough to solve almost any applied problems.
In conclusion, we give the text of the library (since it is very short). An inquisitive reader will find there a couple of useful things that are not mentioned in the article. Please note that the library is written under error_reporting (E_ALL ^ ​​E_NOTICE); Why this is so - the topic of another article on improving programming. But anyone is free to change in this library to your liking.

// =============================================== =========
function sql_get_rows ($ query, $ key_col = false , $ key_col2 = false )
{
$ array = array ();
$ res = sql_query ($ query);
if (mysql_num_rows ($ res)> 0)
{
if ($ key_col)
{
if ($ key_col2) while ($ item = mysql_fetch_assoc ($ res)) $ array [$ item [$ key_col]] [$ item [$ key_col2]] = $ item;
else while ($ item = mysql_fetch_assoc ($ res)) $ array [$ item [$ key_col]] = $ item;
}
else while ($ item = mysql_fetch_assoc ($ res)) $ array [] = $ item;
}
mysql_free_result ($ res);
return $ array;
}
// =============================================== =========
function sql_get_row ($ query)
{
$ res = sql_query ($ query. 'LIMIT 1' );
$ array = mysql_fetch_assoc ($ res);
mysql_free_result ($ res);
return $ array;
}
// =============================================== =========
function sql_get_value ($ query)
{
$ res = sql_query ($ query);
$ array = mysql_fetch_row ($ res);
mysql_free_result ($ res);
return $ array [0];
}
// =============================================== =========
function sql_query ($ query)
{
mysql_query ($ query) or sql_query_die (mysql_error (), $ query);
if (substr ($ query, 0,6) == 'INSERT' ) $ res = mysql_insert_id ();
if (! $ res) $ res = mysql_affected_rows ();
return $ res;
}
// =============================================== =========
function sql_set ($ data, $ skipslashes = false )
{
sql_protect ($ data);
$ set_text = '' ;
foreach ($ data as $ col => $ val)
{
if (! $ skipslashes) $ val = addslashes ($ val);
$ set_text. = ",` $ col` = '$ val' " ;
}
$ set_text = substr ($ set_text, 1); // remove very first comma
return 'SET' . $ set_text;
}
// =============================================== =========
function sql_query_die ($ error, $ query)
{
$ backtrace = debug_backtrace ();
foreach ($ backtrace as $ step => $ trace) if (substr ($ trace [ 'function' ], 0,4)! = 'sql_' ) break ;
$ step--;
die ( "SQL: $ error. Generated at: {$ backtrace [$ step] ['file']}: {$ backtrace [$ step] ['line']} \ nFull query: '$ query'" );
}
* This source code was highlighted with Source Code Highlighter .