PHP - Using Multiple Tables In One Query

Hey,

I have a query running inner joins... which currently works, but I need some more information pulled from other tables...

Code: [Select]
define('WPSC_TABLE_PRODUCT_LIST', "{$wp_table_prefix}wpsc_product_list");
define('WPSC_TABLE_PRODUCTMETA', "{$wp_table_prefix}wpsc_productmeta");
define('WPSC_TABLE_CATREF', "{$wp_table_prefix}wpsc_item_category_assoc");
define('WPSC_TABLE_CATNAME', "{$wp_table_prefix}wpsc_product_categories");


$cf="Linked Products";
$sku = get_post_meta($post->ID, $cf, true);
$array = explode(",",$sku);

$products = array_count_values($array);

foreach($products as $key => $value) {
$product = $wpdb->get_row("SELECT meta.product_id AS pid, list.name AS name, list.price AS price, retailer.category_id AS catid FROM ".WPSC_TABLE_PRODUCTMETA." AS meta INNER JOIN ".WPSC_TABLE_PRODUCT_LIST." AS list ON ( list.id = meta.product_id ) INNER JOIN ".WPSC_TABLE_SHOPNAME." AS retailer ON ( retailer.product_id = meta.product_id ) WHERE `meta_key` IN ( 'sku' ) AND `meta_value` IN ( '{$key}' ) ORDER BY list.id DESC");
?>
But I also need the category name - So I have to cross reference the following...

wordpdem_wpsc_product_list

id      name
433   itemone
432   itemtwo
431   itemthree

wordpdem_wpsc_item_category_assoc

id   product_id    category_id
1    433                    1
2    432                    2
3    410                    3

wordpdem_wpsc_product_categories

id  name
1   Bread
2   Fish
3   Snacks

I've now written:
Code: [Select]
SELECT
meta.product_id AS pid,
list.name AS name,
list.price AS price,
retailer.category_id AS catid,
category.name AS catname

FROM wordpdem_wpsc_productmeta AS meta
INNER JOIN wordpdem_wpsc_product_list AS list ON ( list.id = meta.product_id )
INNER JOIN wordpdem_wpsc_item_category_assoc AS retailer ON ( retailer.product_id = meta.product_id )
INNER JOIN `wordpdem_wpsc_product_categories``wordpdem_wpsc_product_list` AS category ON ( retailer.category_id = category.id )
WHERE `meta_key` IN ( 'sku' ) AND `meta_value` IN ( '{$key}' ) ORDER BY list.id DESC
However it fails on line 12 (the Where statement...)

Any ideas what I've done wrong?

Hi,

i have a database with multiple records. One of those values is an image. When i query the database and i want only the images as a result it gives the result in a field. It then fills up the field horizontal and when there is no more field left it starts a new row. That is what i want.

But now i want some extra info next to the picture so i put that in a table with two colums like this:

echo '<table border="1" width="400"><td><a href="'.$row['website'].'"> <img src="'.$row['prod_img'].'">
               '.$row['prod_name'].' <a href="'.$row['website'].'">'.$row['website'].'</td></table>
            ';}}

But i still want the result to fill up the field and when the field is full i want the next results on a row below this....

Anyone?

Hi guys, I'm trying to query two tables for different data and echo the results that match both tables. Here are the tables I have and the query I'm trying to run.
(Table)
qc_reports

(Fields)
id
report_date
report_lot_number    
report_po
report_supplier    
report_buyer    
report_inspectedby    
report_pulptemprange    
report_carrierconditions
report_supplierclaim    
report_carrierclaim    
report_temprecorder    
report_temprange_N    
report_temprange_M    
report_temprange_B    
report_suppliercontact
report_contactedby    
report_time    
report_comments

(Table)
qc_lots

(Fields)
id
report_id
lot_temprange    
lot_commodity    
lot_rpcs    
lot_brand    
lot_terms    
lot_cases    
lot_orgn    
lot_estnum
lot_avgnum

This is the query that I'm trying to do.

Code: [Select]
<?php
$sql = "SELECT * FROM qc_reports, qc_lots WHERE  ";
if (!empty($start_date) and !empty($end_date)) $sql .= " qc_reports.report_date BETWEEN '$start_date' and '$end_date' AND ";
if (!empty($search_fronteralot)) $sql .= " qc_reports.report_lot_number = '$search_fronteralot' AND ";
if (!empty($search_buyer)) $sql .= " qc_reports.report_buyer = '$search_buyer' AND ";
if (!empty($search_supplier)) $sql .= " qc_reports.report_supplier = '$search_supplier' AND ";
if (!empty($search_po)) $sql .= " qc_reports.report_po = '$search_po' AND ";
if (!empty($search_carrierconditions) and $search_carrierconditions != 'all') $sql .= " qc_reports.report_carrierconditions = '$search_carrierconditions' AND ";
 if (!empty($search_commodity) and $search_commodity != 'all') $sql .= " qc_lots.lot_commodity = '$search_commodity' AND ";
if (!empty($search_inspectedby)) $sql .= " qc_reports.report_inspectedby = '$search_inspectedby' AND ";

$sql = substr($sql, 0, -4);

$query = mysql_query($sql);


$numrows = mysql_num_rows($query);
?>
RESULTS - <?php echo $numrows; ?>
<hr>
<table width='500'><tr><td><b>Date</b></td><td><b>Lot Number</b></td><td><b>PO</b></td><td>&nbsp;</td></tr>
<tr>
<td>&nbsp;</td>
</tr>
<?php
while ($row = mysql_fetch_assoc($query))
{
$id = stripslashes($row['id']);
$report_lot_number = stripslashes($row['report_lot_number']);
$report_po = stripslashes($row['report_po']);
$report_date = stripslashes($row['report_date']);

echo "<tr>
<td>" . $report_date . "</td></td><td>" . $report_lot_number . "</td><td>" . $report_po . "</td><td><a href='view_report.php?id=" . $id . "'>View</a></td>
</tr>";
}
echo '</table><br><br><br><hr><br><br>';

}

