Added Sep 30, 2018  by Admin

Useful PHP functions for working with MySQL

The article offers a few simple functions of the PHP language to interact with the MySQL database, which can greatly facilitate the daily work of programmers.

Useful  PHP functions for working with MySQL

Working with MySQL using PHP built-in functions (such as mysql_query(), mysql_fetch_assoc (), etc.) is not difficult. However, the syntax required to process the results of a query usually takes up a couple of extra lines of code. You have to either repeat this code every time, or use special libraries that are designed to automate this task. These libraries, however, often take on additional (usually quite useless) tasks, as a result of which their interface is complicated and such libraries are inconvenient to use.

The functions offered in this article allow you to solve the vast majority of tasks on sending and processing the results of queries to the MySQL server, using a very small amount of code. In addition, it is easy to turn on the mode of using the improved module mysqli, which is important in connection with the recommendations for the transition from The original MySQL API, which from PHP 5.5.0 will be considered obsolete.

The function code can be downloaded as a single file.

mysql_q() — request and track errors

Often it turns out that the query to MySQL contains an error, which is why the application does not work correctly. The mysql_query() function behaves silently without displaying any messages. Therefore, finding the place where the error occurred often becomes a problem.

Function mysql_q() helps you quickly find the problem — in case of an error in the query, it displays a message with the error information and terminates script.

Consider the example:

<?php
	$query = "SELECT NOW"; // forgot to put brackets
	mysql_query($query); // nothing happens
	mysql_q($query); // mysql_q() will report error
?>

After work mysql_q() will appear on the screen the message:

MySQL error in file /usr/home/www/nassat/test.php at line 7 (function mysql_q):
Unknown column 'NOW' in 'field list'

SELECT NOW

Mysql_q function() suitable for all queries (not only SELECT):

Mysql_q function() suitable for all queries (not only SELECT):

mysql_q("INSERT VALUES ('что-то') INTO some_table");
mysql_q("SET @a = 1");
mysql_q("USE some_other_database");
mysql_q("TRUNCATE some_table");
mysql_q("DROP some_table");
mysql_q("START TRANSACTION");

Parameter substitution in the query

There is the possibility of substitution into the query the elements of the associative array. For this request you need to make with the use of special labels and as a second argument to pass a function mysql_q() array replacements:

$sql = "
    INSERT INTO products
    SET
      id = :id,
      price = :price,
      name = :name
    ";
   
$params = array(
        'id' => 12,
        'price' => 10000,
        'name' => 'TV',
    );

mysql_q($sql, $params);

When replacing, the variable type is taken into account: strings will be escaped and enclosed in quotes, logical values will be converted to numeric, NULL will also be processed as necessary. As a result, the query takes the form:

"
INSERT INTO products
SET
	id = 12,
	price = 10000,
	name = 'Freezer'
"

If the label is not a scalar value, but an array, its elements will be escaped and inserted into the query separated by commas. View code

$sql = "
    SELECT * FROM products
    WHERE id IN (:ids)
       OR brand = :brand
    ";

$params = array(
        'brand' => 'Samsung',
        'ids' => array(
                5,
                115,
                220,
                'here accidentally inserted a string',
                NULL
            ),
    );

mysql_q($sql, $params);

In the end, will give a request

"
	SELECT * FROM products
    WHERE id IN (5,115,220,'here accidentally inserted a string',NULL)
       OR brand = 'Samsung'
"

Parameter substitution in the query is supported by all library functions (see below).

You can get the query text after the substitution by using the function mysql_substitute ($sql, $params); (this is usually useful when debugging).

Next, we will talk about the functions for processing queries that return the result-these are, first of all, SELECT queries, as well as some others. All these functions use mysql_q () to send queries.

The following table will be used in the examples:

mysql> SELECT * FROM products;

+----+-----------+-------+---------------------+
| id | name | price | created |
+----+-----------+-------+---------------------+
| 1 | Broom | 100 | 2010-10-09 22:24:14 |
| 2 | MOP | 500 | 2010-10-09 22:24:26 |
| 3 | Kettle | 1500 | 2010-10-09 22:24:37 |
| 4 | Scoop | 150 | 2010-10-09 22:24:51 |
| 5 | TV | 5000 | 2010-10-09 22:24:59 |
| 6 | Bucket | 150 | 2010-10-09 22:25:18 |
+----+-----------+-------+---------------------+
6 rows in set (0.00 sec)

mysql_getcell() — returns a single cell

The mysql_getcell() function is used to get the results of queries that consist of one row and one column (such queries are called scalar). Results mysql_getcell() returns in a scalar variable:

