Hi,
I have a used car inventory in a MYSQL database. There are 8 fields I need to be able to search. For instance, by Make, Model and price range. Once the results are displayed I need to be able to click on the Model and go to a detail listing page. I have seen this done everywhere but not sure how to do it. I am new to PHP?MYSQL. COuld someone point me to a good tutorial/example that could help me?
Thanks,
Sue
Search Database Help Needed-Newbie
Moderator: General Moderators
- vinyl-junkie
- Forum Newbie
- Posts: 23
- Joined: Sat Nov 22, 2003 2:52 pm
- Location: Seattle, WA area (USA)
I'm a PHP newbie myself but I've done something in ASP which is similar to what you're doing, so I think I might be able to help. In my opinion, your biggest hurdle at this point is setting up your SQL statement.
Correct me if I'm wrong, but you're probably selecting the same database fields each time, no matter what options the user might want to select off the screen. The difference from one query to another will be a WHERE clause in your SQL Select statement. In simplified form, here's how I setup the code for the the WHERE clause using ASP. I think you can easily make the translation of code into PHP. Hope it helps.
Correct me if I'm wrong, but you're probably selecting the same database fields each time, no matter what options the user might want to select off the screen. The difference from one query to another will be a WHERE clause in your SQL Select statement. In simplified form, here's how I setup the code for the the WHERE clause using ASP. I think you can easily make the translation of code into PHP. Hope it helps.
Code: Select all
If field1 > "" Then
varWhere = "WHERE" & some conditions for field1
If field2 > "" Then
varWhere = varWhere & "AND " some conditions for field2
End-If
If field3 > "" Then
varWhere = varWhere & "AND " some conditions for field3
End-If
If field4 > "" Then
varWhere = varWhere & "AND " some conditions for field4
End-If
If field5 > "" Then
varWhere = varWhere & "AND " some conditions for field5
End-If
Else
If field2 > "" Then
varWhere = varWhere = "WHERE" & some conditions for field2
If field3 > "" Then
varWhere = varWhere & "AND " some conditions for field3
End-If
If field4 > "" Then
varWhere = varWhere & "AND " some conditions for field4
End-If
If field5 > "" Then
varWhere = varWhere & "AND " some conditions for field5
End-If
Else
and so on-
john_drayton
- Forum Newbie
- Posts: 4
- Joined: Tue Nov 18, 2003 12:36 am
- Location: Philippines
- Contact:
I'm a newbie too, but i hope this code can further help you a bit on your problem:
Code: Select all
<?php
/* Connecting, selecting database */
$link = mysql_connect("mysql_host", "mysql_user", "mysql_password")
or die("Could not connect");
print "Connected successfully";
mysql_select_db("my_database") or die("Could not select database");
/* Performing SQL query */
$query = "SELECT * FROM my_table";
$result = mysql_query($query) or die("Query failed");
/* Printing results in HTML */
print "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
print "\t<tr>\n";
foreach ($line as $col_value) {
print "\t\t<td>$col_value</td>\n";
}
print "\t</tr>\n";
}
print "</table>\n";
/* Free resultset */
mysql_free_result($result);
/* Closing connection */
mysql_close($link);
?>First you need a form with "make" "model" and "price range" fields.
Price range should probably be an options menu, hard-coded with the price bands you wish to set.
Make & model could also be set up as menus: the script which creates the form page would query the db to get the possible values, then create the options lists.
Next, you need some code to process form submissions. First check if required vars are set. If not, redisplay the form. If yes, go and do the search - a db query, obviously.
If you want to allow searches on just some of the form fields, the search query will have to dynamically create a query. Tag on AND clauses if data was entered in more than one field.
For the price clause, check out the mysql manual at mysql.com to find out how to find values between a set min and max.
To retrieve the $min and $max from the form, you could set option values something like 3300-6600 and then explode() or whatever to pick out $min and $max.
Once you've got a query result, print off a list. The hyperlinks have to link to your "view car model" page - just set them up with whatever query string that script requires.
Incidentally, models should have a db table of their own, as you may already know. If not, find out why here: http://www.oreilly.de/catalog/javadtabp ... r/ch02.pdf
Price range should probably be an options menu, hard-coded with the price bands you wish to set.
Make & model could also be set up as menus: the script which creates the form page would query the db to get the possible values, then create the options lists.
Next, you need some code to process form submissions. First check if required vars are set. If not, redisplay the form. If yes, go and do the search - a db query, obviously.
If you want to allow searches on just some of the form fields, the search query will have to dynamically create a query. Tag on AND clauses if data was entered in more than one field.
For the price clause, check out the mysql manual at mysql.com to find out how to find values between a set min and max.
To retrieve the $min and $max from the form, you could set option values something like 3300-6600 and then explode() or whatever to pick out $min and $max.
Once you've got a query result, print off a list. The hyperlinks have to link to your "view car model" page - just set them up with whatever query string that script requires.
Incidentally, models should have a db table of their own, as you may already know. If not, find out why here: http://www.oreilly.de/catalog/javadtabp ... r/ch02.pdf