phpmyadmin Question posted in
The official documentation can be found here.

Accessing SQL Aliases with PHP – Phpmyadmin

This code works to give me what I want in my phpMyAdmin server:

SET @row_number = 0;
SELECT salesperson, units, profit, (@row_number:=@row_number + 1) AS position
FROM sales AS t ORDER BY profit DESC;

The problem happens when I transfer this to php:

<?php 
    
$sql = "SELECT salesperson, units, profit, (@row_number:=@row_number + 1) AS positionn"
. "FROM sales AS t ORDER BY profit DESC";
$result = mysqli_query($conn, $sql); 
$resultCheck = mysqli_num_rows($result);
if ($resultCheck > 0){
    while ($row = mysqli_fetch_assoc($result)) 
        echo $row['salesperson'] . " " . $row['units'] . " " . $row['profit'] .$row['position'] . "<br>";
}

It outputs the first three rows but not the fourth. The alias created with SQL.

Tags:

2

Answers


  1. 0

    First of all, you choose not exactly the best way to debug your output. Stringifying $row['position'] directly (by piping it into echo) hides a lot of useful info that you would be able to find otherwise.

    Second, the query in your second snippet misses actually setting up that variable, like this:

    $sql = <<<QUERY
  SELECT salesperson, units, profit, 
         (@row_number:=@row_number + 1) AS position
  FROM sales AS t, (SELECT @row_number := 0) AS r
  ORDER BY profit DESC
QUERY;

$result = mysqli_query($conn, $sql); 
$resultCheck = mysqli_num_rows($result);
if ($resultCheck > 0){
  while ($row = mysqli_fetch_assoc($result)) {
    echo "$row[salesperson] $row[units] $row[profit] $row[position]<br>";
  }
}
    Login or Signup to reply.
  2. 0

    Additionally, in the latest version of MySQL (8.0+) window functions are finally supported which avoids the use of @ session variables. Doing so you can also add row numbers by group with PARTITION BY clause:

    SELECT salesperson
       , units
       , profit
       , ROW_NUMBER() OVER(ORDER BY profit DESC) AS `overall_position`
       , ROW_NUMBER() OVER(PARTITION BY salesperson
                           ORDER BY profit DESC) AS `salesperson_position`
FROM sales
ORDER BY profit DESC
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search