Help! Mysql results based on combo box.

amg182 · May 30, 2011

Hi Guys. Hopefully someone can help me with this...New to coding and pretty lost on this.

I have a Mysql database which is displaying results to my webpage with no problems. However I would like to be able to add a combo box to my webpage that would update the mysql database results based on the combo box selection. For example if Ford is chosen from the combo box, the webpage would refresh and show all the results for Ford in the webpage. Can someone please help me?

Here is the code I have at the moment that works just fine. But results of the database are based on the WHERE statement.

<?php

$con = mysql_connect("server","database","password");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db("a5525005_cars", $con);

$result = mysql_query("SELECT * FROM `cars` WHERE Makel='Ford'");

echo "<table class='ex1' border='0' width='113%' style=text-align:center; cellpadding='6' cellspacing='0'>

</tr>";

while($row = mysql_fetch_array($result))

{

echo "<tr style=font-family:verdana;font-size:80%;>";

echo "<td width=13%>" . $row[""] . "<img src=\"" . $row["Photo"] . "\"></a>";

echo '<td width="14%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Model'] . '</a></td>';

echo '<td width="5%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Year'] . '</a></td>';

echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Fuel'] . '</a></td>';

echo "</tr>";

}

echo "</table>";

mysql_close($con);

?>

QuickOldCar · May 30, 2011

You can get as creative as you want with this.

You may use various types of forms to get the users input, a text box, dropdown select, checkbox or w/e.

Look here for more explanation and tutorials.

http://www.tizag.com/phpT/postget.php

http://www.tutorialspoint.com/php/php_get_post.htm

http://www.w3schools.com/html/html_forms.asp

For your example lets call our value make and use a simple form to see different results.

<?php
$make = mysql_real_escape_string($_GET['make']);
?>

<form action="" method="get">
  Car Make: <input type="text" name="make" value="<php echo $make; ?>"/><br />
  <input type="submit" value="Get Make of Car" />
</form>

<?php
if(!isset($_GET['make']) || $_GET['make'] == "") {
echo "Insert the Car Make.";
} else {

$con = mysql_connect("server","database","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("a5525005_cars", $con);

$result = mysql_query("SELECT * FROM `cars` WHERE Makel='$make'");// added the variable in the query

echo "<table class='ex1' border='0' width='113%' style=text-align:center; cellpadding='6' cellspacing='0'>

</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr style=font-family:verdana;font-size:80%;>";

    echo "<td width=13%>" . $row[""] . "<img src=\"" . $row["Photo"] . "\"></a>";
    echo '<td width="14%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Model'] . '</a></td>';   
    echo '<td width="5%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Year'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Fuel'] . '</a></td>';
       
echo "</tr>";
  }
echo "</table>";
mysql_close($con);

}
?>

QuickOldCar · May 30, 2011

Since you asked combo box, I'll show that and also use POST

Just add more value options

<?php
$make = mysql_real_escape_string($_POST['make']);
?>

<form name="input" action="" method="post">
Car Make: <Select name="make">
<option "Input" value="<?php echo $make; ?>"><?php echo $make; ?></option>
<option value="ford">ford</option>
<option value="chevrolet">chevrolet</option>
<option value="honda">honda</option>
<option value="lexus">lexus</option>
</select>
<input type="submit" value="Get Car Make" />
</form>

<?php
if(!isset($_POST['make']) || $_POST['make'] == "") {
echo "Insert the Car Make.";
} else {

$con = mysql_connect("server","database","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("a5525005_cars", $con);

$result = mysql_query("SELECT * FROM `cars` WHERE Makel='$make'");// added the variable in the query

echo "<table class='ex1' border='0' width='113%' style=text-align:center; cellpadding='6' cellspacing='0'>

</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr style=font-family:verdana;font-size:80%;>";

    echo "<td width=13%>" . $row[""] . "<img src=\"" . $row["Photo"] . "\"></a>";
    echo '<td width="14%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Model'] . '</a></td>';   
    echo '<td width="5%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Year'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Fuel'] . '</a></td>';
       
echo "</tr>";
  }
echo "</table>";
mysql_close($con);

}
?>

amg182 · May 30, 2011

