Page 1 of 1

When updating records, how to add them if not present?

Posted: Fri Mar 25, 2011 1:54 pm
by mareksl
Hi guys!
I have a little script on my site that allows people to save their notes on the page.
The code for saving the notes is the following:

Code: Select all

	  <?php
	  $notes = $_REQUEST['notes'];
		$uname = $user->data['username_clean'];
        $con = mysql_connect("***","***","***");
        if (!$con)
        {
			  die('Could not connect: ' . mysql_error());
		}
        mysql_select_db("db_kitchentube", $con);
		mysql_query ("UPDATE notes SET notes = \"$notes\" WHERE username = \"$uname\"");
		mysql_close($con);
		header( 'Location: index.php' );
		?>
Now if the record of the username is not present, the notes stay blank and you can't save them, as he doesn't update the record.
How to check if the record with the username is present and if not, how to add it instead of updating it? (The adding I can manage, but how to check it first?)
Thanks in advance!

Re: When updating records, how to add them if not present?

Posted: Fri Mar 25, 2011 1:57 pm
by mareksl
Ok i got it! Just found a page explaining: http://www.kavoir.com/2009/05/mysql-ins ... g-row.html
I am sorry for asking before doing my research. :banghead:

Re: When updating records, how to add them if not present?

Posted: Fri Mar 25, 2011 1:58 pm
by John Cartwright
Assuming that your username is a unique key, you could do an

Code: Select all

INSERT ..... ON DUPLICATE KEY UPDATE notes = "$notes"
Don't forget to escape your input with mysql_real_escape_string() ;)

Re: When updating records, how to add them if not present?

Posted: Fri Mar 25, 2011 2:05 pm
by mareksl
Oh ok, can try that :)
Thanks!
Don't forget to escape your input with mysql_real_escape_string() ;)
Not sure what this is, could you explain? Thanks!

EDIT: Got it working with:

Code: Select all

"INSERT INTO notes (UID, username, notes) VALUES (\"$uid\", \"$uname\", \"$notes\") ON DUPLICATE KEY UPDATE notes = \"$notes\""

Re: When updating records, how to add them if not present?

Posted: Sat Mar 26, 2011 4:19 am
by Darhazer
You can use REPLACE as well :)
But be extremly careful if you use foreign keys