CODEWITHSUNDEEP
phpmysqlsql
mvdlucas
mvdlucas

Reputation: 43

Relate 2 tables in database

I have 2 tables in a database. One is for product data, and the other is for the images of the products. I have to set up an slider in the index of my page, and there will go specific images (like products with 50% discount, or sth like that). I'll make a query to my product table in my database, to get the id of the product that are on sale

$query1="SELECT id FROM products WHERE price<'100'";
$query2=mysql_query($query1);
$idSales=mysql_fetch_array($query2);

So, now i have an array with the id's of the products that I'm interested in, and here comes the trouble. How can I deal with that array that mysql_fetch_array returned to me? How can I make the query to the images table ( in that table I have the id of the corresponding product, so I've to match one with the other)

Upvotes: 1

Views: 82

Answers (3)

Pavel Stepanets
Pavel Stepanets

Reputation: 158

Or you can use subrequest

SELECT * FROM images WHERE product_id IN (SELECT id FROM products WHERE price < '100')

Upvotes: 1

A J
A J

Reputation: 4024

You can join these two tables to get images of the products. Run this query

  SELECT i.`imageName`
  FROM products p INNER JOIN images i
  ON p.`id`=i.`idProduct`
  WHERE p.price<100

Upvotes: 1

thepiyush13
thepiyush13

Reputation: 1331

You can try running multiple inline queries.For performance considerations I would not suggest doing this but it can get the job done with less number of records:

 while($row = mysql_fetch_array($query2)){
    $id = $row['id'];
    //now create a new query 
    $img_query = "SELECT * FROM ... where id=".$id;
    //run query and process results
    $img_result = mysql_fetch_array($img_query)
        //work with image related results here
}

You can also create a sql array and run the query after the while loop is finished.

Upvotes: 1

Related Questions