php and MYSQL SELECT issues with arrays

Question

I was wondering if you could tell me what is wrong with my code or point out where I am going wrong, as I am not able to display any results. $_POST['checkbox'] is an array.

<?
   $get_id=$_POST['checkbox']; 

    if(empty($get_id)) {
        echo("<h3>You didn't select anything.</h3>");
   } else {  
        $where[]  = sprintf(" id='%s'",$_POST["checkbox"]);
   }

   $where_str = " WHERE ".implode(" AND ",$where);
   $sql = "SELECT * FROM products $where_str";
   $result = mysql_query($sql, $link);

    echo "<table>";
    echo "<tr> <th>Description</th> </tr>";
        while($row = mysql_fetch_array($result)) {
            echo "<tr><td>";     
            echo $row['description'];   
            echo "</td></tr>"; 
        }
   echo "</table>"; 
?>

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial. — insertusernamehere
– insertusernamehere, Commented Feb 23, 2013 at 15:12
Did you echo your query to see what it looks like? It If $_POST['checkbox'] is an array, then you need to loop over it. Otherwise, you are getting id=Array AND id=Array... Also, seems like those should be OR instead of AND, in which case they should be in an IN () clause (after you sanitize against SQL injection) — Michael Berkowski
– Michael Berkowski, Commented Feb 23, 2013 at 15:14
Here are a whole bunch of examples of how to transfer that post array into an IN () clause. — Michael Berkowski
– Michael Berkowski, Commented Feb 23, 2013 at 15:16
Yes, I have done an echo, and id=Array. How would you create a loop to do this then? Thanks for your help. — aero_g
– aero_g, Commented Feb 23, 2013 at 15:18
See PHP Array to select mysql But first, you must sanitize against SQL injection. — Michael Berkowski
– Michael Berkowski, Commented Feb 23, 2013 at 15:20

Kermit · Accepted Answer · 2013-02-23 16:17:53Z

You should refrain from using short tags <? as they are not supported after PHP 5.4.
You are not connecting to MySQL ($link undefined)
You are using a deprecated API (mysql_). See comments for alternatives (mysqli_ or PDO)
You should use the REQUEST_METHOD index of $_SERVER to determine whether your script has been posted.

if( $_SERVER[REQUESTED_METHOD] == 'POST' && !empty($_POST['checkbox']) ) { ... }
You need to use error handling to check for errors. If you echo $sql; you would see that the checkboxes aren't being populated:

SELECT * FROM products WHERE id=''
Your script is vulnerable to SQL injection. When you switch to current API, use binded parameters.
Is $_POST[checkbox] an array?
sprintf will not work as you intend it to because you are passing the entire $_POST[checkbox] array to it. You would need to iterate through it to format it. (See Ollie's answer)

Example

Assuming your HTML looks like this:

<form method="post" ...>
<input type="checkbox" name="checkbox[]" value="1" />
<input type="checkbox" name="checkbox[]" value="2" />
<input type="checkbox" name="checkbox[]" value="3" />
<input type="submit" name="submit" />
</form>

And all three boxes are checked; it will produce this array:

Array
(
    [0] => 1
    [1] => 2
    [2] => 3
)

Following Collie's loop:

foreach ($_POST['checkbox'] as $checkbox) {
    $where[]  = sprintf(" id='%s'",$checkbox);
}

$where will look like:

Array
(
    [0] =>  id='1'
    [1] =>  id='2'
    [2] =>  id='3'
)

The rest of your script should work. However, you should look into using the IN operator.

That will enable you to skip the loop and just use implode:

$where = "'" . implode("', '", $_POST[checkbox]) . "'";

Which produces:

'1', '2', '3'

And combined with IN:

$sql = "SELECT ... FROM WHERE id IN ($where)";

Be aware that this is not sanitized and you're still vulnerable to injection.

Thanks for you comments. Apologies for not being clear, but link was defined in header file. $POST_[checkbox] is an array and I have now edited the code accordingly as per Ollie's suggestion, but am still not getting results.

Ollie · Accepted Answer · 2013-02-23 15:15:29Z

0

If $_POST["checkbox"] is an array like you say then you cannot use it as a string in the sprintf. Try using array_pop to return the last value of that array or similar.

You could foreach through each element in the array:

foreach ($_POST['checkbox'] as $checkbox) {
    $where[]  = sprintf(" id='%s'",$checkbox);
}

Although this will probably just create an invalid SQL statement if asking for ID to be equal to two different integers.

answered Feb 23, 2013 at 15:15

Ollie

19616 bronze badges

Collectives™ on Stack Overflow

php and MYSQL SELECT issues with arrays

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related