Stopping empty results being "displayed" in dropdo

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Wardy7
Forum Commoner
Posts: 38
Joined: Wed Aug 24, 2005 4:45 am
Location: UK

Stopping empty results being "displayed" in dropdo

Post 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]
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
Wardy7
Forum Commoner
Posts: 38
Joined: Wed Aug 24, 2005 4:45 am
Location: UK

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
iknownothing
Forum Contributor
Posts: 337
Joined: Sun Dec 17, 2006 11:53 pm
Location: Sunshine Coast, Australia

Post 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.
Wardy7
Forum Commoner
Posts: 38
Joined: Wed Aug 24, 2005 4:45 am
Location: UK

Post by Wardy7 »

Thanks guys, mucho appreciated. Working a treat :)

Cheers
Wardy
Post Reply