MySQL and PHP

[ale_jrb]

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...

[SpectralDesign.Net]

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 :)

[ale_jrb]

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...

[fenway]

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]