Page 2 of 2
Re: Record manipulation
Posted: Fri Aug 08, 2008 9:56 am
by EverLearning
I already know what the error is. Do you see it? Pay attention to the words
Code: Select all
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use [b][size=150][color=#FF0000]near[/color][/size][/b] 'WHERE N_ID = '3'' at line 1
You have something there that shouldn't be.
Re: Record manipulation
Posted: Fri Aug 08, 2008 8:06 pm
by Stryks
Wow ... it actually did take me a little while to see that.
Here's a tip, it isn't actually shown in that error ... but it certainly is near.
Re: Record manipulation
Posted: Tue Aug 12, 2008 2:42 am
by pritam79
Stryks wrote:Here's a tip, it isn't actually shown in that error ... but it certainly is near.
Thanks,
The error was very minute to identify, but thanks a lot. Its ok now
Re: Record manipulation
Posted: Tue Aug 12, 2008 5:25 am
by EverLearning
Sorry if I came off as arrogant in my postings, but you learn much more, if you find stuff out by yourself

Re: Record manipulation
Posted: Tue Aug 12, 2008 8:46 am
by pritam79
EverLearning wrote:Sorry if I came off as arrogant in my postings, but you learn much more, if you find stuff out by yourself

Its fine with me. I guess you always learn something new if you try.
I am using mysql, and when i insert date in mysql through text boxes they get stored as yyyy-mm-dd, but when i update the records the date fields in mysql become 0000-00-00 and are displayed on pages as 0000-00-00. The table im mysql is
Code: Select all
CREATE TABLE notes (
N_ID int(3) NOT NULL auto_increment,
username varchar(16) NOT NULL,
n_date date NOT NULL,
n_notes text NOT NULL,
PRIMARY KEY (N_ID)
);
Re: Record manipulation
Posted: Tue Aug 12, 2008 9:00 am
by EverLearning
Your code
Code: Select all
$sql = "UPDATE notes SET ";
$sql .= " n_date = '" . mysql_real_escape_string($_POST['n_date']). "', ";
$sql .= " n_notes = '" . mysql_real_escape_string($_POST['n_notes']). "' ";
$sql .= " WHERE N_ID = '" . mysql_real_escape_string($_POST['N_ID']) . "'";
produced
Code: Select all
UPDATE notes SET n_date = '', n_notes = 'q' WHERE N_ID = '3'
Thats why you date gets set to '0000-00-00'. $_POST['n_date'] was empty.
Also I don't think that your users are going to enter information in YYYY-mm-dd format. So you have to parse the date and enter the correct value. Something along these lines
Code: Select all
//lets say that your dates are entered as mm/dd/YYYY
list($day, $month, $year) = explode(''/", $_POST['n_date']);
$date = "{$year}-{$month}-{$day}"; // the variable you should use in you update statement.