Page 1 of 1

search query

Posted: Mon Jun 14, 2010 4:02 am
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

Re: search query

Posted: Mon Jun 14, 2010 5:39 am
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);

Re: search query

Posted: Tue Jun 15, 2010 12:52 am
by DonthDragon
Thats sounds like exactly what i need.
Thank you very much!

Re: search query

Posted: Tue Jun 15, 2010 1:49 am
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]

Re: search query

Posted: Tue Jun 15, 2010 5:59 am
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.)

Re: search query

Posted: Fri Jun 18, 2010 3:52 am
by DonthDragon
please can someone help me out on this.
My knowledge of mysql is quite limited

Thanks
Donovan

Re: search query

Posted: Mon Jun 21, 2010 6:19 am
by VladSun
What have you tried so far? Post your queries.

Re: search query

Posted: Mon Jun 21, 2010 11:22 am
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"];
?>

Re: search query

Posted: Mon Jun 21, 2010 1:05 pm
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)

Re: search query

Posted: Tue Jun 22, 2010 3:16 am
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.

Re: search query

Posted: Tue Jun 22, 2010 8:11 am
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.

Re: search query

Posted: Wed Jun 23, 2010 1:21 am
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>

Re: search query

Posted: Wed Jun 23, 2010 1:57 am
by VladSun
So, insert the user submitted values in your SQL queries as you did in your previous post.

Re: search query

Posted: Wed Jun 23, 2010 9:09 am
by DonthDragon
I think I got it.
I just replaced the "between" with "<=".

thanks for all you help. Really appreciate it.

Donovan