Guys, Thank you, thank you, Thank you!!!

Works a treat. Although, I am just wondering is it possible to display results based on 2 combobox results? For example Make=Lexus + Model=IS200 = Lexus IS200 results.

Again, thank you so much for your time. I really appreciate it!

QuickOldCar · May 30, 2011

Yet is very possible, you just add another field into the same submit form.

But unless use ajax for the form, the results are gonna be what the user selects, like as an example they could select ford as make and model as civic, and would get no results naturally.

<?php
$make = mysql_real_escape_string($_POST['make']);
$model = mysql_real_escape_string($_POST['model']);
?>

<form name="input" action="" method="post">
Car Make: <Select name="make">
<option "Input" value="<?php echo $make; ?>"><?php echo $make; ?></option>
<option value="ford">ford</option>
<option value="chevrolet">chevrolet</option>
<option value="honda">honda</option>
<option value="lexus">lexus</option>
</select>
Car Model: <Select name="model">
<option "Input" value="<?php echo $model; ?>"><?php echo $model; ?></option>
<option value="civic">civic</option>
<option value="corsica">corsica</option>
<option value="lumina">lumina</option>
<option value="delsol">delsol</option>
</select>
<input type="submit" value="Get The Results" />
</form>

<?php
if(!isset($_POST['submit']) || $_POST['make'] == "" || $_POST['model'] == "") {
echo "<h2>Please check both values.</h2>";
} else {

$con = mysql_connect("server","database","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("a5525005_cars", $con);

$result = mysql_query("SELECT * FROM `cars` WHERE Makel='$make' AND Model='$model'");// added the variables in the query

echo "<table class='ex1' border='0' width='113%' style=text-align:center; cellpadding='6' cellspacing='0'>

</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr style=font-family:verdana;font-size:80%;>";

    echo "<td width=13%>" . $row[""] . "<img src=\"" . $row["Photo"] . "\"></a>";
    echo '<td width="14%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Model'] . '</a></td>';   
    echo '<td width="5%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Year'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Fuel'] . '</a></td>';
       
echo "</tr>";
  }
echo "</table>";
mysql_close($con);

}
?>

Like I said, unless do a more complex ajax queries to get dynamic values upon selections, this is a crude way.

Here is an example of the ajax way

http://php-ajax-code.blogspot.com/2007/07/ajax-triple-dropdown-with-states-cities.html

(yeah it's a bit more complex)

I could see you doing multiple forms though, when make is selected, query the model results, then let them select the model....then onto the final results.

You can direct them to each form using form names in the action

<form name="input" action="model.php" method="post">

QuickOldCar · May 30, 2011

Here's another ajax dropdown example.

http://www.onlinehowto.net/Tutorials/AJAX/AJAX-Drop-Down-Select/1338

Or you can read all about ajax.

Is tutorials there as well.

https://developer.mozilla.org/en/AJAX

amg182 · May 30, 2011

Hi again, Yeah I will have to look into Ajax some time.

I just tried the code you submitted for 2 combos, unfortunately its not returning any results...?

The first one works a treat when you move

<?php
$make = mysql_real_escape_string($_POST['make']);
?>

after the database connection code.

Any ideas to why the two combobox isnt returning results?

Thanks again, I am very grateful for your time!

QuickOldCar · May 30, 2011

try this

<form name="input" action="" method="post">
Car Make: <Select name="make">
<option "Input" value="<?php echo $_POST['make']; ?>"><?php echo $_POST['make']; ?></option>
<option value="ford">ford</option>
<option value="chevrolet">chevrolet</option>
<option value="honda">honda</option>
<option value="lexus">lexus</option>
</select>
Car Model: <Select name="model">
<option "Input" value="<?php echo $_POST['model']; ?>"><?php echo $_POST['model']; ?></option>
<option value="civic">civic</option>
<option value="corsica">corsica</option>
<option value="lumina">lumina</option>
<option value="delsol">delsol</option>
</select>
<input type="submit" value="Get The Results" />
</form>

<?php
if(!isset($_POST) || $_POST['make'] == "" || $_POST['model'] == "") {
echo "<h2>Please check both values.</h2>";
} else {
$make = mysql_real_escape_string($_POST['make']);
$model = mysql_real_escape_string($_POST['model']);
$con = mysql_connect("server","database","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("a5525005_cars", $con);

$result = mysql_query("SELECT * FROM `cars` WHERE Makel='$make' AND Model='$model'");// added the variables in the query

echo "<table class='ex1' border='0' width='113%' style=text-align:center; cellpadding='6' cellspacing='0'>

</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr style=font-family:verdana;font-size:80%;>";

    echo "<td width=13%>" . $row[""] . "<img src=\"" . $row["Photo"] . "\"></a>";
    echo '<td width="14%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Model'] . '</a></td>';   
    echo '<td width="5%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Year'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Fuel'] . '</a></td>';
       
echo "</tr>";
  }
echo "</table>";
mysql_close($con);

}
?>

