Incorrent number of rows returned by mysqli_num_rows

[angelcool]

Hello community, this is a long one so make sure you have your thinking hat on.

 

1) I'm writing a site that will assign  some tokens to a user upon completing an order.

2) I have been testing my code to make sure no repeated tokens are assigned.

3) After tests I found out repeated tokens are in database.

 

The max number of tokens per order will be 15, however I have pushed the number of tokens in my tests to be sure my code is not inserting duplicated tokens.

 

 

This is my testing code:

function db_link()
{

//@ suppresses the error mysqli_connect returns, and instead we use our die() eror
$db_link=@mysqli_connect('xxx','xxx','xxx');




if($db_link==false)
echo 'Could NOT connect to MySQL, check credentials. ';


//select database
if(@mysqli_select_db($db_link,'paylessdb')==false)
echo 'Could NOT select DB, check DB name.';


return $db_link;

}




function BulkTokens($Num)
{

	for($iii=0;$iii<=$Num-1;)
	{

	 //Step 1 Generate a token.
		$length = 5; 
		$chars =  'ABCDEFGHIJKLMNPQRSTUVWXYZ123456789'; //ABCDEFGHIJKLMNPQRTWXYZ123456789    123456789abcdefghijklmnopqrstwxzABCDEFGHIJKLMNPQRTWXYZ
		$string = ''; 
		for ($i = 0; $i < $length; $i++) { 
			$pos = rand(0, strlen($chars)-1); 
			$string .= $chars{$pos}; }

	/*

	//Run this query to find out duplicate tokens
	SELECT * FROM BulkTokens INNER JOIN (SELECT Token,COUNT(Token) AS Duplicated FROM BulkTokens 
			     GROUP BY Token HAVING Duplicated > 1) dup ON BulkTokens.Token=dup.Token;


	*/

			//Step 2 Check if token DOES NOT exist in database (check if it is duplicated, if it is skip this iteration).
			$query="SELECT * FROM BulkTokens WHERE BINARY Token='".$string."'";
			$query=mysqli_query(db_link(),$query);
			$y=mysqli_num_rows($query);

			//if token exists skip iteration.
			if($y==0)
			{

			//If token does not exist add 1 to $iii and assign the token to array.	

			$TokensArray[$iii]=$string;
			$iii++;

			}
			else
			echo "Skipped(Repeated): ".$string." Num of rows:$y<br>";


	}


	//insert each token in database.
	for($iii=0;$iii<=sizeof($TokensArray)-1;$iii++)
	{

	$query="INSERT INTO BulkTokens VALUES('".$_SESSION['reshash']['TRANSACTIONID']."','".$TokensArray[$iii]."',1);";
	mysqli_query(db_link(),$query) or die('Unable to insert tokens. );

	}





return sizeof($TokensArray);

}


echo BulkTokens(10000);

 

When I execute this code I get:

Skipped(Repeated): 3DQ2Q Num of rows:1
Skipped(Repeated): FK5R9 Num of rows:1
Skipped(Repeated): FYEJZ Num of rows:1
Skipped(Repeated): WI8A9 Num of rows:1
10000

 

This means these tokens are in DB already and will not be assigned to $TokensArray.

 

However  I found out repeated tokens when i run the following query in MySQL.

	//Run this query to find out duplicate tokens
	SELECT * FROM BulkTokens INNER JOIN (SELECT Token,COUNT(Token) AS Duplicated FROM BulkTokens 
			     GROUP BY Token HAVING Duplicated > 1) dup ON BulkTokens.Token=dup.Token;

 

The only explanation is that mysqli_num_rows is returning 0 when in reality a token for the given string exists and the number of rows should be 1 (and iteration should be skipped and string should not be assinged to $TokensArray).

 

A possible solution I consider is to create a subquery to find repeated tokens, but aside from this possibility is the fact to find out why mysqli_num_rows is getting the wrong number of rows.

 

I will appreciate any constructive help/suggestions.

 

Thank you

Angel

[angelcool]

SOLVED!!! 

 

Repeated tokens were happening in $TokensArray. The solution was to place the insert statement within the if statement that checked for repeated tokens and get rid of the last loop.