[Resolved] INSERT INTO MySQL (and update)

[kikki]

Hi guys,
I need a little help on updating mysql fields. I let users to save some info into mysql db, inserting the lines works fine, but I don't want the user inserts the same data many times in mysql.

I want to know how to insert these informations into mysql only once and on next time it should update the specific field.

Insert:

$query = "INSERT INTO def_pictures (username, default_pic, entry_date) " .
             "VALUES ('$userx', '$picname', current_date)";

Any help would be appreciated.

[planetsim]

UPDATE tblname SET fieldname = '$fieldnameval' WHERE id = id

You should consult the MySQL Manual on Update

[ShiggyWiggy]

Before you do your insert run a query to check if what is being entered is already in the table.

eg
$query = "SELECT * from def_pictures WHERE username = '$userx'";
$result = mysql_query($query);

Then use an if statement
if ($result && mysql_num_rows($result) > 0)
{
do the update query here - the post above highlights how to do this.
}
else
{
do the insert query
}

hope that this helps

[kikki]

Hi guys thanks to your the replies..
It's inserting the same data again and again 🙁

please take a look at this code(I've followed your advices)

if ($result && mysql_num_rows($result) > 0)
{
 $query = "SELECT * from def_pictures WHERE username = '$userx'";
 $query = "UPDATE def_pictures SET default_pic = '$picname' WHERE username = '$userx'";
}
else
{
$query = "INSERT INTO def_pictures (username, default_pic, entry_date) " .
             "VALUES ('$userx', '$picname', current_date)";
} 	

// execute the query to insert the input to database
// if query fail the script will terminate        
mysql_query($query) or die('Error, query failed');

[tsinka]

How does the query look like you execute in front of

if ($result && mysql_num_rows($result) > 0)

Thomas

[kikki]

Originally posted by tsinka
How does the query look like you execute in front of

if ($result && mysql_num_rows($result) > 0)


Thomas

if I execute before that line, query fails.

[tsinka]

Ah, ok, I got it. You tried the code ShiggyWiggy posted. Where does $userx come from ?

Thomas

[kikki]

Originally posted by tsinka
Ah, ok, I got it. You tried the code ShiggyWiggy posted. Where does $userx come from ?

Thomas

thats the current logged in username from the session, is set to $userx.

what I'm trying to do is, check if the username and def_pictures is set then only update without inserting many same lines from the same user.

[tsinka]

Ok,

let's try it with the following code:

$username = "";
if (isset($_SESSION['userx']) && strlen($_SESSION['userx'])) {
  $username = $_SESSION['userx'];
} 

if ($username != "") {
  $query = "SELECT * FROM def_pictures WHERE username = '$username'";
  echo "$query<br>\n";
  $res = mysql_query($query) or die('SQL error: '.mysql_error());

  if (mysql_num_rows($res) > 0) {
    $query = "UPDATE def_pictures SET default_pic='$picname' WHERE username = '$username'";
    echo "$query<br>\n";
  } else {
    $query = "INSERT INTO def_pictures (username,default_pic,entry_date) "
            ."VALUES ('$username','$picname',CURRENT_DATE)";
    echo "$query<br>\n";
  }

  mysql_query($query) or die('SQL error: '.mysql_error());
} else {
  echo "no valid user<br>\n";
}

Clean up the table a little bit if neccessary to make sure that the user you're testing that code with has either one record or no record in the table.

Thomas

[kikki]

thanks guys, BIG thanks to you Thomas, your code is working perfectly, exactly what I want..(I was stuck on my other things too since I didn't know how to update the specific section in mysql, your code really helps me on the other things too) 😃😃😃 I appreciate your help, the problem is solved, thanks again man..