This question has already been answered:

Hello. There is a prod table, in it is a cell id_specz , in which the value is 3,2,1

I sample the database with the following query:

$vib = mysql_query("SELECT * FROM `prod` WHERE `id_specz` IN (".$numbers.") ORDER BY `id` DESC");

If the variable $ numbers = 3 - record finds. And if the variable = 2 or 1 - the record does not find. Another thing to keep in mind is that the $ numbers variable can have values ​​of 2.3 or 1.3, and so on.

What am I doing wrong? PS The fact that MySQL is outdated - I know, the project is ancient, I make changes.

I would be very grateful for the help.

Reported as a duplicate by members Mike , Ipatiev , D-side , Akina , user194374 9 Feb '17 at 6:20 .

A similar question was asked earlier and an answer has already been received. If the answers provided are not exhaustive, please ask a new question .

  • because in a cell there should not be values ​​of the form 3,2,1 - Ipatyev
  • @ Ipatiev but they are - iKey
  • MySQL is not outdated at all; the functions mysql_* are outdated. You have problems with the types of cells in the table. - ilyaplot
  • @ D-side yes ...... -
  • @fens mysql_fetch_assoc () expects parameter 1, be resource, boolean given - iKey

1 answer 1

Here is the solution. I hope.

 <?php require_once 'ФАЙЛ_ПОДКЛЮЧЕНИЯ_К_БД.php'; //подключение к БД //$connection - это переменная с подключением. У вас, возможно, другая. //Получаем и упорядочиваем данные из БД $query = "SELECT prod.id, prod.id_specz FROM prod"; $result = $connection->query($query); if (!$result) {die($connection->error);}; $new_arr=[]; //массив с уже отстортированными данными while ($myrow = $result->fetch_array(MYSQLI_ASSOC)) { $id_specz=$myrow['id_specz']; if (iconv_strlen($id_specz)>1) { $d=explode(',', $id_specz); //режем строку по запятой sort($d); //спортируем знаечения по возрастанию $id_specz=implode(',', $d); //соединяем в строку } $new_arr[$myrow['id']]=$id_specz; }; unset($result); //удаляем ненужное //Теперь обновим БД. Используем транзакцию, чтобы гарантировать запись. //Тут я использую одну для всей работы. Но если база большая, то лучше разбить на несколько частей и использовать подготовленные выражения (погуглите) $connection->autocommit(false); foreach ($new_arr as $key => $value) { $query = "UPDATE prod SET id_specz = '$value' WHERE id = '$key' LIMIT 1"; $result = $connection->query($query); if (!$result) {die($connection->error);}; } if (!$connection->commit()) {die($connection->error);}; //Теперь у нас в БД всё упорядочено и приведено к единому виду. Можно с ними работать нормально. $want_to_find='1,2'; //ищем такую строку $query = "SELECT * FROM prod WHERE prod.id_specz = '$want_to_find'"; $result = $connection->query($query); if (!$result) {die($connection->error);}; while ($myrow = $result->fetch_array(MYSQLI_ASSOC)) { echo $myrow['id'].'<br>'; }; ?>

If you need to search for all the options (i.e. if you want to find all the records where there are 2 - for example, 1,2,3 or 1,2), then use

 $query = "SELECT * FROM prod WHERE prod.id_specz SOUNDS LIKE '$want_to_find'";

Then you can not lead to a single view. But better still lead.

  • What makes you think that there is an id in the table by id_specz? And why all these dances with a tambourine with a selection of all records and then updating, only to sort one value? - fens
  • in 10 minutes I’ll check it out :) thanks - iKey
  • @fens Always happy to look at the best solution. There should lead to a record of a single species. I brought. I will be glad to learn something new. - n.osennij
  • @Denis notice at the very bottom of the answer to SOUNDS LIKE - n.osennij
  • Pay attention to the fact that if the author of the question states that the correct solution does not work for him, then this does not mean that it is necessary to throw at full speed and invent curved crutches. sometimes you just need to knock the author on the head, until the right decision reaches him. - Ipatiev