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
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)) {
$v = $rowїview];
$vplus = $v + 1;
$view_update = mysql_query("update my_tutorial where id=$id set view = '$vplus'");
}
but it isn't add 1 to the existing number
Re: Yeah...
Posted: Sun Feb 02, 2003 11:26 am
by AVATAr
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 їb]SETї/b] view = view + 1 where id=$id");
Use that! It will work