Jump to content

Archived

This topic is now archived and is closed to further replies.

ale_jrb

MySQL and PHP

Recommended Posts

Hi,

I;m writing a script to update a database. The rows are already set up and I need to change one field in a random row. Here's my script:

[code]
$number = mysql_query("SELECT * FROM `mytable`");
$number = mysql_num_rows($number);

$i = 1;
while ($i < 2) {
$setfield = rand(1, $number);
$checkempty = mysql_query("SELECT * FROM `mytable`
            WHERE fieldiwannaupdate!='' AND ID='$setfield'");
$checkempty = mysql_num_rows($checkempty);
if(checkempty == 0) {
$sql = mysql_query("UPDATE `mytable` SET
`fieldiwannaupdate` = '$thingiwannaupdateto'
WHERE `ID` ='$setfield' LIMIT 1;
") or die (mysql_error());
break;
}
}
[/code]

But it isn't working - it will always overwrite something, even if there is something there already.

Help please...

Share this post


Link to post
Share on other sites
your if ($checkempty) is only looking at the number of rows returned... is that what you want?

If you want to check a specific field try something like:

[code]
$query_check = @mysql_query("SELECT * FROM `islands`
  WHERE fieldiwannaupdate!='' AND ID='$setfield'") or die(mysql_error));
     while ($row = mysql_fetch_assoc($query_check)) {
          $checker = $row['fieldiwannaupdate'];
          if ($checker = '') {
$sql = mysql_query("UPDATE `mytable` SET
`fieldiwannaupdate` = '$thingiwannaupdateto'
WHERE `ID` ='setfield' LIMIT 1;
") or die (mysql_error());
break;
          }
     }
[/code]

I think.... it's something to try anyway :)

Share this post


Link to post
Share on other sites
Thanks, I'll try that.

But what the script is doing, is the random part gives it an ID to work with. It checks how many rows have that ID (1) and are [b]not[/b] empty. If it is empty, then there will be 0 rows that agree to the if statement. If it is full there will be 1.

The theory is that it only overwrites if it returns 0. But its not working...

Share this post


Link to post
Share on other sites
Why don't you simply do this in a single UPDATE statment?

[code]"UPDATE mytable SET fieldiwannaupdate = '$thingiwannaupdateto' WHERE fieldiwannaupdate = '' AND ID = FLOOR(1 + RAND() * ( SELECT COUNT(*) FROM mytable ) ) LIMIT 1"[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.