SQL queries within while loops

Question

I have a series of mysql queries within while loop.. just like this.

 $sql =  mysql_query("SELECT productid FROM cart WHERE userid='$userid'")
 while($row = mysql_fetch_assoc($sql)){
 $pid = $row['product_id'];

 //Second one

 $sql2 =  mysql_query("SELECT barcode,quantity FROM products WHERE id='$pid'")`
 while($row2 = mysql_fetch_assoc($sql2)){
 $quantity = $row2['quantity'];
 $barcode = $row2['barcode'];

 //Third one

 $sql3 = mysql_query("SELECT name,price FROM inventory WHERE product_barcode=$barcode");
 while($row3 = mysql_fetch_assoc($sql3)){
 $name = $row3['name'];
 $price = $row3['price'];

 echo $name.'<BR />'.$price.'<BR />.$quantity';

   }
 }
}

You can see that these queries are dependent on one another so used the while loop to get all possible results. But I don't think this way of querying is a good practice. It takes much time..Does it? I dont know.. Can anyone please edit and show me a better way of querying and output these similar queries. Thanks

no $sql2 will return as many rows associated with $pid and $sql3 will as many associated with $barcode... If it's one then one or if it's more then more — user3184462
– user3184462, Commented Feb 12, 2014 at 16:21
So there could be more than one product with id = 2, for example? Is id not unique? And the same with product_barcode in the inventory table -- is it not unique? — Michael
– Michael, Commented Feb 12, 2014 at 16:22
id is unique.. the thing is that first we check how many products are in the cart. from there we are selecting all the product ids..from there all the barcodes associated with those ids. and from there the name and price etc.. id and barcode both are unique — user3184462
– user3184462, Commented Feb 12, 2014 at 16:24

Community · Accepted Answer · 2023-11-17 20:24:57Z

You can use a single query and join the tables together:

SELECT
  inventory.name,
  inventory.price,
  products.quantity
FROM
  cart
INNER JOIN
  products ON cart.product_id = products.id
INNER JOIN
  inventory ON products.barcode = inventory.product_barcode
WHERE
  cart.userid = 1

It is also worth looking into the mysqli extension and using prepared statements.

try
{

  $mysqli = new mysqli('hostname', 'username', 'password', 'database');

  if ($mysqli->connect_error)
  {
    throw new Exception($mysqli->connect_error);
  }

  if (!$stmt = $mysqli->prepare("

    SELECT
      inventory.name,
      inventory.price,
      products.quantity
    FROM
      cart
    INNER JOIN
      products ON cart.product_id = products.id
    INNER JOIN
      inventory ON products.barcode = inventory.product_barcode
    WHERE
      cart.userid = ?

  "))
  {
    throw new Exception($mysqli->error);
  }

  if (!$stmt->bind_param('i', $userid))
  {
    throw new Exception($stmt->error);
  }

  if (!$stmt->execute())
  {
    throw new Exception($stmt->error);
  }

  if (!$stmt->bind_result($name, $price, $quantity))
  {
    throw new Exception($stmt->error);
  }

  while ($result = $stmt->fetch())
  {
    echo $name . '<br>' . $price . '<br>' . $quantity;
  }

  if (FALSE === $result)
  {
    throw new Exception($stmt->error);
  }

}

catch (Exception $e)
{
  echo $e->getMessage();
}

I use prepared statement. just for saving some time wrote mysql_query here.. by the way will this query pull quantity from products table and name and price from inventory? Just exactly how the query I posted will work?
Each row returned will contain the inventory name (name), inventory price (price), and product quantity (quantity) from the database. The relationship can be written as "Give me the inventory name, price, and product quantity of products that this user has in their cart. Each row will represent a cart row assigned to the user ID given, but will display the price and quantity of the product, rather than the product id.
thanks a lot!! I have to research about joins .. I haven't use them... By the way can you tell me the execution time difference between these two? Just about 2 times or 5 times.. just like that...
Depends entirely on the structure of your table, the presence of indexes, the number of rows, etc.
@MichaelRushton has it right. Generally speaking, one query like this is a lot faster than looping through and running multiple queries. It does, however, depend on the scale, structure, and indexing of your DB.

Sean · Accepted Answer · 2014-02-12 16:28:18Z

When database tables have relationships between them, you can use table JOINs to effectively squish multiple queries into one. Table joins and relationships are fairly big subjects, so I won't go into too much depth.

Take your first two queries:

$sql =  mysql_query("SELECT productid FROM cart WHERE userid='$userid'")
$sql2 = mysql_query("SELECT barcode,quantity FROM products WHERE id='$pid'")`

The relationship between your two tables here is that your cart table holds a list of product IDs (productid) per user. Your products table holds a list of products (id), each with a product ID.

From this, you can say that one product may belong in many cart rows. This is known as a one-to-many relationship.

To say "Give me the quantity of products that are in this user's cart" can be expressed with the following SQL query:

SELECT 
    p.quantity 
FROM 
    products AS p
RIGHT JOIN 
    cart AS c
ON p.id = c.productid
WHERE c.userid = '$userid'

@MichaelRushton has given you a very good, workable answer, but you should research more into MySQL Joins and what types of joins there are.

They will end up saving you a lot of time, and a lot of processing power.

Bryan · Accepted Answer · 2014-02-12 16:30:40Z

You should think about using JOIN s instead of doing it this way.

 $sql = "SELECT `c`.`productid`, `p`.`barcode`,`p`.`quantity`,
               `i`.`name`, `i`.`price` 
         FROM `cart` c
         JOIN `products` p ON `p`.`id` = `c`.`product_id`
         JOIN `inventory` i ON `i`.`product_barcode` = `p`.`barcode`
         WHERE userid='$userid'";

$result =  mysql_query($sql);
$row = mysql_fetch_assoc($result);
echo $row['name'].'<br />'.$row['price'].'<br />'.$row['quantity'];

I Know MichaelRushton all ready supplied the answer but I was half way through with this when he posted his answer, and this is using your code, so here it is anyways.

Collectives™ on Stack Overflow

SQL queries within while loops

3 Answers 3

5 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Related