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() 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() — Sean
– Sean, Commented Dec 28, 2016 at 3: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
– Ricardo Ortega Magaña, Commented Dec 28, 2016 at 3:23
For your own good, stop using mysql_* functions with immediate effect. They are deprecated. — asprin
– asprin, Commented Dec 28, 2016 at 3:32

dhruv jadia · Accepted Answer · 2016-12-28 04:16:41Z

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, 2016 at 4:16

dhruv jadia

1,6802 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dhruv jadia Over a year ago

Welcome :) I here to help

Panda · Accepted Answer · 2016-12-28 03:44:35Z

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, 2016 at 3:44

answered Dec 28, 2016 at 3:30

Panda

6,8846 gold badges46 silver badges61 bronze badges

11 Comments

manikandan Over a year ago

Hi thanks for your suggestion, i tried the same still i am getting the below error.

Sean Over a year ago

@Panda, you recommend to use mysqli_multi_query(), but use mysqli_query($conn, $sel)?

manikandan Over a year ago

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given

Panda Over a year ago

@manikandan It's a typo, change mysqli_query to mysqli_multi_query, updated answer

Panda Over a year ago

@Sean Thanks for pointing out, it's a typo since I usually don't use multi_query :D I've updated my answer

|

J Ha · Accepted Answer · 2016-12-28 03:43:29Z

1

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++
}
?>

answered Dec 28, 2016 at 3:43

J Ha

1,30213 silver badges17 bronze badges

1 Comment

manikandan Over a year ago

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.

Pankaj · Accepted Answer · 2019-12-13 13:15:59Z

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.

Collectives™ on Stack Overflow

How to print row number in mysql in PHP application

4 Answers 4

1 Comment

11 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

11 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related