search query

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
DonthDragon
Forum Newbie
Posts: 14
Joined: Sun Jan 10, 2010 4:03 am

search query

Post by DonthDragon »

Hi.

I would like to do a search on my website. I seem to be stuck with one small thing though.
My website contains conference venues that can each seat a certain amount of delegates.
I have a form which states on one of the field inputs : "maximum amount of people".
The user will put in eg : 50 (This would be how many delegates they need to seat).
I have a few venues that can hold up to 50 and more delegates.

I want to be able to search all the venues that can seat 50 delegates. Including venues that can seat more people.

How would I do this?
I hope this makes sense.

Kind regards
Donovan
amargharat
Forum Commoner
Posts: 82
Joined: Wed Sep 16, 2009 2:43 am
Location: Mumbai, India
Contact:

Re: search query

Post by amargharat »

consider,
venues table having two fields as "venuename" and "delegates"

so query will be to find out seat which has more than or equals to 50 delegates

Code: Select all

$sql = "SELECT venuename, delegates FROM venues having delegates >= 50";
mysql_query($sql);
DonthDragon
Forum Newbie
Posts: 14
Joined: Sun Jan 10, 2010 4:03 am

Re: search query

Post by DonthDragon »

Thats sounds like exactly what i need.
Thank you very much!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: search query

Post by VladSun »

One should use WHERE clause instead of HAVING in case there is no GROUP BY clause.

[sql]SELECT
venuename,
delegates
FROM
venues
where
delegates >= 50[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
DonthDragon
Forum Newbie
Posts: 14
Joined: Sun Jan 10, 2010 4:03 am

Re: search query

Post by DonthDragon »

Thanks for your responses.
I still seem to be a bit stuck here though.

What will happen : The user will put in any random number for the field "maximum number of guests".

I want the search to find all venues that can hold the amount of guests that the user puts in.

How would i construct my query?

NB*. I have three fields :
area (a drop down list showing the differant areas to look in)
category (a drop down list showing differant venue types eg: conference, wedding)
max_guests (an input box where the user will put in how many delegates/guests they will be expecting)

I hope I am able to be clear (Novice in progress.)
DonthDragon
Forum Newbie
Posts: 14
Joined: Sun Jan 10, 2010 4:03 am

Re: search query

Post by DonthDragon »

please can someone help me out on this.
My knowledge of mysql is quite limited

Thanks
Donovan
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: search query

Post by VladSun »

What have you tried so far? Post your queries.
There are 10 types of people in this world, those who understand binary and those who don't
DonthDragon
Forum Newbie
Posts: 14
Joined: Sun Jan 10, 2010 4:03 am

Re: search query

Post by DonthDragon »

my form goes to a "results.php" page.

This handles the search :
<?php
$area = $_POST['area'];
$category = $_POST['category'];
$max_guests = $_POST['max_guests'];

$db = mysql_connect(localhost,'root','****') or die("could not connect");
mysql_select_db('****') or die( "Unable to select database");

$query = "select * from property_profile where area='".mysql_real_escape_string($area)."' and category='".mysql_real_escape_string($category)."' and (property_profile.max_guests between '".mysql_real_escape_string($max_guests)."')";
$numresults = mysql_query($query) or die("Query error: ". mysql_error());
$num_rows = mysql_num_rows($numresults);
$page = 1;
$prev_page = $page - 1;
$next_page = $page +1;
$num_pages = ceil($num_rows / 2) -1;
$page_start = (2 * $page) - 2;
$query = "select * from property_profile where area='".mysql_real_escape_string($area)."' and category='".mysql_real_escape_string($category)."' and (property_profile.max_guests between '".mysql_real_escape_string($max_guests)."') LIMIT $page_start, 2";
$result = mysql_query($query) or die("Query error: ". mysql_error());
while($row = mysql_fetch_array($result)) {
$venue_name = $row["venue_name"];
$area = $row["area"];
$max_guests = $row["max_guests"];
$description = $row["description"];
$list_image = $row["list_image"];
$package_desc = $row["package_desc"];
?>
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: search query

Post by VladSun »

Your BETWEEN clause is not good, read the manual:
http://dev.mysql.com/doc/refman/5.0/en/ ... or_between

PS: And please post only SQL code in this forum section... (surrounded by [ syntax=sql]...[/syntax] BBCode tags)
There are 10 types of people in this world, those who understand binary and those who don't
DonthDragon
Forum Newbie
Posts: 14
Joined: Sun Jan 10, 2010 4:03 am

Re: search query

Post by DonthDragon »

Thanks.

I had a feeling the "between" was wrong.
Just looking at the link you gave me, I see there is something that might work. Its the : "<=" (Less than or equal operator)
How can I add that into my query, so that it looks for all "venues" that can hold the same amount or less than the "max_guests" (max. amount of guests the venue can hold)?

Ps.Sorry about the code. Will get it right next time.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: search query

Post by VladSun »

You put conditions in the WHERE clause by using AND or OR operators:

Code: Select all

SELECT
    *
FROM
    mytable
WHERE
    a > 10
    AND
    b <= 20
will select all records from mytable having values for column a greater than 10 *and* values for column b equal or less than 20.
There are 10 types of people in this world, those who understand binary and those who don't
DonthDragon
Forum Newbie
Posts: 14
Joined: Sun Jan 10, 2010 4:03 am

Re: search query

Post by DonthDragon »

Thanks for the help.
My problem though is that I wont know what amount the user will put in the input field. Could be any random number.

This is my search form :

Code: Select all

 
<form action="search_results.php" method="post">
					<fieldset>
						<legend>Search for a venue</legend>
						<label for="area">Area :</label>
						<select name="area">
							<option>Select an area</option>
							<option>Hartbeespoort Dam</option>
							<option>Muldersdrift</option>
						</select>
						<br /><br />
						<label for="category">What is the occasion for? :</label>
						<select name="category">
							<option>Select venue type</option>
							<option>Conference</option>
							<option>Wedding</option>
						</select>
						<br /><br />
						<label for="max_guests">Number of people attending :</label>
						<input type="text" name="max_guests" id="max_guests" size="4" />
						<input type="submit" name="submit" value="Search" id="submit" style="float:left;" />
					</fieldset>
				</form>
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: search query

Post by VladSun »

So, insert the user submitted values in your SQL queries as you did in your previous post.
There are 10 types of people in this world, those who understand binary and those who don't
DonthDragon
Forum Newbie
Posts: 14
Joined: Sun Jan 10, 2010 4:03 am

Re: search query

Post by DonthDragon »

I think I got it.
I just replaced the "between" with "<=".

thanks for all you help. Really appreciate it.

Donovan
Post Reply