Page 1 of 1

Can someone help me with a simple query?

Posted: Sun Sep 06, 2009 2:20 am
by Rumrunner
I'm stuck on this simple query, I can't get this query to update my database, I know the variables have values, and it has the right connection information... is there something wrong with this syntax?

Code: Select all

mysql_query("UPDATE tbl_loads2 SET
                                   
            control_number = '{$control_number}', load_number = $load_number}', orgin_address = '{$orgin_address}', orgin_city = '{$orgin_city}', departure_month = '{$departure_month}',
            departure_date = '{$departure_date}', departure_year = '{$departure_year}', departure_time = '{$departure_time}', destination_city = '{$destination_city}',
            destination_address = '{$destination_address}', due_date = '{$due_date}', due_month = '{$due_month}', due_year = '{$due_year}', due_time = '{$due_time}', WHERE load_number = '{$load_number}' ");
Thanks

Re: Can someone help me with a simple query?

Posted: Sun Sep 06, 2009 2:47 am
by Salaria
Missing '{' at load_number = $load_number}'. Please check and notify us if still not fixed the issue.

Re: Can someone help me with a simple query?

Posted: Sun Sep 06, 2009 3:08 am
by Rumrunner
Thanks, I fixed that, but still no dice... I'm stumped. I know it's connecting, because my working die error doesn't come up...

Re: Can someone help me with a simple query?

Posted: Sun Sep 06, 2009 3:28 am
by requinix

Code: Select all

...due_time = '{$due_time}', WHERE...
You mentioned a "die error" - why don't you have one on the mysql_query too?

Re: Can someone help me with a simple query?

Posted: Sun Sep 06, 2009 4:31 am
by Rumrunner
^ Good point I forgot about that. Ok so I have a query error does anybody know what this is trying to tell me?

failed.
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 '}', orgin_address = '154 LOGAN RD ', orgin_city = 'EZSXDC', departure_month = '1' at line 3

my current code:

Code: Select all

$query = "UPDATE tbl_loads2 SET
                                   
            control_number = '{$control_number}', load_number = {$load_number}', orgin_address = '{$orgin_address}', orgin_city = '{$orgin_city}', departure_month = '{$departure_month}',
            departure_date = '{$departure_date}', departure_year = '{$departure_year}', departure_time = '{$departure_time}', destination_city = '{$destination_city}',
            destination_address = '{$destination_address}', due_date = '{$due_date}', due_month = '{$due_month}', due_year = '{$due_year}', due_time = '{$due_time}', WHERE load_number = '{$load_number}' ";
            
            $result = mysql_query($query);
        if (mysql_affected_rows() == 1) {
        echo "working.";
        }
                else
                {
        echo "failed.";
        echo "<br />". mysql_error();
                }

Re: Can someone help me with a simple query?

Posted: Sun Sep 06, 2009 5:12 am
by requinix
The error tells you what's just after the problem area. Use that to see where exactly in the query the mistake is.

Re: Can someone help me with a simple query?

Posted: Sun Sep 06, 2009 5:25 am
by Rumrunner
Well, I tried just removing those parts, but then it comes up with another error for something else. I kept removing them until I had nothing left in the query.

"failed.
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 '', departure_date='1', departure_year='2009', departure_time='00:09' at line 2"

current code:

Code: Select all

$query = "UPDATE tbl_loads2
            SET control_number='$control_number', load_number=$load_number', departure_date='$departure_date',
            departure_year='$departure_year', departure_time='$departure_time', destination_city='$destination_city',
            destination_address='$destination_address', due_date='$due_date', due_month='$due_month',
            due_year='$due_year', due_time='$due_time'
            WHERE load_number='$load_number'";
            
            $result = mysql_query($query);
        if (mysql_affected_rows() == 1) {
        echo "working.";
        }
                else
                {
        echo "failed.";
        echo "<br />". mysql_error();
                }

Re: Can someone help me with a simple query?

Posted: Sun Sep 06, 2009 5:31 am
by jayshields
Only surround values with single quotes in queries when they are stored as strings. This applies to MySQL date(/time) types, but not integers.

Re: Can someone help me with a simple query?

Posted: Sun Sep 06, 2009 6:12 am
by Rumrunner
Ok I wrote a very simple query to see if it would work. The query "failed" with this simple script.

Code: Select all

<?php
 
    require_once("includes/db_loads_connect.php");
    
    db_loads_conn();
    
    $query = "UPDATE tbl_loads2 SET orgin_city = 'working' WHERE destination_city = 'EL PASO, TX'";
    
    $result = mysql_query($query);
        if (mysql_affected_rows() == 1) {
        echo "working.";
        }
                else
                {
        echo "failed.";
        echo "<br />". mysql_error();
                }
?>
Should I start looking at other things?

Re: Can someone help me with a simple query?

Posted: Sun Sep 06, 2009 9:02 am
by jayshields
And mysql_error() said?

If nothing, then your UPDATE query affected a number of rows different to 1.

Re: Can someone help me with a simple query?

Posted: Sun Sep 06, 2009 8:23 pm
by Rumrunner
Ok thanks for all the help guys, I got it figured out. I had something wrong with the time variables. I'm not exactly sure why it started working after I manually reentered the times into the database. Is there any type of syntax that will work for any type of variable in a query?