echo mysql_getcell("SELECT 1"); // 1
echo mysql_getcell("SELECT NOW()"); // 2010-10-09 22:30:05
echo mysql_getcell("SELECT COUNT(*) FROM products"); // 6
echo mysql_getcell("SELECT name FROM some_table ORDER BY price LIMIT 1"); // TV

If an error occurs in the request, the message will indicate the place where mysql_getcell is called():

<?php
 echo mysql_getcell("SELECT NOW");
?>

MySQL error in file /usr/home/www/testuser/html/test.php at line 2 (function mysql_getcell):
Unknown column 'NOW' in 'field list'

SELECT NOW

You can pass parameters to the query as a second argument.

mysql_getrow() — retrieve a single row

Function mysql_getrow() is used to retrieve query results, which consist of one line. mysql_getrow () returns the result of the query as a one-dimensional associative array whose keys are column names and values are stored in the corresponding cells:

<?php
	$query = "SELECT * FROM products WHERE id = 5";
	$data = mysql_getrow($query);
	print_r($data);
?>
Array
(
    [id] => 5
    [name] => TV
    [price] => 5000
    [created] => 2010-10-09 22:24:59
)

Parameters to be inserted into the query are passed as a second argument.

mysql_getcolumn() — returns a single column

The mysql_getcolumn() function is used to get the results of queries that consist of several rows and one column. mysql_getcolumn () returns the result of the query as a one-dimensional array:

<?php
	$query = "SELECT name FROM products ORDER BY price DESC";
	$data = mysql_getcolumn($query);
	print_r($data);
?>
Array
(
    [0] = > TV
    [1] = > Kettle
    [2] = > MOP
    [3] = > Scoop
    [4] = > Bucket
    [5] = > Broom
)

You can write such queries:

<?php
	print_r(mysql_getcolumn("SHOW TABLES LIKE 'products'"));
?>
Array
(
    [0] => products
)

Sometimes it is necessary to have such an array, the keys of which contain the values of some unique field (column) of the corresponding records. With mysql_getcolumn () it is very easy to do this — you need to add this field to the query and specify TRUE as the second argument. The keys are the values of the field specified in the query first: you can write such queries:

<?php
	$query = "SELECT id, name FROM products ORDER BY price DESC";
	$data = mysql_getcolumn($query, TRUE);
	print_r($data);
?>
Array
(
    [5] => TV
    [3] => Kettle
    [2] => MOP
    [4] => Scoop
    [6] => Bucket
    [1] => Broom
)

Parameters for substitution are passed by the third argument.

mysql_gettable () - for table queries

A table query is a query whose result contains more than one row and more than one column. Function mysql_gettable() returns it in the form of a two-dimensional array:

<?php
	$query = "SELECT * FROM products ORDER BY price DESC LIMIT 3";
	$data = mysql_gettable($query);
	print_r($data);
?>
Array
(
    [0] => Array
        (
            [id] => 5
            [name] => TV
            [price] => 5000
            [created] => 2010-10-09 22:24:59
        )

    [1] => Array
        (
            [id] => 3
            [name] => Kettle
            [price] => 1500
            [created] => 2010-10-09 22:24:37
        )

    [2] => Array
        (
            [id] => 2
            [name] => MOP
            [price] => 500
            [created] => 2010-10-09 22:24:26
        )
)
<?php
	print_r(mysql_gettable("SHOW VARIABLES LIKE 'query_cache%'"));
?>
Array
(
    [0] => Array
        (
            [Variable_name] => query_cache_limit
            [Value] => 1048576
        )

    [1] => Array
        (
            [Variable_name] => query_cache_min_res_unit
            [Value] => 4096
        )

    [2] => Array
        (
            [Variable_name] => query_cache_size
            [Value] => 134217728
        )

    [3] => Array
        (
            [Variable_name] => query_cache_type
            [Value] => ON
        )

    [4] => Array
        (
            [Variable_name] => query_cache_wlock_invalidate
            [Value] => OFF
        )
)

It is possible to write the values of a unique field to the array keys. To do this, add the field to the query (if it is not already implicitly present as part of*) and specify the field name as the second argument: <?php $query = "SELECT * FROM products ORDER BY price DESC LIMIT 3"; // instead of * you could write id, name, price, created $data = mysql_gettable($query, 'id'); print_r($data); ?>

Array
(
    [5] => Array
        (
            [id] => 5
            [name] => TV
            [price] => 5000
            [created] => 2010-10-09 22:24:59
        )

    [3] => Array
        (
            [id] => 3
            [name] => Kettle
            [price] => 1500
            [created] => 2010-10-09 22:24:37
        )

    [2] => Array
        (
            [id] => 2
            [name] => MOP
            [price] => 500
            [created] => 2010-10-09 22:24:26
        )

)

