Page 1 of 1

How many rows in my table?

Posted: Sat Feb 01, 2003 1:33 am
by JavaScript
How can i count the number of rows in my table and display them to the browser?

And if i need to count the number of rows in multiple tables (e.g. 7 tables), how can i do that?

My table contains a field of ID (auto increment), but since i sometimes delete certain rows, this ID value doesn't show the exact rows in the table

Thank for you help!

Posted: Sat Feb 01, 2003 5:31 am
by Rob the R
Assuming a MySQL database:

Code: Select all

$sql = "select count(*) from table_name" ;
$result = mysql_query($sql) ;
$row = mysql_fetch_array($result) ;
echo "table_name has " . $row[0] . "rows." ;
You would do this for each table you wish to count the rows of.

Posted: Sat Feb 01, 2003 5:44 am
by bionicdonkey
you could also do...

Code: Select all

<?php
$query = mysql_query("SELECT * FROM table");
$num_rows = mysql_num_rows($query);
echo $num_rows;
?>

Posted: Sat Feb 01, 2003 11:06 am
by Stoker
..you should NEVER do SELECT * FROM.. to count rows, that is extremely ineffective..

Posted: Sat Feb 01, 2003 12:11 pm
by AVATAr
bionicdonkey wrote:you could also do...

Code: Select all

<?php
$query = mysql_query("SELECT * FROM table");
$num_rows = mysql_num_rows($query);
echo $num_rows;
?>
Use this only if you are going to display the result of the query... if you only need the number of rows use

Code: Select all

SELECT count(row) FROM table

So...

Posted: Sun Feb 02, 2003 12:11 am
by JavaScript
So... what does the exact code look like?

My table structure is here

Code: Select all

table my_tutorial (
id int not null auto_increment,
title varchar(255) not null,
author varchar(55) not null,
description text not null,
date int(14) not null,
tutorial mediumtext not null,
view int not null,
primary key (id),
unique id (id)
)
I just need to count how many rows in that table, then print out something like

We have $num_of_rows tutorials

One more question (This is another topic in this forum, but since i can't figure out the way, i ask it in this real thread)

If i want to show

The latest tutorial is link_to_id_of_the_last_row $title_of_the_last_row

How can i do that?

the solution

Posted: Sun Feb 02, 2003 8:26 am
by AVATAr
The solution is in previous posts!!!!

Code: Select all

<?php
$sql = "select count(id) from my_tutorial" ; 
$result = mysql_query($sql) ; 
$row = mysql_fetch_array($result) ; 
echo "we have " . $row[0] . " tutorials." ; 
?>
For the second question you have to change the SQL statement and use MAX(id).

Yeah...

Posted: Sun Feb 02, 2003 8:47 am
by JavaScript
The code surely helped

But for now, another problem appears - i can't update column view

Code: Select all

$result = mysql_query("select * from my_tutorial where id=$id");
while($row = mysql_fetch_array($result)) &#123;
$v = $row&#1111;view];
$vplus = $v + 1;
$view_update = mysql_query("update my_tutorial where id=$id set view = '$vplus'");
&#125;
but it isn't add 1 to the existing number

Re: Yeah...

Posted: Sun Feb 02, 2003 11:26 am
by AVATAr
Check:

Code: Select all

<?php
$v = $row["view"];
?>

Posted: Wed Feb 05, 2003 8:17 am
by Caroline
Your code contains error in the SQL statement

Code: Select all

$view_update = mysql_query("update my_tutorial &#1111;b]SET&#1111;/b] view = view + 1 where id=$id");
Use that! It will work