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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
mareksl
Forum Newbie
Posts: 18
Joined: Tue Mar 08, 2011 12:24 pm
Location: Poland

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

Post 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!
Last edited by mareksl on Fri Mar 25, 2011 2:21 pm, edited 1 time in total.
User avatar
mareksl
Forum Newbie
Posts: 18
Joined: Tue Mar 08, 2011 12:24 pm
Location: Poland

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

Post 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:
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

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

Post 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() ;)
User avatar
mareksl
Forum Newbie
Posts: 18
Joined: Tue Mar 08, 2011 12:24 pm
Location: Poland

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

Post 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\""
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

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

Post by Darhazer »

You can use REPLACE as well :)
But be extremly careful if you use foreign keys
Post Reply