?>
All the variables are passed from a HTML form with $_POST. I need the search to work like this: If there is a value in a form field then the query gets appended with that value but when it gets to the $search_commodity it needs to search the second table (qc_lots) and check for the results. Any results that match have to be matched to the results from the first table (qc_reports) and display (echo) only qc_reports that match to both tables. The only common field is the report_id on the qc_lots table and the id on the qc_reports table.

I'm stuck and need some guidance. Can someone help please?

I have a table that contains the schools in my system:

schools
id
name
location
...

Then, I have three tables that use the id of this table:

schoolAdmins
schoolID

schoolContests
schoolID

students
schoolID

When I go to delete a school from my system, I want to check to see if that school is connected to any of these three other tables first.  This is what I tried (but obviously failed because I'm here) where I'm passing the query the $studentID in question:

SELECT * FROM schoolAdmins, schoolContests, students WHERE (schoolAdmins.schoolID = $schoolID) OR (schoolContests.schoolID = $schoolID) OR (students.schoolID = $schoolID)

I'm really new to the concept of querying multiple tables in a single statement, so I'm just kind of guessing at this point.  Thanks in advance.

Below is a page which is supposed to output the name, blog contribution and picture of contributing members of a website.
<div id="blog_content" class="" style="height:90%; width:97%; border:5px solid #c0c0c0; background-color: #FFFFFF;"> <!--opens blog content--> 



                             
<?php


//address error handling

ini_set ('display_errors', 1);
error_reporting (E_ALL & ~E_NOTICE);



//include the config file

require_once("config.php");



//Define the query. Select all rows from firstname column in members table, title column in blogs table,and entry column in blogs table, sorting in ascneding order by the title entry, knowing that the id column in mebers table is the same as the id column in blogs table.


$sql = "SELECT


    blogs.title,blogs.entry,members.firstname,images.image
FROM
    blogs
LEFT JOIN
    members
ON
    blogs.member_id = members.member_id
LEFT JOIN
    images
ON
    blogs.member_id = images.member_id

ORDER BY
     blogs.title ASC

"; 

$query = mysql_query($sql);

                 if($query !== false && mysql_num_rows($query) > 0)

                 {

         while(($row = mysql_fetch_assoc($query)) !== false)

         {

 echo

                
                              '<div id="blog_content1" style="float:left; position:relative;bottom:18px;left:13px; background-color: #FFFFFF; height:16.7%; width:100%; border:0px none none;"                              
                              <!--opens blog_content1  same as main center top 1 and 2 from index page everything scaled down by a factor of 3, heightwise--> 
        
 
                                   <div class="red_bar" style="height:3%; width:100%; border:1px solid #959595;"> <!--a--> 
 
                                       <div class="shade1" style="height:5px; width:100%; border:0px none none;"> </div>                                      
                                       <div class="shade2" style="height:5px; width:100%; border:0px none none"> </div> 
                                       <div class="shade3" style="height:5px%; width:100%; border:0px none none"> </div>

 
                                   </div> <!-- closes red bar--> 
 
 
 
                                   <div class="content" style="height:28.3%; width:100%; border:0px none none;"> <!----> 
 
                                       
                                           <div class="slideshow" id="keylin" style="float:left; width:20%;  border:0px none none;"> <!--a-->
 
                                                  
                                                  
 
                                                 <div><img header("Content-type: image/jpeg"); name="" alt="" id="" height="105" width="105" src="$row[image]" /></div>                              
 
 
                                       
                                           </div> <!-- closes pic--> 
 
 
 
                                           <div class="content_text"  style="float:right;  position:relative;top:7px;left:0px; max-height:150px; width:78.5%; border-width:4.5px; border-bottom-style:solid; border-right-style:solid; border-color:#c0c0c0; "> <!--a-->'; 
 
                                           

                                               
                                                echo "<h3>".$row['title']."</h3>";

echo "<p>" .$row['entry']."<br />".$row['firstname']."</p>";

         
                                             
                                                                                           
                                           echo                                         
                                           '</div> <!-- closes content text--> 
 
  
                                   </div> <!-- closes content--> 
 
                                   
                                                                                                                                                                                                                            
                            </div> <!-- closes blog_content1-->';



                                                 }

                 }

                 else if($query == false)

                 {

               

 echo "<p>Query was not successful because:<strong>".mysql_error()."</strong></p>";

         echo "<p>The query being run was \"".$sql."\"</p>";

                 }

                 else if($query !== false && mysql_num_rows($query) == 0)

                 {

         echo "<p>The query returned 0 results.</p>";

                 }


                                                 mysql_close(); //Close the database connection.





                                                 ?> 

                      
                        </div> <!-- closes blog content-->


The select query is designed to retrieve all the blog contributions(represented by the fields blogs.title and blogs.entry) from the database, alongside the contributing member (member.firstname) and the member's picture(images.image), using the member_id column to join the 3 tables involved, and outputs them on the webpage.   The title, entry and firstname values are successfully displayed on the resulting page.  However, I can't seem to figure out how to get the picture to be displayed.  Note that the picture was successfully stored in the database and I was able to view it on a separate page using a simple select query.  It is now just a question of how to get it to display on this particularly crowded page.  Anyone knows how I can output the picture in the img tag?  I tried placing the header("Content-type: image/jpeg"); statement at the top of the php segment, then just right below the select query and finally just right above the img tag, but in every case, I just got a big white blank page starring at me.  How and where should I place the header statement? And what else am I to do to get this picture displayed?  Any help is appreciated.