Techniques of working with MySQL on PHP

Example of getting the id of the next added record and autoincrement field

Attention! In a multi-user system, you risk getting an id, which will be used before you.

function nextId($tablename){
if($row=mysql_fetch_assoc(mysql_query("SHOW TABLE STATUS LIKE '$tablename'")))
    return intval($row['Auto_increment']);
else
    return 0;
}

$id=nextId('myTable');
echo $id;

To get the most recent entry, use mysql_insert_id () .

An example of setting the Russian language for tables and for the current locale

mysql_query("SET NAMES cp1251");
//mysql_query("set names utf8");

function _USER_SetLocaleRus()
{
 $arrLocales = array('ru_RU.CP1251', 'ru_RU.cp1251', 'ru_RU', 'RU');
 foreach ($arrLocales as $strLocaleName)
 {setlocale(LC_ALL, $strLocaleName);
  if (strtolower("qwertyёЁАБГДЯQWERTYZ") == "qwertyёёабгдяqwertyz") break;
 }
}

select with group by day

SELECT COUNT (`id`), DATE_FORMAT (` time`, '% Y% m% d% H') as dat FROM `tbl` GROUP BY dat ORDER BY dat DESC

Copying a table (including data)

CREATE TABLE NewTab SELECT * FROM OldTab;

Copying a table (only a structure, without data)

CREATE TABLE NewTab LIKE OldTab;

Adding a record from one table to another (insert & select)

INSERT INTO table1 (column1, column2, someInt, someVarChar)
SELECT table2.column1, table2.column2, 8, 'some string etc.'
FROM table2
WHERE table2.ID = 7;

Filling the missing data in table2, from table1 (update & select)

update table2 t2, table1 t1
set t2.pole1 = t1.pole1, t2.pole2 = t1.pole2
where t1.name = t2.name;
select * from table2;

Copying all tables from one MySQL database to another

If the shell is available to you, use the following command:

mysqldump -q -uLOGIN -hlocalhost -p PASSWORD BASE1 | mysql -uLOGIN2 -hlocalhost -pPAROL2 BASE2

If you want to copy all tables from one MySQL database to another using PhpMyAdmin or similar service shells, use the built-in copy commands, for this you will need a user that allows you to work with both databases.

The following MySql command is used for table copying:

CREATE TABLE db2.table LIKE db1.table;
INSERT INTO db2.table SELECT * FROM db1.table;

If you want to copy all tables from one MySQL database to another in PHP, use the following example:

define("UserName","");    
define("Password","");    
define("HostName","localhost");       

define("DBName","db2");        
define("DBNameFrom","db1");    

$link = mysqli_connect(HostName, UserName, Password);

$res = mysqli_query($link, $query);
$r = mysqli_query($link, "SHOW TABLES");
while($row = mysqli_fetch_row($r))
{
    $tbl = $row[0];
    mysqli_query($link, "CREATE TABLE ".db2.".".$tbl." LIKE ".db1.".".$tbl);
    mysqli_query($link, "INSERT INTO ".db2.".".$tbl." SELECT * FROM ".db1.".".$tbl);
}

Optimization of tables.

Optimization is necessary when many changes have been made to the table: either most of the data has been deleted, or many changes have been made with variable-length strings - text, varchar, blob. The matter is that deleted records continue to be supported in the index file, and when you insert new records, the positions of the old entries are used. To defragment a data file, use the OPTIMIZE command.

OPTIMIZE TABLE `table1`,` table2` ...

Do not forget that during the optimization run, access to the table is blocked.

Rebuild data in a table.

After frequent changes in the table, this command can improve the performance of working with data. It rebuilds them in a table and sorts them by a specific field.

ALTER TABLE `table1` ORDER BY` id`

Write and read bit fields in the table.

For those who do not remember what a binary system is. Left binary system, right - decimal.

00001 = 1 - the first flag
00010 = 2 - second
00100 = 4 - the third
01000 = 8 - the fourth
10000 = 16 - the fifth
and so on
01011 = 1 + 2 + 8 = 11 - first, second and fourth

Mysql allows you to execute bitwise operations directly in the query, a maximum of 64 "check marks" for BIGINT. As a result, we get the following query, where n is the number obtained as a result of packing the values ​​of the checkboxes, which we put using the bitwise AND operation on the data field (assuming that the packed data is stored in the variable field):

SELECT * FROM `dataTable` WHERE` variable` & n = n

or

SELECT * FROM `dataTable` WHERE` variable` & n! = N

If the search is strict, then in the end, we need records that, after executing bitwise operations on the services field, the result will be equal to our number. If the search is not strict, then the query will be:

SELECT * FROM `dataTable` WHERE` variable` & n> 0

In this case, we need records with the result of this operation being greater than zero.

To set a single bit in the field, you can use the MySQL bit operations:

set the rightmost bit to 1:
update `dataTable` set` variable` = `variable` | 1

set the rightmost bit to 0
update `dataTable` set` variable` = `variable` & ~ 1

This might look like the html code with a choice:

<input type="checkbox" name="variable[]" value="1">
<input type="checkbox" name="variable[]" value="2">
<input type="checkbox" name="variable[]" value="3">
<input type="checkbox" name="variable[]" value="4">

To package all received checkboxes in one field, use the following PHP code:

$n=10; 

$resultValue=0;
for($i=0;$i<$n;++$i){
    if(!empty($_POST['variable'][$i])){
        $resultValue|=$_POST['variable'][$i]<<$i;
    }
}

To install a bit in PHP, you can use it:

$number = pow(2, $num % 8); 

To check the installed bit in PHP

$number = (bool)strlen(trim($needle & $findIn, "\0"));

Examples of working with bit fields on php are given here.

update `dataTable` set `variable`="1" 
update `dataTable` set `variable`=1 

If you are using a MySQL field of type BIT, then you must step on the raker: when writing to this field, you can not enclose the number in quotes:

An example of using in one MySQL query two GROUP_CONCAT.

Rake without the use of internal sorting.

SELECT
    receiver_id,
    GROUP_CONCAT(template_id ORDER BY id DESC SEPARATOR ',') as tpl,
    GROUP_CONCAT(id ORDER BY id DESC SEPARATOR ',') as id
    FROM notifications
    WHERE `template_id` in ( 52, 51 )
    GROUP BY  receiver_id

What the join chooses will be clearly shown in the following picture

Search for records falling within a five-minute interval

we search in the database for the last calls from the current number, and if there are more than X in Y minutes - we reset the call.

SELECT count(`calldate`) FROM `cdr` WHERE (`src`='79123123123')
    AND (`calldate` BETWEEN NOW() - INTERVAL 5 MINUTE AND NOW())

The interval is specified directly in the request. The request will select the number of calls from the current number in the last 5 minutes and display them in the result field in one line.