Page 1 of 1

Stopping empty results being "displayed" in dropdo

Posted: Tue Aug 07, 2007 7:25 am
by Wardy7
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I am creating a drop down menu of car "models" using the results from a MYSQL database based on what car "make" someone has previously selected. However as some makes have lots of differnt models and some not many I am struggling to get it so that only the fields with an actual result in are displayed.

Currently as it is below, if there is just 2 "models" of car available it displays these in the drop down menu but also 8 more empty enteries under it which is not what I want.
Can anyone help sort what I need to add as I know it shoudl be relativly simple but can't actually do it myself

Code: Select all

<?php 
   ***database connection stuff***

    $limit          = 100;                 
    $query_count    = "SELECT count(*) FROM cars";      
    $result_count   = mysql_query($query_count);      
    $row_count      =mysql_fetch_row($result_count); 
    $totalrows      =$row_count[0]; 

    if(empty($page)){ 
        $page = 1; 
    } 
          

    $limitvalue = $page * $limit - ($limit); 
    $query  = "SELECT * FROM cars WHERE make='$make' LIMIT $limitvalue, $limit";          
    $result = mysql_query($query) or die("Error: " . mysql_error()); 

    if(mysql_num_rows($result) == 0){ 
        echo("No articles yet! Why not be the first to submit one?!"); 
    } 
    echo("<table>");
    echo('<form method="post" action="">'); 
    echo("<p>");
    echo('<select name="model">');
      
    while($row = mysql_fetch_array($result)){ 
        if ($bgcolor == "#FFFFFF"){ 
            $bgcolor = "#FFFFFF"; 
        }else{ 
            $bgcolor = "#FFFFFF"; 
     } 
    echo("<option>Select Model</option>");
    echo('<option value="' . $row["model1"] . '">' . $row["model1"] . '</option>');
    echo('<option value="' . $row["model2"] . '">' . $row["model2"] . '</option>');
    echo('<option value="' . $row["model3"] . '">' . $row["model3"] . '</option>');
    echo('<option value="' . $row["model4"] . '">' . $row["model4"] . '</option>');
    echo('<option value="' . $row["model5"] . '">' . $row["model5"] . '</option>');
    echo('<option value="' . $row["model6"] . '">' . $row["model6"] . '</option>');
    echo('<option value="' . $row["model7"] . '">' . $row["model7"] . '</option>');
    echo('<option value="' . $row["model8"] . '">' . $row["model8"] . '</option>');
    echo('<option value="' . $row["model9"] . '">' . $row["model9"] . '</option>');
    echo('<option value="' . $row["model10"] . '">' . $row["model10"] . '</option>');
    echo("</select>");
    } 

    echo("</table>");
?>
Cheers
Wardy


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Tue Aug 07, 2007 7:35 am
by VladSun
Your DB design is wrong. You should normalize your tables - i.e. instead of having 20 columns of models, you should create another table - you store the models' data in it.

Posted: Tue Aug 07, 2007 8:13 am
by Wardy7
Hi VladSun, thanks for the reply.
Is there no way of doing it how it is at themoment as otherwise I am going to have to create 70+ new tables?

Cheers
Wardy

Posted: Tue Aug 07, 2007 8:21 am
by VladSun
You don't have to create 70 tables, just 2.

E.g.:

Cars
_______
id int(11)
name char(30)

and

Models
_______
id int(11)
car_id int(11)
name char(30)

You have 1 car record and multiple modles records with car_id equal to the corresponding id in Cars table.

Posted: Tue Aug 07, 2007 8:54 am
by iknownothing
If you are confused to why he is saying this, its because your while statement should only need to loop a single line, eg:

Code: Select all

echo('<option value="' . $row["model"] . '">' . $row["model"] . '</option>');
and it will produce the same results, with alot less code. But if you absolutely have to:

Code: Select all

while($row = mysql_fetch_array($result)){ 
        if ($bgcolor == "#FFFFFF"){ 
            $bgcolor = "#FFFFFF"; 
        }else{ 
            $bgcolor = "#FFFFFF"; 
     } 
    echo("<option>Select Model</option>"); 
    if ($row["model1"]){ echo('<option value="' . $row["model1"] . '">' . $row["model1"] . '</option>') }; 
    if ($row["model2"]){ echo('<option value="' . $row["model2"] . '">' . $row["model2"] . '</option>') }; 
    if ($row["model3"]){ echo('<option value="' . $row["model3"] . '">' . $row["model3"] . '</option>') }; 
    if ($row["model4"]){ echo('<option value="' . $row["model4"] . '">' . $row["model4"] . '</option>') }; 
    if ($row["model5"]){ echo('<option value="' . $row["model5"] . '">' . $row["model5"] . '</option>') }; 
    if ($row["model6"]){ echo('<option value="' . $row["model6"] . '">' . $row["model6"] . '</option>') }; 
    if ($row["model7"]){ echo('<option value="' . $row["model7"] . '">' . $row["model7"] . '</option>') }; 
    if ($row["model8"]){ echo('<option value="' . $row["model8"] . '">' . $row["model8"] . '</option>') }; 
    if ($row["model9"]){ echo('<option value="' . $row["model9"] . '">' . $row["model9"] . '</option>') }; 
    if ($row["model10"]){ echo('<option value="' . $row["model10"] . '">' . $row["model10"] . '</option>') }; 
    echo("</select>"); 
    }
Messy, but it will work.

Posted: Wed Aug 08, 2007 4:28 am
by Wardy7
Thanks guys, mucho appreciated. Working a treat :)

Cheers
Wardy