Quick Q regarding Select *

Anon

I have a

mysql_query("SELECT * FROM $table WHERE something=something", $db);

//to count the number of rows.

$numrows=mysql_num_rows($query_rows);

$result = mysql_query("SELECT * FROM $table WHERE something=something
order by something", $db);

//then it's selected again to display all the results.

Does the script actually do select 2 times? Or does it keep the results of the first select?
Since I wish to return all the selected results, is it better to count() for the number of rows then select , or is there any other more efficient ways?

THanks

Anon

Why do you need to count your selection before doing your selection? You can just do the second query and put all of the results into an array and then count that if you need to know how many have been returned.

Yes 2 queries are run. Depending on how MySQL caches it's queries you may be getting some benefit from the first query, but you can't count on that.

lawnmowerman

Why do the 2nd select at all?

Once you have the result, you can count the returned rows as many times as you want and still use that set later on with mysql_fetch_row(), mysqld_fetch_array(), etc.

Just put your 'order by' clause in the first query.

Hope this helps!

-Rich

Anon

oops, sorry I left the limit out.. for the second query, I have a limit on the selected rows..

So I can't simply use that to count the number of rows since I need the total number of rows.

Should I stick with 2 selects *?

Anon

I guess that depends on how many rows there are going to be. If you are only going to have a few rows then you might as well just select them all and only display the number that you want to display. If you are going to have thousands of rows then stick with the 2 queries.

lawnmowerman

Oh, if you want the total number of rows in the database, you could use this for your first query:

SELECT count(*) as total FROM $table WHERE something=$something;

This will return a single result called 'total' which will contain the number of rows in the database.

-Rich

Anon

Hi Rich,

I don't quite get how to use the AS 'total' to return number of rows.

say I need $numrows as a variable later on to do some calculation, how can I pass the number of rows from using count() to the variable $numrows?

Thanks

lawnmowerman

If you're going to be doing much SQL work in PHP, I recommend reading the docs regarding MySQL:
http://www.phpbuilder.com/manual/ref.mysql.php

There seems to be a lot of functions there, but read through each one. You'll pick it up fast.

You can assign the "total" returned value like this:

<?php
$result = mysql_query( "SELECT count(*) as total FROM $table WHERE something=$something" );

$row = mysql_fetch_row( $result );

$numrows = $row[0];
?>

<?php
$result = mysql_query( "SELECT count(*) as total FROM $table WHERE something=$something" );

$row = mysql_fetch_array( $result );

$numrows = $row[total];
?>

-Rich

Anon

ok, i'll make sure to read it up..

what's confusing me is that I dont see the use of 'total' in this one:

$row = mysql_fetch_row( $result );

$numrows = $row[0];

and I tried to get rid of 'as total' and it still worked..

lawnmowerman

Very perceptive 🙂

In PHP, associative arrays (name indexed arrays, sometimes called a hash) are the same as numerically indexed arrays.

In a "normal" array (numerically indexed) you access the values stored in the array with a index number.

$array[0]
$array[23]
etc

An associative array is a "normal" numerically indexed array with an additional index for each value. This additional index is the "name" of the position in the array. Therefore, you can access the same value in an associative array two ways.

Let's say at the beginning of your script you have:

$array[foo] = 'bar';
$array[funky] = 'monkey';

The first value of the array, in this case the string 'bar', can be accessed using $array[foo] AND $array[0]. Remember that arrays start at 0 in PHP (and many other languages).

Likewise, 'monkey', the 2nd value, can be accessed using $array[funky] AND $array[1];

When you call '$row = mysql_fetch_row( $result )', PHP creates a new array (or resets an old array) called $row, which has a single index for each value. Since our select statement in question has a single returned field, $row contains a single value at index 0.

You must access the single value with $row[0].

When you call '$row = mysql_fetch_array( $result )', PHP creates an array called $row, which has 2 indexes for each value: a numerical index and an alphanumeric index which is the field name.

In this case, you can access the single returned value with $row[0] AND $row[total].

Using mysql_fetch_array() is nice because you don't have to know the order of the field in the result.

If you do "SELECT name, age FROM Users", you know that $row[0] contains the name field nd $row[1] contains the age field.

But, suppose you did not create the table or the database. You may know the names of the fields, but not necessarily the order that they were added to the table. So, if you do "SELECT * FROM Users", name could be the first field, last field, or anywhere in between.

But, if you use mysql_fetch_array(), you KNOW that you can access the name field as $row[name] even if you don't know what numeric index is also assigned to that field.

Does this help?

Let me know if you have any further questions.

-Rich

Anon

thanks for the detailed explanation 🙂

It'll take some time for me to digest..

lawnmowerman

Digest away! 🙂

If you need any clarifications, please let me know!

You can e-mail me directly, if you wish.

-Rich