looking for a more efficient way to code this query

[OwenTheSamoan]

Here is an abbreviated version of a query I am using. The code works well with no problems accept every now and then my database almost comes to a grinding halt from it. Is there a more efficient way of streamlining this code or doing it differently?


$query = "SELECT distinct a.username, a.sex, a.state, a.age, a.height, a.ethnicity, a.haircolor, a.last_login FROM profile as a, images as b WHERE";

$query .= " (a.sex in ('$sex'))";

if($state != All)
$query .= " AND (a.state in ('$state'))";

$query .= " AND (a.age between '$age1' and '$age2')";

$query .= " AND(a.height between '$height1' and '$height2')";


if(!empty($ethnicity1) or !empty($ethnicity2) or !empty($ethnicity3) or !empty($ethnicity4) or !empty($ethnicity5) or !empty($ethnicity6) or !empty($ethnicity7) or !empty($ethnicity8))
$query .= " AND (a.ethnicity in ('$ethnicity1 ','$ethnicity2 ','$ethnicity3 ','$ethnicity4 ','$ethnicity5 ','$ethnicity6 ','$ethnicity7 ','$ethnicity8','$ethnicity9'))";


if(!empty($haircolor1) or !empty($haircolor2) or !empty($haircolor3) or !empty($haircolor4) or !empty($haircolor5) or !empty($haircolor6) or !empty($haircolor7))
$query .= " AND (a.haircolor in ('$haircolor1 ','$haircolor2 ','$haircolor3 ','$haircolor4 ','$haircolor5 ','$haircolor6 ','$haircolor7 '))";


if(!empty($photo))
$query .=" AND a.username = b.username AND ( b.img_id != 0)";


$query .= " order by last_login desc";


Thanks,
Owen The Samoan

[highphilosopher]

that is a long query instead of going with all the where's and since it's a distinct query consider setting a unique identifier on the tables such as the username. If one of these is unique then you can where search by them instead of going off everything

[travelbuff]

can you post the table structures?

[bubblenut]

First off could you please remember to use [php[[/php] tags, it makes the code so much easier to read.
A example query would be helpfull as well here. I have spotted one thing which will be causing problems, the way you're joining to the images table. Here's an example query I've knocked together from your example (minus some of the unnecesarry brackets).

SELECT distinct a.username, a.sex, a.state, a.age, a.height, a.ethnicity, a.haircolor, a.last_login
FROM profile as a, images as b
WHERE a.sex in ('M')
AND a.age between 21 and 25
order by last_login desc

Now, in this case we have a user without an image. You state that you're querying the images table but you never goin to it. This means that for every row returned from profile, it could be any one of the rows from images, so the database returns every one. So, in short, when the case is such that $photo is empty, then it's going to go a little screwy. There are two ways around this that I can think of. First, you can use the same condition to decide whether or not to look at the images table at all. This is untidy and escpecially bad form when SQL provides a way around this. That is the left join. A left join says "If there are matching entries in table 2 return them, if there aren't still return the matching row from table 1 just return NULL for the table 2 fields.". To implement this the example query I used above would look like this.

SELECT distinct a.username, a.sex, a.state, a.age, a.height, a.ethnicity, a.haircolor, a.last_login
FROM profile as a
LEFT JOIN images as b on (a.username = b.username AND b.img_id != 0)
WHERE a.sex in ('M')
AND a.age between 21 and 25
order by last_login desc