CODEWITHSUNDEEP
phpsqlmysqlmysqli
Codezy
Codezy

Reputation: 5570

Mysqli query with a SET variable statment (ie multiple queries)

I want to set a variable to use in a mysqli query. This doesn't quite work. Prior to mysqli I used to set query calls. I played around with db->multi_query($sql) with no luck. Anyone out there have an idea how to make this work including a set statement?

$sql = 'SET @rownum := 0;';
$sql .= 'SELECT @rownum :=@rownum + 1 AS Rank, User_Id, COUNT(User_ID) AS Block_Count
               FROM Block_Owners;

$stmt = $db->prepare($sql);
$stmt->bind_param('ii', $world, $userId);
// execute the query
$stmt->execute();

Upvotes: 4

Views: 6140

Answers (1)

Quassnoi
Quassnoi

Reputation: 425261

Do it in two separate queries:

$db->query('SET @rownum := 0');
$sql = 'SELECT @rownum :=@rownum + 1 AS Rank, User_Id, COUNT(User_ID) AS Block_Count FROM Block_Owners'
$stmt = $db->prepare($sql);
$stmt->bind_param('ii', $world, $userId);
$stmt->execute();

Note, however, that the query you want to run will always return a single row (with Rank = 1) since you are using an aggregate function without GROUP BY.

Upvotes: 7

Related Questions