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  

Heroku

This site is built on Heroku

Join the ranks of developers at Salesforce, Airbase, DEV, and more who deploy their mission critical applications on Heroku. Sign up today and launch your first app!

Get Started

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Immerse yourself in a wealth of knowledge with this piece, supported by the inclusive DEV Community—every developer, no matter where they are in their journey, is invited to contribute to our collective wisdom.

A simple “thank you” goes a long way—express your gratitude below in the comments!

Gathering insights enriches our journey on DEV and fortifies our community ties. Did you find this article valuable? Taking a moment to thank the author can have a significant impact.

Okay