[JiminSA]

I am using the following code to do a search on my db...

PHP Code:

        $element = $_REQUEST["element"];
        $criteria = trim($_REQUEST["criteria"]);

        $row = mysql_query("SELECT * FROM vehicles WHERE $element LIKE '%$criteria%'") or die(mysql_error()); 


and am getting this response

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

I cannot for the life of me see why - can you help?

[dukevyner]

I've seen this error before... But don't know why when I did

[dukevyner]

what's on line 1

[JiminSA]

Line 1 is the mysql query line...

[dukevyner]

can you post the full code? where did you specify the mysql_connect?

[JiminSA]

Connect...

PHP Code:

    function OpenDB()
    {
        global $result;

        $con = mysql_connect("localhost","surfwhee_jim","********************") or die(mysql_error());

        if (!$con)
        {
            die('Could not connect: ' . mysql_error());
        }
        
        mysql_select_db("surfwhee_vehicles", $con) or die(mysql_error());
    
    } 


(Password obscured)
This is the CMS compatible php code...

PHP Code:

    else if (isset($_REQUEST["action"]) && $_REQUEST["action"] == "do_search")
    {
        OpenDB();
        
        $element = $_REQUEST["element"];
        $criteria = trim($_REQUEST["criteria"]);

        $row = mysql_query("SELECT * FROM vehicles WHERE $element LIKE $criteria ORDER BY id") or die(mysql_error());

        BB_RedirectPage("success", "Search Results for " . $criteria . " in " . $element, array("action=managevehicles&sec_t=" . BB_CreateSecurityToken("managevehicles") . "&order=" . $order));
    }
    else if (isset($_REQUEST["action"]) && $_REQUEST["action"] == "search")
    {
        
        $criteria = "";
        $element = "";
        $contentopts = array(
            "desc" => "Search the Database for specific criteria.",
                "nonce" => "action",
                "hidden" => array(
                    "action" => "do_search",
                ),
                "fields" => array(
                "startrow",
                array(
                    "title" => "Search in...",
                    "type" => "select",
                    "name" => "element",
                    "options" => array("datetime" => "Date", "fullname" => "Name", "make" => "Make", "year" => "Year", "model" => "Model",),
                    "width" => "150px",
                    "value" => BB_GetValue("element", $element),
                    "desc" => ""
                ),
                array(
                    "title" => "Search for...",
                    "type" => "text",
                    "name" => "criteria",
                    "width" => "200px",
                    "value" => BB_GetValue("criteria", $criteria),
                    "desc" => ""
                ),
            ),
            "submit" => "Go",
            "focus" => true
        );

        BB_GeneratePage("Search For", $menuopts, $contentopts);

    } 


[JiminSA]

Since we last comm'd I have taken out the element variable by using switch and case viz...

PHP Code:

        $element = $_REQUEST["element"];
        $criteria = mysql_real_escape_string($_REQUEST["criteria"]);
        switch ($element)
        {
        case "datetime":
            $row = mysql_query("SELECT * FROM vehicles WHERE datetime LIKE '%$criteria%'") or die(mysql_error());
            break;
        case "fullname":
            $row = mysql_query("SELECT * FROM vehicles WHERE fullname LIKE '%$criteria%'") or die(mysql_error());
            break;
        case "make":
            $row = mysql_query("SELECT * FROM vehicles WHERE make LIKE '%$criteria%'") or die(mysql_error());
            break;
        case "year":
            $row = mysql_query("SELECT * FROM vehicles WHERE year LIKE '%$criteria%'") or die(mysql_error());
            break;
        case "suburb":
            $row = mysql_query("SELECT * FROM vehicles WHERE suburb LIKE '%$criteria%'") or die(mysql_error());
        } 


...but it still doesn't work

[JiminSA]

I found the problem! It was after the search in the display routine - using the wrong select variable...

But thanks for all your help Duke