Need Help with Table Join

[rcastoro]

I stopped coding for like 5 months, and mostly forgot the advanced stuff. I made a Inventory search system for a company awhile back. I'm now trying to get each listing to show a camera icon if there is a image associated with that listing. So basically, There is the table the images url's are held, that are associated to the listings Called: Images, and the table that holds the listings Called: Listings. In the Images table, there is 3 rows, id, file (holds urls), and listing_id (holds the listings.id) I'm running a while(mysql_fetch_array) to create the list of inventory, so i figure if I just add the images rows to the query where the listings are queried, and just add to the area which displays each listing in text, if ($row[images.file] != "") { echo" Image found" }else{ echo "no image found" { but as soon as I try to add the images table to the Query it gives me a error on page, and lists no inventory: Heres the error:

 

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /data/9/1/25/146/1351472/user/1449089/htdocs/ileap/admin/main.php on line 500

 

The code in red is what I added, and made the page  display this error, it was working fine before this:

 

 

}else{

$query = "SELECT listings.id, listings.request, listings.date_submitted, listings.sold, listings.item, listings.manufacturer_id, listings.model_id, listings.category_id, listings.condition_id, listings.hours,

listings.cost, listings.price, listings.inprogress, listings.serial, listings.servicedate, manufacturers.manufacturer, models.model, categories.category, images.file, images.listing_id,

FROM listings

JOIN manufacturers ON listings.manufacturer_id = manufacturers.id

JOIN categories ON listings.category_id = categories.id

JOIN models ON listings.model_id = models.id

JOIN images ON listings.id = images.listing_id

ORDER BY manufacturers.manufacturer, models.model, categories.category ASC";

}

 

$result = mysql_query($query);

 

 

 

What am I doing wrong?

[chronister]

$result = mysql_query($query) or die(mysql_error());

 

Add that to get the mysql error. This should probably give you a much better idea why it isn't working.

 

 

Nate

[rameshfaj]

Can u provide us the table structures and what u actually wanted so that we can analyse the query u send.

[aschk]

The least you could do is show us line 500, but I suspect you're not getting a valid MySQL result because your query is wrong.

As ramesh suggested, show us your structure, column names and what you want (sample set) and we'll give you a helping hand.

[paul2463]

your problem is in the query...you first red entries, there is an extra comma after "images.listing_id" and right before the "FROM" command which will throw an error, you have not set the query up to catch the error so you will chase yourself until you are blue in the face....always get the system to throw an error so you can debug quickly - follow Chronisters advice

[redarrow]

try this mate

<?php
$query = "SELECT listings.id, listings.request, listings.date_submitted, listings.sold, listings.item, listings.manufacturer_id, listings.model_id, listings.category_id, listings.condition_id, listings.hours,
listings.cost, listings.price, listings.inprogress, listings.serial, listings.servicedate, manufacturers.manufacturer, models.model, categories.category, images.file, images.listing_id
FROM listings
JOIN manufacturers ON listings.manufacturer_id = manufacturers.id
JOIN categories ON listings.category_id = categories.id
JOIN models ON listings.model_id = models.id
JOIN images ON listings.id = images.listing_id
ORDER BY manufacturers.manufacturer, models.model, categories.category ASC";
}
$result = mysql_query($query)or die(mysql_error());
?>

[rcastoro]

Thanks Guys. Redarrow -This code works, I tried that last night and got that part - However, it's only now showing listings that have a image, (which is like 1/3 of the listings). and on top of that, it re-shows that listing for however many pictures are associated with that listing. So I have the same listing posted like 6 times in a row, then the next listing same thing...

 

How would I be able to list ALL inventory from Database listings table - but only show a icon or a  "Picture Found" next to the listings that actually have an image(s). and not repeat that listing?

 

 

Table Structure is aas follows:

 

Database: DBWORK

 

Tables (The Two Important Tables, in Question):

 

images

listings

 

Column Names in Images Table:

 

id (the images unique id)

file (which is a url)

thumbfile (which is also a url)

listing_id (the listings coorosponding ID, so in the listings table, this would be matched to the id column)

 

Column Names in Listings Table:

 

id  serial  item  hours  servicedate  cost  price  inprogress  notes  used  condition_id  class_id  category_id  manufacturer_id  model_id  date_submitted  sold  request  request_id

 

There is no linking column in the listings table, like there is in the images table. Images column, listing_id, matches to the listings ID column.

 

For Example, here is a Row in the Images Table:

id     file                                  thumbfile                                    listing_id 

20    uploads/100_1274.jpg      uploads/thumbs/100_1274.jpg     39

 

 

