[SOLVED] getting database count from mysql query

[heldenbrau]

I want to count the amount of rows in a database and display them, then increment the number and pass it to a filename.  The filename needs to be called case1, case2, case3, case4, etc.  depending on how many cases are already in the database.  I have tried the following code but I keep getting Parse error: syntax error, unexpected T_VARIABLE

 

$mysqli = new mysqli("localhost", "username", "pasword", "cases");
    if ($mysqli === false) {
      die("ERROR: Could not connect to database. " . mysqli_connect_error());
}
  $sql = "SELECT COUNT(*) FROM 'cases';
    if ($result = $mysqli->query($sql)) {
      $row = $result->fetch_assoc();
      $add = $row;
      $add ++;
      echo "$add";
      $newcase = "case"$add;
    }else{
      die("ERROR: Could not execute query: $sql. " . $mysqli->error);
}

 

[alexdemers]

Well, you have a lot of errors in that script. First, it only get the rows 1 time since you don't have any sort of loops. Second, there's a parse error: "case"$add; which should be concatenated with a dot:  "case".$add; Third, you try to select the table with its name surrounded in quotes. Remove the quotes after FROM.

 

Here's a much better code. But, right now, every loops overwrites the variable $newcase which you should handle to your likings.

 

<?php
$mysqli = new mysqli("localhost", "username", "pasword", "cases");
    if ($mysqli === false) {
      die("ERROR: Could not connect to database. " . mysqli_connect_error());
}

$sql = "SELECT * FROM cases";
$count = 0;
$result = $mysqli->query($sql);

while ($row = mysqli_fetch_array($result)) {
    $count++;
    echo $count;
    $newcase = 'case'.$count;
}

// die("ERROR: Could not execute query: $sql. " . $mysqli->error);
?>

[heldenbrau]

Thanks for typing that out.  When I run it nothing happens, is that because there is no data in the database?  The only data in the database is the columns waiting to be filled.

[heldenbrau]

I changed the code to below, but the $count isn't incrementing, it just stays at 0 even when there is an entry in the database.

 

<?php

$mysqli = new mysqli("localhost", "username", "password", "cases");
    if ($mysqli === false) {
      die("ERROR: Could not connect to database. " . mysqli_connect_error());
}

  $sql = "SELECT * FROM cases";
  $count = 0;
  $result = $mysqli->query($sql);

  while ($row = mysqli_fetch_array($result)) {
    $count++;
}
    if ($count==0){
    $count++;
    }
    $newcase = 'case'.$count;
    echo $newcase;
?>

[alexdemers]

Well, yes you need to have data in your table. Each row will increase the counter by 1. So if you have 100 records, $coutn will be = to 100 after the loop.

[mikesta707]

a much easier way to get the count of rows in your table is to use the mysqli_num_rows function

this will get your count:

$sql = "SELECT * FROM cases";

$result = $mysqli->query($sql);
$count = mysqli_num_rows($result)

 

EDIT: syntax error and also link to function: http://us2.php.net/manual/en/mysqli-result.num-rows.php

[heldenbrau]

I added a record to the database, so $count should count that and = 1, it should then increment by 1 because of the $count++ so end up as 2 and print out case2.  But it doesn't, it stays at 1.

[mikesta707]

well it doesn't get incremented because in your code

 

if ($count==0){
    $count++;
    }

 

this never runs because count is not zero. if you want to always increment the count function, then remove the if statement and simply have $count++

[heldenbrau]

Using the num rows has sorted it out.  Thanks.