Table JOin - Need Help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
rcastoro
Forum Newbie
Posts: 7
Joined: Thu Feb 14, 2008 7:20 pm

Table JOin - Need Help

Post by rcastoro »

Everah | Please use [code] or [code={lang}] tags when posting code in the forms (where {lang} is the lowercase name of the language you are highlighting as.

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:

Code: Select all

}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?

Everah | Please use [code] or [code={lang}] tags when posting code in the forms (where {lang} is the lowercase name of the language you are highlighting as.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Table JOin - Need Help

Post by Benjamin »

Try running the query from a terminal or phpMyAdmin to make sure it works. Please use the code tags when posting code.
rcastoro
Forum Newbie
Posts: 7
Joined: Thu Feb 14, 2008 7:20 pm

Re: Table JOin - Need Help

Post by rcastoro »

Uhh thats the thing, i havn't coded for like months and am having trouble remembering how to get this to work. I need this done for work.

Since I forgot to just make the two tables associated by linking the image id to a listings.image_id row, now I just have a images table with the Listings ID. Not sure how to query that into the fetch array and match it up with the listing that it's associated with.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Table JOin - Need Help

Post by Benjamin »

Try this, if it doesn't work echo mysql_error();

Code: Select all

 
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
 
rcastoro
Forum Newbie
Posts: 7
Joined: Thu Feb 14, 2008 7:20 pm

Re: Table JOin - Need Help

Post by rcastoro »

ok now im getting no error, and like 1/3 of the listings are showing up. The other 2/3 are mmissing? And I do not see 1 "Image found" just no image found. Something isnt right?

Please ask questions, do you all understand my setup and what im trying to accomplish?

Thanks for the help, that did get rid of the error code, however, didn't work correctly.
rcastoro
Forum Newbie
Posts: 7
Joined: Thu Feb 14, 2008 7:20 pm

Re: Table JOin - Need Help

Post by rcastoro »

Thanks Guys. This new 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.
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Re: Table JOin - Need Help

Post by liljester »

Code: Select all

SELECT L.*, (SELECT COUNT(*) FROM Images WHERE listing_id = L.id ) AS images
FROM Listings L
check to see if images > 0 in php,if it is, query out the image url, or just put in your "Picture Found".
rcastoro
Forum Newbie
Posts: 7
Joined: Thu Feb 14, 2008 7:20 pm

Re: Table JOin - Need Help

Post by rcastoro »

I dont understand that /basically it's finding all listings with that join script - that have images. Then it repeats the listing for every image that listing is connected too. This is bad, also, it doesnt show listings that DONT have images. Why?
rcastoro
Forum Newbie
Posts: 7
Joined: Thu Feb 14, 2008 7:20 pm

Re: Table JOin - Need Help

Post by 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. The new additions are in red. 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?

Code: Select all

//////////// 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") {
            
            [color=#FF0000]$listing_id = $row[listings.id];
            
                $query1 = "SELECT * FROM images WHERE listing_id='$listing_id' LIMIT 1";
                $result1 = mysql_query($query1);
                $image_answer = $result1;[/color]
    
        $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";
[color=#FF0000]     echo "      <td>$result1</td>\n";[/color]
        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>&nbsp;&nbsp;<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>&nbsp;&nbsp;<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>&nbsp;&nbsp;<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;
        }
        
    }
}
rcastoro
Forum Newbie
Posts: 7
Joined: Thu Feb 14, 2008 7:20 pm

Re: Table JOin - Need Help

Post by rcastoro »

A good ole' if $ mysql_num_rows $result == 0 echo none else echo true worked for me, Thanks for your help everybody. this was a simple task I should have got this a long time ago.
Post Reply