Page 1 of 1

[SOLVED] Nested query problem, help!!! Beginner to PHP

Posted: Tue Sep 28, 2004 4:26 pm
by nicky2k
feyd | Please use

Code: Select all

tags when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


I have a table called products and another called images. Images has a product id, but not all products have images. What I want to do is display all products with a thumbnail for thoser who have images and no thumbnail for products without an image. Sounds simple enough. So I started the following code:-

Code: Select all

<?php
//ob_start();
session_start();

// User not logged in, redirect to login page
if (!$_SESSION["userid"])
{

Header("Location: login.php");
ob_end_clean();
die(1);
exit;
}
	
// dBase file
include "dbConfig.php";

$query = "select * from products"; 

$results = array();
$queryres = mysql_query($query);

if (!$queryres) 
{
  echo("<p>Error performing query: " . mysql_error() . "</p>"); 
  exit(); 
 }

while($row = mysql_fetch_array($queryres))
{
  $results[] = $row["name"].",".$row["order_code"].",".$row["price"];
  $id=$row["productid"];
}

$num_elements=count($results);

print "<html><head><title> A test</title></head><body>";
print "<table width='100%' height='100%' align='center' border='1' style='border:1px;'>";
print "<tR><tD>Thumbnail</tD><td>Item</td></tR>";


for($c=0; $c < $num_elements; $c++)
{
//print $results[$c]."<br>";

$queryimg = mysql_query("SELECT image_name FROM generic_images WHERE productid=". $id);

    while($row2=mysql_fetch_array($queryimg))
    {
          if($row2["productid"]=$id)
         {
            $thumbnail=$row2["image_name"];
            //echo "Thumbnail is: " .$thumbnail;
         }
       
         print "<tr><td><img src='../images/'".$thumbnail." height='50' width='50' border='0'></td>";
       print "<td>".$results[$c]."<td></tr>";
         }
}

print  "</table></body></html>";

?>

The problem is that I either get all the products, but only the first image. Can anybody help?


feyd | Please use

Code: Select all

tags when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

Posted: Tue Sep 28, 2004 4:48 pm
by Christopher
How about:

Code: Select all

$result = $dn->query('SELECT * FROM products LEFT JOIN generic_images ON product.id=generic_images.productid');

while ($row = $result->fetchRow() ) {
    if ($row["image_name"]) {
// show image
    } else {
// show generic image
    }
}

Posted: Wed Sep 29, 2004 3:04 am
by nicky2k
I've done that, now I get the error: "Fatal error: Call to a member function on a non-object in /home/elephant/public_html/development/admin/test.php on line 24"

Line 24 is:

Code: Select all

<?php
result = $dn->query('SELECT * FROM products LEFT JOIN generic_images ON product.id=generic_images.productid'
?>
Am I to assume $dn is my database name?

Posted: Wed Sep 29, 2004 3:05 am
by mudkicker
$dn-> query should be mysql_query

Posted: Wed Sep 29, 2004 3:39 am
by nicky2k
Ok, I get it now. So while trying to be clever, I then did:-

Code: Select all

<?php
$result = mysql_query("SELECT * FROM products LEFT JOIN generic_images ON product.id=generic_images.productid");

while ($row = mysql_fetch_array($result)) 
{    
      if ($row["image_name"]) 
      {
	  // show image    
       print "<tr><td><img src='../images/'".$row["image_name"]."' height='50' width='50' border='0'></td><td>".$row["name"]."</tD><td>".$row["order_code"]."</td><td>".$row["price"]."</td></tr>";
      } 

     else 
      {
	  // show nothing
       print "<tr><td>&nbsp;</td><td>".$row["name"]."</tD><td>".$row["order_code"]."</td><td>".$row["price"]."</td></tr>";
      }
}


?>
Now it does not like mysql_fetch_array. Eh???!!! What was that

Code: Select all

<?php
$result -> fetchrow() 
?>
function doing?

Posted: Wed Sep 29, 2004 4:22 am
by nicky2k
Found out what it was, the join should be products.productid=generic_images.productid.

Thanks for your help.