[SOLVED] Updating database

[cheechm]

Hi,

I can't seem to make this work. Nothing happens:

 

<?php
$con1 = mysql_connect("****");
$db = mysql_select_db("***", ($con1))
or die ("Couldn't select database.");

$con2 = mysql_connect("****");
$db = mysql_select_db("***", ($con2))
or die ("Couldn't select database.");

$sql = "SELECT * FROM ****";
$query = mysql_query($sql, $con1) or die(mysql_error());
if (mysql_num_rows($query) == 0) { // The record doesn't exist, so insert it

while($result=mysql_fetch_assoc($query))
{
$isql="INSERT INTO ****(username,password,email,regdate,birthdate)
VALUES('".$result['username']."','".$result['password']."','".$result['email']."','".$result['joindate']."','".$result['birthday_search']."')";
mysql_query($isql, $con2);
}
} else // The record already exists, so update it.
{
while ($result=mysql_fetch_assoc($query))
{
$usql = "UPDATE **** SET username = '".$result['username']."', password = '".$result['password']."', email = '".$result['email']."', regdate = '".$result['joindate']."', birthdate = '".$result['birthday_search']."' WHERE username = '".$result['username']."'";
mysql_query($usql, $con2) or die(mysql_error());
}
}


mysql_close($con1);
mysql_close($con2);

echo "finished";

?>

 

Do you have any idea why no data is going in to the database?

Thanks

[btherl]

Whenever you do a query, do it like this:

 

mysql_query($isql, $con2) or die("Error in $isql:\n" . mysql_error());

 

That will tell you if there's a problem with your query.

 

You should also add an "or die" to your mysql_connect().

[cheechm]

Not getting any problems with the query.

It is running perfectly and displaying finished.

THanks though

[BlueSkyIS]

Your code says "if there are no records, loop over them":

 

if (mysql_num_rows($query) == 0) { // No records

    while($result=mysql_fetch_assoc($query)) // So it's pointless trying to loop over them.

[cheechm]

So then how would I make it insert if the record didn't exist? How would I be able to check?

Thanks

[BlueSkyIS]

just insert the record using INSERT.

 

if (record doesn't exist) {

    INSERT the record.

}

[cheechm]

Yeh I got that bit, but how do I check if the record doesn't already exist in the table and then send it to Update instead?

Thanks

[BlueSkyIS]

you already know the record doesn't exist here:

 

if (mysql_num_rows($query) == 0) { // No record exists.

    // Insert record

 

} else {

    // Update record

}

[cheechm]

But that is what I was trying to do with this:

 

if (mysql_num_rows($query) == 0) { // No records
    while($result=mysql_fetch_assoc($query)) // So it's pointless trying to loop over them.

 

It is taking info from one database and putting it in another.

[cheechm]

Just thinking would this work:

 

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

[BlueSkyIS]

But that is what I was trying to do with this:

 

if (mysql_num_rows($query) == 0) { // No records
    while($result=mysql_fetch_assoc($query)) // So it's pointless trying to loop over them.

 

It is taking info from one database and putting it in another.

 

No, it's not. We perform $query and see that the number of rows returned is 0,

 

mysql_num_rows($query) == 0 <-- The number of rows from $query is 0. There is no matching data.

 

but then we try to loop over those 0 records:

 

while($result=mysql_fetch_assoc($query)) <-- We already know there are no rows in $query, so this will never do anything. ever.

 

$query has 0 rows, so there is no data.

 

Replace that loop with a simple INSERT INTO TABLE () statement. If your database is correct, there should be no need to check for duplicate keys, nor increment anything after the INSERT.

 

 

[cheechm]

But the number of rows returned will never be 0. There will be always data, it is just a question of has that data been inserted into the other database and if so it should be updated instead of inserted again.

 

So effectively the mysql_num_rows($query) does absolutley nothing, because there will always be data fetched.

[BlueSkyIS]

if the number of rows will always be != 0, then the lower part of the code simplifies from this:

 

if (mysql_num_rows($query) == 0) { // The record doesn't exist, so insert it

while($result=mysql_fetch_assoc($query))
{
$isql="INSERT INTO ****(username,password,email,regdate,birthdate)
VALUES('".$result['username']."','".$result['password']."','".$result['email']."','".$result['joindate']."','".$result['birthday_search']."')";
mysql_query($isql, $con2);
}
} else // The record already exists, so update it.
{
while ($result=mysql_fetch_assoc($query))
{
$usql = "UPDATE **** SET username = '".$result['username']."', password = '".$result['password']."', email = '".$result['email']."', regdate = '".$result['joindate']."', birthdate = '".$result['birthday_search']."' WHERE username = '".$result['username']."'";
mysql_query($usql, $con2) or die(mysql_error());
}
}

 

to this:

 

while ($result=mysql_fetch_assoc($query)) {
$usql = "UPDATE **** SET username = '".$result['username']."', password = '".$result['password']."', email = '".$result['email']."', regdate = '".$result['joindate']."', birthdate = '".$result['birthday_search']."' WHERE username = '".$result['username']."'";
mysql_query($usql, $con2) or die(mysql_error());
}

 

Now we're getting somewhere. Loop over the records from db1 and grab the values for each field. Look for those values in db2. If there is no match, insert the record into db2.

 

while ($result=mysql_fetch_assoc($query)) {
      $t_sql = "SELECT username FROM ***** WHERE username = '{$result['username']}' AND password = '{$result['password']}' AND email = '{$result['email']}' AND birthdate = '{$result['birthdate']}";
      $t_result = mysql_query($t_sql, $con2) or die(mysql_error());
      if (mysql_numrows($t_result) > 0) { // Record exists in other db, so update
            $usql = "UPDATE **** SET username = '".$result['username']."', password = '".$result['password']."', email = '".$result['email']."', regdate = '".$result['joindate']."', birthdate = '".$result['birthday_search']."' WHERE username = '".$result['username']."'";
      } else { // Record doesn't exist in other db, so insert it.
            $usql = "INSERT INTO **** SET username = '".$result['username']."', password = '".$result['password']."', email = '".$result['email']."', regdate = '".$result['joindate']."', birthdate = '".$result['birthday_search']."'";
      }

       mysql_query($usql, $con2) or die(mysql_error());
}

 

 

 

[cheechm]

I can't thank you enough BlueSkyIS. You are the best. It all works now ..

 

Thank you