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>
-
\$\begingroup\$ 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. \$\endgroup\$Mike Brant– Mike Brant2016年09月08日 17:35:55 +00:00Commented Sep 8, 2016 at 17:35
-
\$\begingroup\$ 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. \$\endgroup\$ToCode– ToCode2016年09月10日 09:18:44 +00:00Commented Sep 10, 2016 at 9:18
-
\$\begingroup\$ If you think something is "missing" in the code please can you expand on this, thank you very much. \$\endgroup\$ToCode– ToCode2016年09月10日 09:26:10 +00:00Commented Sep 10, 2016 at 9:26
-
\$\begingroup\$ The is no code inside your for each that deals with the result set. \$\endgroup\$Mike Brant– Mike Brant2016年09月12日 04:00:58 +00:00Commented Sep 12, 2016 at 4:00
-
\$\begingroup\$ It's working for me though. \$\endgroup\$ToCode– ToCode2016年09月12日 07:30:20 +00:00Commented Sep 12, 2016 at 7:30
1 Answer 1
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.