get total number of rows from mySQL

[meckr]

OK I need to get the total number of rows in a database

Here is my entire script
[code]
//connect to server with username and password, this is the default settings
require "includes/database.php";

//we now get get the sort parameter from the url
switch ( @ $_GET ['sort'] )
{
    // This checks whether sort is either one of the following
    // id, offender or tstamp
    // if it is it'll set sort_order to sort URL parameter
    case 'id':
    case 'offender':
    case 'tstamp':
        $sort_order = $_GET['sort'];
    break;

    // if sort was not one of the above we use a defualt value, id
    default:
        $sort_order = "id";
    break;
}

$sql = "SELECT count(*) FROM banned_ips";
$res = mysql_query($sql);
$numrows = mysql_fetch_row($res);

//our SQL query
$sql_query = "SELECT id, offender, tstamp FROM banned_ips ORDER BY $sort_order ASC";

//store the SQL query in the result variable
$result = mysql_query($sql_query);

echo ("
<div align=\"center\">
<div class=\"wrapper\">
<table class=\"data\" cellspacing=\"4\" cellpadding=\"4\" width=\"500\">
<thead>
<th align=\"center\" colspan=\"3\"><b><h2>Total Bans: $numrows</h2></b></th>
<tr align=\"left\">
    <th><a href=\"?sort=id\">Sort by ID</a> </th>
    <th><a href=\"?sort=offender\">Sort by IP ADDRESS</a> </th>
    <th><a href=\"?sort=tstamp\">Sort by DATE OF BAN</a></th>
</tr>
</thead>");

  if(mysql_num_rows($result))
  {
    //output as long as there are still available fields
    while ( $row = mysql_fetch_row ( $result ) )
    {

echo ( "<tbody id=\"b\">
<tr align=\"left\">
        <td><b>ID:</b> $row[0] </td>
      <td><b>IP:</b> $row[1] </td>
      <td><b>Date:</b>" . date('Y-m-d H:i:s',$row[2]) . "<br></td>
  </tr>
  </tbody>");
 
  }
}


//if no fields exist
else
{
  echo "There are currently no bans at this time!";
}
echo ("</table>
</div>
</div>");

[/code]

however, all I get for the results is: Total Bans: Array.

I can't figure out what is wrong ???

please help.

[redarrow]

example ok what this * for ?

SELECT COUNT(store_name)
FROM Store_Information

[meckr]

Thanks for your help but I have tried my current query from within SQL and it gives me the correct number.

[redarrow]

working example ok.
[code]
<?php

$db=mysql_connect("xxxx","xxx","xxxx");
mysql_select_db("xxxx",$db);

$query="select count(*) as counted from xxxxx";
$result=mysql_query($query);

while($record=mysql_fetch_assoc($result)){

echo $record['counted'];

}

?>
[/code]

[hitman6003]

Keep your code the same as in your first post, but change:

[code]$numrows = mysql_fetch_row($res);[/code]

to:

[code]$numrows = mysql_result($res, 0);[/code]

http://www.php.net/mysql_result

[meckr]

[quote author=hitman6003 link=topic=105872.msg423095#msg423095 date=1156727825]
Keep your code the same as in your first post, but change:

[code]$numrows = mysql_fetch_row($res);[/code]

to:

[code]$numrows = mysql_result($res, 0);[/code]

http://www.php.net/mysql_result
[/quote]
Thanks Hitman6003.

It worked perfect.  I didn't even think about using mysql_result().  I spent hours and hours trying to figure that out and I tried almost everything except mysql_result(). 

You rock dude!

[.josh]

or you could just use mysql_num_rows.

$numrows = mysql_num_rows($res);
echo $numrows;