CODEWITHSUNDEEP
phpmysql
user3282696
user3282696

Reputation: 3

how to use Arraylist in sql query?

I want product details from product table. so i get productid from tbl_product_color and now i want product's all detail which are stored in tbl_product table from product_id and in array $data product_id is stored but my below code is not working : one more problem is here that everytime this page refresh color_id =0 is stored in tbl_user_color which i really dont want to store and even there there is no record with color_id =0 in tbl_color. 

    $query="select * from tbl_user_color where color_session_id='".$sid."'";
    //echo $query;

    $resultset=mysql_query($query);
    $query="SELECT DISTINCT (product_id) FROM tbl_product_color";

    $cnt=0;

    while($row=mysql_fetch_assoc($resultset))
    {
        extract($row);
        if($cnt==0)
        {
            $query=$query." where color_id=".$color_id."";
        }
        else
        {
            $query=$query." or color_id=".$color_id."";
        }
        $cnt++;
            //  echo $color_id."<br>";
    }

     //echo $query;

    $resultset=mysql_query($query);

    echo "<div id='productdiv'>";
    $data = array();
    while($row=mysql_fetch_assoc($resultset))
    {

        $data[]=$row;
    }
    echo "<pre>";
    print_r($data); /* here i get an array with product_id list */
    echo "</pre>";
    echo "</div>";
    echo "<br>";
    echo "<br>";

    //$idStr = implode(',', $data); 
    /* here i want product details using $data array */
    $sql = "SELECT * FROM tbl_product WHERE product_id =".$data;
    $res=mysql_query($query);
    while($r=mysql_fetch_assoc($res))
    {
            extract($r);
        echo "   ".$product_name;
    }

    ?>

Upvotes: 0

Views: 875

Answers (1)

Rikesh
Rikesh

Reputation: 26421

Use IN clause,

"SELECT * FROM tbl_product WHERE product_id IN (" . implode(",",$data). ")";

Note: 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.

Upvotes: 1

Related Questions