Jump to content


Photo

get total number of rows from mySQL


  • Please log in to reply
6 replies to this topic

#1 meckr

meckr
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 28 August 2006 - 12:29 AM

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

Here is my entire script
//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>");


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

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

please help.
And what is more, I agree with everything I have just said!

Education is what remains after one has forgotten everything he learned in school. --Albert Einstein.

www.tspadvantage.com

#2 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 28 August 2006 - 12:37 AM


example ok what this * for ?

SELECT COUNT(store_name)
FROM Store_Information
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#3 meckr

meckr
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 28 August 2006 - 01:01 AM

Thanks for your help but I have tried my current query from within SQL and it gives me the correct number.
And what is more, I agree with everything I have just said!

Education is what remains after one has forgotten everything he learned in school. --Albert Einstein.

www.tspadvantage.com

#4 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 28 August 2006 - 01:16 AM

working example ok.
<?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'];

}

?>

Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#5 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 28 August 2006 - 01:17 AM

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

$numrows = mysql_fetch_row($res);

to:

$numrows = mysql_result($res, 0);

http://www.php.net/mysql_result

#6 meckr

meckr
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 28 August 2006 - 01:14 PM

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

$numrows = mysql_fetch_row($res);

to:

$numrows = mysql_result($res, 0);

http://www.php.net/mysql_result

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!
And what is more, I agree with everything I have just said!

Education is what remains after one has forgotten everything he learned in school. --Albert Einstein.

www.tspadvantage.com

#7 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 28 August 2006 - 01:28 PM

or you could just use mysql_num_rows.

$numrows = mysql_num_rows($res);
echo $numrows;
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users