amg182 · May 31, 2011

Yeah!

Just had to move the "real_escape_strings" below the connection coding!

You are a genius QuickOldCar

Thank you ever so much! The reason I want this to work is so that i can replace model with Fuel. Therefore different make CAN have different Fuel types!

Have a great day! and thanks again!

QuickOldCar · May 31, 2011

I "meant" to move it below the connection, obviously I didn't, ha ha.

amg182 · May 31, 2011

Guessed so!

Ok, I am probably annoying you now, but I have one final query...

Lets say a user defines Make as "Honda" and Model as "Civic". Obviously the result will bring back Honda Civics. But lets say I wanted a "Any Model" Option that returned all Honda's of all models. Is this possible?

You prob sick of me now...

Thanks again :shy:

QuickOldCar · May 31, 2011

Nope, not sick of you, am glad to help and is why I come here.

Here's a trick I do and use a multiple query.

Either one should work.

<form name="input" action="" method="post">
Car Make: <Select name="make">
<option "Input" value="<?php echo $_POST['make']; ?>"><?php echo $_POST['make']; ?></option>
<option value="ford">ford</option>
<option value="chevrolet">chevrolet</option>
<option value="honda">honda</option>
<option value="lexus">lexus</option>
</select>
Car Model: <Select name="model">
<option "Input" value="<?php echo $_POST['model']; ?>"><?php echo $_POST['model']; ?></option>
<option value="ALL">ALL</option>
<option value="civic">civic</option>
<option value="corsica">corsica</option>
<option value="lumina">lumina</option>
<option value="delsol">delsol</option>
</select>
<input type="submit" value="Get The Results" />
</form>

<?php
if(!isset($_POST) || $_POST['make'] == "" || $_POST['model'] == "") {
echo "<h2>Please check both values.</h2>";
} else {
$con = mysql_connect("server","database","password");
$make = mysql_real_escape_string($_POST['make']);
$model = mysql_real_escape_string($_POST['model']);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("a5525005_cars", $con);

if($model == "ALL") {
$model_query = "";
} else {
$model_query = "AND Model='$model'";
}

$result = mysql_query("SELECT * FROM `cars` WHERE Makel='$make' $model_query");// added the variables in the query

echo "<table class='ex1' border='0' width='113%' style=text-align:center; cellpadding='6' cellspacing='0'>

</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr style=font-family:verdana;font-size:80%;>";

    echo "<td width=13%>" . $row[""] . "<img src=\"" . $row["Photo"] . "\"></a>";
    echo '<td width="14%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Model'] . '</a></td>';   
    echo '<td width="5%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Year'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Fuel'] . '</a></td>';
       
echo "</tr>";
  }
echo "</table>";
mysql_close($con);

}
?>

or you can also do it with different select queries

<form name="input" action="" method="post">
Car Make: <Select name="make">
<option "Input" value="<?php echo $_POST['make']; ?>"><?php echo $_POST['make']; ?></option>
<option value="ford">ford</option>
<option value="chevrolet">chevrolet</option>
<option value="honda">honda</option>
<option value="lexus">lexus</option>
</select>
Car Model: <Select name="model">
<option "Input" value="<?php echo $_POST['model']; ?>"><?php echo $_POST['model']; ?></option>
<option value="ALL">ALL</option>
<option value="civic">civic</option>
<option value="corsica">corsica</option>
<option value="lumina">lumina</option>
<option value="delsol">delsol</option>
</select>
<input type="submit" value="Get The Results" />
</form>

