php mysqli - getting results with for loop

Posted on 2009-12-29
Last Modified: 2013-12-12

I'm just wondering how I can retrieve data using mysqli using a for loop and foreach loop rather than using a while statement. I heard or read somewhere that while is a little slower.

I have this bit of code:
/* prepare statement */
if ($stmt = $mysqli->prepare("SELECT name, email FROM freecd WHERE email LIKE ? LIMIT 5")) {

    $stmt->bind_param("s", $code);
    $code = "vkim%";


    /* bind variables to prepared statement */
    $stmt->bind_result($col1, $col2);

    /* fetch values */
    while ($stmt->fetch()) {
        printf("%s %s\n", $col1, $col2);

    /* close statement */
} else {

      echo $mysqli->error;

Question by:Victor Kimura
    LVL 107

    Expert Comment

    by:Ray Paseur
    The "while" is not slower.  Use it!
    LVL 107

    Accepted Solution

    Now having said that let me expand on the answer a little bit.

    The while iterator is designed for EXACTLY what you want to do with SQL results sets - to iterate over the data.

    The speed of your server is roughly 100,000,000 times faster than a motivated typist.  it will never be possible to find the optimization of "while" if you make measurements in human terms (and I don't know of other terms that matter very much).

    You can spend a lot of energy optimizing the wrong thing.  If you do even ONE query in your lifetime that uses a WHERE clause on an unindexed column, you will have lost all the time you might have gained by trying to make "while" faster.  In other words, the performance of "while" is a red herring.

    Best wishes for the new year, ~Ray
    LVL 82

    Assisted Solution

    I doubt very much that the for is faster, but if you really want a for loop:

     for ( ; $stmt->fetch(); ) {
            printf("%s %s\n", $col1, $col2);

    Author Closing Comment

    by:Victor Kimura
    Thank you.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit ( and similar technologies have enjoyed wide adoption, making it possib…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now