Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
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 create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

581 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