How many rows in my table?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
JavaScript
Forum Newbie
Posts: 7
Joined: Tue Jan 28, 2003 8:54 pm

How many rows in my table?

Post 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!
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post 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.
bionicdonkey
Forum Contributor
Posts: 132
Joined: Fri Jan 31, 2003 2:28 am
Location: Sydney, Australia
Contact:

Post 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;
?>
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

..you should NEVER do SELECT * FROM.. to count rows, that is extremely ineffective..
User avatar
AVATAr
Forum Regular
Posts: 524
Joined: Tue Jul 16, 2002 4:19 pm
Location: Uruguay -- Montevideo
Contact:

Post 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
JavaScript
Forum Newbie
Posts: 7
Joined: Tue Jan 28, 2003 8:54 pm

So...

Post 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?
User avatar
AVATAr
Forum Regular
Posts: 524
Joined: Tue Jul 16, 2002 4:19 pm
Location: Uruguay -- Montevideo
Contact:

the solution

Post 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).
JavaScript
Forum Newbie
Posts: 7
Joined: Tue Jan 28, 2003 8:54 pm

Yeah...

Post 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
User avatar
AVATAr
Forum Regular
Posts: 524
Joined: Tue Jul 16, 2002 4:19 pm
Location: Uruguay -- Montevideo
Contact:

Re: Yeah...

Post by AVATAr »

Check:

Code: Select all

<?php
$v = $row["view"];
?>
Caroline
Forum Commoner
Posts: 25
Joined: Thu Jan 30, 2003 1:38 pm

Post 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
Post Reply