cutting out mysql_num_rows?

[freelance84]

When fetching results from a query I have always used the following method (the one i was taught)

 

1. get the query

 

2. if i know there might be more than one row returned, count the rows

 

3. use a for loop to get all the mysql_fetch_row results.

 

Is there a more efficient way?

 

eg, i am now writing a section that i know will either return one row or two, but never anymore, can i run a foreach on a mysql fetching function to get all the rows instead of counting first? (ie cutting out the middle man)

[kickstart]

Hi

 

No reason you need to get the number of rows first unless you actually want that info.

 

All the best

 

Keith

[trq]

Hi

 

No reason you need to get the number of rows first unless you actually want that info.

 

All the best

 

Keith

 

Except that you should always check your query actually returns a result before using it.

 

A typical SELECT query should pretty much look like....

 

$sql = "SELECT foo FROM bar";
if ($result = mysql_query($sql)) {
  if (mysql_num_rows($result)) {
    while ($row = mysql_fetch_assoc($result)) {
      // use row
    }
  } else {
    // no result found
  }
} else {
  // query failed
}

 

A while loop is generally better than a foreach in such a case, but if you only expect 1 row, you should use a LIMIT clause in your query and obviously can leave the while loop out all together.

[kickstart]

Hi

 

Given that mysql_fetch_assoc() will return false if there are no rows it doesn't seem worthwhile to check the number of rows are greater than 0 first. Or am I missing something there?

 

If there is a single row (eg, from a LIMIT) then just use an IF instead of a WHILE.

 

All the best

 

Keith

[trq]

Given that mysql_fetch_assoc() will return false if there are no rows it doesn't seem worthwhile to check the number of rows are greater than 0 first. Or am I missing something there?

 

It gives you the opertunity to display a message if no records are found.

[the182guy]

My preferred method for this is to extend the MySQLi class and add a method called something like fetchAssocList() or fetchObjectList(), and another method for getting  a single row.

 

This is good for when you're just selecting rows from the database because it cuts down on repeated and duplicated code. Also, it makes the code more readable and tidy because you can concentrate on the logic and flow of the program rather than getting bogged down with repetitions of that block of code.

 

Another benefit is if you ever want to change the RDMS to something else such as MSSQL or Oracle then it will be reasonably simple to change your fetch methods, however it would be a pain to have to go through hundreds of mysql_fetch_row mysql_num_rows calls.

[kickstart]

It gives you the opertunity to display a message if no records are found.

 

Fair point, but that comes under having an actual need for the count (and can be done in the else of an IF when you are expecting a single record rather than looping round multiples with a WHILE).

 

All the best

 

Keith

[trq]

So, your saying if you execute a SELECT that returns no records you don't display an error message or something?

 

I would, ALWAYS. The easiest way is to check to see how many results you got.

[kickstart]

Hi

 

Quite often no. If it is fully expected that there should be at least one returned then yes, but often zero is valid (especially when processing large amounts of batched up data).

 

All the best

 

Keith

[trq]

Quite often no. If it is fully expected that there should be at least one returned then yes, but often zero is valid

 

I'm not sure I understand. SELECT queries are used to retrieve data, why would selecting zero data be valid?

[freelance84]

WOW! Cheers guys, that's a lot of info 

[kickstart]

I'm not sure I understand. SELECT queries are used to retrieve data, why would selecting zero data be valid?

 

Many examples. How about a list of resellers for a product and then extracting their clients. Might be none or hundreds of clients, and both are valid. On screen you might want a message saying "none", but an extract for later processing for statistics wouldn't want that. Or having a recursive routine looping through people and their children and displaying them indented on the screen (or any other tree structure), where a message saying "no children" is likely to be confusing.

 

On occasions when you want the first record processed differently you can do:-

 

$sql = "SELECT foo FROM bar";
if ($result = mysql_query($sql)) 
{
if ($row = mysql_fetch_assoc($result)) 
{
	// Do first record processing and call routine to process records
	while ($row = mysql_fetch_assoc($result)) 
	{
		// Call routine to process records
	}
} 
else 
{
	// no result found
}
} 
else 
{
// query failed
}

 

If you are expecting 1 result then

 

$sql = "SELECT foo FROM bar LIMIT 1";
if ($result = mysql_query($sql)) 
{
if ($row = mysql_fetch_assoc($result)) 
{
	// Process record
} 
else 
{
	// no result found
}
} 
else 
{
// query failed
}

 

No need or benefit in either case from doing an extra call to check the number of records returned

 

All the best

 

Keith