Topic: trying to cut down this code a bit and it's locking up my server

[spdwrench]

ok I have a bit of code that pulls members thumbnails from the database.

but the original code had alot more information being pulled ... All I need is the filename_1 pulled with the gender and the id

Code: [Select]

$search_query = "SELECT p.id, p.gender, p.name, p.general_info, m.age, f.filename_1 FROM (dt_profile p, dt_photos f, dt_members m, dt_privacy pr) WHERE (p.status='1' AND (pr.featured_yn IS NULL OR pr.featured_yn='') AND pr.member_id=m.id AND p.member_id=m.id AND f.member_id=m.id AND f.filename_1<>'' AND p.gender='$genders[name]') ";

//$search_query = "SELECT p.id, p.gender, f.filename_1 FROM (dt_profile p, dt_photos f) WHERE (p.status='1' AND f.filename_1<>'' AND p.gender='$genders[name]') ";


the second part that is commented out is what I tried to do to cut back the strain on the server but when I run the line that is commented out it seems to be stuck and I get no results.

is there a problem with the syntax in the second part that I have commented out in this code?

thanks for any help

Paul

[sdi126]

have you tried to run that second query inside the database itself to see if it returns any rows?

paste that sql statement in your query browser and let it run....it might be returning a bunch of records...which could be what appears to be "hanging"

[spdwrench]

well here is the code that is added to the end of that last bit

Code: [Select]

$search_query .= "ORDER BY RAND()";
        $search_query .= " LIMIT 1";
shouldnt this force it to return only one result???

Is my sytanx in the original post ok?

Paul

[recklessgeneral]

Just thought I'd chip in with another idea to explore - wondered whether the "p.member_id=m.id AND f.member_id=m.id" condition had anything to do with it. It seems to be the link between the two tables, although that would also mean having to include the members table in your query.

What I imagine would be happening is the database is attempting to return all combinations of <profile, photo> pairs without restricting the set to only those that make sense (i.e. represent member data). Try the following query:

Code: [Select]

$search_query = "SELECT p.id, p.gender, f.filename_1 FROM (dt_profile p, dt_photos f, dt_members m) WHERE (p.status='1' AND p.member_id=m.id AND f.member_id=m.id AND f.filename_1<>'' AND p.gender='$genders[name]') ";

Cheers,
Darren.

[fenway]

Post the EXPLAIN output.