WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

PHP Select Data From MySQL


Select Data From a MySQL Database

The SELECT explanation is utilized to choose information from at least one tables:

SELECT column_name(s) FROM table_name

or we can utilize the * character to choose ALL segments from a table:

SELECT * FROM table_name

To study SQL, if it's not too much trouble visit our SQL tutorial.


Select Data With MySQLi

The following model chooses the id, firstname and lastname segments from the MyGuests table and shows it on the page:

Example (MySQLi Object-oriented)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

//Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
//Check connection
if ($conn->connect_error) {
    die("Connection fizzled: " . $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    /yield information of each row
    while($row = $result->fetch_assoc()) {
        reverberation "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    reverberation "0 results";
}
$conn->close();
?>

Code lines to clarify from the precedent above:

First, we set up a SQL inquiry that chooses the id, firstname and lastname segments from the MyGuests table. The following line of code runs the inquiry and puts the subsequent information into a variable called $result.

Then, the capacity num_rows() checks if there are more than zero columns returned.

If there are in excess of zero lines restored, the work fetch_assoc() puts every one of the outcomes into a cooperative exhibit that we can circle through. The while() circle circles through the outcome set and yields the information from the id, firstname and lastname columns.

The following model demonstrates equivalent to the precedent above, in the MySQLi procedural way:

Example (MySQLi Procedural)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

//Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
/Check connection
in the event that (!$conn) {
    die("Connection fizzled: " . mysqli_connect_error());
}

$sql = "SELECT id, lastname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    /yield information of each row
    while($row = mysqli_fetch_assoc($result)) {
        reverberation "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    reverberation "0 results";
}

mysqli_close($conn);
?>

You can likewise put the outcome in a HTML table:

Example (MySQLi Object-oriented)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

//Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
//Check connection
if ($conn->connect_error) {
    die("Connection fizzled: " . $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    reverberation "<table><tr><th>ID</th><th>Name</th></tr>";
   / yield information of each row
    while($row = $result->fetch_assoc()) {
        reverberation "<tr><td>".$row["id"]."</td><td>".$row["firstname"]." ".$row["lastname"]."</td></tr>";
    }
    reverberation "</table>";
} else {
    reverberation "0 results";
}
$conn->close();
?>

Select Data With PDO (+ Prepared Statements)

The following precedent uses arranged statements.

It chooses the id, firstname and lastname sections from the MyGuests table and shows it in a HTML table:

Example (PDO)

<?php
echo "<table style='border: strong 1px black;'>";
reverberation "<tr><th>Id</th><th>Firstname</th><th>Lastname</th></tr>";

class TableRows expands RecursiveIteratorIterator {
    work __construct($it) {
        parent::__construct($it, self::LEAVES_ONLY);
    }

    work current() {
        return "<td style='width:150px;border:1px strong black;'>" . parent::current(). "</td>";
    }

    work beginChildren() {
        reverberation "<tr>";
    }

    work endChildren() {
        reverberation "</tr>" . "\n";
    }
}

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

attempt {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests");
    $stmt->execute();

   /set the subsequent exhibit to associative
    $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
    foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
        reverberation $v;
    }
}
catch(PDOException $e) {
    reverberation "Error: " . $e->getMessage();
}
$conn = null;
echo "</table>";
?>