Problem with query keep geting - sign infront of string

PHP
1

Hi
i have a databse with 3 tables, id, called, and num, i have 90 rows, in each row there is a number from 1 to 90.
i use a query to select the rows (number) at random then update the database table called, from 0 to 1
the problem is every so many numbers i get a - sign in front of the number why does this happen. also should only get the number with 0 in the called table and not 1.
here is the code i use
i use actionscript 3 and flash to refresh the page every 5 seconds every 5 seconds i get a different random number. but after say 6 numbers are called i get -infront of the number.

$query1 = sprintf("SELECT num FROM randnumber where called = 0 ORDER BY RAND() ",
 
																 
   mysql_real_escape_string($num));
 $result = mysql_query($query1);

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\
";
    $message .= 'Whole query: ' . $query1;
    die($message);
}
while ($row = mysql_fetch_assoc($result)) {
 $num= $row['num'];
} 
$sql = "UPDATE randnumber SET
          called=1
          WHERE num='$num'";
		   if (@mysql_query($sql)) {

  } else {
    echo '<p>Error: ' .
        mysql_error() . '</p>';
  }
// Perform Query
$result = mysql_query($query1);
$returnVars = array();
$returnVars['num'] = "$num";

		
$returnString = http_build_query($returnVars);
$num = $_POST['num'];
echo $returnString ;
2

Hi
sorry i stand corrected,
you are right just tried through my browser works called all 90 then gave me a 0 when all 90 has been called,
so is this as3 problem and not php sql.
thanks paul

3

Nope.

You’re either modifying the code we have provided or not showing us the complete, relevant, section of your code.

Place a standalone script on your server, and verify it works and outputs the values you expect using your browser. When, and only when, it appears to be working fine integrate it into your Flash application.

“It is better to take many small steps in the right direction than to make a great leap forward only to stumble backward.” - Old Chinese Proverb.

4

I don’t know you want something like below:


$sql = "SELECT num FROM randnumber WHERE called=0 ORDER BY RAND() LIMIT 1";
$result = mysql_query($sql) or die('Invalid SQL : ' . mysql_error());
$row = mysql_fetch_assoc($result);
$returnVars = array();
$returnVars['num'] = $row['num'];

// update the record to set 1
$sql = "UPDATE randnumber SET called=1 WHERE num='$num'";
mysql_query($sql) or die(mysql_error());

$returnString = http_build_query($returnVars); 
$num = $_POST['num'];
echo $returnString;
5

You will, at some point, come across a race condition. However, this should solve your current issue… :wink:


<?php
$num = 0;
$res = mysql_query('SELECT num FROM randnumber WHERE called = 0 ORDER BY RAND() LIMIT 1');
if(1 === mysql_num_rows($res)){
  $row = mysql_fetch_assoc($res);
  mysql_query(sprintf('UPDATE randnumber SET called = 1 WHERE num = &#37;d LIMIT 1', $row['num']));
  $num = $row['num'];
}
printf('num=%d', $num);
?>
6

You’re wrong.

7

Your code is a bit confused, it looks to me like there are some statements in there that are of no use.
And a question: what happens if there are no more rows with called = 0 ?

8

Hi
thankyou for your reply
i have tried both codes above AnthonySterling code and rajug

both codes and mine all do the same thing they select all from the database and not those with 0 in the called table, also the query doesnt update

9

Hi
im trying to get a random number from table named num when the number is called it changes the table called from 0 to 1 so that it is not called again.

when all 90 numbers have been called its the end of the game.

but cant understand where the - sign is coming from and also its not selecting the num from table where called = 0 its selecting all numbers.

10

Hi

i have just tested your code again it doesn’t work after calling 3 random numbers i got this -3 no such number, also doesn’t call number from table that has 0 it selects all if it has 0 in the table or 1,

would it be better when a number has been selected delete the row instead of trying to update