How to Compare 2 MySQL tables with PHP (nested loops?)

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
ShaunaBear
Forum Newbie
Posts: 6
Joined: Mon Jun 30, 2008 1:30 pm

How to Compare 2 MySQL tables with PHP (nested loops?)

Post by ShaunaBear »

I have been working on the problems for several days. I am rusy with my programming skills and I am just learning PHP, so I need a little guidance!!

Here is my situation: I have two tables, Items and Specifications. One Item to one specification. Both have a field named "productcode". The Items table is the main table that contains fields for price, short description, catagory, website (display off or on the website), etc. I am trying to create an internal webpage to update the specifications table for the items (table) displayed on the website. I have created a search that searches the Items database then takes you to the results page. On the results page I am trying to compare the Items table to the Specifications table to see whether or not the productcode in Items exists in Specifications. If it DOES NOT exist then the link of the result takes you to an INSERT form via productcode. If productcode DOES exist then the link of the result takes you to an UPDATE form via productcode.

I have tried a LEFT JOIN and it is not what I need for this situation. I need to loop through all the productcode in the Items table and all the productcode in the Specifications table and output a variable or "true" or "false" whether or not the items.productcode == specifications.product code. ALL results need to be displayed, not just the ones that exist or don't exist in Specifications. Just not so sure how to implement!

Here is my code so far. (It does not work, only the first item in the specifications table is true, the other items show false ... I assume because it is not looping through the specifications table)

Code: Select all

 
<?PHP
$srch = "-1";
if (isset($_GET['fldproduct_code'])) {
  $srch = $_GET['fldproduct_code'];
  $srch="%".$srch."%"; 
}
 
mysql_select_db($database_shauna, $shauna);
$query_rsItems = "SELECT productcode, description FROM items WHERE ((website = 'ON') AND (productcode LIKE '$srch' OR description LIKE '$srch')) ORDER BY productcode";
$rsItems = mysql_query($query_rsItems, $shauna) or die(mysql_error());
$row_rsItems = mysql_fetch_assoc($rsItems);
$totalRows_rsItems = mysql_num_rows($rsItems);
 
mysql_select_db($database_shauna, $shauna);
$query_rsSpecs = "SELECT productcode, shortdescription FROM specifications ORDER BY productcode";
$rsSpecs = mysql_query($query_rsSpecs, $shauna) or die(mysql_error());
$row_rsSpecs = mysql_fetch_assoc($rsSpecs);
$totalRows_rsSpecs = mysql_num_rows($rsSpecs);
?>
 
<BODY>
    <p class="style1">Total Records Found:&nbsp;<?php echo $totalRows_rsItems ?> </p>
 
    <!-- START Show if recordset not empty  -->
    <?php if ($totalRows_rsItems > 0) { ?>
      <table width="98%" border="0" cellspacing="0" cellpadding="2">
        <!-- START display results -->
        <?php while (($row_rsItems = mysql_fetch_assoc($rsItems)) OR ($row_rsSpecs = mysql_fetch_assoc($rsSpecs))){ ?>  
          <!-- START check to see if productcode in Items matches productcode in Specifications -->
          <?php if (($row_rsItems['productcode']) == ($row_rsSpecs['productcode'])) { ?>         
          <!-- IF productcode matches, echo link with update=tue -->
          <tr>
            <td>                    
            <?php                
            echo '<a href="update_specs.php?recordID='.$row_rsItems['productcode'].'&update=true">'.$row_rsItems['description'].'</a>'; ?></td>
          </tr>
          <tr>
            <td class="style1">&nbsp;&nbsp;<?php echo $row_rsItems['productcode']; ?></td>
          </tr>
          <tr>
            <td><hr size="1" color="#999999" noshade="noshade" /></td>
          </tr>
          <?php
          }
            else { ?>
          <!-- IF product code does not match, echo link with update=false --> 
          <tr>
            <td>                    
            <?php        
            echo '<a href="update_specs.php?recordID='.$row_rsItems['productcode'].'&update=false">'.$row_rsItems['description'].'</a>'; ?></td>
          </tr>
          <tr>
            <td class="style1">&nbsp;&nbsp;<?php echo $row_rsItems['productcode']; ?></td>
          </tr>
          <tr>
            <td><hr size="1" color="#999999" noshade="noshade" /></td>
          </tr>  
          <?php } ?>
          <!-- END check to see if productcode in Items matches productcode in Specifications -->  
          <?php } ?>
          <!-- END Show results -->
      </table>
      <?php } ?>
      <!-- END Show if recordset not empty  -->
      <!-- START Show if recordset empty  -->
    <?php if ($totalRows_rsItems == 0) { ?>    
  <p>Go back and search again...</p>
  <?php } ?>
  <!-- END Show if recordset empty  -->
</BODY>
 
<?php
mysql_free_result($rsItems);
?>
 
Below is a picture of my results page. The highlighted one (update=true). The one furtherdown, (off-page) that is also in the specs page update=false.
Attachments
Search Results
Search Results
PHP_Resutls.jpg (133.23 KiB) Viewed 106 times
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: How to Compare 2 MySQL tables with PHP (nested loops?)

Post by jaoudestudios »

Does this help?

I have addressed a similar issue before...

http://www.forum.jaoudestudios.com/view ... 2f590b#p35
Post Reply