[RESOLVED] any thoughts on my query statement and what might be wrong?

[pheck]

I cannot for the life of me figure out how to get the logic right on this query:

www.poadocs.com/site/testimonials#review

You can see what I’m trying to do here.

And here’s my query:

$sql = "SELECT doc, joint, sex, testimonial, firstName, lastName, showName FROM $table WHERE $theseDocs && $theseOpSites && $theseSexes ORDER BY doc";

But it isn’t working. When I change the && to OR it works displays results but is incorrect (displays results it shouldn’t).

Here’s how I build my query. I wasn’t sure if I needed the % or not in front of everything but it seems to work both ways:

//which docs selected?

        $theseDocs = "(";

        for($i = 0; $i < sizeof($doc); $i++) {

                    if($i > 0) {

                                $theseDocs .= ' OR ';

                    }

                    $theseDocs .= "(doc LIKE '%$doc[$i]%')";

        }

        $theseDocs .= ")";

        //which op sites selected?

        $theseOpSites = "(";

        $i=0;

        for($i = 0; $i < sizeof($opSite); $i++) {

                    if($i > 0) {

                                $theseOpSites .= ' OR ';

                    }

                    $theseOpSites .= "(joint LIKE '%$opSite[$i]%')";

        }

        $theseOpSites .= ")";

        //which sex?

        if($sex == "Both") {

                    $theseSexes = "sex LIKE '%Male%' OR sex LIKE '%Female%'";

        } else if ($sex == "Male") {

                    $theseSexes = "sex LIKE '%Male%'";

        } else {

                    $theseSexes = "sex LIKE '%Female%'";

        }[/QUOTE]

[NogDog]

Have you tried doing an echo of $sql after you define it to see what it ends up as? (You could even cut-and-paste it into your database command line (or phpMyAdmin SQL screen) to see what it does.)

[pheck]

thanks for the reply.

when i echo it out as you suggest, when i select everything (all docs and all joints with both sexes) i get the following which seems right to me at first glance but maybe you can point me in the right direction?:

SELECT doc, joint, sex, testimonial, firstName, lastName, showName FROM testimonials WHERE ((doc LIKE '%barber%') OR (doc LIKE '%conrad%') OR (doc LIKE '%fernandez%') OR (doc LIKE '%khan%') OR (doc LIKE '%reyes%')) && ((joint LIKE '%general%') OR (joint LIKE '%neck%') OR (joint LIKE '%armElbow%') OR (joint LIKE '%hand%') OR (joint LIKE '%knee%') OR (joint LIKE '%shoulder%') OR (joint LIKE '%spine%') OR (joint LIKE '%hip%') OR (joint LIKE '%footLeg%')) && sex LIKE '%Male%' OR sex LIKE '%Female%' ORDER BY doc

[HalfaBee]

Put brackets around the sex selection

&& (sex LIKE '%Male%' OR sex LIKE '%Female%') ORDER BY doc

[pheck]

Thanks tried that but it didn't return any results still.

[HalfaBee]

When you build your query, you should check if any boxes are clicked and not put the () in.

This might help in debugging the problem.

[pheck]

Thanks HalfaBee, I've done that but I'm still left scratching the head 🙂 Any thoughts on another approach? Maybe some sort of join (but then again I'm only dealing with one table here)? How about a UNION approach?

[pheck]

Any other thoughts? I'm still stumped.

[HalfaBee]

Why don't you echo out the query so we can see what is being created.

[bpat1434]

pheck: Take the echoed out query, go into phpmyadmin or mysql Query Browser and run that SQL on the database. See if in fact mySQL even finds any rows. It could be that your SQL is just too restrictive and no rows match all your constraints.

[pheck]

got it! my joint parameter was messed up. i had it trying to pull armElbow instead of Arm-Elbow. once i got the vars named and matched up right, the query works! thanks for the help.

[bpat1434]

Don't forget to mark this as resolved if it is.