Retrieving values from 2 db tables and selecting the checkboxes.

Posted on 2003-03-12
Medium Priority
Last Modified: 2009-07-29
I currently have a problem and I've been working on it the whole day but I'm still getting no where.
The situation is...

I have 2 db tables: Tyres and Customers

|     Brand     | tyreId |
| Yokohama   |   10     |
| GoodYear    |   11     |
| Michelin      |   12     |

| Name | custId | tyreId |
| John     |    J1     |  10     |
| John     |    J1     |  11     |
| Tom     |    T2     |  10     |

I'm displaying the Tyres table using checkboxes on a page.
Then, for a customer, say John, he can have more than 1 tyre order.
How do I select 2 checkboxes out of the 3?
I've tried using nested while loop but it seems that it only loop through once and selects one checkbox and that's it.
This was what I did:

$query2 = "SELECT * FROM Customer where custID='J1'";
$result2 = mysql_query($query2) or die( mysql_error() );
while ($r = mysql_fetch_array($result2)){
          $tyreID[ ] = $r["tyreID"];

echo "<tr>";
     echo "<td>";
        echo "Tyres:";
     echo "</td>";
     echo "<td>";
     $query = "SELECT * FROM Tyres";
     $result = mysql_query($query) or die( mysql_error() );
     while ($r = mysql_fetch_array($result)){
          $Brand = $r["Brand"];
          $tyreId = $r["tyreId"];

          if (is_array($tyreID)){
               while(list($key, $ID) = each($tyreID)){

                    echo "<tr>";
                         echo "<td>";
                         echo "</td>";
                         echo "<td>";
                              if ($tyreId == $ID){
                                   echo "<input type=checkbox name='custOrder' Value=$typeId selected> $Brand";
                                   echo "<input type=checkbox name='custOrder' Value=$typeId> $Brand";
                         echo "</td>";
                    echo "</tr>";
     echo "</td>";
echo "</tr>";

I'm not sure if this logic is the right one to use.
If you have better suggestions, feel free to comment.
I need help as soon as possible. Thanks!
Question by:G-Force
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8118472
try replacing the nested while with this:

/* code */
foreach($tireID as $ID){
/* end code */

just this one line, nothing else
LVL 11

Accepted Solution

jausions earned 90 total points
ID: 8118488

You need to do a SQL OUTER JOIN statement:


    $sSQLQuery = 'SELECT t.tyreId As tyreId, Brand, custID FROM tyres As t LEFT OUTER JOIN Customer USING (tyreId) where custID=\'J1\'';
    $iResult = mysql_query($sSQLQuery) or die( mysql_error() );

    echo "<tr>\r\n";
    echo "<td>Tyres:</td>\r\n";

    $iLabel = 0;

    while ($aRow = mysql_fetch_array($result)){
         $Brand = $aRow['Brand'];
         $tyreId = $aRow['tyreId'];
         $sSelected = ($aRow['custID']) ? ' selected="selected"' : '';
         echo '<td><input type="checkbox" name="custOrder[]" Value="' . $typeId . '"' . $sSelected . ' id="custOrder_' . $iLabel . '"> <label for="custOrder_' . $iLabel++ . '">' . htmlspecialchars($Brand) . "</label></td>\r\n";

    echo "</tr>\r\n";

Author Comment

ID: 8125359
Oh, thanks to you both, but I've found a way to solve this problem here.
jausions, I'll give you the 30 points 'cause I see that you do put in effort.
Anyway, thanks!

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question