0

Im always using while loop in generating all record in my database, and some of my friend told me that it is better to use foreach in generating record from a database, but i dont know how.

<?php
    $query =  mysql_query("select * from sampleTABLE");
    while($i =  mysql_fetch_array){
    echo $i['samplefieldName'];
    }
?>

My question is, how to display records from my database using foreach? and can some one compare it in the while loop in terms in syntax and generating its result, thank you.

Robert John Concepcion
  • 239
  • 1
  • 10
  • 27
  • 1
    "and some of my friend told me that it is better to use foreach in generating record from a database" --- don't listen that guy anymore, he doesn't understand what he is talking about. – zerkms Feb 06 '13 at 11:20
  • 1
    PS: when someone gives you some advice - don't believe blindly but ask some reference/proof – zerkms Feb 06 '13 at 11:21
  • sorry im noob im just Curious what other suggest... ;( – Robert John Concepcion Feb 06 '13 at 11:43
  • I guess the real lessons coming out of this question are: (1) Don't use `mysql_query()`; switch to a better API for your DB access and (2) Yes you can use `foreach` for recordsets if you're using one of those better APIs, but it's not any "better" than `while`; it's just a different way of doing the same thing. – SDC Feb 06 '13 at 12:21

4 Answers4

2

There is no need to use foreach instead of while here as @Zerkms says 

 while($i =  mysql_fetch_array( $query)){

however ou can do this by below code but i am sure its not good approach 

$result_list = array();
while($row = mysql_fetch_array($query)) {
   result_list[] = $row;
}

foreach($result_list as $item) {
   //you can now echo $item ; or whatever you want
}

Note

  1. The entire ext/mysql PHP extension, which provides all functions named with the prefix mysql_, is officially deprecated as of PHP v5.5.0 and will be removed in the future. So use either PDO or MySQLi

Good read

  1. The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead
  2. PDO Tutorial for MySQL Developers
  3. Pdo Tutorial For Beginners
Community
  • 1
  • 1
NullPoiиteя
  • 56,591
  • 22
  • 125
  • 143
2

Firstly, the mysql_xxx() functions are deprecated. They are not recommended for use. There are two alternatives in PHP that are recommended instead -- mysqli() and PDO.

The older mysql_xxx() functions do not allow you to use foreach to loop through a recordset.

However, both the newer alternative APIs do allow this, as they implement the Iterator interface.

So yes, it is possible to use foreach to loop through a recordset in PHP, but not with the old mysql_xxx() functions.

You could write code like this:

$conn = new mysqli(....);
foreach ( $conn->query('SELECT ....') as $row ) {
    print_r($row);
}

or like this:

$db = new PDO('mysql:....', $user, $pass);
foreach ($db->query('SELECT ....') as $row) {
    print_r($row);
}

Having said that, please note that it's only been possible to do this with mysqli since PHP v5.4, so you'll need to be up-to-date with your PHP version for that. PDO on the other hand has supported this feature for ages.

They can, of course, both also use a while loop as well, and this is where your friend isn't quite right, because really there isn't any difference between while and foreach here. Switching from while to foreach won't make any difference to the performance of your code. They do the same thing under the hood. foreach in this case is really just "syntactic sugar".

I would strongly recommend switching to one of these newer APIs, even if you don't plan to use foreach to do your looping, because as I say, the old mysql functions are deprecated, which means that they are likely to be removed entirely from future PHP versions. So if you want your code to keep running into the future, you should switch now.

SDC
  • 14,192
  • 2
  • 35
  • 48
1

It's not possible to iterate over a result set using foreach.

foreach only works for cases when you already have the data fetched.

So your friend was just wrong and his advice doesn't make any sense.

zerkms
  • 249,484
  • 69
  • 436
  • 539
  • `foreach` works with Iterators. `mysqli` and `PDO` recordsets are Iterators. So although his friend is wrong if he's talking specifically about `mysql_query()`, he is right in a more general sense in that it **is** possible to use `foreach` with a DB recordset. – SDC Feb 06 '13 at 11:44
  • @SDC: "he is right" "friend told me that it is better to use foreach" --- in what universe `foreach` is **better** than `while`? How would you *compare* them? – zerkms Feb 06 '13 at 11:47
  • Re-read my sentence: "he is right in a more general sense in that it is possible". I wasn't saying anything about it being better or not, but I was saying that you're incorrect in saying it's not possible at all. As we both know, there's no real difference here logically between `while` and `foreach`. Using `foreach` in this case is pure syntactic sugar. – SDC Feb 06 '13 at 11:52
  • @SDC: there is a huge difference. You cannot use foreach with things that are not iterable. " you're incorrect in saying it's not possible at all." ---- prove your words. Show how you would iterate over `mysql_query` result with `foreach` – zerkms Feb 07 '13 at 00:27
  • @SDC: "but I was saying that you're incorrect in saying it's not possible at all" --- am I still incorrect? The question is about `mysql_query`, I answered to the question about `mysql_query`, I didn't say anything about other DBALs. Thoughts? "uh, I clearly said that mysql_query couldn't use foreach." --- you clearly said I'm wrong. – zerkms Feb 07 '13 at 19:57
0

@zerkms

I also thought like that.. But following works...

My 'tbl_login' table structure also attached as a 

<?php
    include '../common/dbConnection.php';

     class foreachtest{
        function foreachtesting(){
            $sql="SELECT * FROM tbl_login";
            $query_result=$GLOBALS['con']->query($sql);
            return $query_result;
        }
    }

    $myobject = new foreachtest();
    $result=$myobject->foreachtesting();


    foreach ($result as $a){
        echo $a['username'];
    }

?>

tbl_login MYSQL table screenshot

user2475322
  • 1