Creating a MySql query with IN statement with php

Question

I'm trying to create a PHP file for my Android application that performs a SELECT query from my database.

I need to use an IN statement such as UserID IN (3,5,9). I send as parameters the list in a format of () or (1,2,3...).

I try to avoid having something like UserID IN () so I added Ifs to determine if the list is empty.

I have two lists.

I check if the lists are empty and according to the result I chose how to write the SELECT query. This is my PHP file:

<?php
mysql_connect(/*It's GOOD*/);
mysql_select_db(/*It's GOOD*/);

$StrName = $_GET['StrName'];
$lstMl = $_GET['lstMl'];
$lstCat = $_GET['lstCat'];
$strCheck = "()";

echo $lstMl;
echo $lstCat;

if(strcmp($strCheck, $lstMl) == 0)
{

    if(strcmp($strCheck, $lstCat) == 0)
    {
        $sql=mysql_query("SELECT    DISTINCT Store.StoreID, Store.Name, Store.Description
        FROM    Store INNER JOIN
        StoreCategory ON Store.StoreID = StoreCategory.StoreID
        WHERE   (Store.Name LIKE '%$StrName%')");
    }
    else
    {
        $sql=mysql_query("SELECT    DISTINCT Store.StoreID, Store.Name, Store.Description
        FROM    Store INNER JOIN
        StoreCategory ON Store.StoreID = StoreCategory.StoreID
        WHERE   (Store.Name LIKE '%$StrName%') OR (StoreCategory.CategoryID IN '$lstCat')");
    }
}
else
{
    if(strcmp($strCheck, $lstCat) == 0)
    {
        $sql=mysql_query("SELECT    DISTINCT Store.StoreID, Store.Name, Store.Description
        FROM    Store INNER JOIN
        StoreCategory ON Store.StoreID = StoreCategory.StoreID
        WHERE   (Store.Name LIKE '%$StrName%') OR (Store.MallID IN '$lstMl')");
    }
    else
    {
        $sql=mysql_query("SELECT    DISTINCT Store.StoreID, Store.Name, Store.Description
        FROM    Store INNER JOIN
        StoreCategory ON Store.StoreID = StoreCategory.StoreID
        WHERE   (Store.Name LIKE '%$StrName%') OR (StoreCategory.CategoryID IN '$lstCat') OR (Store.MallID IN '$lstMl')");
    }
}

while($row=mysql_fetch_assoc($sql))
    $output[]=$row;

print(json_encode($output));

mysql_close();

?>

The lists that are printed looks like this: (1) and (1,3,5)

And this is the error message I get when I try to write the url in the browser: "Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /home/u566430764/public_html/ibuy/query39.php on line 50 null"

My PHP file falls in the row I wrote while($row=mysql_fetch_assoc($sql)). I assume that I have a syntax error. What am i doing wrong? Thank you in advance!

I changed the parameters that are sent to the PHP file and now they look like this: "1,3,9", without the brackets. And in the SQL SELECT query I did: Store.MallID IN ('$lstMl'). And Now it works perfectly. Thank you all for your advice and help.

Do not use deprecated `mysql_*` API. Use `mysqli_` or `pdo` with prepared statements. — Jens, Mar 09 '15 at 07:22
above your `while` loop you can `echo $sql` to see what query currently you have. You can just copy the query and run it directly in your database. — Raheel, Mar 09 '15 at 07:25
Also turn on the php errors to see if there are any errors rather than assuming. http://stackoverflow.com/questions/845021/how-to-get-useful-error-messages-in-php — Raheel, Mar 09 '15 at 07:27

score -2 · Answer 1 · answered Mar 09 '15 at 07:30

-2

The parameters passed for IN statement in query should be inside brackets like below

$sql=mysql_query("SELECT DISTINCT Store.StoreID, Store.Name, Store.Description FROM Store INNER JOIN StoreCategory ON Store.StoreID = StoreCategory.StoreID WHERE (Store.Name LIKE '%$StrName%') OR (StoreCategory.CategoryID IN ('$lstCat')) OR (Store.MallID IN ('$lstMl'))");

answered Mar 09 '15 at 07:30

Sameer K

795
1
6
25

But my $lstMl parameter is already in a format of "(1,2,3)" with the brackets. – Itzick Binder Mar 09 '15 at 07:46
Can you please print the query and post it here ?? – Sameer K Mar 09 '15 at 08:00
the error message appears to refer to some other line of the PHP code – Strawberry Mar 09 '15 at 08:04

Creating a MySql query with IN statement with php

1 Answers1