Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Filtering Results With Php Sql Query

html5 Query Filter Code

  • Please log in to reply
12 replies to this topic

#1 teensicle

teensicle

    CC Resident

  • Advanced Member
  • PipPipPipPip
  • 93 posts
  • Location:Jamaica
  • Learning:C, JavaScript, PL/SQL, Others

Posted 08 July 2012 - 08:55 PM

So i am back again :cool: looking for some well needed help. So i decided to build a website, where people can buy and sell cars from. This is based in Jamaica. However i'm fairly new to the PHP and SQL world but i know how to get around. Sadly i've been stuck at a specific area for some time now and have no idea how to continue. For example for the make someone would select "Nissan" and for the body they might select "Hatch Back" and when they press submit they will then get the new results.

Posted Image

This is a snippet of my code.


<div id="search_query" >
Make
<select id="1" size="0">
<option value="all">All</option>
<option value="acura">Acura</option>
<option value="bmw">BMW</option>
<option value="cherry_qq">Cherry QQ</option>
<option value="nissan">Nissan</option>
</select>
Year
<select id="2" size="0">
<option value="all">All</option>
<option value="2013">2013</option>
<option value="2012">2012</option>
<option value="2011">2011</option>
<option value="2010">2010</option>
<option value="2009">2009</option>
<option value="2008">2008</option>
<option value="2007">2007</option>
<option value="2006">2006</option>
<option value="2005">2005</option>
<option value="2004">2004</option>
<option value="2003">2003</option>
<option value="2002">2002</option>
<option value="2001">2001</option>
<option value="2000">2000</option>

</select>
Transmission
<select id="3" size="0">
<option value="all">All</option>
<option value="automatic">Automatic</option>
<option value="manual">Manual</option>
</select>
Body Style
<select id="4" size="0">
<option value="all">All</option>
<option value="sedan">Sedan</option>
<option value="coupe">Coupe</option>
<option value="hatchback">Hatch Back</option>
</select>
Price
<select id="5" size="0">
<option value="all">All</option>
<option value="<100000">Under $100,000</option>
<option value=">10000<250000">Between $100,000 and $250,000</option>
<option value=">250000<500000">Between $250,000 and $500,000</option>
<option value=">500000<750000">Between $500, 000 and $750,000</option>
<option value=">7500000">Over $750,000</option>
</select>
Condition
<select id="6" size="0">
<option value="6">All</option>
<option value="used">Used</option>
<option value="new">New</option>
</select>


<input type="submit" name="filter" id="filter" value="Filter">
</div>

<?php
$db_con = mysql_connect('localhost', 'username', 'password');
if (!$db_con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db('my_db', $db_con);
$result = mysql_query("SELECT * FROM mydb_vehicles");
	
echo "<table width= 970 border=1>
	 <tr>
	 <th width='120' scope='col'>Image</th>
	 <th width='170' scope='col'>Details</th>
	 <th width='185' scope='col'>Seller</th>
	 <th width='126' scope='col'>Price</th>
	 </tr>";
while($row = mysql_fetch_array($result))
{
	 echo "<tr>";
echo "<td> <img src=" .$row['v_image']. " width =200 height = 130>" . "</td>";
	 echo "<td>". $row['v_year'] . "&nbsp;" . $row['v_make'] . "&nbsp;". $row['v_model'] . "&nbsp;". $row['b_type']. "</td>";
	 echo "<td>". $row['user_id'] ."</td>";
	 echo "<td>". $row['v_price'] ."</td>";
	 echo "</tr>";
}
echo "</table>";

mysql_close($db_con);
?>

  • 0
PePe===> BLACKSPADE EMPIRE<===PePe

www.blackspade-ent.com

#2 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts
  • Location:Argentina
  • Programming Language:C, C++, C#, PHP, JavaScript, Transact-SQL, Bash, Others
  • Learning:Java, Others

Posted 09 July 2012 - 11:07 AM

Hello teenside,
maybe i am wrong but it looks to my your pretty new to this :D

i will refer you to some tutorial, but i will give you a head start first, your php code
will receive the data you selected on the form you submit, as in this example

MAKE
<select id="1" size="0">

for php, it will be like : $1 after you convert the POST variables to regular php variables ( which i wonder if that would be error cause the field is ided as 1 )

good thing to do is to give proper name to the form inputs, like this :

<select id="make" name="make" size="0">

so later in your php you got $make with the form's make selected value

and you you can use it to filter the database like this :

"SELECT * FROM mydb_vehicles where make=" . $make;

that is the general situation, now to learn a little more about how it works why don't you get a look at
this tutorial : creating login/registration forms in php

there you got a very clear step by step explanation on how the whole thing works :D

i hope to see you here soon talking about how good it went ;)
  • 1

