Question 301838 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How can I parse through an array in PHP and treat each element as a string?

I am working on a project where I need to turn an array of strings into an SQL query. In order to do so, I am attempting to parse through the array and place each element in a string to be sent to the SQL database. However, when I try to do so, I encounter an error, and looking online I’ve read that it is not possible in the way I am trying to accomplish this.

Here is the section of PHP causing the issue:

$wherestring = "(";
    foreach ($wherearray as $curcat) {
        $wherestring = "(mcat1 = " . $curcat . " OR mcat2 = ". $curcat . " OR mcat3 = ". $curcat . " OR mcat4 = ". $curcat . " OR mcat5 = " . $curcat . ")";
        if ($curcat != $lastelement) {
            $wherestring = " OR ";
        }
    }
    $wherestring = ");";

… where $wherearray is the array of strings $wherestring is the string to eventually be used in the SQL query, and $lastelement is the last element of the array.

The following error occurs:

PHP Notice: Array to string conversion in … on line 48 (repeats 4 more times)

My goal would be to create the following string, given $wherearray is (1 => ‘Summer’):

'((mcat1 = "Summer" OR mcat2 = "Summer" OR mcat3 = "Summer" OR mcat4 = "Summer" OR mcat5 = "Summer"));'
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Adding implode to each instance of $curcat fixed the errors. Also, as stated in the comments, the string wasn't actually being added to because of incorrect syntax.

    $wherestring = "(";
    foreach ($wherearray as $curcat) {
        $wherestring .= "(mcat1 = " . implode('',$curcat) . " OR mcat2 = ". implode('',$curcat) . " OR mcat3 = ". implode('',$curcat) . " OR mcat4 = ". implode('',$curcat) . " OR mcat5 = " . implode('',$curcat) . ")";
        if ($curcat != $lastelement) {
            $wherestring .= " OR ";
        }
    }
    $wherestring .= ");";

  2. 0

    The idea would be to end up with something like

    "? IN (mcat1, mcat2, mcat3, mcat4, mcat5)"

    where ? will be a query parameter. Let’s do it with PDO:

    $items = [];
$params = [];
foreach ($wherearray as $curcat) {
    $items[]="(? IN (mcat1, mcat2, mcat3, mcat4, mcat5)");
    $params[]=$curcat;
}
$wherestring = "(" . implode(" OR ", $items) . ")";

    Now, let’s suppose that you append your $wherestring to your query and you will end up having a $query string. Let’s run it:

    $sth = $dbh->prepare($query);
$sth->execute($params);
$result = $sth->fetchAll();

    In the code above I have passed $params to execute, which are the values corresponding to the parameters I have embedded into your where clause. If you have other params in other clauses, then they also need to be added and the parameters need to be put in the correct order.

    Alternatively you can embed named parameters into PDO queries, like :foobar, however, that did not seem to be a neat option in your scenario because you have unknown amount of similar params to be added, so it would not be intuitive to name them. But it’s doable that way as well.

    If $curcat is an array for whatever reason, you will need to convert it into a string in order to concatenate it as a substring to a string. But you will need to be very careful while you study what $curcat is, so you convert it into the right string and possibly change your algorithm as well if it turns out to be needed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search