How to check in mysql if records have already been updated?

[mark103]

Hi guys,

I need your help. I am checking on a database as I want to see if the records have already been existed in the database.

<?php
session_start();
    define('DB_HOST', 'localhost');
    define('DB_USER', 'mydbuser');
    define('DB_PASSWORD', 'mydbpass');
    define('DB_DATABASE', 'mydbtable');

$errmsg_arr = array();
$errflag = false;

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if(!$link) {
  die('Failed to connect to server: ' . mysql_error());
    }

$db = mysql_select_db(DB_DATABASE);
if(!$db) {
  die("Unable to select database");
    }

   function clean($var){

return mysql_real_escape_string(strip_tags($var));
    }

$username = clean($_GET['user']);
$password = clean($_GET['pass']);
$test = clean($_GET['test']);
$public = clean($_GET['public']);


   if (isset($_GET['user']) && (isset($_GET['pass']))) {
    if($username == '' || $password == '') {
  $errmsg_arr[] = 'username or password are missing';
  $errflag = true;
    }
} elseif (isset($_GET['user']) || (isset($_GET['test'])) || (isset($_GET['public']))) {
    if($username == '' || $test == '' || $public == '') {
  $errmsg_arr[] = 'user or others are missing';
  $errflag = true;
    }
  }

if($errflag) {
  $_SESSION['ERRMSG_ARR'] = $errmsg_arr;
  echo implode('<br />',$errmsg_arr);
   }
   else {
  $qry="SELECT * FROM members WHERE username='$username' AND passwd='$password'";
  $result=mysql_query($qry) or die('Error:<br />' . $qry . '<br />' . mysql_error());


if ($username && $password) {
  if(mysql_num_rows($result) > 0) {
     $qrytable1="SELECT images, id, test, links, Public FROM user_channel_list WHERE username='$username'";
    $result1=mysql_query($qrytable1) or die('Error:<br />' . $qry . '<br />' . mysql_error());

while ($row = mysql_fetch_array($result1)) {
    echo "<p id='test'>";
    echo $row['test'] . "</p>";
    echo '<p id="images"> <a href="images.php?test=test&id='.$row['id'].'">Images</a></td> | <a href="http://' .  $row["links"] . '">Link</a> </td> | <a href="delete.php?test=test&id='.$row['id'].'">Delete</a> </td> | <span id="test">'.$row['Public'].'</td>';
 }
   } else {
    echo "user not found";
  }
} elseif($username && $test && $public) {
    $qry="SELECT * FROM members WHERE username='$username'";
    $result1=mysql_query($qry) or die('Error:<br />' . $qry . '<br />' . mysql_error());

if(mysql_num_rows($result1) > 0) {     
  $qrytable1="SELECT Public FROM user_channel_list WHERE username='$username' && test='$test'";
  $result2=mysql_query($qrytable1) or die('Error:<br />' . $qry . '<br />' . mysql_error());

  if(mysql_num_rows($result2) > 0) {
    $row = mysql_fetch_row($result2);
    mysql_query("UPDATE user_list SET Public=('$_GET[public]') WHERE username='$username' AND test='$test'");
    echo "update!";
  } else {
    echo "already updated!";
  }
} else {
  echo "user not found";
}
  }
} 
?>

When I run debug on my php, if i input the data in a url bar while the records are the same as the data that I enter in the url, i should get the print out on my php "already updated", but I keep getting "update!".

Do you know how i can check the records from the url to go through on mysql database to see if the records have already been updated??

Any advice would be much appreicated.

Thanks,
Mark

[Weedpacket]

[man]mysql_rows_affected[/man] will say whether any records after the update are different from the same records before the update:

When using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possibility that mysql_affected_rows() may not actually equal the number of rows matched, only the number of rows that were literally affected by the query.

Usually, an update clause that doesn't change any values is harmless: updating a field to have a value of "foo" when its value is already "foo" will result in a field with a value of "foo" - which is the same result as not doing the update.

[sfullman]

try using a timestamp field also - and be sure and index it. This way you can read records in a range. timestamp will update if the row was affected, unless specifically referenced in the update query.

[mark103]

Well I have tried to use this to check the value in the database:

if (mysql_affected_rows($result2) > 0) {
         mysql_query("UPDATE user_list SET Public=('$_GET[public]') WHERE username='$username' AND test='$test'");
         echo "you have update it!";
       } else if (mysql_affected_rows($result2) < 0) {
         echo "it is not on the database";
       } else {
         echo "you have already updated!";
       }

When i input the different value in a url bar while the records are not the same as the value in the url and in the database, i can't get passed and I am keep getting "you have already updated!!" when the value in a database are different than I have input in a url.

Do you know how i can get pass it when I have input the different value in the url while it is not the same in the database?

[Weedpacket]

mark103 wrote:

i can't get passed[sic]

Look at your code:

(mysql_affected_rows($result2) < 0)

How often is a query going to affect fewer than zero rows? (Not finding any rows to update is not an error.)

I am keep getting "you have already updated!!"[sic]

Look at your code:

mysql_query("UPDATE user_list SET Public=('$_GET[public]') WHERE username='$username' AND test='$test'");
         echo "you have update it!";

The very first thing you are doing after running the update is say "you have update it!"[sic]. Before you even check anything.