Jump to content


Photo

MySQL and PHP


  • Please log in to reply
3 replies to this topic

#1 ale_jrb

ale_jrb
  • Members
  • PipPip
  • Member
  • 17 posts
  • LocationLondon, UK

Posted 05 March 2006 - 02:24 PM

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:

$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;
}
}

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

Help please...

#2 SpectralDesign.Net

SpectralDesign.Net
  • Members
  • PipPip
  • Member
  • 15 posts
  • LocationToronto, Ontario

Posted 05 March 2006 - 03:52 PM

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:

$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;
          }
     }

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

#3 ale_jrb

ale_jrb
  • Members
  • PipPip
  • Member
  • 17 posts
  • LocationLondon, UK

Posted 05 March 2006 - 05:01 PM

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

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 March 2006 - 11:03 AM

Why don't you simply do this in a single UPDATE statment?

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

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users