Page 1 of 1

Search form help(error)

Posted: Sat Jul 13, 2013 7:07 pm
by nite4000
Hello everyone. I have a serach form that searches tables (4 to be exact) and anyway when I try to search I get a error. I didn't write this code however its a issue with the dates I believe

each table has a user_id in the table and date fields in the table as well. The code is below

The error I get is this : 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 'AND date >= '2013-01-01' AND date <= '2013-08-01'' at line 4

line for of course is a db file but I thinks its a issue with the code I put in bold below

Code: Select all

$q = mysql_query("SELECT * FROM members") or die(mysql_error());
  $mem = mysql_fetch_array($q, MYSQL_ASSOC);
  @mysql_free_result($q);


$search = array('deposits' => '',
    'withdrawals' => '',
	'shares' => '',
	'transfers' => '');
$resultss = array('deposits' => '',
    'withdrawals' => '',
	'shares' => '',
	'transfers' => '');

/*
 * @TODO: Generate dummy data to test searches
 */
if (!empty($_POST))
{
    $start_date = $_POST['f_year'] . "-" . $_POST['f_month'] . "-" . $_POST['f_day'];
    $end_date = $_POST['t_year'] . "-" . $_POST['t_month'] . "-" . $_POST['t_day'];
 [b]   $date_sql = " AND date >= '$start_date' AND date <= '$end_date'";[/b]
    if ($_POST['processor'] != "any")
    {
        $processor = mysql_real_escape_string($_POST['processor']);
    }
    if (!empty($_POST['trans_id']))
    {
        $transaction = mysql_real_escape_string($_POST['trans_id']);
    }

    /*
     * If a type isn't specified, we need multiple queries across multiple
     * tables
     */
    if ($_POST['type'] == "any")
    {
        foreach ($search as $k => $v)
        {
            $table = $k;
            $search[$k] = "SELECT *
                           FROM $table
                           WHERE user_id = '".$mem['id']."'
                           {$date_sql}";
		    if ($table == "deposits" || $table == "withdrawals")
            {
                if (isset($processor))
                {
                    $query .= " AND processor = '{$processor}'";
                }
                if (isset($transaction))
                {
                    $query .= " AND transaction_num = '{$transaction}'";
                }
				
				
				
            }
        }

        foreach ($resultss as $k => $v)
        {
           		
			$resultss[$k] = mysql_query($search[$k]) or die(mysql_error());
			
		//	echo $search[$k];
			//print_r(mysql_fetch_array($resultss[$k]));
        }
    }
    /*
     * If they did specify a type, we know exactly which table we need to query
     */
    else
    {
        $table = mysql_real_escape_string($_POST['type']);
        $query = "SELECT *
                  FROM {$table}
                  WHERE user_id = '".$mem['id']."'
                  {$date_sql}";
        /*
         * Processor and transaction ID only exist in deposits and
         * withdrawals tables.  We can ignore these values if they choose other
         * types.
         */
        if ($table == 'deposits' || $table == 'withdrawals')
        {
            if (isset($processor) && $processor != '')
            {
                $query .= " AND processor = '{$processor}'";
            }
            if (isset($transaction))
            {
                $query .= " AND transaction_num = '{$transaction}'";
            }
        }
		
        $resultss[$table] = mysql_query($query) or die(mysql_error());
		
    }
}

any help would be great

Re: Search form help(error)

Posted: Sat Jul 13, 2013 7:23 pm
by requinix
Print out whichever of those variables has the query that is failing and check that it is valid SQL. (It isn't.) The two you have there look right so somehow something snuck in that shouldn't be there.

Re: Search form help(error)

Posted: Sat Jul 13, 2013 8:02 pm
by nite4000
well that's the thing I cant check anything as when I press search it throws the error

Re: Search form help(error)

Posted: Sat Jul 13, 2013 8:44 pm
by mecha_godzilla
Hi,

I think what requinix is suggesting is that you should echo() out the values before running your query. Another way to do this would be to echo() out the $query variable before running the query - once you do this, it should then be obvious what the problem is.

Also, MySQL is trying to tell you what the problem is:

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 'AND date >= '2013-01-01' AND date <= '2013-08-01'' at line 4

The error will normally be immediately before or after the line it's referencing.

HTH,

Mecha Godzilla