[RESOLVED] Ordering two searches

devsie

I have a query, which roughly works like....

Search1. uses the users postcode/zipcode and finds the coords for it from table1.

Search2. Then searchs for any postcodes within +/- 10 degrees of those coords from table1.

//this lets me know all the postcodes nearby to the one the user inputted.

Search3. Then search table 2 for anyone with any of those postcodes which also matches another criteria (that is dates they are free)

It's set up in a while loop to process things, however this returns everything in order of Search 2 whereas i'd like to order things by a value in Search3. I did try swapping those 2 searchs around, but got into a bit of a mess. Ok, ok a big mess.

Can anyone help me get things so they can be returned according to an order from 'search3'.

Current code - abridged.

//search1
$result = mysql_query("SELECT postcode1, lon, latt FROM postcode WHERE postcode1='$postcode1'")

//search2
$result2 = mysql_query("SELECT * FROM postcode WHERE lon BETWEEN {$lonMIN} AND {$lonMAX} AND latt BETWEEN {$latMIN} AND {$latMAX}") or die(mysql_error());

// see if any rows were returned
if (mysql_num_rows($result2) > 0) {

//Start a while loop for any results in database with postcode between those from search2
while($row2 = mysql_fetch_row($result2)) {

//For each of the postcodes returned from proximity search, check to see if date matches

//row2[0]= postcode from search
$result3 = mysql_query("SELECT * FROM co_details WHERE postcode1 = '$row2[0]' AND (date0 = '$sqldate' OR date1 = '$sqldate' OR date2='$sqldate')") or die(mysql_error());

	while($row3 = mysql_fetch_array($result3))
		{

//Print basic details for companies which match the area specified and the date required.
echo ucwords($row3['coname']);
}

//end while
}
//end 'no results' if.
}

Thank-you

tomhath

Seems like you should be able to do it with just one query. Play around with this:

SELECT distinct co_details.* 
FROM postcode p1
INNER JOIN postcode p2
  ON  p2.lon  BETWEEN p1.lon - 10.0  AND p1.lon + 10
  AND p2.latt BETWEEN p1.latt - 10.0 AND p1.latt + 10
INNER JOIN co_details cd
  ON cd.postcode1 = p2.postcode1
WHERE p1.postcode1='$postcode1'
 AND (cd.date0 = '$sqldate' OR cd.date1 = '$sqldate' OR cd.date2='$sqldate')

devsie

Thank-you for your reply.

And gulp.

Perhaps I should have mentioned i read my first bit of mysql and php about a month back now and i'm still kinda n00b to it all. 🙂
I'll try and read up some on "distinct" and get my head around "joins" better.

Thanks again

devsie

Ignore 🙂

devsie

oooo, i think i got there.

$query = "SELECT distinct *
FROM co_details
INNER JOIN postcode p2
ON p2.lon BETWEEN $lonMIN AND $lonMAX
AND p2.latt BETWEEN $latMIN AND $latMAX
INNER JOIN postcode
ON co_details.postcode1 = p2.postcode1
WHERE postcode.postcode1='$postcode1'
ORDER BY co_details.dj
" ;

Seems to be working. Thank-you again 🙂

Just one question about dealing with the resultsl though. I was using this just to debug/read the results...

$result1 = mysql_query($query);

while($row1 = mysql_fetch_array($result1))
{ echo "Name: $row1[0] and postcode ".$row1['postcode1']." and date joined ".$row1['dj']."<br><br>";}

However, the $row1['postcode1'] does not seem to return the 'postcode1' value from the co_details table for that particular company.

tomhath

The "SELECT DISTINCT *" doesn't tell MySql which of the three tables you want to pull postcode1 from. It's better to list all the columns you want returned, qualified by table name:

SELECT DISTINCT co_details.co_name, co_details.postcode1, co_details.dj
FROM ...

devsie

Thankyou again.

1 last query, if i may.

If the "where date=" clause was actualy for dates held in a different table.

This seems to have been working...

$query = "SELECT DISTINCT co_details.*
FROM co_details
INNER JOIN postcode p2
ON p2.lon BETWEEN $lonMIN AND $lonMAX
AND p2.latt BETWEEN $latMIN AND $latMAX
INNER JOIN postcode
ON co_details.postcode1 = p2.postcode1
WHERE postcode.postcode1='$postcode1'
AND (co_details.date0 = '$sqldate' OR co_details.date1 = '$sqldate')
ORDER BY co_details.dj
" ;

If the date0=$sqldate was in a different table then I can't just add in "mydatetable.date0=$sqldate"

I'd need to do another "join". The tables are linked by $co_name.
e.g.
$query = "SELECT DISTINCT co_details.*
FROM co_details
INNER JOIN postcode p2
ON p2.lon BETWEEN $lonMIN AND $lonMAX
AND p2.latt BETWEEN $latMIN AND $latMAX
INNER JOIN postcode
ON co_details.postcode1 = p2.postcode1
WHERE postcode.postcode1='$postcode1'
INNER JOIN mydatetable
ON co_details.co_name = mydatetable.co_name
WHERE (mydatetable.date0 = '$sqldate' OR mydatetable.date1 = '$sqldate')
ORDER BY co_details.dj
" ;

?
🙂

devsie

Nm. I see, join then do where at end. makes sense.

All sorted. 🙂