Is there a built-in hasNext property when looping MySQL results?

[soycharliente]

In a language like Velocity, when I loop through an array and process nodes, I can do something like this:

 

<p>
#foreach ( $item in $list )
   ${item.value}
   #if ( $foreach.hasNext )
     <br />
  #end
#end
</p>

 

The loop object knows if the current node in the list has another node after it. It makes it easy to get the line break only when needed.

 

What would be the PHP equivalent to this? So far I'm rocking this:

 

$num = mysql_num_rows($result);
$count = 0;
if ( $num > 0 )
{
  echo '<p>';
  while ( $r = mysql_fetch_array($result, MYSQL_ASSOC) )
  {
     printf("%s", $r['word']);
     if ( $count++ != $num-1 ) { echo '<br />'; }
  }
echo '</p>';
}

 

Curious if I could stop counting and use something built-in.

[Jessica]

Typically I would use an array and implode for something like that. 

[Psycho]

Why store the result of mysql_num_rows() in a variable if it will only be used once? But to your question, I'm not really sure what you are asking. The PHP code you posted appears that it would only do a line break when completing all of the records of the result. So, not sure why you added all that complexity.

 

What I *think* you are trying to do is insert a line break between what I would call "parent" records in the result. For example, if I did a query of an authors table and JOINed it with a books table I would expect to get multiple records where the author data is the same. So, I might want to put a line break between the records for each author in the output. The Velocity code you posted seems to be similar to processing an array in PHP - which can be done with foreach loops.

 

But, databases do not have that kind of structure in the results (at least none of the ones I've used). But, the solution is pretty simple - just use a variable as a flag.

 

if (mysql_num_rows($result))
{
  //Set flag to track changes in author
  $currentAuthor = false;
  echo '<p>';
  while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
  {
  printf("%s", $r['word']);
  //Check if this record has the same author as the last
  if ($currentAuthor != $row['author_id'])
  {
	 echo '<br />';
	 $currentAuthor = $row['author_id']
  }
  }
   echo '</p>';
}

[Jessica]

I think OP is trying to enter a line break after every entry except the last. 

[soycharliente]

Typically I would use an array and implode for something like that.

 

You know, I've done that before. I don't know why I didn't think to do that now.

 

Would you add each item to an array inside the while loop, or is there a way to return the array straight from MySQL?

 

Doing this isn't returning the entire array.

 

$words = mysql_fetch_array($result);
//print_r($words);

Edited January 6, 2013 by charlieholder

[salathe]

There is nothing built-in for your particular example. You could look at rolling your own iterator with the functionality that you need; something like a stripped-down version of a CachingIterator might be handy.

[salathe]

Why store the result of mysql_num_rows() in a variable if it will only be used once? 

 

Look at the example, it is used (number-of-results + 1) times.

[soycharliente]

Why store the result of mysql_num_rows() in a variable if it will only be used once? But to your question, I'm not really sure what you are asking. The PHP code you posted appears that it would only do a line break when completing all of the records of the result.

 

It gets used every time? When the count does not equal the number of results, echo the line break. Echo the line break every time except the last.

 

I think OP is trying to enter a line break after every entry except the last.

 

Correct.

 

And this code works. Works perfectly. Just wondering if there's a better way.

Edited January 6, 2013 by charlieholder

[salathe]

Would you add each item to an array inside the while loop, or is there a way to return the array straight from MySQL?

 

We do not recommend using the mysql_* family of functions when writing new code.  The better and more up-to-date alternatives (MySQLi and PDO) have methods available for returning the whole result set into an array with one method call.

[soycharliente]

We do not recommend using the mysql_* family of functions when writing new code.

 

Yeah. I learned that today as well. Was poking around on SO for a while trying to find some other stuff and read a comment about that there.

 

I started off today trying to restructure some of my code using mysqli_* but ran into a few issues (number of parameters are a bit different, etc). Guess I just need to hit the manual a little harder and deal with it. lol

[salathe]

Guess I just need to hit the manual a little harder and deal with it. lol

 

Good luck, and feel free to ask more questions here too.

[PFMaBiSmAd]

Two words of warning about the mysqli_fetch_all statement. It requires the MySQL Native Driver (php 5.3+ and php must be built to use the native driver.)

 

It also returns an array of the row arrays, so it actually wouldn't directly allow you to implode the data.

[soycharliente]

We do not recommend using the mysql_* family of functions when writing new code. The better and more up-to-date alternatives (MySQLi and PDO) have methods available for returning the whole result set into an array with one method call.

 

Sounds like two birds. By switching to MySQLi, I'll be able to return the result as an array (giving me the option to use implode) AND I'll be doing the programming correctly. lol

[soycharliente]

It requires the MySQL Native Driver (php 5.3+ and php must be built to use the native driver.)

 

I have confirmed that I'm using PHP 5.3+ (using 5.4), but I'm not sure what the "built to use the native driver" part means. I'd email my hosting provider, but I'm not quite sure what question to ask as I don't fully understand what I'm checking for. Can you clarify a bit more?

Edited January 6, 2013 by charlieholder

[Barand]

Instead of adding "<br>" after every one but the last, try adding "<br>" before every one but the first. You always know when you are on the first one.

Edited January 6, 2013 by Barand

[Jessica]

Barand, how do you always know you're on the first?

[Barand]

$first = true;
while (whatever) {
  if (!$first) {
  echo '<br>';
  }
  else {
  $first = false;
  }
  echo "other data";
}

[Psycho]

-- Deleted --

Edited January 7, 2013 by Psycho

[Jessica]

I thought you were saying there was a built in way to know, like what he was asking for. 

[Barand]

I suppose what I meant is that you can easily detect when you are on the first, whereas you cannot detect when you are on the last without the extra overhead of counting

[soycharliente]

I couldn't get mysqli_fetch_all to work (probably because I don't have mysqlnd installed). Still waiting on feedback from hosting support.

 

This is what I ended up with.

 

while ( $r = mysqli_fetch_array($result, MYSQLI_ASSOC) )
{
 $words[] = $r['word'];
}

echo implode('<br/>', $words);

Edited January 11, 2013 by charlieholder

[PFMaBiSmAd]

Even with mysqli_fetch_all, your code would still look like -

 

foreach(mysqli_fetch_all($result, MYSQLI_ASSOC) as $r)
{
 $words[] = $r['word'];
}

echo implode('<br/>', $words);