Enviro: MySQL, PHP 5.5
I have a situation where I need to return a random result from the Database. I have tried two methods of doing this. One which returns an array of results and I randomly pick one using the array_rand($results) and the other is adding order by rand() limit1;
When I returned a list of results the execution time was recorded reported from the MySQL console like so:
602 rows in set (0.05 sec)
Query OK, 0 rows affected (49.37 sec)
This is not good. So I choose the other method which reported:
1 row in set (0.05 sec)
Query OK, 0 rows affected (0.36 sec)
The issue became when run this query for a second time, I am passing in the arg that records the previous record retrieved so I avoid duplicates
Here is my routine:
CREATE PROCEDURE `getRandomRecord`(
IN _args varchar(100)
)
BEGIN
SET @query = CONCAT('SELECT C.*, B.*
FROM C
LEFT JOIN B
ON B.id = c.Bid
where (C.Type=27 || C.Type=28 || C.Type=29)
and C.EndDate>CURRENT_TIMESTAMP() ',_args,' order by rand() limit 1;');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END//
This executes the first time without an issue, but on the PHP side when I run this a second time the call fails.
The args are being recorded like so:
$args= !empty($args) ? $args : '';
//args is empty first time
$dbHandler = new RandomRecordDatabase();
$results = $dbHandler->getRandomRecord($args);
//Do Stuff HERE
$args .= ' AND C.id!='.$results['C.id'];
$dealer_results = $dbHandler->getRandomRecord($args);
The function is like this:
public function getRandomRecord($args){
try
{
$procedure = "Call getRandomRecord(?)";
$statement = $this -> _dbh -> prepare($procedure);
$statement -> bindParam(1, $args);
$statement -> execute();
$results = $statement -> fetchAll(PDO::FETCH_ASSOC);
return $results[0];
}
catch(PDOException $e)
{
echo $e -> getMessage();
}
}
On the second call to this method, I get the following PDOException:
Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"387 order by rand() limit 1'
Which references the line $statement -> execute();
How can I fix this error? Thank you!
UPDATE(SOLUTION):
I should of reviewed the Output of args more closely and I would of seen the error: echo $args;
In one instance I noted a mix case: " AND C.id=123 AND AND C.id=456 and C.id="111" AND C.id=789.
I modified the one case which I never noted located in a different script. This now fixes the issue.