#3 teensicle

teensicle

    CC Resident

  • Advanced Member
  • PipPipPipPip
  • 93 posts
  • Location:Jamaica
  • Learning:C, JavaScript, PL/SQL, Others

Posted 10 July 2012 - 05:29 AM

@Blackrabbit lol you are right about one thing i'm new to PHP development however i think with time and patience i'll get there. This is what i have after reading the tutorial and doing some more google-ing. I decided to work on one query at a time instead of trying to tackle then all at once.

<div id="main">
<form method="post" action="">
<div id="search_query" >
	 Make
    <select name="make" size="0">
    <option value="all">All</option>
    <option value="honda">Honda</option>
  <option value="toyota">Toyota</option>
  <option value="nissan">Nissan</option>
  <option value="bmw">BMW</option>
    </select>
   
	 <input type="submit" name="submit" value="submit">
	
</div>
</form>
<div id="main_container">
<?php
$db_con = mysql_connect('localhost', 'root', '');
if (!$db_con) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('car_hunt_ja', $db_con);
if(isset($_POST['submit']))
{
$make = mysql_real_escape_string($_POST['make']);
$sql = sprintf("SELECT * FROM vehicles WHERE v_make= '$make' ");

$result = mysql_query($sql);
	
echo "<table width= 970 border=1>
	 <tr>
	   <th width='120' scope='col'>Image</th>
	   <th width='170' scope='col'>Details</th>
	   <th width='185' scope='col'>Seller</th>
	   <th width='126' scope='col'>Price</th>
	 </tr>";
while($row = mysql_fetch_array($result))
  {
	 echo "<tr>";
    echo "<td> <img src=" .$row['v_image']. " width =200 height = 130>" . "</td>";
	   echo "<td>". $row['v_year'] . "&nbsp;" . $row['v_make'] . "&nbsp;". $row['v_model'] . "&nbsp;". $row['b_type']. "</td>";
	   echo "<td>". $row['user_id'] ."</td>";
	   echo "<td>". $row['v_price'] ."</td>";
	 echo "</tr>";
  }
   echo "</table>";
}
else
{
$sql = sprintf("SELECT * FROM vehicles");
$result = mysql_query($sql);
	
echo "<table width= 970 border=1>
	 <tr>
	   <th width='120' scope='col'>Image</th>
	   <th width='170' scope='col'>Details</th>
	   <th width='185' scope='col'>Seller</th>
	   <th width='126' scope='col'>Price</th>
	 </tr>";
while($row = mysql_fetch_array($result))
  {
	 echo "<tr>";
    echo "<td> <img src=" .$row['v_image']. " width =200 height = 130>" . "</td>";
	   echo "<td>". $row['v_year'] . "&nbsp;" . $row['v_make'] . "&nbsp;". $row['v_model'] . "&nbsp;". $row['b_type']. "</td>";
	   echo "<td>". $row['user_id'] ."</td>";
	   echo "<td>". $row['v_price'] ."</td>";
	 echo "</tr>";
  }
   echo "</table>";
} 
  
mysql_close($db_con);
?>

  • 0
PePe===> BLACKSPADE EMPIRE<===PePe

www.blackspade-ent.com

#4 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts
  • Location:Argentina
  • Programming Language:C, C++, C#, PHP, JavaScript, Transact-SQL, Bash, Others
  • Learning:Java, Others

