Search Database Help Needed-Newbie

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
User avatar
sroussie
Forum Newbie
Posts: 19
Joined: Wed Aug 27, 2003 12:26 pm
Location: Buffalo, NY

Search Database Help Needed-Newbie

Post by sroussie »

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
User avatar
vinyl-junkie
Forum Newbie
Posts: 23
Joined: Sat Nov 22, 2003 2:52 pm
Location: Seattle, WA area (USA)

Post by vinyl-junkie »

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.

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:

Post by john_drayton »

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);
?>
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

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
User avatar
sroussie
Forum Newbie
Posts: 19
Joined: Wed Aug 27, 2003 12:26 pm
Location: Buffalo, NY

Post by sroussie »

Thank you for the help. I'll give these a shot and see what happens.

Sue
Post Reply