How to print row number in mysql in PHP application

Question

I have a mysql table need to display the data along with the row number in front end aplication. The following query works perfectly in phpMyadmin

SET @row_num=0; SELECT (@row_num:=@row_num+1) AS num,INV,DOS,PTNAME,BAL,PROV     from sheet;

But when i use the same code in php projects it is returning the following error Warning: mysql_fetch_array(): supplied argument is not a valid MySQL

Below is my code:

<?php 
$conn = mysql_connect("localhost","root","");
mysql_select_db("omega",$conn);
$sel="SET @row_num=0; SELECT (@row_num:=@row_num+1) AS             num,INV,DOS,PTNAME,BAL,PROV from sheet";
$sqlquery=mysql_query($sel);

while($dis=mysql_fetch_array($sqlquery))
{
echo"<tr>";
echo "<td>".$dis['num']."</td>";
echo "<td>".$dis['INV']."</td>";
echo "<td>".$dis['DOS']."</td>";
echo "<td>".$dis['PTNAME']."</td>"; 
echo "<td>".$dis['BAL']."</td>";    
echo "<td>".$dis['PROV']."</td>";       
echo"</tr>";
}

Your error message is caused because [`mysql_query()`](http://php.net/manual/en/function.mysql-query.php) *sends a unique query (**multiple queries are not supported**)*. `**1** SET @row_num=0 ; **2** SELECT ( ...`. You need to update to `mysqli` and use [`mysqli_multi_query()`](http://php.net/manual/en/mysqli.multi-query.php) — Sean, Dec 28 '16 at 03:23
You probably getting wrong data back, since there are 2 commands (Set, and select) on your query, try Query the Set and then The Select. In a context, that problem could be solved with a $row=1; before the loop, and inside the loop with: $row++; — Ricardo Ortega Magaña, Dec 28 '16 at 03:23
For your own good, stop using `mysql_*` functions with immediate effect. They are deprecated. — asprin, Dec 28 '16 at 03:32
@dhruvjadia i can not add id fields.user will be able to sorts the data and the query should display with the row number. — manikandan, Dec 28 '16 at 04:15
@dhruvjadia dude you are a genius thanks it works with mysql_query itself. — manikandan, Dec 28 '16 at 04:30

score 5 · Accepted Answer · answered Dec 28 '16 at 04:16

5

change from

$sel="SET @row_num=0; SELECT (@row_num:=@row_num+1) AS num,INV,DOS,PTNAME,BAL,PROV from sheet";

to

$sel="SELECT s.*, @rownum := @rownum + 1 AS num FROM sheet s, (SELECT @rownum := 0) r";

answered Dec 28 '16 at 04:16

dhruv jadia

1,684
2
15
28

Welcome :) I here to help – dhruv jadia Dec 28 '16 at 04:34

Panda · Answer 2 · 2016-12-28T03:44:35.053

2

Your query doesn't work because you set 2 queries in mysql_query which is not supported. Instead use mysqli_multi_query() function.

Anyway, mysql_* functions are already deprecated, so use mysqli_* functions instead.

$conn = mysqli_connect("localhost", "root", "", "omega");

$sel = "SET @row_num=0;";
$sel .= "SELECT (@row_num:=@row_num+1) AS num, INV, DOS, PTNAME, BAL, PROV from sheet";
$sqlquery = mysqli_multi_query($conn, $sel);

while($dis = mysqli_fetch_array($sqlquery))
{

// rest of your code

edited Dec 28 '16 at 03:44

answered Dec 28 '16 at 03:30

Panda

6,955
6
40
55

Hi thanks for your suggestion, i tried the same still i am getting the below error. – manikandan Dec 28 '16 at 03:42
@Panda, you recommend to use `mysqli_multi_query()`, but use `mysqli_query($conn, $sel)`? – Sean Dec 28 '16 at 03:43
Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given – manikandan Dec 28 '16 at 03:44
@manikandan It's a typo, change `mysqli_query` to `mysqli_multi_query`, updated answer – Panda Dec 28 '16 at 03:44
@Sean Thanks for pointing out, it's a typo since I usually don't use `multi_query` :D I've updated my answer – Panda Dec 28 '16 at 03:45
yes i tried both mysqli_multi_query and mysqli_query – manikandan Dec 28 '16 at 03:45
@Panda even after changing mysqli_query to mysqli_multi_query i am getting the below error:- Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given. the parameter 1 is $conn = mysqli_connect("localhost","root","","omega"); – manikandan Dec 28 '16 at 04:04
@manikandan The connection seems to be correct, the problem might be to do with the queries – Panda Dec 28 '16 at 04:06
MySQLi Connect -- `mysqli_connect("localhost","my_user","my_password","my_db");` – Panda Dec 28 '16 at 04:06
@Panda $sql="SET @row_num=0;"; $sql .= "SELECT (@row_num:=@row_num+1) AS num,INV,DOS,PTNAME,BAL,PROV from sheet"; i am new to this what could possibly wrong with this query. – manikandan Dec 28 '16 at 04:18
@Panda thanks panda after changing the query it works fine. – manikandan Dec 28 '16 at 04:33

score 1 · Answer 3 · answered Dec 28 '16 at 03:43

i took a look and think you were tried to make complex, see the code below, it's easy for your purpose

<?php 
$conn = mysql_connect("localhost","root","");
mysql_select_db("omega",$conn);
$sel="SET INV,DOS,PTNAME,BAL,PROV from sheet";
$sqlquery=mysql_query($sel);
$i=0;
while($dis=mysql_fetch_array($sqlquery))
{
echo"<tr>";
echo "<td>".$i."</td>";
echo "<td>".$dis['INV']."</td>";
echo "<td>".$dis['DOS']."</td>";
echo "<td>".$dis['PTNAME']."</td>"; 
echo "<td>".$dis['BAL']."</td>";    
echo "<td>".$dis['PROV']."</td>";       
echo"</tr>";
$i++
}
?>

I could use this method if i just want to print the number but, i want my query to return the row number based on the row number returned by the query i have other functionality. — manikandan, Dec 28 '16 at 03:51

score 0 · Answer 4 · answered Dec 13 '19 at 13:15

Here I have solution for you , How you will get total rows number using PHP nd MYSQL . Please see below code.

    <?php
           $pdoconnection = 
          "mysql:host=localhost;dbname=database_name;charset=utf8mb4";
           $options = [
          PDO::ATTR_EMULATE_PREPARES => false,
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
         PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
      ];
   try {
        $pdo = new PDO($pdoconnection, "userName", "passWord", $options);
        $emp=$pdo->prepare("SELECT count(*) FROM emp_tab");
        $emp->execute();
        $emprow = $emp->fetch(PDO::FETCH_NUM);
      //total count
       $empcount = $emprow[0];
       } catch (Exception $e) {
          error_log($e->getMessage());
             exit('oops ! some problems');
          }
 ?>

I hope , you will get some idea using in php and mysql to get total number of rows.

How to print row number in mysql in PHP application

4 Answers4

Linked