php mysqli_multi_query not getting data

Question

I try results from db for autocomplete search. I use this code for getting names, in db I have a row with four columns that can contain the searched keyword.

function search_results($conn,$str){

$sql = "SELECT g_custom_1 as str FROM gallery WHERE  g_custom_1 LIKE '%{$str}%'";
$sql .= "SELECT g_custom_2 as str FROM gallery WHERE  g_custom_2 LIKE '%{$str}%'";
$sql .= "SELECT g_custom_3 as str FROM gallery WHERE  g_custom_3 LIKE '%{$str}%'";
$sql .= "SELECT g_custom_4 as str FROM gallery WHERE  g_custom_4 LIKE '%{$str}%'";


//$sub_data=array();
if (mysqli_multi_query($conn,$sql))
{
    $data=array();
    do
    {
        // Store first result set
        if ($result=mysqli_store_result($conn)) {
        // Fetch one and one row
        while ($row=mysqli_fetch_row($result))
        {
            $data[]=$row['str'];
        }
        // Free result set
        mysqli_free_result($result);
        }
    }
    while (mysqli_next_result($conn));
}
else{
    echo "error";
}
$data_unique=array_unique($data);
return $data_unique;
   }

You get a wrong query during on concat. You can get the same result with one SELECT query. — Taron Saribekyan, Jun 20 '16 at 06:45
error Warning: array_unique() expects parameter 1 to be array, null given in functions.php on line 41 — Angela Homenok, Jun 20 '16 at 06:48
Write `print_r(mysqli_multi_query($conn,$sql))` before `if` statement. Tell me what you got — Poonam, Jun 20 '16 at 07:25
It should print 1 then `if` will execute. Make sure you had mysql connection in `$conn` — Poonam, Jun 20 '16 at 08:42
in one function call you are creating $sql variable , with concatination of multiple qureis and the genrated query in $sql variable is wrong, please correct yur query first. you can match multiple columns using 'OR' opreator . — aniket ashtekar, Jun 20 '16 at 08:47

score 0 · Answer 1 · edited May 23 '17 at 12:01

You need to separate each query using a semicolon. Here is your current string:

$sql="SELECT g_custom_1 as str FROM gallery WHERE  g_custom_1 LIKE '%{$str}%'SELECT g_custom_2 as str FROM gallery WHERE  g_custom_2 LIKE '%{$str}%'SELECT g_custom_3 as str FROM gallery WHERE  g_custom_3 LIKE '%{$str}%'SELECT g_custom_4 as str FROM gallery WHERE  g_custom_4 LIKE '%{$str}%'";

Notice how the SELECT in the subsequent queries are smashed into the rear end of the previous query?

I might suggest:

$sql[]="SELECT g_custom_1 as str FROM gallery WHERE  g_custom_1 LIKE '%{$str}%'";
$sql[]="SELECT g_custom_2 as str FROM gallery WHERE  g_custom_2 LIKE '%{$str}%'";
$sql[]="SELECT g_custom_3 as str FROM gallery WHERE  g_custom_3 LIKE '%{$str}%'";
$sql[]="SELECT g_custom_4 as str FROM gallery WHERE  g_custom_4 LIKE '%{$str}%'";
if(mysqli_multi_query($conn,implode(';',$sql))){

You might also find this answer informative: Strict Standards: mysqli_next_result() error with mysqli_multi_query

php mysqli_multi_query not getting data

1 Answers1