[SOLVED] PHP MYSQL Select Remainder of Database

[njdirt]

Hi, I'm trying to finish a bit of code to select and order products by brand, and then by model.  The brands that it lists are viariable, and therefore the first section of the big "while" statement works with parts of an array, which may have to iterate 2, 5, or a thousand times.  After all of the listed brands have been pulled, I want to pull every other brand in the db that wasn't listed.  An "Other" category.  Here is the code I have thus far.  Please le me know if this is unclear.  Thanks!

 

The wildcard '%' I used returns an error in the 'miscellaneous' section. 

 

<?php  //check the models carried, create tables for them and one for miscellaneous bikes
                    $query = "SELECT makes_carried FROM $table1 WHERE id='1'";
                    $result = mysql_db_query ($dbname, $query, $link);
                    $row = mysql_fetch_array($result);
                    //handle the string, create array
                    $make_array = explode(",", $row[makes_carried]);
                    //count how many makes are listed
                    $list_number = count($make_array);
                    
                    $make_iteration = "0";
                    while ($make_iteration <= $list_number){
                      echo "<tr><Td align=\"left\" colspan=\"5\"><b>$make_array[$make_iteration]</b></td></tr>";
                      //get models per make and list by model
                      $query2 = "SELECT * FROM $table9 WHERE make='$make_array[$make_iteration]' ORDER BY model asc";
                      $result2 = mysql_db_query ($dbname, $query2, $link);
                      while ($row2 = mysql_fetch_array($result2)){
                      echo "<tr><td> </td><td>$row2[model]</td><td>$row2[frame_size]</td><td>$row2[color]</td><td></td>";
                      }
                      $make_iteration++;
                    }
                    //print miscellaneous
                    echo "<tr><Td align=\"left\" colspan=\"5\"><b>Miscellaneous</b></td></tr>";
                      //get models per make and list by model
                      $query2 = "SELECT * FROM $table9 WHERE make NOT LIKE '$make_array[%]' ORDER BY model asc";
                      $result2 = mysql_db_query ($dbname, $query2, $link);
                      while ($row2 = mysql_fetch_array($result2)){
                      echo "<tr><td>$row2[make]</td><td>$row2[model]</td><td>$row2[frame_size]</td><td>$row2[color]</td><td></td>";
                    }


                 ?>

 

-Mark

[Barand]

Assuming makes_carried column looks like "1,4,7,9" try

<?php
$query = "SELECT makes_carried FROM $table1 WHERE id='1'";
$res = mysql_query($query);
//
// change makes to look like " 1','4','7','9 " in case they are string values
//
$makes_carried = str_replace(",", "','", mysql_result ($res, 0));

$query2 = "SELECT * FROM $table9 WHERE make IN ('$makes_carried') ORDER BY model";
while ($row2 = mysql_fetch_array($result2)){
    echo "<tr><td> </td><td>{$row2['model']}</td><td>{$row2['frame_size']}</td><td>{$row2['color']}</td><td></tr>";
}

echo "<tr><Td align=\"left\" colspan=\"5\"><b>Miscellaneous</b></td></tr>";

$query2 = "SELECT * FROM $table9 WHERE make NOT IN ('$makes_carried') ORDER BY model";
while ($row2 = mysql_fetch_array($result2)){
    echo "<tr><td> </td><td>{$row2['model']}</td><td>{$row2['frame_size']}</td><td>{$row2['color']}</td><td></tr>";
}

?>

[njdirt]

Hi, thanks for the quick reply.    'makes carried' looks like Trek,Gary Fisher, Klein.  $make_array breaks this string into an array, with $make_array[0] = Trek.  Then $list_number finds out how many names are in the array, so that it can run the loop one time for each name.  After these have all run, I want the final Misc. loop to retrieve all of the entries that do not have brand names found in $make_array[]. 

 

I feel like if I could find the proper way to say "SELECT * WHERE make != $make_array[%]" then it would work, since the make in the db will be GT, Schwinn, or anything else besides the ones listed as $make_array[0,1,2...].  As I said, the sql wildcard % is not working to do this.  Must it be in PHP since $make_array[] is a php call to an array?  * as a wildcard doesn't work either.

 

I hope this as clarified, and thanks again!

 

-Mark

[Barand]

    'makes carried' looks like Trek,Gary Fisher, Klein. 

 

The queries I posted should work with string makes also. That's why I added the 's

[njdirt]

Thanks very much!  I tweaked a few tiny things, but you're right, the  code works as I asked.  Here is the final code...

 

<?php  //check the models carried, create tables for them and one for miscellaneous bikes
                    $query = "SELECT makes_carried FROM $table1 WHERE id='1'";
                    $result = mysql_db_query ($dbname, $query, $link);
                    $res = mysql_db_query ($dbname, $query, $link);
                    //
                    // change makes to look like " 1','4','7','9 " in case they are string values
                    //
                    $makes_carried = str_replace(",", "','", mysql_result ($res, 0));
                    
                    $row = mysql_fetch_array($result);
                    //handle the string, create array
                    $make_array = explode(",", $row[makes_carried]);
                    //count how many makes are listed
                    $list_number = count($make_array);
                    
                    $make_iteration = "0";
                    while ($make_iteration <= $list_number){
                      echo "<tr><Td colspan=\"5\"><h2>$make_array[$make_iteration]</h2></td></tr>";
                      //get models per make and list by model
                      $query2 = "SELECT * FROM $table9 WHERE make IN ('$make_array[$make_iteration]') ORDER BY model";
                      $result2 = mysql_db_query ($dbname, $query2, $link);
                      while ($row2 = mysql_fetch_array($result2)){
                      echo "<tr><td> </td><td>{$row2['model']}</td><td>{$row2['frame_size']}</td><td>{$row2['color']}</td><td></tr>";
                      }
                      $make_iteration++;
                    }
                    //print miscellaneous
                    echo "<tr><Td colspan=\"5\"><h2>Miscellaneous</h2></td></tr>";
                      //get models per make and list by model
                      $query2 = "SELECT * FROM $table9 WHERE make NOT IN ('$makes_carried') ORDER BY model";
                      $result2 = mysql_db_query ($dbname, $query2, $link);
                      while ($row2 = mysql_fetch_array($result2)){
                      echo "<tr><td> </td><td>{$row2['model']}</td><td>{$row2['frame_size']}</td><td>{$row2['color']}</td><td></tr>";
                    }


                 ?>

 

Thanks again!