mysql – Array to string conversion in… SQLquery and PHP

mysql – Array to string conversion in… SQLquery and PHP

Important! See bottom of answer for optimal solution!

As I mentioned in my comment, you will have to loop through your array to retrieve the different indexes that youd like to use for your search query. You cant simply chunk the array into a search string and hope to get results, which is why youre getting the errors that youre getting.

In order to loop through your array, we must first know the length of the array.
You can use the PHP function count(); to achieve this.

Like so,

//$a being the name of your array
$length=count($a);

The way Ill go about doing this, is by constructing an SQL with a where clause. I will then create another SQL, which I will concatenate on the previous SQL, where I will then loop through the array to get each index with an OR statement to get every result where there is a match.

Full example:

<?php
$a[0] = 1;
$a[1] = 2;
$a[2] = 3;
$a[3] = 4;

$length=count($a);
$i=0;

$sql=SELECT * FROM table_name WHERE ;

while($length > $i)
{    
    $sql .=name=$a[$i] ;
    if($i+1 < $length)
    {
        $sql .= OR ;
    }

    $i=$i+1;
}
echo $sql;   
?>

Outputs:

SELECT * FROM table_name WHERE name=1 OR name=2 OR name=3 OR name=4

Hope this was of any use.

There are also other ways of looping through an array, such as foreach where you also have the option to use keys.


Optimal answer:

After having done some research, Ive come to a conclusion, that the most optimal query you can do in the situation where you have hundreds of instances, is to use the WHERE IN() in SQL.

In order to achieve this, wed need to perform an implode(); which is a PHP function that will take your array, and transform it into a string. The way that works, is that you declare a separator in your implode(); and that will become the joint connection of your array indexes in the string. As an example:

implode(, , $a);

This will break up your array, and make the following string: 1, 2, 3, 4.

In other words, we transform your array into a string that matches the syntax of the IN() function in SQL.

Your query would then look like this:

$sqlQuery = SELECT * FROM table WHERE name IN( . implode( , , $a ) . );

The opposite of implode(); would be explode(); , where you take a string and turn it into an array, with the use of a separator. Same approach as with the implode(); function.

mysql – Array to string conversion in… SQLquery and PHP

Leave a Reply

Your email address will not be published. Required fields are marked *