homepage Welcome to WebmasterWorld Guest from 54.211.7.174
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Help developing MySQL search query
based on form input
javashackgirl




msg:1298354
 8:36 pm on Nov 13, 2003 (gmt 0)

I am trying to develop a search query with a search page, but I am struggling with the fact I don't know enough and I have a deadline that is inhibiting me from LEARNING what I need to know in order to do it.

Is there anyone out there that could help?

TIA
J

 

dreamcatcher




msg:1298355
 9:18 pm on Nov 13, 2003 (gmt 0)

<input type=text name=keyword>

$query = "SELECT * FROM table WHERE something like '%$keyword%' or something like '%$keyword%'";
$result = mysql_query($query) or die(mysql_error());

Are you after something like that?

javashackgirl




msg:1298356
 9:47 pm on Nov 13, 2003 (gmt 0)

Yes, something like that. Trouble is, I have ... well, I can't post the link to the page, so here's the code:

[php]
<form action="test.php" method="get" name="search">
<table border="0" cellspacing="5" cellpadding="4">
<tr>
<td colspan="2">
<div align="center">
<h2><br>
<br>
<font face="Courier New,Courier,Monaco"><b><font color="#cb4927">Property Search</font></b></font></h2>
</div>
</td>
</tr>
<tr>
<td valign="top" width="50%">
<div align="right">
<font color="#cb4927"><b>Property Type </b></font></div>
</td>
<td valign="top" width="50%">
<input type="checkbox" name="prop_type[]" value="Residential" border="0"> Residential<br>
<input type="checkbox" name="prop_type[]" value="Residential On Acreage" border="0"> Residential On Acreage<br>
<input type="checkbox" name="prop_type[]" value="Bare Land" border="0"> Bare Land
</td>
</tr>
<tr>
<td valign="top" width="50%">
<div align="right">
<font color="#cb4927"><b>Area </b></font></div>
</td>
<td width="50%">
<input type="checkbox" name="area[]" value="Council" border="0"> Council <br>
and surrounding area<br>
<input type="checkbox" name="area[]" value="Cambridge" border="0"> Cambridge<br>
<input type="checkbox" name="area[]" value="Fruitvale" border="0"> Fruitvale</td>
</tr>
<tr>
<td colspan="2">
<div align="center">
<font color="#cb4927"><b>Min. and Max. Price Range</b></font></div>
</td>
</tr>
<tr>
<td width="50%">
<div align="right">
<select name="minprice" size="1">
<option value="0">None</option>
<option value="20,000">$20,000</option>
<option value="40,000">$40,000</option>
<option selected value="60,000">$60,000</option>
<option value="80,000">$80,000</option>
<option value="100,000">$100,000</option>
<option value="120,000">$120,000</option>
<option value="140,000">$140,000</option>
</select></div>
</td>
<td width="50%"><select name="maxprice" size="1">
<option value="0">None</option>
<option value="20,000">$20,000</option>
<option value="40,000">$40,000</option>
<option value="60,000">$60,000</option>
<option value="80,000">$80,000</option>
<option value="100,000">$100,000</option>
</select></td>
</tr>
<tr>
<td colspan="2">
<div align="center">
<input type="submit" name="Submit" value="Perform Search" border="0"></div>
</td>
</tr>
</table>
</form>
</body>
[/php]

There are 3 main fields they have to choose from: prop_type[], area[], and price (min and max). The query must produce homes that fit ALL of those fields. That's where I'm getting stuck.

And then they have to post in order of descending price. (I have the first page my db guy wrote, and I can copy a lot of the format from that for layout.)

[edited by: jatar_k at 5:19 am (utc) on Nov. 14, 2003]
[edit reason] trimmed down the code for readability [/edit]

Shane




msg:1298357
 9:58 pm on Nov 13, 2003 (gmt 0)

From looking at the code (& I don't know php, .....) you are doing three </selects> which probably means three querys each with one parameter.

You need to (in my humble and naive opnion) get the values the user wants to query on loaded/inputted/whatever and then run one query using all three inputted values.

I don't know how to do this right now though.

My very humble thoughts,
Shane

javashackgirl




msg:1298358
 10:02 pm on Nov 13, 2003 (gmt 0)

Yep, you've got it right on. Mind you, there are several selections they can make in each field, and EVERY one of those fields must be true before it returns the data. At least I understand THAT much. Trouble is, I don't know the code to make it work.

:)