Posted 10 July 2012 - 09:36 AM

it looks much better your code now :D
and of course, in programing everything is about time and some skill too

so, you already have a better version, i expect a new and better one soon, so you can already start with the questions, lol

i will be glad to help !
  • 0

#5 teensicle

teensicle

    CC Resident

  • Advanced Member
  • PipPipPipPip
  • 93 posts
  • Location:Jamaica
  • Learning:C, JavaScript, PL/SQL, Others

Posted 11 July 2012 - 04:11 AM

So im back again and thanks to a few tips i received from BlackRabbit; thumbs up to him of course i was able to get my little website design a bit further. Anyhow i have one question though. This is what i have so far, and all but one of the query conditions have been configured. Lol i doubt it would be a surprise where i got stuck again and of course, at setting the price condition. I know how to check for int values in SQL to obtain values in a specific range. However what what i'm not sure of is what is the option value that i should used i.e

<select name="price" size="0">
<option value="100000">Under $100,000</option>
<option value=">100000<250000">Between $100,000 and $250,000</option>
<option value=">250000<500000">Between $250,000 and $500,000</option>
<option value=">500000<750000">Between $500, 000 and $750,000</option>
<option value=">7500000">Over $750,000</option>
</select>

as for some of the options im checking for specific ranges. How can i approach this? Below is a copy of my php code.

