How to count for how many rows exist in a mysql table

PHP
1

How to count for how many rows exist in a mysql table.

I have tried this:

$row_count=mysql_query(SELECT COUNT (*) FROM texts);

But this gives me the following error:

Parse error: syntax error, unexpected T_STRING in /home/a6110619/public_html/viewer.php on line 12

1 Like
2

You should not still be using mysql. It is obsolete, removed from the latest php and depreciated in earlier versions.
Use mysqli or PDO.

1 Like
3

Yeah, I heard about that…

Okay I am going to try mysqli than.
Can you tell me how do I count rows in a db with mysqli than?

4

Something like this should do it.

$sql = $db->query("SELECT COUNT(*) FROM table");
$row = $sql->fetch_row();
$count = $row[0];

$db is your database connection.

1 Like
5

If you also want to be able to actually process the data as well as count it you can do something like:

$query = "SELECT code, txt FROM texts";
if ($stmt = $mysqli->prepare($query);) {
$stmt->execute();
$stmt->store_result();
$numrows = $stmt->num_rows;
// processing of the returned data goes hee - using $stmt->fetch() to retrieve each row
$stmt->free_result();
$stmt->close();
}
3 Likes
6

Hey felgall, thanks for the help.

I actually did manage to count rows and process data. But not quite the same way you did.
I am going to post my code so you might tell what is the difference between your way and mine, also which one would be more secure.

else{

$sql1 = $conn->query("SELECT * FROM texts");


$row_count= mysqli_num_rows($sql1);

$e= 1;

$max_row= $row_count - $e;

$count= rand(0,$max_row);

$sql = $conn->query( "SELECT * FROM texts WHERE id=$count ");


}

$row = $sql->fetch_assoc();

$file= $row["name"];

Btw, before the else I just have another sql request in $sql.

1 Like
7

Selecting all rows in the database just to calculate a count is a highly problematic way to achieve something so simple. Just use a basic select query with a count() function and fetch the first column of the first row.

3 Likes
8

I agree. I offered the code as an alternative approach for when you also need to process all the data.

1 Like
9

How many rows are there likely to ever be in the “texts” table?

1 Like
10

As the OP is having to ask how to count them there are unlikely to be very many. Probably a tiny to microscopic sized table.

1 Like
11

You can try the following query to get rows per table:

SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘YOUR SCHEMA’;

1 Like
12

If it’s only a few then the OP may as well just go straight for using ORDER BY rand() and combine that with LIMIT 1 as for that few it’s not worth worrying about any performance penalty and it saves them an unneeded query

2 Likes
13

Hi, lets say texts table will be the same as “videos” would be for youtube. That means if people use the website, at some point there will be a lot. Hopefully haha.

14

If you expect it to become very busy, then efficiency may be an issue.
What is the objective of the script? To select just the ‘name’ field from one random entry form the table?
That can be done with one query as SpacePhoenix describes.

$sql = $conn->query( "SELECT name FROM texts ORDER BY rand() LIMIT 1 ");

Note this selects only ‘name’. Use * only if you need all columns data.

15

I wanted to count the total number of rows, so that I can use rand() to get a random number between 0 and total_nb_of_rows, and than I get the txt name for the numver rand gave me.
All of this had one goal: choose a random txt name from texts table.

So my code works, but I really think I have done some bad code, because there must be a much more simple way to do such a request.
Below is my code, that actually does what I want, but I think this is the worst way it could be done, and I can do better. (Once again I am not new to programming, but new to web-dev(php and sql) ).

$sql1 = $conn->query("SELECT * FROM texts");


$row_count= mysqli_num_rows($sql1);

$e= 1;

$max_row= $row_count - $e;

$count= rand(0,$max_row);

$sql = $conn->query( "SELECT * FROM texts WHERE id=$count ");
16

The query above.
ORDER BY RAND() will shuffle the table, LIMIT 1 will limit the number of entries selected to just one. The result should be one random selection, of just the name column.

1 Like
17

Yeah… That will be much better. Thank you very much.

Topic can be closed, but I couldn’t find how to do it myself. May be it doesn’t exist on sitepoint.

Anyways, thank you a lot guys. See you on the next question :smile:

18

Tests I did some time ago showed the break even point for retrieving just one random record to be around about 1000 records. If you want to retrieve more than one then the number of records before order by rand should be discarded gets progressively higher - if you want 30 records then order by rand is still more efficient with many millions of records.

19

Was that just on MySQL only? it’d be interesting to see if the break-even point is similar with different database server software and different storage engines

20

Yes that was mySQL only - I’d expect other databases to be different (assuming they even support order by rand)