SQL Help

[furious5]

I'm currently finishing off writing an availability search.

Everything is working apart from my final SQL statement.

Basically I have a table named pricing1, which contains price_id, id, week, price

I am trying to search through for villa's available over a number of dates which are fixed in weekly periods.

At present the SQL I am trying to execute is:

SELECT * FROM villas, pricing1 WHERE villas.resort = 'pollenca' AND villas.beds LIKE '%%' AND pricing1.week = '03-01-04' AND pricing1.week = '17-01-04' AND pricing1.price != '0' AND villas.id = pricing1.id

This does not return any searches although if I search another way it returns results e.g:

SELECT * FROM villas, pricing1 WHERE villas.resort = 'pollenca' AND villas.beds LIKE '%%' AND pricing1.week = '03-01-04' AND pricing1.price != '0' AND villas.id = pricing1.id

I know their is a bug in the top code fragment but I'm struggling with this at the moment. Anyone??

I thought by using AND to search under the two dates, this would be sufficient?

[Shrike]

AND pricing1.week = '03-01-04' AND pricing1.week = '17-01-04'

Surely that's not going to give you any results 😉

[furious5]

Exactly it isn't giving me any results!!

My question is how can I get round this, I had thought about using between but this just seems to time out.

Any ideas?

[Shrike]

What is the actual data in pricing1.week?

[furious5]

Each week has a date in, e.g 03-01-04, 10-01-04, 17-01-04

Therefore each id relates to an individual week's pricing for a villa.

[Shrike]

You really need to be using some kind of string that you can process with < and > operators, timestamps being the most obvious.

To go with what you have got, select all of the rows, then while iterating through the result set, compress the date into a string which you can do operations on.

For example 1st Feb 2003 would be 01-02-03 in your database. Make this 030201 (reversed) and then you can do easy comparisons.

$sql = "SELECT * FROM villas, pricing1 WHERE villas.resort = 'pollenca' AND villas.beds LIKE '%%'  AND pricing1.price != '0' AND villas.id = pricing1.id";
$result = mysql_query($sql);
$okrows = array();
$while($row = mysql_fetch_array($result)){
  if($startdate <= substr($row['week'], -2).substr($row['week'], 4,2).substr($row['week'], 2) && $enddate >= substr($row['week'], -2).substr($row['week'], 4,2).substr($row['week'], 2) ){
    array_push($okdates, $row);
  }
}
print_r($okrows);// these dates are within $startdate and $enddate

$startdate and $enddate would already have to be in the correct format e.g yymmdd

Sorry for any parsing errors 🙂

[furious5]

I'm still struggling with this, the SQL seemed to time out everytime I have tried to run the code.

I have gone back and rewritten a function to calculate the date in to a timestamp, but still need some guidance.

Can anyone help:

$date = "03-01-04";
$temp = "14";

//###############################################################################//
//###############################################################################//

function DateCalculation($date, $temp){ // Function to deal with date calculation

$datearray = split("-", $date); // Split the date, and place in an array
	foreach($datearray as $item)

$days = array(7 => '+7 days', 14 => '+14 days'); // Days Array

$temp1= mktime(0,0,0,$datearray[1],$datearray[0],$datearray[2]); // Create Timestamp for Deconstructed String
$temp2= mktime($temp); // Create Timestamp for Days Variable

$dateout = strtotime($days[$temp], $temp1); // Convert the string to a timestamp
$datefinished = strftime("%d-%m-%y" ,$dateout); // Convert the timestamp to a valid date

$final = array($temp1,$dateout); // Array to combine upper and lower date timestamps

return ($final); // Return the final date

}

//###############################################################################//
//###############################################################################//

$date1 = DateCalculation($date, $temp);

print_r($date1);



$sql = "SELECT * FROM villas, pricing2 WHERE villas.resort = 'pollenca' AND villas.beds LIKE '%%' AND pricing2.price != '0' AND pricing2.availability = '1' AND pricing2.week <= '$date1[0]' AND pricing2.week >= '$date1[1]' AND villas.id = pricing2.id ";

echo $sql;

[Shrike]

I take it that the exit date is calculated by adding $days[$temp] to $date. You don't need to break down the date as you have done, strtotime() can handle alot of different date formats.

$date1 = strtotime($date);//start date
$date2 = strtotime($days[$temp], $date);//end date - if this works then groovy

(read up in the manual on the date formats which strtotime() will accept) and then your SQL string will simply be

pricing2.week >= $date1 AND pricing2.week <= $date2

Remember the database dates need to be timestamps too. The goal is to create values which can be mathematically compared. Thats all. 🙂

[furious5]

Thats more like it.

Seemed very long winded the other way!

My only problem now is that it returns the same villa, twice given that it is available on both dates.

[furious5]

Any ideas how best to handle the results once I have returned the SQL statement?

At the moment I am getting duplicate entries e.g Villa 9 as these are both available over the time frame I am search.

[Shrike]

Not quite sure what you mean - can you elaborate a little?

[furious5]

At the moment if I run the following SQL:

SELECT * FROM villas, pricing1 WHERE villas.resort = 'pollenca' AND villas.beds LIKE '%%' AND AND pricing2.week >= 1073088000 AND pricing2.week <= 1074297600 AND pricing1.price != '0' AND pricing1.availability = '1' AND villas.id = pricing1.id

This itself matches 1 record, villa id 9. However the way the search is being ran, it matches the ID twice (available on both dates) and thus when you iterate through the results using a while loop it prints out the same record, villa 9 twice.

Basically I am wondering how this can be refined.

So only 1 record is returned when two of the same record or matched.

[Shrike]

I think I get what you mean...

$sql = "sql stuff...";
$result = mysql_query($sql);
while($row = mysql_fetch_assoc($result)){
  // Any duplication _should_ overwrite the previous instance
  $rows[$row['id']] = $row;
}
foreach($rows as $row){
  print_r($row);
}