A couple of SQL questions

furious5

I was wondering if you guys can help me, I've got a couple of SQL related questions.

Basically as part of a search I am currently building, I want disregard any records which contain a 0, off the top of my head I could have sworn the SQL for this is '! whatever value'. However my SQL statement is falling over at this. Any ideas?

Secondly, one table has 52 fields, labelled week1, week2, week3 etc, and the same for prices price1, price2, price3 etc. My question is how do I create a search which will iterate through each of these. At the moment my SQL statement looks something like this:

SELECT * FROM villas, pricing WHERE villas.resort = 'menorca' AND pricing.week1 = '02-01-04' WHERE price1 != '0'

Would I need to use a sub query to iterate through these prior to moving on to the next record? 😕

bubblenut

You have already started your where condition. Replace your second "WHERE" with an "AND"
HTH
Bubble

furious5

Spot on, I hadn't spotted that the other day. I only just came back to the code and simply posted this message straight away. Thank you.

Anyone got any ideas about the second part of my question??

furious5

The only way to acheive this off the top my head is enclosing the statement within a for loop like so:

// Iterate through and build the code on the fly, dependent upon search

for($i; $i < 53; $i++){ // Start For Loop

$sql = "SELECT * FROM villas, pricing WHERE";
$sql_where = "";

if(isset($resort)){ // Resort

	if($sql_where != "") { 
			$sql_where = $sql_where . "AND "; 
	} 
			$sql_where = $sql_where . " villas.resort = '" . $resort_details[$resort] . "' "; 
}

if(isset($sleeps)){ // Sleeps

	if($sleeps == "%"){
			if($sql_where != "") { 
			$sql_where = $sql_where . "AND "; 
			} 
			$sql_where = $sql_where . " villas.beds LIKE '%%' "; 
			}

	} else {

	if($sql_where != "") { 
			$sql_where = $sql_where . "AND "; 
	} 
			$sql_where = $sql_where . " villas.beds = '" . $sleeps . "' "; 
}

if(isset($days)){ // Days
	$date_temp = date('d-m-y', strtotime("$date + $day_calculate[$days]"));


	if($sql_where != "") { // Pricing 
			$sql_where = $sql_where . "AND "; 
	} 
			$sql_where = $sql_where . " pricing.week" .$i. " = '" . $date . "' ";

}

$sql_where .= "AND price" .$i. " != '0'";

$sql = $sql.$sql_where; // Combine SQL and SQL WHERE strings

The only problem being I have hooked it up to the database, and the query times out given how long it is taking to process.

Surely there must be a more efficient way to do this, as it is currently processing the SQL statement, 52 x 290 records
:eek:

bubblenut

Could you not do
SELECT * FROM villas, pricing WHERE villas.resort = 'menorca' AND pricing.week1 = '02-01-04' AND price1 > '0' AND price1 < 54 ORDER BY price1

Then you would have all items with a price between 0 and 54 on order of their price. Is that what you want?
Bubble

furious5

The price itself isn't really an issue at the moment, all I want to do is exclude any records which are set to 0 as these are not available. Therefore any records with actual prices in are available.

My problem is having the flexibility to traverse through each of the week fields i.e week1, week2, week3 to match the date passed from the search form, whilst still referencing the same Recordset ID.

Initially the query was timing out, so I've edited my PHP.ini to take off the timeout, the script has now ran but the results it is returning at the moment are all over the shop.

At the moment it seems to have returned the correct ID's for the resort, but each of the Villa names is duplicated for each recordset e.g

17 - Ca’n Toni
17 - Villa Mosson
17 - Villa Camp del Bosch

50 - Ca’n Toni
50 - Villa Mosson
50 - Villa Camp del Bosch

furious5

Had a bit more of a think about this, and I reckon sub queries are probably the way to go. Except I haven't used them before.

I've put together the following simple script just to test things, but my SQL is falling over.

Anyone any ideas?

 $sql = "SELECT  DISTINCT * FROM villas WHERE ";

$i = "1";
$date = "03-01-04";

for($i; $i < 53; $i++){
	$sql_where = "(SELECT * FROM pricing WHERE week" .$i. " = '" . $date . "' AND price" .$i. "!= '0')";

	$sql = $sql.$sql_where;
	echo $sql;
	echo "<p>"; 
}

ahundiak

What exactly is the link between villas and pricing? It seems to me you are trying to retrieve two distinct record sets (villas and pricing) with one query. And that's just no going to work well. Using DISTINCT will probably not help as you will get one villa records for each pricing record that matches the search criteria.

Is there a link between villas and pricing i.e. does pricing have a villia_id field in it?

And by the way, anytime you find yourself repeating the same column a bunch of times week1, week2 etc) you shoud consider changing your database schema. It will probably be much easier to have 52 records in your pricing table instead of 52 columns.

furious5

Sorry I neglected to say that both share the same field e.g villa_id.

I agree that the way this has been coded is cumbersome, but I couldn't think of a better way to do this, as the user want's to sit down and input all the dates and prices in one go.

The only thing I could think to simplify this is to create a table named pricing with:

villa_id
week
price

From here, all the info entered in the form is parsed in to an array and then iterate through this and INSERT the values on the fly?

ahundiak

Changing your pricing table to:
price_id
villa_id
week
price

Is the best long term solution. You will want a unique price_id field to make updating it easier. Remember to create an index on week to speed up the queries.