Shane




msg:1298359
 12:33 am on Nov 14, 2003 (gmt 0)

Have you looked at:

AGoodRead [webmasterworld.com]

I think it would help greatly.

..... Shane

jatar_k




msg:1298360
 5:18 am on Nov 14, 2003 (gmt 0)

So we need to build a SELECT [mysql.com] statement based on form data

If you take a quick look at the syntax it des seem a little overwhelming. Let's it break it down a little.

SELECT colname1,colname2 FROM table1 WHERE ...

SELECT - this is the command we are issuing. It basically means get some data from our database based on the criteria to follow.

colname1,colname2 - this is the columns we would like to have returned from our table (or tables). This a list of column names seperated by a comma. You can use * to signify all columns.

FROM table1 - this is the table we are getting the data from. You can specify more than one table by listing all of the tables seperated by commas but that would go beyond a basic look at selecting.

WHERE ... - So here is where it gets more complex. The WHERE clause gives criteria for matching the data that we need.

Lets look at a few examples.

SELECT * FROM table1 WHERE name='jatar_k'

This would return all rows where the name column has a value of 'jatar_k'. Often in this case you are looking for a specific row of data. When a value you are searching for is a string you need to surround it with single quotes. If you are selecting an integer you do not need any symbol surrounding it. You can also use wildcards in your string comparisons.

The % means many characters so

... WHERE name='jatar%' - looks for jatar at the beginning of the string
... WHERE name='%jatar' - looks for jatar at the end of the string
... WHERE name='%jatar%' - looks for jatar anywhere in the string

you can also use more than one column value

... WHERE name='jatar_k' AND site='webmasterworld'

This would only return a row where both of the columns matched the values exactly.

You can also use comparison operators [mysql.com] in your WHERE clause to have ranges of data such as >,<,<=,>=,BETWEEN,IN.

Greater than, less than, less than or equal to, greater than or equal to
The above are fairly self explanatory but the others may not be

... WHERE price BETWEEN minvalue AND maxvalue - this will return any values from the specified columns that are between the minimum and maximum

... WHERE value IN (1,2,3,4) - this is for selecting values that are within the specified set. This will only return rows where the values match one of the specified values exactly. Or

... WHERE value NOT IN (1,2,3,4) - this will return values that don't match any of the specified values.

each of the above has its uses.

Another useful part of the SELECT statement is the ability to order your results using
ORDER BY colname1 [DESC¦ASC]

SELECT * FROM table1 ORDER BY colname1 DESC

The ORDER BY clause orders the results by the specified column in either descending or ascending order.

So based on the above we can look at your specific case\

What values do we have?

prop_type - multiple specific selections
area - multiple specific selections
maxprice - integer value to be used in a range
minprice - integer value to be used in a range

So we now know what kind of data we have, how do we get what we need? We will assume your table is named listings and that we want to return all data from each row and use *. We will also assume that the column for house prices is called price and it is an integer column. All other values will be used as the column name.

SELECT * FROM listings WHERE prop_type IN ('','','','') AND area IN ('','','','') AND price BETWEEN $_POST['minprice'] AND $_POST['maxprice']

ok so I left 2 parts out. We need to cycle through the 2 posted arrays prop_type and area to build our IN clause. Let's do that first.

foreach ($_POST['prop_type'] as $pt) { 
$in1 .= "'" . $pt . "',";
}

$in1 = substr($in1,0,strlen($pt)-1);

the last line makes sure we strip the final comma.

and then

foreach ($_POST['area'] as $pt) { 
$in2 .= "'" . $pt . "',";
}

$in2 = substr($in2,0,strlen($pt)-1);

So let's put it together

foreach ($_POST['prop_type'] as $pt) { 
$in1 .= "'" . $pt . "',";
}
$in1 = substr($in1,0,strlen($pt)-1);
foreach ($_POST['area'] as $pt) {
$in2 .= "'" . $pt . "',";
}
$in2 = substr($in2,0,strlen($pt)-1);

$q = "SELECT * FROM listings WHERE prop_type IN (" . $in1 . ") AND area IN (" . $in2 . ") AND price BETWEEN " . $_POST['minprice'] . " AND " . $_POST['maxprice'];
$result = mysql_query($q);

et voila, no problem, just a matter of understanding our data.

see also
Commonly Used MySQL Commands [webmasterworld.com] - msg 9

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved