Question

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();

Answer 1

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.

友情链接