<?php
if(!isset($_POST) || $_POST['make'] == "" || $_POST['model'] == "") {
echo "<h2>Please check both values.</h2>";
} else {
$con = mysql_connect("server","database","password");
$make = mysql_real_escape_string($_POST['make']);
$model = mysql_real_escape_string($_POST['model']);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("a5525005_cars", $con);

if($model == "ALL") {
$result = mysql_query("SELECT * FROM `cars` WHERE Makel='$make'");
} else {
$result = mysql_query("SELECT * FROM `cars` WHERE Makel='$make'  AND Model='$model'");
}

echo "<table class='ex1' border='0' width='113%' style=text-align:center; cellpadding='6' cellspacing='0'>

</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr style=font-family:verdana;font-size:80%;>";

    echo "<td width=13%>" . $row[""] . "<img src=\"" . $row["Photo"] . "\"></a>";
    echo '<td width="14%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Model'] . '</a></td>';   
    echo '<td width="5%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Year'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Fuel'] . '</a></td>';
       
echo "</tr>";
  }
echo "</table>";
mysql_close($con);

}
?>

I also have another idea, we can set the default for model as ALL, this way will always get a result.

<form name="input" action="" method="post">
Car Make: <Select name="make">
<option "Input" value="<?php echo $_POST['make']; ?>"><?php echo $_POST['make']; ?></option>
<option value="ford">ford</option>
<option value="chevrolet">chevrolet</option>
<option value="honda">honda</option>
<option value="lexus">lexus</option>
</select>
Car Model: <Select name="model">
<option "Input" value="<?php echo $_POST['model']; ?>"><?php echo $_POST['model']; ?></option>
<option value="ALL">ALL</option>
<option value="civic">civic</option>
<option value="corsica">corsica</option>
<option value="lumina">lumina</option>
<option value="delsol">delsol</option>
</select>
<input type="submit" value="Get The Results" />
</form>

<?php
if(!isset($_POST) || $_POST['make'] == "") {
echo "<h2>Please check both values.</h2>";
} else {


$con = mysql_connect("server","database","password");
$make = mysql_real_escape_string($_POST['make']);
$model = mysql_real_escape_string($_POST['model']);

if($_POST['model'] == ""){
$model = "ALL";
}

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("a5525005_cars", $con);

if($model == "ALL") {
$result = mysql_query("SELECT * FROM `cars` WHERE Makel='$make'");
} else {
$result = mysql_query("SELECT * FROM `cars` WHERE Makel='$make'  AND Model='$model'");
}

echo "<table class='ex1' border='0' width='113%' style=text-align:center; cellpadding='6' cellspacing='0'>

</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr style=font-family:verdana;font-size:80%;>";

    echo "<td width=13%>" . $row[""] . "<img src=\"" . $row["Photo"] . "\"></a>";
    echo '<td width="14%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Model'] . '</a></td>';   
    echo '<td width="5%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Year'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Fuel'] . '</a></td>';
       
echo "</tr>";
  }
echo "</table>";
mysql_close($con);

}
?>

amg182 · May 31, 2011

Your too kind!

That's me for now! I am so grateful!

Enjoy the rest of your day!

Thanks again

:D

amg182 · May 31, 2011

Back again.....

I thought I could work this one out based on the code you gave me, but failed to get it to do what I want.

is it possible to have 3 Combo box filters? In this case Make, Model & Fuel that work even if one or more comboboxes is set to "ALL". I can get the 3 to work together but only if there are 3 values within the comboboxes. Doesn't work if one or more combo box is set to ALL.

Here is what I am

Thanks!

QuickOldCar · May 31, 2011

Could you post your current code so can see what are trying to accomplish?

amg182 · May 31, 2011

Please don't laugh at my pathetic attempt.... :-\

///Combo for Makes

<form name="input" action="" method="post">
Car Make: <Select name="make">
<option "Input" value="<?php echo $_POST['make']; ?>"><?php echo $_POST['make']; ?></option>
<option value="ALL">ALL</option>
<option value="Ford">ford</option>
<option value="BMW">BMW</option>
<option value="Honda">Honda</option>
<option value="Lexus">Lexus</option>
</select>

