Man input to mysql works but not variable.

Codes here !

Moderators: egami, macek, gesf

Post Reply
tommytx123
New php-forum User
New php-forum User
Posts: 20
Joined: Sat Sep 19, 2015 4:57 pm

Wed Apr 05, 2017 6:49 am

Anyone have any ideas why this will not work..
Bottom line is that when i run the date entry by entering the date/time physically into the program all is fine.. but any attempt to enter the date via varialbe ($dte) fails with the results shown below..

Code: Select all

// Good test
// Remove quotes to run this is the good run with date physically installed.
// $sql = "UPDATE $mydb SET $myfield = '2017-04-05 09:49:05'";

// Bad test
// Remove quotes to run this is the bad run with date provided by a varialbe $dte.
// $dte = "2017-04-05 09:49:05";
// $sql = "UPDATE $mydb SET $myfield = " . $dte;
// also tried this $sql = "UPDATE $mydb SET $myfield = $dte";

echo "SQL = $sql<br>"; // print just for viewing actual sql.

// Prepare statement
$stmt = $conn->prepare($sql);
// execute the query
$stmt->execute();

// echo a message to say the UPDATE succeeded
echo $stmt->rowCount() . " records UPDATED successfully";
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
Results of running the code above..

Good Run with manual input of the date/time..
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

This is a print out of the $sql statement before execution..using manual input..
SQL = UPDATE wp_realty_listingsdb SET listingsdb_last_modified = '2017-04-05 09:49:05'


This is a print out of the results of the execution.
1741 records UPDATED successfully


Bad Run with variable $dte input of the date/time..
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

This is a print out of the $sql statement before execution.. using variable input
SQL = UPDATE wp_realty_listingsdb SET listingsdb_last_modified = 2017-04-05 09:49:05

This is a print out of the results of the execution.
UPDATE wp_realty_listingsdb SET listingsdb_last_modified = 2017-04-05 09:49:05
SQLSTATE[42000]: Syntax error or access violation: 1064 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 '09:49:05' at line 1
AdoptiveSolution
php-forum Super User
php-forum Super User
Posts: 167
Joined: Wed Jun 15, 2016 8:35 am

Wed Apr 05, 2017 7:42 am

Look carefully and notice that the bad test has the date/time without quotes.

Try this :

Code: Select all

$sql = "UPDATE $mydb SET $myfield = '" . $dte . "'";
or

Code: Select all

$sql = "UPDATE $mydb SET $myfield = '$dte'";
Then echo the line to see it.
tommytx123
New php-forum User
New php-forum User
Posts: 20
Joined: Sat Sep 19, 2015 4:57 pm

Wed Apr 05, 2017 9:24 am

Wow Mr. Mysql wizard.. works perfect.. guess i just kept overlooing that simple little thing...
Very grateful... I douse you Mr. Mysql Wizard for the Month.. haha.

Hey you are so good if you have a minute pls look over my other question at
https://www.php-forum.com/phpforum/view ... =2&t=27905
Nigel has given a lot of support but for me its not working.. but I did try it on two hosts..
If you have and suggestion i would be very gratefful..
Tom
Post Reply