Probably easy fix

[lamajlooc]

Hi,
I have a site where users register and display their wares with various attributes.  At the moment, I want results to be narrowed according to three attributes.  I have a script that reads through the DB and displays drop-down boxes with options taken only from the DB so there's no need for searching something that's not there.  For example it makes all the location options only from actual locations stored in the DB.

I want it to be possible to narrow based on one, two or all three atrributes.  The problem I'm having now is that if I narrow for one or two I get an error.  Three works fine.

Attribute 1 (***) is a number
Attribute 2 (---) is a string
Attribute 3 (+++) is a number

Here's the error if one or two are searched for:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY name' at line 1

And here's the code:
<?php
    if (isset($_POST['submit'])) {
        if (isset($_POST['***']) && !isset($_POST['---']) && !isset($_POST['+++'])) { // If *** searched for
            include ('../mysql_connect.php');
            $query = "SELECT username, name, website, phone, email FROM users WHERE *** = $_POST[***] ORDER BY name";
            $result = mysql_query($query) or die(mysql_error());
            while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
                $un = $row[0];
                $n = $row[1];
                $w = $row[2];
                $p = $row[3];
                $e = $row[4];
                $query = "SELECT file_name FROM uploads WHERE username = '$un'";
                $result = mysql_query($query) or die(mysql_error());
                $found = FALSE;
                while ($rr = mysql_fetch_array($result, MYSQL_NUM)) {
                    $file = $rr[0];
                    echo "<table border=\"0\"><tr><td class=\"competing_im***s\">";
                    echo "<a href=\"" . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']) . "users/$un/\"><img border=\"0\" src=\"./uploads/thumbs/th_$file\" /></a>";
                    echo "</td><td class=\"competing_im***s\" style=\"text-align:left;\">";
                    echo "$n<br />$w<br />$p<br />$e";
                    echo "</td></tr></table>";
                    $found = TRUE;
                }
                if ($found <> TRUE) {
                    echo "Sorry, could not find any search terms.";
                }
            }
            mysql_close();
        } elseif (isset($_POST['***']) && isset($_POST['---']) && !isset($_POST['+++'])) { // If *** and --- are searched for
            include ('../mysql_connect.php');
            $query = "SELECT username, name, website, phone, email FROM users WHERE *** = $_POST[***] AND --- = '$_POST[---]' ORDER BY name";
            $result = mysql_query($query) or die(mysql_error());
            while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
                $un = $row[0];
                $n = $row[1];
                $w = $row[2];
                $p = $row[3];
                $e = $row[4];
                $query = "SELECT file_name FROM uploads WHERE username = '$un'";
                $result = mysql_query($query) or die(mysql_error());
                $found = FALSE;
                while ($rr = mysql_fetch_array($result, MYSQL_NUM)) {
                    $file = $rr[0];
                    echo "<table border=\"0\"><tr><td class=\"competing_im***s\">";
                    echo "<a href=\"" . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']) . "users/$un/\"><img border=\"0\" src=\"./uploads/thumbs/th_$file\" /></a>";
                    echo "</td><td class=\"competing_im***s\" style=\"text-align:left;\">";
                    echo "$n<br />$w<br />$p<br />$e";
                    echo "</td></tr></table>";
                    $found = TRUE;
                }
                if ($found <> TRUE) {
                    echo "Sorry, could not find any search terms.";
                }
            }
            mysql_close();
        } elseif (isset($_POST['***']) && isset($_POST['---']) && isset($_POST['+++'])) { // If all are searched for
            include ('../mysql_connect.php');
            $query = "SELECT username, name, website, phone, email FROM users WHERE *** = $_POST[***] AND --- = '$_POST[---]' AND +++ = $_POST[+++] ORDER BY name";
            $result = mysql_query($query) or die(mysql_error());
            while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
                $un = $row[0];
                $n = $row[1];
                $w = $row[2];
                $p = $row[3];
                $e = $row[4];
                $query = "SELECT file_name FROM uploads WHERE username = '$un'";
                $result = mysql_query($query) or die(mysql_error());
                $found = FALSE;
                while ($rr = mysql_fetch_array($result, MYSQL_NUM)) {
                    $file = $rr[0];
                    echo "<table border=\"0\"><tr><td class=\"competing_im***s\">";
                    echo "<a href=\"" . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']) . "users/$un/\"><img border=\"0\" src=\"./uploads/thumbs/th_$file\" /></a>";
                    echo "</td><td class=\"competing_im***s\" style=\"text-align:left;\">";
                    echo "$n<br />$w<br />$p<br />$e";
                    echo "</td></tr></table>";
                    $found = TRUE;
                }
                if ($found <> TRUE) {
                    echo "Sorry, could not find any search terms.";
                }
            }
        } else {
            echo "You must search all terms.";
        }
    } else {
        echo "No search submitted!";
    }
    mysql_close();
?>

[btherl]

Try this for your queries:

[code=php:0]$result = mysql_query($query) or die("Error in $query: " . mysql_error());[/code]


Then we can see the actual mysql statement which had the syntax error.  After that, solving it should be easy :)

[lamajlooc]

Good idea.  I did that and it said the error was with the "ORDER BY name" so I took it out and now I get:

Error in SELECT username, name, website, phone, email FROM users WHERE age = 20 AND race = '' AND zip = : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

So I took out the ' quotes around '$_POST[---]' in the query and I got an error with +++ attribute in the third if clause.  Could it be a problem with my if statements?  Can you write if(isset(blah) && !isset(blah)) { ??

Thanks for your help.

[bljepp69]

Try formatting your SELECT statement like:

[code]
SELECT username, name, website, phone, email FROM users WHERE *** = {$_POST[***]} AND --- = '{$_POST[---]}' AND +++ = {$_POST[+++]} ORDER BY name
[/code]
Note the {} around the POST variables.  If you have something like $_POST['number'], the single quotes around 'number' will screw up the parsing of the SELECT statement.  Putting the {} around the variable tell PHP where the variable starts and ends.