So the Listing in the listings table with a ID of 39 would be mathed to this pic

[rcastoro]

Well i tooka new approach, my rust is wearing off and I'm getting closer. Here is what I did: I removed the join query additions of images all together, and made a seperate query to find the image id that matches with the listing's id. I Nested it within the script I made to display every listing in the database, which works fine. This produces a Resource ID next to EVERY SINGLE listing, which doesnt make sence, since I thought this code would only produce a listing_id in image_answer for the listings WITH a image, which is only like 1/3 of the listings. Anyways, am I on the right path? Why can I not get the image_answer to display only the ID for the listings WITH IMAGES and blank for listings without, and why are they resource ID's instead of the listings_id like im trying to get to show up in image_answer variable?

The new additions to the code below are:

 

$listing_id = $row[listings.id];
            
                $query1 = "SELECT * FROM images WHERE listing_id='$listing_id' LIMIT 1";
                $result1 = mysql_query($query1);
                $image_answer = $result1;

and

 

        echo "      <td>$result1</td>\n";

 

 

//////////// If User is a Admin - Display Below Code With Actions and Administrative Features ///////////
if($_SESSION[uperms] == 2){
    while($row = mysql_fetch_array($result)){
        if ($row[sold] != "sold") {
            
            $listing_id = $row[listings.id];
            
                $query1 = "SELECT * FROM images WHERE listing_id='$listing_id' LIMIT 1";
                $result1 = mysql_query($query1);
                $image_answer = $result1;
    
        $category = get_category($row[category_id]);
        $manufacturer = get_manufacturer($row[manufacturer_id]);
        $model = get_model($row[model_id]);
        $condition = get_condition($row[condition_id]);?>
        <tr  onClick="HighLightTR('#fcfcfc','000000');" <?echo $code;?>>
        <?
        echo "      <td>$manufacturer</td>\n";
        echo "      <td>$category</td>\n";
        echo "      <td>$model</td>\n";
        echo "      <td><a href=\"listing.php?action=view&id=$row[id]\">$row[item]</a></td>\n";
        echo "      <td>$row[hours]</td>\n";
        echo "      <td>$condition</td>\n";
        echo "      <td>$$row[cost].00</td>\n";
            if ($row[request] == "Y") {
        echo "      <td><font color='red'><b>$$row[price].00</b></font></td>\n";        
            }else{
        echo "      <td>$$row[price].00</td>\n";        
            }
        echo "      <td>$row[date_submitted]</td>\n";
        echo "      <td>$row[serial]</td>\n";
            if ($row[inprogress] != "") {
        echo "      <td><font color='red'><b>$row[inprogress]</b></font></td>\n";
            }else{
        echo "      <td>No Sale Pending</td>\n";
            }
        echo "      <td>$row[servicedate]</td>\n";
        echo "      <td>$result1</td>\n";
       echo "      <td width=\"125\" align=\"center\">";
        echo "<a href=\"listing.php?action=images&id=$row[id]\"><img src=\"images/image_edit.png\" border=\"0\" alt=\"Manage This Listing's Images\" title=\"Manage This Listing's Images\"></a>  <a href=\"listing.php?action=edit&id=$row[id]\"><img src=\"images/page_white_edit.png\" border=\"0\" alt=\"Edit This Listing\" title=\"Edit This Listing\"></a>  <a href=\"Javascript&#058;deleteListing('$row[id]')\"><img src=\"images/page_white_delete.png\" border=\"0\" alt=\"Delete This Listing\" title=\"Delete This Listing\"></a>  <a href=\"listing.php?action=sold&id=$row[id]\"><img src=\"images/bell.png\" border=\"0\" alt=\"Mark As *Sold*\" title=\"Mark As *Sold*\"></a></td>\n";
        echo "  </tr>\n\n";
        
        }
        
        if($t == "0"){
            $code = "class=\"darkRow\" onMouseOver=\"this.className='highRow'\" onMouseOut=\"this.className='darkRow'\"";
            $t++;
        }else{
            $code = "class=\"lightRow\" onMouseOver=\"this.className='highRow'\" onMouseOut=\"this.className='lightRow'\"";
            $t = 0;
        }
        
    }
}

[Barand]

Since there isn't always an image record you need to use a LEFT JOIN

...
LEFT JOIN images ON listings.id = images.listing_id
...

 

Where there is no matching image record, values from the image table will contain NULL

[rcastoro]

A Simple mysql_num_rows after a individual query for the images table linking the id to the listings id worked. That was so simple, I just needed to warm back up. Thanks for all your helpp everyoine.