///combo for Models
Car Model: <Select name="model">
<option "Input" value="<?php echo $_POST['model']; ?>"><?php echo $_POST['model']; ?></option>
<option value="ALL">ALL</option>
<option value="Civic">Civic</option>
<option value="3 Series">3 Series</option>
<option value="Fiesta">Fiesta</option>
<option value="IS200">IS200</option>
</select>

///Combo for Fuel
Car Model: <Select name="fuel">
<option "Input" value="<?php echo $_POST['fuel']; ?>"><?php echo $_POST['fuel']; ?></option>
<option value="ALL">ALL</option>
<option value="Petrol">Petrol</option>
<option value="Diesel">Diesel</option>

</select>
<input type="submit" value="Search Cars" />
</form>


<?php
if(!isset($_POST) || $_POST['make'] == "" || $_POST['model'] == "" || $_POST['fuel'] == "") {
echo "<h2>Please check all 3 values.</h2>";
} else {

$con = mysql_connect("server","a5525005_cars","password");


$make = mysql_real_escape_string($_POST['make']);
$model = mysql_real_escape_string($_POST['model']);
$fuel = mysql_real_escape_string($_POST['fuel']);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("a5525005_cars", $con);

if($model == "ALL" AND $fuel == "ALL") {
$result = mysql_query("SELECT * FROM `cars` WHERE Make='$make' AND Model='$model'");
} else {
$result = mysql_query("SELECT * FROM `cars` WHERE Make='$make'  AND Model='$model' AND Fuel='$fuel'");
}

echo "<table class='ex1' border='0' width='120%' style=text-align:center; cellpadding='6' cellspacing='0'>

</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr style=font-family:verdana;font-size:80%;>";

    echo "<td width=13%>" . $row[""] . "<img src=\"" . $row["Photo"] . "\"></a>";
    echo '<td width="14%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Make'] . '</a></td>';   
    echo '<td width="5%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Model'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Fuel'] . '</a></td>';
       
echo "</tr>";
  }
echo "</table>";
mysql_close($con);

}
?>

What I am trying to achieve is to have the ability to "filter" the database results based on 3 combo boxes(Make, Model and Fuel). If all comboboxes are set to ALL, all results in the database will be shown.

Thanks!

QuickOldCar · May 31, 2011

Give this a whirl

///Combo for Makes

<form name="input" action="" method="post">
Make: <Select name="make">
<option "Input" value="<?php echo $_POST['make']; ?>"><?php echo $_POST['make']; ?></option>
<option value="Ford">ford</option>
<option value="BMW">BMW</option>
<option value="Honda">Honda</option>
<option value="Lexus">Lexus</option>
</select>

///combo for Models
Model: <Select name="model">
<option "Input" value="<?php echo $_POST['model']; ?>"><?php echo $_POST['model']; ?></option>
<option value="ALL">ALL</option>
<option value="Civic">Civic</option>
<option value="3 Series">3 Series</option>
<option value="Fiesta">Fiesta</option>
<option value="IS200">IS200</option>
</select>

///Combo for Fuel
Fuel: <Select name="fuel">
<option "Input" value="<?php echo $_POST['fuel']; ?>"><?php echo $_POST['fuel']; ?></option>
<option value="ALL">ALL</option>
<option value="Petrol">Petrol</option>
<option value="Diesel">Diesel</option>

</select>
<input type="submit" value="Search Cars" />
</form>


