search query
Moderator: General Moderators
-
DonthDragon
- Forum Newbie
- Posts: 14
- Joined: Sun Jan 10, 2010 4:03 am
search query
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
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
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
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
Thats sounds like exactly what i need.
Thank you very much!
Thank you very much!
Re: search query
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]
[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
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.)
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
please can someone help me out on this.
My knowledge of mysql is quite limited
Thanks
Donovan
My knowledge of mysql is quite limited
Thanks
Donovan
Re: search query
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
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"];
?>
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
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)
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
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.
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
You put conditions in the WHERE clause by using AND or OR operators:
will select all records from mytable having values for column a greater than 10 *and* values for column b equal or less than 20.
Code: Select all
SELECT
*
FROM
mytable
WHERE
a > 10
AND
b <= 20There 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
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 :
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
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
I think I got it.
I just replaced the "between" with "<=".
thanks for all you help. Really appreciate it.
Donovan
I just replaced the "between" with "<=".
thanks for all you help. Really appreciate it.
Donovan