Display data from two MySQL database tables

Question

This is my first attempt at PHP PDO. I needed to get data from two tables in my MySQL database. I'm pleased to say the code works, and I was just wondering if what I've done is correct.

<?php

// MySQL Database Connection
$db = new PDO('mysql:host=localhost;dbname=database;charset=utf8mb4', 'root', '');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

// Display Data
foreach($db->query('SELECT * FROM data1 LEFT JOIN data2 ON data1.id = data2.id WHERE data1.id=1') as $row) {
    // Data is displayed in their respected places.
}

?>
<!doctype html>

<html lang="en">
<head>
<meta charset="utf-8">

<title>Title</title>
<meta name="description" content="Description">
<meta name="author" content="Author">

<link rel="stylesheet" href="css/styles.css">

<!--[if lt IE 9]>
<script src="https://cdnjs.cloudflare.com/ajax/libs/html5shiv/3.7.3/html5shiv.js"></script>
<![endif]-->
</head>

<body>
<table border="1">
    <tr>
        <td><?php echo $row['sample1']; ?></td>
        <td><?php echo $row['sample2']; ?></td>
    </tr>
    <tr>
        <td><?php echo $row['extra1']; ?></td>
        <td><?php echo $row['extra2']; ?></td>
    </tr>
</table>
</body>
</html>

This is not working code as, at a minimum, you would need another iteration through data set to echo out the results to the table. Show your whole code. — Mike Brant, Commented Sep 8, 2016 at 17:35
Yes it is, the code is working on my end, the above is the whole code from my php file, the only thing "missing" is my database dump. — ToCode, Commented Sep 10, 2016 at 9:18
If you think something is "missing" in the code please can you expand on this, thank you very much. — ToCode, Commented Sep 10, 2016 at 9:26
The is no code inside your for each that deals with the result set. — Mike Brant, Commented Sep 12, 2016 at 4:00

Community · Accepted Answer · 2020-06-10 13:24:26Z

foreach

Don't have an empty foreach loop... ever. Similarly, just because php lets $row escape from the context it is in does not mean you should use it after the foreach-loop is over.

PDO::query

I recommend against using PDO::query, especially because you lack a thorough understanding of what you are doing. You can use it when executing complete static queries (those that do not have variables), but you already have a where-clause in that sql statement and it is only a matter of time until you want to change that to something dynamic. Use PDO::prepare instead.

PDO::prepare

You are using $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);. Good. Except that you do not use prepared statements at all. Keep in mind that your mysql driver must be able to use native prepared statements too.

PDO::ERRMODE_EXCEPTION

You are setting the error mode to PDO::ERRMODE_EXCEPTION. Good. Except that you do not catch any of them and instead let the page return blank.

Stack Exchange Network

Display data from two MySQL database tables

1 Answer 1

foreach

PDO::query

PDO::prepare

PDO::ERRMODE_EXCEPTION

Hot Network Questions

Display data from two MySQL database tables

1 Answer 1

foreach

PDO::query

PDO::prepare

PDO::ERRMODE_EXCEPTION

Related

Hot Network Questions