mysql_fetch_array() error but not on first result.

[andrewgarn]

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/www/file.php on line 18

 

$dbhost = "***";
$dbname = "****";
$dbuser = "****";
$dbpass = "****";

mysql_connect ( $dbhost, $dbuser, $dbpass)or die("Could not connect: ".mysql_error());
mysql_select_db($dbname) or die(mysql_error());

$today = date("Y-m-d");
$sql = "SELECT * FROM user";
$result = mysql_query($sql) or die('MySQL Error: ' . mysql_error() . '<hr>Query: ' . $sql); 

echo "<h2>Updating the highscores of members: </h2>";
echo "<ul>";
while($r = mysql_fetch_array($result)) // LINE 18
{ 
	$username=$r["username"];
	echo "<li>".$username;
	echo "<br>";
	$file = file_get_contents ('http://hiscore.player='.$username.'');
	//echo $file;
	$file = ereg_replace("\n", " ", $file);

	$a = explode(' ',$file); // explode each space separated set into an array for each position in the array...
	foreach ($a as $b) { 
	   $c[] = explode(',',$b); // explode each comma separated set into a nested array, making a 2d array overall

	//code for extracting the split variables here e.g. $var = $c[0][0];

	//sql query inserting data into database
	}

 

The code functions correctly for the first username retrieved, but fails on the second giving the error at the top. Line 18 has been marked in the code.

 

Any ideas? The connection settings must be right as its inserting the first set of scores into database fine.

[darkfreaks]

try using single quotes in your variables not double

[hitman6003]

//sql query inserting data into database

 

Are you reassigning the $result variable to what is returned by this query?

[andrewgarn]

//sql query inserting data into database

 

Are you reassigning the $result variable to what is returned by this query?

 

Thanks! you were right i was reusing the $result variable inside the while function. Changing it fixed the problem

 

 

 

On a side question, I know you can make a combination of two fields unique in mySQL i.e. there can only be one field where username = auser and date = today.

 

How would i put that key into the database? Its to stop duplicates of highscores being added to the database.

[hitman6003]

You could make them both a part of the primary key (not recommended), or you could implement a check in your code...i.e., query the database before doing the insert to verify that the score has not already been inserted.

[andrewgarn]

You could make them both a part of the primary key (not recommended), or you could implement a check in your code...i.e., query the database before doing the insert to verify that the score has not already been inserted.

 

Why is it not recommended?

[hitman6003]

Because the primary key is used to find every row in the table.  The primary key will always have an index on it that is the size of the entire field (e.g., if it is a CHAR(255) field, the index will be all 255 characters).  So, if you have an INT (4 bytes) field and a DATETIME field (8 bytes) set as a hybrid primary key, then each row will add 12 bytes to the size of the index.

 

Doesn't sound like much, but what if your application has a million rows?  Suddenly you have a huge index that needs to be stored in memory, or read off disk.

 

This is especially bad with InnoDB tables with multiple indexes.  Each tuple in the non-primary key indexes stores a copy of the primary key for the record that it is indexed for.  So, using the same example from above, if you have two indexes, suddenly each insert requires a minimum of 24 bytes, not including the size of the column(s) indexed by the second index or the size of the record itself.

[andrewgarn]

ok that makes sense.

 

while($r = mysql_fetch_array($result))

{

 

$query = select from track where username='$user' and date='$today'

$result = mysql_query($query);

 

if (mysql_num_rows($result) <1) {

//do query

}

else {

//do nothing

}

}