Parameters for substitution are passed by the third argument.

mysql_write_row()

One of the most common tasks is to insert a new record into the table or update an existing one by a unique key with a preliminary screening of the transmitted data. This task allows to solve a function mysql_write_row().

Simple INSERT

To insert a record into a table as a function argument, you need a table name and an associative array of data to insert, in which the keys correspond to the column names. The function returns the ID of the inserted record (see mysql_insert_id).

<?php
// Допустим, есть пустая таблица вида
// CREATE TABLE goods (
//  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
//    name VARCHAR(255),
//    price DECIMAL(5),
//    description TEXT
// );

// I want to insert this line:

$data = array(
    "name" => "Kettle",
    "price" => 3000,
    "description" => "Good and expensive Kettle. ' - you do not need to escape the quotation mark"
);
   
echo mysql_write_row('goods', $data); // вернет 1
?>

Simple UPDATE

mysql_write_row () can also be used to update table entries. To do this, it needs to pass the value of a unique key as the third argument. The result of the function in this case is the number of changed records in the table (see mysql_affected_rows):

<?php
$data = array(
        "description" => "Best Kettle",
        "price" => 3500
    );

// The following two entries are identical:
echo mysql_write_row('goods', $data,  array('id' => 3) );  // returns 1 because one line has changed

// the default scalar value is considered to be related to the column 'id'
echo mysql_write_row('goods', $data, 3); // returns 0 because the data remains the same
?>

It is also possible to update by a condition on several columns — in this case the array with keys will contain several elements. In this case, the same column can appear in the SET part and in the WHERE part: code

$set_keys = array(
    'f1'=>11,
    'f2'=>22
);
$where_keys = array(
    'f2'=>2,
    'f3'=>3
);
mysql_write_row('tablename', $set_keys, $where_keys);

will give a request

UPDATE tablename SET f1=11, f2=22 WHERE f2=2 AND f3=3

INSERT ... ON DUPLICATE KEY UPDATE

When you insert records, you can enable the on DUPLICATE key UPDATE mode. To do this, the fourth argument must be passed 'DUPLICATE', and the second and third arguments must be composed as in the case of a regular UPDATE:

mysql_write_row('goods', $data, $unique_keys, 'DUPLICATE');

This code will result in a request

INSERT INTO goods SET
     -- here are fields from both $data and $unique_keys
    id = 3,
    description = 'Best Kettle',
    price = 3500
ON DUPLICATE KEY UPDATE
    -- here-only fields from $data
    description = 'BestKettle',
    price = 3500

There are situations when it is more convenient to pass all the necessary data in $data, and in $unique_keys-specify which keys of them belong to the unique fields:

<?php
$all_data = array(
	"id" => 3,
	"description" => "Best Kettle",
	"price" => 3500
);
$unique = array('id'); // will be excluded from the part ON DUPLICATE KEY UPDATE

mysql_write_row('goods', $data, $unique, 'DUPLICATE');
// Will result in the same query as in the previous example
?>

INSERT IGNORE and REPLACE

You can also insert records in the INSERT IGNORE and REPLACE modes — in this case, you must specify them as the fourth argument:

mysql_write_row('goods', $data, FALSE, 'IGNORE');
mysql_write_row('goods', $data, FALSE, 'REPLACE');

mysql_escape()

Function mysql_escape() screen variable, given its type:

  • the string will be escaped and enclosed in single quotes
  • the number will be left unchanged
  • instead of NULL, a NULL expression is inserted into the query string
  • in all other cases, the intval function is used()

This allows all variables to be escaped uniformly:

$id = 5;
$name = "O'Reilly";

$sql = "
	SELECT ...
	WHERE id = " . mysql_escape($id) . "
	  AND name = " . mysql_escape($name) . ";
	";
// The result is a query
// SELECT ...
// WHERE id = 5
//   AND name = 'O\'Reilly'

If power to the array, it will return a list of shielded elements, separated by commas. This is very useful when making queries with IN:

$values = array(10, NULL, "string", "D'artagnan");

$sql = "SELECT ... WHERE col IN (" . mysql_escape($values) . ")";
// Get the request
// SELECT ... WHERE col IN (10, NULL, 'string', 'D\'artagnan')

Support mysqli

Initially, the functions were written on the basis of the Original MySQL API, but they can work in compatibility mode with the "improved" module — mysqli. To achieve this is very simple: you need to have the $mysqli variable containing the corresponding object in the global scope by the time the functions are called:

$mysqli = mysqli_connect(...); / / you can use object syntax - it doesn't matter
// continue as usual
mysql_q("SELECT 1");

No other additional action is required.

Source: https://webew.ru/articles/3237.webew


(2 ratings, average: 4.5 out of 5)