DEV Community

Falah Al Fitri
Falah Al Fitri

Posted on • Edited on

PHP and MySQL 30: query select


Happy Coding


Home Previous

In this post, we will learn about how to use query SELECT in PHP for retrieve data from database MySQL, with 3 ways:

  1. MySQLi procedural
  2. MySQLi object-oriented
  3. PDO

For known, how to connect to database, can be learned in here

Insert query is used for insert data or record into database

Syntax:

    SELECT column1, column2, column3 
    FROM table_name;
Enter fullscreen mode Exit fullscreen mode

using wilcard (*) for select all column

    SELECT *
    FROM table_name;
Enter fullscreen mode Exit fullscreen mode

Example:

    $sql  = "SELECT id, firstname, lastname ";
    $sql .= "FROM users " ;
Enter fullscreen mode Exit fullscreen mode
    $sql  = "SELECT * ";
    $sql .= "FROM users " ;
Enter fullscreen mode Exit fullscreen mode

And, for execute the query, we will use 3 ways:

1. MySQLi1 procedural

Back to Home

Execute query using mysqli::query2 and get a $result:

    $result = mysqli_query( $connection, $sql );
Enter fullscreen mode Exit fullscreen mode

Display data using mysqli_result::fetch_assoc3, alternative using mysqli_result::fetch_object4:

    echo "<pre>";

    echo "No \t";
    echo "ID \t";
    echo "Firstname \t\t";
    echo "Lastname \n";

    $no = 1; /* variabel $no */

    /* loop while */
    while ( $row = mysqli_fetch_assoc($result) )
    {

        echo $no . "\t";
        echo $row['id'] . "\t";
        echo $row['firstname'] . "\t\t";
        echo $row['lastname'] . "\n";

        $no++; /* increment */

    }

    echo "</pre>";
Enter fullscreen mode Exit fullscreen mode

Close connection using mysqli::close5:

    mysqli_close($connection);
Enter fullscreen mode Exit fullscreen mode

Source Code:


2. MySQLi1 object-oriented

Back to Home

Execute query using mysqli::query2 and get a $result:

    $result = $connection->query($sql);
Enter fullscreen mode Exit fullscreen mode

Display data using mysqli_result::fetch_object4:

    echo "<pre>";

    echo "No \t";
    echo "ID \t";
    echo "Firstname \t";
    echo "Lastname \n";

    $no = 1; /* variabel $no */

    /* loop while */
    while ( $row = $result->fetch_object() )
    {

        echo $no . "\t";
        echo $row->id . "\t";
        echo $row->firstname . "\t\t";
        echo $row->lastname . "\n";

        $no++; /* increment */

    }

    echo "</pre>";
Enter fullscreen mode Exit fullscreen mode

Close connection using mysqli::close5:

     $connection->close();
Enter fullscreen mode Exit fullscreen mode

Source Code:


3. PDO6

Back to Home

Execute query and get a $result:

    $result = $connection->query($sql);
Enter fullscreen mode Exit fullscreen mode

Display Data:

    echo "<pre>";

    echo "No \t";
    echo "ID \t";
    echo "Firstname \t";
    echo "Lastname \n";

    $no = 1; /* variabel $no */

    /* loop foreach */
    foreach ( $result as $row  )
    {

        echo $no . "\t";
        echo $row->id . "\t";
        echo $row->firstname . "\t\t";
        echo $row->lastname . "\n";

        $no++; /* increment */

    } 

    echo "</pre>";
Enter fullscreen mode Exit fullscreen mode

Close connection:

    $connection = null;
Enter fullscreen mode Exit fullscreen mode

Source Code:

Back to Home | Next#


Thank for reading :)


  1. php.net, "MySQL Improved Extension", accessed on date 21 december 2019 and from https://www.php.net/manual/en/book.mysqli.php 

  2. php.net, "mysqli::query", accessed on date 22 december 2019 and from https://www.php.net/manual/en/mysqli.query.php 

  3. php.net, "mysqli_result::fetch_assoc", accessed on date 23 december 2019 and from https://www.php.net/manual/en/mysqli-result.fetch-assoc.php 

  4. php.net, "mysqli_result::fetch_object", accessed on date 23 december 2019 and from https://www.php.net/manual/en/mysqli-result.fetch-object.php 

  5. php.net, "mysqli::close", accessed on date 21 december 2019 and from https://www.php.net/manual/en/mysqli.close.php 

  6. php.net, "PDO::__construct", accessed on date 21 december 2019 and from https://www.php.net/manual/en/pdo.construct.php  

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

Top comments (0)

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay