0

I'm struggling to find a solution to this.

I have a select statement that returns 5 rows based on a condition (WHERE job position).

UserNames column: John Jake Alvin Allan Robert

I wanted a column where it will show the row number, hence:

RowNumber: 1 2 3 4 5

UserNames column: John Jake Alvin Allan Robert

I figured that the query is this:

set @rownumber=0;
select (@rownumber:=@rownumber+1) AS rownumber FROM (select * from usernames WHERE position='Team Lead') as t;

It works in MySQL Workbench as expected. The problem is I cannot seem to output the result to PHP. It's throwing an error:

mysqli_num_rows() expects parameter 1 to be mysqli_result, bool given

My PHP code is this:

$userQuery = "set @rownumber=0;";
$userQuery .= "select (@rownumber:=@rownumber+1) AS rownumber FROM (select * from usernames WHERE position='Team Lead') as t";

$userQueryResult = mysqli_query($dbConnection, $userQuery);
$row = mysqli_num_rows($userQueryResult);

Note that I also used mysqli_multi_query but to no avail.

Community
  • 1
  • 1
user874737
  • 469
  • 1
  • 9
  • 24
  • Does this answer your question? [Error Trying to Use "SET @rownum = 0;" in PHP](https://stackoverflow.com/questions/18802513/error-trying-to-use-set-rownum-0-in-php) – Cadu De Castro Alves Mar 05 '20 at 10:40
  • @CaduDeCastroAlves I don't agree this is the right one. – Dharman Mar 05 '20 at 10:41
  • Does this answer your question? [What is the php equivalent of @row:=@row+1](https://stackoverflow.com/questions/59737178/what-is-the-php-equivalent-of-row-row1) – Dharman Mar 05 '20 at 10:42
  • Do not use `mysqli_multi_query()`. You don't need it. – Dharman Mar 05 '20 at 10:43
  • Also you must enable error reporting: [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) – Dharman Mar 05 '20 at 10:44
  • 1
    For more information see: [ROW_NUMBER() in MySQL](https://stackoverflow.com/q/1895110/1839439) – Dharman Mar 05 '20 at 10:45

0 Answers0