CODEWITHSUNDEEP
phpmysql
Brandon
Brandon

Reputation: 1

PHP - mysqli_num_rows - Saying I only have 1 row when I have many rows

I am trying to display the number of rows in a table using mysqli_num_rows. When I print the results, it says I only have 1 row, when I really have several rows.

When I tested the SQL in phpMyAdmin, it counts the correct number of rows. But when I display the results on my web page, it counts only one row.

Please help me. What I am doing wrong?

$mysqli = new mysqli("localhost", "myusername", "mypass", "mydatabase");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

if ($result = $mysqli->query("SELECT COUNT(*) FROM mytable")) {

    /* determine number of rows result set */
    $row_cnt = $result->num_rows;

    printf("Result set has %d rows.\n", $row_cnt);

    /* close result set */
    $result->close();
}

/* close connection */
$mysqli->close();

The above codes prints the following, no matter how many rows I have:

Result set has 1 rows.

Upvotes: 0

Views: 2167

Answers (4)

Carl
Carl

Reputation: 99

You could do like this if the database i small. 

if ($result = $mysqli->query("SELECT * FROM mytable")) {

    /* determine number of rows result set */
    $row_cnt = $result->num_rows;

    printf("Result set has %d rows.\n", $row_cnt);

    /* close result set */
    $result->close();
}

With i big database you could be doing like this as mentioned in the comments. I'm not so good with PDO or object oriented requests so this will be procedural.

$conn = mysqli_connect($servername, $username, $password, $dbname);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT COUNT(*) FROM users";
$result = mysqli_query($conn, $sql);

if(mysqli_num_rows($result)){
    $row = mysqli_fetch_row($result);
    printf("Result set has %d rows.\n", $row[0]);
}

mysqli_close($conn);

Upvotes: 0

Naruto
Naruto

Reputation: 4329

<?php
    $mysqli = new mysqli("localhost", "myusername", "mypass", "mydatabase");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$sql="SELECT * FROM mytable";

if ($result=mysqli_query($con,$sql))
  {
  // Return the number of rows in result set
  $rowcount=mysqli_num_rows($result);
  printf("Result set has %d rows.\n",$rowcount);
  // Free result set
  mysqli_free_result($result);
  }

mysqli_close($con);
?>

Use mysqli_num_rows() method to fetch number of rows in the resultset.

Refer link http://php.net/manual/en/mysqli-result.num-rows.php

Upvotes: 0

Dominik Dosoudil
Dominik Dosoudil

Reputation: 959

Actually when you use COUNT, the result of the query is the number of rows. I usually try to write the sql query straight to phpmyadmin to see what happens. It would reveal the mistake ;)

Upvotes: 1

tadman
tadman

Reputation: 211670

The result of a simple COUNT(*) statement is always one row. You want to fetch that row and get the value returned from the first column.

Upvotes: 3

Related Questions