<?php
//Connecting to database
$db_con = mysql_connect('localhost', 'root', '');
if (!$db_con) {
die('Could not connect: ' . mysql_error());
}
//Selecting database to use
mysql_select_db('car_hunt_ja', $db_con);
//Setting search condition
if(isset($_POST['submit']))
{

$make = mysql_real_escape_string($_POST['make']);
$year = mysql_real_escape_string($_POST['year']);
$trans = mysql_real_escape_string($_POST['trans']);
$b_style = mysql_real_escape_string($_POST['b_style']);
$cond = mysql_real_escape_string($_POST['cond']);
$price = mysql_real_escape_string($_POST['price']);

echo $price;
$sql = sprintf("SELECT *
FROM `vehicles`
WHERE `v_make` LIKE '$make'
AND `v_year` = '$year'
AND `v_trans` LIKE '$trans'
AND `condition` LIKE '$cond'
AND `b_style` LIKE '$b_style'
AND `v_price` '$price'");

$result = mysql_query($sql);
	
echo "<table width= 970 border=1>
	 <tr>
	 <th width='120' scope='col'>Image</th>
	 <th width='170' scope='col'>Details</th>
	 <th width='185' scope='col'>Seller</th>
	 <th width='126' scope='col'>Price</th>
	 </tr>";
while($row = mysql_fetch_array($result))
{
	 echo "<tr>";
echo "<td> <img src=" .$row['v_image']. " width =200 height = 130>" . "</td>";
	 echo "<td>". $row['v_year'] . "&nbsp;" . $row['v_make'] . "&nbsp;". $row['v_model'] . "&nbsp;". $row['b_style']. "</td>";
	 echo "<td>". $row['user_id'] ."</td>";
	 echo "<td>". $row['v_price'] ."</td>";
	 echo "</tr>";
}
echo "</table>";
}

  • 0
PePe===> BLACKSPADE EMPIRE<===PePe

www.blackspade-ent.com

#6 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts
  • Location:Argentina
  • Programming Language:C, C++, C#, PHP, JavaScript, Transact-SQL, Bash, Others
  • Learning:Java, Others

Posted 11 July 2012 - 04:46 PM

in sql you can use the clause between, something like :

and $v_price between low_price and $high_price

of you can also do :

and ( $v_price > $low_price and $v_price < $high_price )

parenthesis makes for the AND inside the parenthesis to be computed local to that parenthesis scope


still i think you in the selector are not providing the range to do such query, maybe the value for those should be like 100-250, 250-500, etc
so when it comes back you split the value, and then multiply the number by 1000 :D
  • 0

#7 teensicle

teensicle

    CC Resident

  • Advanced Member
  • PipPipPipPip
  • 93 posts
  • Location:Jamaica
  • Learning:C, JavaScript, PL/SQL, Others

Posted 11 July 2012 - 04:49 PM

My new select option:

Price
	<select name="price" size="0">
	<option value="< 100000">Under $100,000</option>
<option value=">= 100000 AND `v_price` <= 250000">Between $100,000 and $250,000</option>
<option value=">= 250000 AND `v_price` <= 500000">Between $250,000 and $500,000</option>
<option value=">= 500000 AND `v_price` <= 750000">Between $500, 000 and $750,000</option>
<option value=">7500000">Over $750,000</option>
	</select>

PHP CODE:

$sql = sprintf("SELECT *
FROM  `chjadb_vehicles`
WHERE  `v_make` LIKE  '$make'
AND  `v_year` =  '$year'
AND  `v_trans` LIKE  '$trans'
AND  `v_price` $price
AND  `condition` LIKE  '$cond'
AND  `b_style` LIKE  '$b_style'");

Thanks it now works!

i find that when i set a criteria and press submit the options reset itself. How can i control that? if you notice the make goes back to BMW, which is at the top of the select query list and also does the other options see example below:

P.S ignore the horrible layout just trying to get the functionality working

Posted Image
  • 0
PePe===> BLACKSPADE EMPIRE<===PePe

www.blackspade-ent.com

#8 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts
  • Location:Argentina
  • Programming Language:C, C++, C#, PHP, JavaScript, Transact-SQL, Bash, Others
  • Learning:Java, Others

Posted 11 July 2012 - 04:58 PM

i advice you to reasearch andlearn some javascript and get to know how you set those values after submit by yourself :D
  • 0

#9 teensicle

teensicle

    CC Resident

  • Advanced Member
  • PipPipPipPip
  • 93 posts
  • Location:Jamaica
  • Learning:C, JavaScript, PL/SQL, Others

Posted 11 July 2012 - 05:14 PM

i advice you to reasearch andlearn some javascript and get to know how you set those values after submit by yourself :D


can you give me a hint as to what to search for?
  • 0
PePe===> BLACKSPADE EMPIRE<===PePe

www.blackspade-ent.com

#10 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts
  • Location:Argentina
  • Programming Language:C, C++, C#, PHP, JavaScript, Transact-SQL, Bash, Others
  • Learning:Java, Others

Posted 12 July 2012 - 01:00 PM

Of course, this is a very very silly tutorial, and it will help you to understand how Javascript works with html form tags
Click Here for the tutorial

then you should read about getElementByID

now, first thing i told you, about naming and id-ing the selects starts to make sense does not it ?
here is what you gonna do,

- you will make a javascript function, to be called on submit
- in that function you will do the submit ( like ) form1.submit()
- after that, you will set the values of the selectors again

here is a little tutorial about the html select in javascript's point of view : click here

as a hint, you should aim to the selectedindex property ;)

now get your hands to work and come back with the job done to get your medal, lol
  • 0

#11 teensicle

teensicle

    CC Resident

  • Advanced Member
  • PipPipPipPip
  • 93 posts
  • Location:Jamaica
  • Learning:C, JavaScript, PL/SQL, Others

Posted 12 July 2012 - 01:03 PM

Do you think it would be better to use jquery instead of javascript to do this as i'll be using jquery in the project soon... Looking at my present coding i think i might have a problem with sql injections, how can i tighten security? Gonna read up on all the material you sent thanks
  • 0
PePe===> BLACKSPADE EMPIRE<===PePe

www.blackspade-ent.com

#12 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts
  • Location:Argentina
  • Programming Language:C, C++, C#, PHP, JavaScript, Transact-SQL, Bash, Others
  • Learning:Java, Others

Posted 12 July 2012 - 01:18 PM

well ... you are just starting, your questions i think are little bit ahead of present state,
you need first to see how the whole mechanics work, then go for security :D
  • 0





Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download