Page 1 of 1
In trying to generate pages for results...
Posted: Sun Oct 12, 2008 2:09 pm
by omika
Hi all!
I need to somehow get the in array function into the mysql query along with the price from, price to etc so that I can limit the results and do the pagination. If anyone could help that would be great.
Code: Select all
if ($ssuburb!='')
{
$query .= " suburb = '".$ssuburb."' AND";
}
if ($sbedrooms!='')
{
if ($sbedrooms!='5')
{
$query .= " bedrooms = '".$sbedrooms."' AND";
}
else
{
$query .= " bedrooms >= '".$sbedrooms."' AND";
}
}
if ($sbathrooms!='')
{
if ($sbathrooms!='5')
{
$query .= " bathrooms = '".$sbathrooms."' AND";
}
else
{
$query .= " bathrooms >= '".$sbathrooms."' AND";
}
}
$ret = "SELECT * FROM listings WHERE ".$query ." id != '' ORDER BY $order";
//echo $ret;
$result = mysql_query($ret) or die("Query failed : " . mysql_error() . mysql_errno());
$i = 0;
while ($listing = mysql_fetch_array($result))
{
$array= explode(',', $listing['category']);
if (in_array($spropertytype, $array) && $listing['price'] >= $spricefrom && $listing['price'] <= $spriceto || $spropertytype == '' && $listing['price'] >= $spricefrom && $listing['price'] <= $spriceto)
{
RESULTS HERE
$y='list'; //A listing has been made ... This tells the if below to display the no result text
}
if (!in_array($spropertytype, $array) && $i == '0' || $y != 'list')
{
echo "<center>Unfortunately your search returned no listings</center>";
}
}
Re: In trying to generate pages for results...
Posted: Mon Oct 13, 2008 3:50 am
by omika
Problem solved!
It seems that LIKE with a wild card on both sides can pluck the value out of a csv stored in a field.
For reference here is the new code:
Code: Select all
if ($ssuburb!=''){
$query .= " suburb = '".$ssuburb."' AND";}
if ($sbedrooms!=''){if ($sbedrooms!='5'){
$query .= " bedrooms = '".$sbedrooms."' AND"; }else{
$query .= " bedrooms >= '".$sbedrooms."' AND";}
}if ($sbathrooms!=''){ if ($sbathrooms!='5'){
$query .= " bathrooms = '".$sbathrooms."' AND";} else {
$query .= " bathrooms >= '".$sbathrooms."' AND";}}
$ret = "SELECT * FROM listings WHERE price BETWEEN '".$spricefrom."' AND '".$spriceto."' AND category LIKE '%".$spropertytype."%' AND ".$query ." id != '' ORDER BY $order";
$result = mysql_query($ret) or die("Query failed : " . mysql_error() . mysql_errno());
$i = 0;
while ($listing = mysql_fetch_array($result))
{
RESULTS HERE
$y='list'; //A listing has been made
}
if ($y != 'list')
{
echo "<center>Unfortunately your search returned no listings</center>";
}
Re: In trying to generate pages for results...
Posted: Mon Oct 13, 2008 3:53 am
by onion2k
You should probably be storing category data in a separate table...
Re: In trying to generate pages for results...
Posted: Mon Oct 13, 2008 4:12 am
by omika
They are in a separate table but each listing can be in multiple categories and categories can be added or removed. So it gets kind of busy if I do it all in a separate table but I suppose it depends on how many categories there are. I'm sure no one would want more than 10 property categories.
Also its really easy to generate the check boxes and store the data without joining table queries.
Generates check boxes from the category field. And checks it if its in the stored array.
Code: Select all
$getcat = mysql_query("Select * from categories");
$array= explode(',', $rowe['category']);
while($crow = mysql_fetch_array($getcat))
{
echo "<input type='checkbox' name='category[]' value='".$crow['category']."' ";
if (in_array($crow['category'], $array)){echo "checked";}
echo "/>".$crow['category']."<br>";
}
Storing the array of check boxes.
Code: Select all
$category = implode(',', $_POST['category']); //Converts an array into a single string
//Insert query here
Re: In trying to generate pages for results...
Posted: Mon Oct 13, 2008 5:25 am
by onion2k
If you put the category data into a separate table you can still pull it into an array when you need to display the list of categories.
Anyway.. When you're doing "category LIKE '%".$spropertytype."%'" ... If you're just using an int won't a search for all the records in category '3' also bring back everything in 13, 23, 30, 31, 32, 33, etc? You'll need to use a zero padding int or a string. If you've planned for 100 categories (eg 2 digits), then you'll need a varchar(20) to store 10 categories (or varchar(29) if you're comma separating them). And as soon as someone wants 11 categories for a record the code will break down. Worse still, if someone decides there needs to be 101 different categories you'll have a hell of a time recoding all the category management stuff, plus you'll have to write a script that takes all the zero padded ints and adds another 0 to the start of them all in every record.
There's a good reason why many-to-many relationships are stored in a separate table. A couple of hours more time spent on it now will save you loads of hassle in the future.
EDIT: Just to clarify.. I'm talking about something like this:

Re: In trying to generate pages for results...
Posted: Mon Oct 13, 2008 2:15 pm
by omika
Here is the URL
http://websitequote.co.nz/real/index.php
The navigation on the left hand side represents the categories they are stored in a separate table. I do understand your ERD, but an INT will not be used just a string. Imagine 100 check boxes in the edit listing page!
A listing has the
option of going in many categories. It's only going to go into 3 or 4 max.
Say if its a residential property that's an investment also and the agent wanted it in the feature page. Residential,Investment,Featured is the only information that would be stored.
You are right, if someone had a category Invest and then Investment, it wouldn't work.
I will be looking into it further...
Thanks
Re: In trying to generate pages for results...
Posted: Mon Oct 13, 2008 2:55 pm
by onion2k
In my experience if you give the user an option they will eventually use it even if you think it's unlikely. If an agent can put a property in 100 categories then, at some point, they will. Even if it's just them playing around with (eg testing) the system. If it breaks then you and your company look bad.