CODEWITHSUNDEEP
phpmysqlsql
user3184462
user3184462

Reputation: 51

SQL queries within while loops

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

Upvotes: 0

Views: 265

Answers (3)

Michael
Michael

Reputation: 12806

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();
}

Upvotes: 4

Bryan
Bryan

Reputation: 3494

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.

Upvotes: 0

Sean
Sean

Reputation: 2418

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.

Upvotes: 1

Related Questions