<?php
if(!isset($_POST) || $_POST['make'] == "") {
echo "<h2>Please check all values.</h2>";
} else {

$con = mysql_connect("server","a5525005_cars","password");


$make = mysql_real_escape_string($_POST['make']);
$model = mysql_real_escape_string($_POST['model']);
$fuel = mysql_real_escape_string($_POST['fuel']);

//set to ALL if empty
if($model == ""){
$model == "ALL";
}

if($fuel == ""){
$fuel == "ALL";
}

//set queries
if($model == "ALL"){
$model_query = "";
} else {
$model_query = " AND Model='$model'";
}

if($fuel == "ALL"){
$fuel_query = "";
} else {
$fuel_query = "  AND Fuel='$fuel'";
}

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("a5525005_cars", $con);


$result = mysql_query("SELECT * FROM `cars` WHERE Make='$make' $model_query $fuel_query");

if(!$result){
echo "No Results <br />";
} else {

echo "<table class='ex1' border='0' width='120%' style=text-align:center; cellpadding='6' cellspacing='0'>

</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr style=font-family:verdana;font-size:80%;>";

    echo "<td width=13%>" . $row[""] . "<img src=\"" . $row["Photo"] . "\"></a>";
    echo '<td width="14%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Make'] . '</a></td>';   
    echo '<td width="5%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Model'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Fuel'] . '</a></td>';
       
echo "</tr>";
  }
echo "</table>";
}

mysql_close($con);

}
?>

xyph · May 31, 2011

You want something like this.

<form action="#" method="post">

<select name="make">
	<option value="all">All</option>
	<option value="ford">Ford</option>
	<option value="chev">Chevy</option>
</select>
<select name="model">
	<option value="all">All</option>
	<option value="mustang">Mustang</option>
	<option value="camaro">Camaro</option>
</select>
<select name="fuel">
	<option value="all">All</option>
	<option value="regular">Regular</option>
	<option value="premium">Premium</option>
</select>
<input type="submit" name="submit" />

</form>

<?php

$sql = new mysqli('localhost','root','','test');

if( isset($_POST['submit']) ) {

$query = "SELECT `columns` FROM `table`";
$where = array();
if ( isset($_POST['make']) && $_POST['make'] != 'all' )
	$where[] = '`make`=\''.$sql->escape_string($_POST['make']).'\'';
if ( isset($_POST['model']) && $_POST['model'] != 'all' )
	$where[] = '`model`=\''.$sql->escape_string($_POST['model']).'\'';
if ( isset($_POST['fuel']) && $_POST['fuel'] != 'all' )
	$where[] = '`make`=\''.$sql->escape_string($_POST['fuel']).'\'';
$query .= empty($where) ? '' : 'WHERE '.implode(',',$where);

echo 'Your query will be: '. $query;
}

?>

QuickOldCar · May 31, 2011

I sat here for a bit, and I think I came up with something that can work for you.

When you visit the page it will show all results, and you can then select any options to see the different values for each.

The query will build from the selects, and place the WHERE,AND in the proper places.

The POST empty or ALL gets the same results

<form name="input" action="" method="post">
Make: <Select name="make">
<option "Input" value="<?php echo $_POST['make']; ?>"><?php echo $_POST['make']; ?></option>
<option value="ALL">ALL</option>
<option value="Ford">ford</option>
<option value="BMW">BMW</option>
<option value="Honda">Honda</option>
<option value="Lexus">Lexus</option>
</select>

Model: <Select name="model">
<option "Input" value="<?php echo $_POST['model']; ?>"><?php echo $_POST['model']; ?></option>
<option value="ALL">ALL</option>
<option value="Civic">Civic</option>
<option value="3 Series">3 Series</option>
<option value="Fiesta">Fiesta</option>
<option value="IS200">IS200</option>
</select>

Fuel: <Select name="fuel">
<option "Input" value="<?php echo $_POST['fuel']; ?>"><?php echo $_POST['fuel']; ?></option>
<option value="ALL">ALL</option>
<option value="Petrol">Petrol</option>
<option value="Diesel">Diesel</option>

</select>
<input type="submit" value="Search Cars" />
</form>


