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