select data from table where column like array

Question

Trying to get an array from the database and use this array to execute a query like: " select from table where column like that array" .

after a search in many old questions, can't get a solution.

<?php
$mysqli = new mysqli("localhost", "hhtt", "htt", "htt");

/* Vérification de la connexion */
if ($mysqli->connect_errno) {
    printf("Echec de la connexion : %s\n", $mysqli->connect_error);
    exit();
}

$query = "SELECT site FROM user_info";
$result = $mysqli->query($query);

/* Tableau numérique */
while($row = $result->fetch_array())
{
$rows[] = $row;
}

$string = implode(' OR arrondissement LIKE ', $rows);


$query2 = "SELECT path FROM photos WHERE arrondissement LIKE {$string}";

$myArray = array();

$result2 = $mysqli->query($query2);

while($row2 = $result2->fetch_array(MYSQLI_ASSOC)) {
    $myArray[] = $row2;
}
echo json_encode($myArray);

/*Fermeture de la connexion */
$mysqli->close();


?>

get json data from table using array in mysql query.

It is a very bad idea to use `die($mysqli->connect_error);` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Oct 28 '19 at 17:16
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Oct 28 '19 at 17:16

ScaisEdge · Accepted Answer · 2019-10-28T15:32:29.237

1

Looking to your code You could use a single query avoiding implode and the related select based on array

    select  path FROM photos p
    INNER JOIN  user_info u ON  u.site = p.arrondissement

or if you really need like

    select  path FROM photos p
    INNER JOIN  user_info u ON  p.arrondissement like concat('%', u.site, '%')

an dif you need others where condition yu jst add the where clause after the join

    select  path FROM photos p
    INNER JOIN  user_info u ON  p.arrondissement like concat('%', u.site, '%')
    where p.is_clean = 0

or extend the JOIN clause

    select  path FROM photos p
    INNER JOIN  user_info u ON  p.arrondissement like concat('%', u.site, '%')
       AND  p.is_clean = 0

edited Oct 28 '19 at 15:32

answered Oct 28 '19 at 11:56

ScaisEdge

131,976
10
91
107

thank you soo much it works well but when i add where it doesnt work select path FROM photos p where is_clean = 0 INNER JOIN user_info u ON u.site = p.arrondissement – Oualid Oukassou Oct 28 '19 at 15:30
1

use distinct you need not double records ... select distinct path from ... – ScaisEdge Oct 28 '19 at 16:19

select data from table where column like array

1 Answers1