<?php
//connect to mysql
$con = mysql_connect("server","a5525005_cars","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
//select database
mysql_select_db("a5525005_cars", $con);

//variables from selection form
$make = mysql_real_escape_string($_POST['make']);
$model = mysql_real_escape_string($_POST['model']);
$fuel = mysql_real_escape_string($_POST['fuel']);

//default query
$query .= "SELECT * FROM `cars`";

//count the selections for queries
$query_count = 0;

//set queries and associate WHERE,AND
if(isset($_POST['make']) && $_POST['make'] != "ALL" && $_POST['make'] != ""){
$query_count = $query_count +1;
if($query_count == 1) {
$where_and = "WHERE";
} else {
$where_and = "AND";
}
$query .= " $where_and Make='$make'";
}

if(isset($_POST['model']) && $_POST['model'] != "ALL" && $_POST['model'] != ""){
$query_count = $query_count +1;
if($query_count == 1) {
$where_and = "WHERE";
} else {
$where_and = "AND";
}
$query .= " $where_and Model='$model'";
}

if(isset($_POST['fuel']) && $_POST['fuel'] != "ALL" && $_POST['fuel'] != ""){
$query_count = $query_count +1;
if($query_count == 1) {
$where_and = "WHERE";
} else {
$where_and = "AND";
}
$query .= "  $where_and Fuel='$fuel'";
}

//echo $query;//query built from values above depending on selections
$result = mysql_query("$query");

//check if results
if(!$result){
echo "No Results <br />";
} else {

echo "<table class='ex1' border='0' width='120%' style=text-align:center; cellpadding='6' cellspacing='0'>

</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr style=font-family:verdana;font-size:80%;>";

    echo "<td width=13%>" . $row[""] . "<img src=\"" . $row["Photo"] . "\"></a>";
    echo '<td width="14%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Make'] . '</a></td>';   
    echo '<td width="5%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Model'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Fuel'] . '</a></td>';
       
echo "</tr>";
  }
echo "</table>";
}

mysql_close($con);

?>

Next you need to work on pagination.

amg182 · June 1, 2011

Excellent, that is superb! That was going to be my next query! Thanks so much.

I am overwhelmed with the support you have provided my over the past few days...a true gentleman-THANKS YOU!

Yeah pagination next, although i had to google the word pagination to find out what it is.... :shy:

I expect in the region of 30-40 records per make therefore would need to split into septerate pages.

I hope its not to daunting! I will give it a whirl, hopefully try and find some tutorials on-line to help me.

Again, I cant thank you enough. Although other forums and member have helped me in the past, much of their replies go way over my head as I am very new to PHP and sql.

Very grateful for providing my with all the coding and making it as easy as possible!

QuickOldCar · June 2, 2011

Here at phpfreaks is a pagination tutorial.

http://www.phpfreaks.com/tutorial/basic-pagination

I made a pagination myself, each link below is a bit different.

The demo and codes are at each.

This one is hard set to 10 results per page, the more calculations isn't worth the effort to me.

http://get.blogdns.com/dynaindex/paginate.php

Then this one can set any amount per page you want.

http://get.blogdns.com/paginate/

You will see the idea of pagination is to specify what row to start on, how many results from that point, then for each new page that many more results are displayed, until there is no more results.

The mysql start row begins at zero.

amg182 · June 2, 2011

Brilliant thanks, however the get.bobsdns links done seem to work for me...

Thanks again

QuickOldCar · June 4, 2011

Yeah sorry about that, was some huge storms here and my server went kaput, then lost dns for a few days as well.

I got it sorted and the above links should work now.

amg182 · June 5, 2011

oh, sorry to hear about your server, pretty bad weather huh... I will check back reguarly to see if its up and running.

I had a go at pagination using (youtube tutorial videos). I can get a standard query to display results with pagination, but when I try to add pagination to the 4 variable combo-box script you provided me with it doesn't seem to like it. Is it even possible? here is what I was working with.(You will prob laugh at it but hey)

<form name="input" action="" method="post">
Make: <Select name="make">
<option "Input" value="<?php echo $_POST['make']; ?>"><?php echo $_POST['make']; ?></option>
<option value="All Makes">All Makes</option>
<option value="Ford">ford</option>
<option value="BMW">BMW</option>
<option value="Honda">Honda</option>
<option value="Lexus">Lexus</option>
</select>

Model: <Select name="model">
<option "Input" value="<?php echo $_POST['model']; ?>"><?php echo $_POST['model']; ?></option>
<option value="All Models">All Models </option>
<option value="Civic">Civic</option>
<option value="3 Series">3 Series</option>
<option value="Fiesta">Fiesta</option>
<option value="IS200">IS200</option>
</select>

