CODEWITHSUNDEEP
phpmysql
Marc Howard
Marc Howard

Reputation: 425

select data from two tables, return data and get product name from item id

<?php
$transactionOutput = "";
$sql = mysql_query("SELECT * FROM transactions WHERE emailaddress='$email'");
$productCount = mysql_num_rows($sql);
if($productCount > 0) {
while($row = mysql_fetch_array($sql)) {
    $item_id = $row["item_id"];
    $quantity = $row["quantity"];
    $size = $row["size"];
    $price = $row["price"]; 

    $sql = mysql_query("SELECT * FROM products WHERE id='$item_id'");
    $productCount = mysql_num_rows($sql);
    while($row = mysql_fetch_array($sql)) {
    $product_name = $row["product_name"];
    }

    $transactionOutput .= "<tr>";
    $transactionOutput .= "<td align='center'>" .$product_name. "</td>";
    $transactionOutput .= "<td align='center'>" .$quantity. "</td>";    
    $transactionOutput .= "<td align='center'>" .$size. "</td>";
    $transactionOutput .= "<td align='center'>" .$price. "</td>";
    $transactionOutput .= "</tr>";
}

    } else {
$transaction_list = "You have made no transactions yet";
}


?>

I'm trying to access data from two different tables and then return the product name of each item by matching the id in the product table with the item_id returned from the transactions table. This does output the correct information however it only shows the first transaction and no others, i know this is also probably horribly programmed too

Upvotes: 0

Views: 1042

Answers (2)

Abhay
Abhay

Reputation: 6645

The problem seems to be that you are using the same variable $sql to store the resultsets from both the queries. So what might be happening here is this:

  1. the transaction query runs and the resultset is stored in $sql
  2. first record is read from the transaction resultset
  3. for the first transaction record, the product query runs and the resultset is stored again in $sql
  4. the output is stored into $transactionOutput
  5. next record is read from the transaction resultset

AND the step 5 is the problem because the original transaction resultset - $sql - was overwritten by the product resultset.

Try using another variable for the product query:

$rsProduct = mysql_query("SELECT * FROM products WHERE id='$item_id'");
    $productCount = mysql_num_rows($rsProduct);
    while($row = mysql_fetch_array($rsProduct)) {
    $product_name = $row["product_name"];
}

Hope the above makes sense!

EDIT: as an additional suggestion, you may like to try using JOIN queries to retrieve both transaction and product in the same query. Here:

SELECT `t`.*, `p`.`product_name`
FROM `transactions` `t`
LEFT JOIN `products` `p` ON `t`.`item_id` = `p`.`id`
WHERE `t`.`emailaddress` = '$email';

Just loop the resultset and you are done!

Upvotes: 1

Roadmaster
Roadmaster

Reputation: 5357

<?php
$transactionOutput = "";
$sql = mysql_query("SELECT * FROM transactions WHERE emailaddress='$email'");
$productCount = mysql_num_rows($sql);
if($productCount > 0) {

while($row = mysql_fetch_array($sql)) {
    $item_id = $row["item_id"];
    $quantity = $row["quantity"];
    $size = $row["size"];
    $price = $row["price"]; 

    $sql = mysql_query("SELECT * FROM products WHERE id='$item_id'");
    $productCount = mysql_num_rows($sql);

   #I suggest you use a different variable (i.e. not $row) here;
    #at this point $row from the first while is still in scope
    #and clobbering it may be causing the problem you see with
    #only the first transaction showing.        
    while($row = mysql_fetch_array($sql)) {
     #If you only need the first product_name, then you don't need the loop, just
     #$row2=mysql_fetch_arrqy($sql);
     #$product_name = $row2["product_name"];

    $product_name = $row["product_name"];
    }

    $transactionOutput .= "<tr>";
    $transactionOutput .= "<td align='center'>" .$product_name. "</td>";
    $transactionOutput .= "<td align='center'>" .$quantity. "</td>";    
    $transactionOutput .= "<td align='center'>" .$size. "</td>";
    $transactionOutput .= "<td align='center'>" .$price. "</td>";
    $transactionOutput .= "</tr>";
}

    } else {
$transaction_list = "You have made no transactions yet";
}


?>

Upvotes: 0

Related Questions