Fuel: <Select name="fuel">
<option "Input" value="<?php echo $_POST['fuel']; ?>"><?php echo $_POST['fuel']; ?></option>
<option value="Any">Any</option>
<option value="Petrol">Petrol</option>
<option value="Diesel">Diesel</option>
</select>

Location: <Select name="location">
<option "Input" value="<?php echo $_POST['location']; ?>"><?php echo $_POST['location']; ?></option>
<option value="UK">UK</option>
<option value="London">London</option>
<option value="Kent">Kent</option>
</select>

<input type="submit" value="Search Cars" />
</form>


<?php
include 'db.inc.php';

$per_page = 2;

//count of pages which is displays amount of page if you want to count rows you need to remove the divide sign
$pages_query = mysql_query("SELECT COUNT('id') FROM cars");

$pages = ceil(mysql_result($pages_query, 0) / $per_page);

$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
$start = ($page - 1) * $per_page;

//variables from selection form
$make = mysql_real_escape_string($_POST['make']);
$model = mysql_real_escape_string($_POST['model']);
$fuel = mysql_real_escape_string($_POST['fuel']);
$location = mysql_real_escape_string($_POST['location']);

//default query
$query = "SELECT * FROM `cars` LIMIT $start, $per_page";

//count the selections for queries
$query_count = 0;

//set queries and associate WHERE,AND
if(isset($_POST['make']) && $_POST['make'] != "All Makes" && $_POST['make'] != ""){
$query_count = $query_count +1;
if($query_count == 1) {
$where_and = "WHERE";
} else {
$where_and = "AND";
}
$query .= " $where_and Make='$make'";
}

if(isset($_POST['model']) && $_POST['model'] != "All Models" && $_POST['model'] != ""){
$query_count = $query_count +1;
if($query_count == 1) {
$where_and = "WHERE";
} else {
$where_and = "AND";
}
$query .= " $where_and Model='$model'";
}

if(isset($_POST['fuel']) && $_POST['fuel'] != "Any" && $_POST['fuel'] != ""){
$query_count = $query_count +1;
if($query_count == 1) {
$where_and = "WHERE";
} else {
$where_and = "AND";
}
$query .= "  $where_and Fuel='$fuel'";
}

if(isset($_POST['location']) && $_POST['location'] != "UK" && $_POST['location'] != ""){
$query_count = $query_count +1;
if($query_count == 1) {
$where_and = "WHERE";
} else {
$where_and = "AND";
}
$query .= "  $where_and Location='$location'";
}

//echo $query;//query built from values above depending on selections
$result = mysql_query("$query");

//check if results
if(!$result){
echo "No Results <br />";
} else {

echo "<table class='ex1' border='0' width='120%' style=text-align:center; cellpadding='6' cellspacing='0'>

</tr>";

while($result_row = mysql_fetch_array($result)) {

  echo "<tr style=font-family:verdana;font-size:80%;>";

    echo "<td width=13%>" . $row[""] . "<img src=\"" . $row["Photo"] . "\"></a>";
    echo '<td width="14%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Make'] . '</a></td>';   
    echo '<td width="5%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Model'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Fuel'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Location'] . '</a></td>';
       
echo "</tr>";
  }
echo "</table>";
}

//pagination number of pages at footer
if ($pages >= 1 && $page <= $pages) {

for ($x =1; $x<=$pages; $x++ ){
   echo ($x == $page) ? '<strong><a href="?page='.$x.'">'.$x.'</a></strong> ' : '<a href="?page='.$x.'">'.$x.'</a> ' ;

}


}
?>

Its not showing any errors, just not outputting the results... :confused:

QuickOldCar · June 6, 2011

I didn't look too much into the pagination process you did, but I did notice this.

change this:

while($result_row = mysql_fetch_array($result)) {

to this:

while($row = mysql_fetch_array($result)) {

Even basic pagination is not too easy to do, and I'm talking work correctly.

That pagination you did may work for all the queries, but the count will be incorrect because you set the query to all of them, and when you do a certain select or more........it will still be all post results.

Anyway, I'm not gonna even bother trying to fix your pagination.

Right now I'm writing up your code using my own pagination, I'll post it when I get it done for you to try.

Sign In

Help! Mysql results based on combo box.

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information