File structure website using PDO to connect to database

Hi,
I’ve gone through more than a dozen tutorials on how to connect my MySQL database to my website with a PDO connection. They all more or less recommend code that looks like:

<?php $host = 'localhost'; $db = 'database'; $user = 'myName'; $password = 'password'; $dsn = "mysql:host=$host;dbname=$db;charset=UTF8"; ?>

I believe this code goes in its own document in the root directory, sometimes called “config.php”. How this document relates to/interacts with other documents in the root directory isn’t really clear to me. Sometimes the tutorials seem to recommend adding a second document (sometimes named “connect.php”) with the following code:

<?php require 'config.php'; $dsn = "mysql:host=$host;dbname=$db;charset=UTF8"; try { $options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]; $conn = new PDO($dsn, $user, $password, $options); if ($conn) { echo "Connected to the $db database successfully!"; } } catch (PDOException $e) { echo $e->getMessage(); } Once I add this, i can't seem to get any of my html pages to load anymore. Also, I'm not sure how to get the data from my database to show up where I want it on my html pages. All the tutorials I found either focus almost exclusively on PHP syntax (and not how to use it with html), or they assume advanced knowledge PHP web development.

Hi,
a common way to connect your site to a database would be building an API. In case that is over the top for what you need, there is not really a way to connect an index.html file with an index.php file. You would rather just have an index.php that contains your html code. You could just rename your index.html to index.php and it would still work the same as long as your file is either delivered from a server or you have Xampp running. You could then have a separate php block with the functionality in that file and inject php snippets in your html structure.

If an API is what you need

With a basic knowledge of php I used the Slim Framework to build a basic API. It took me a few days, getting everything installed, reading the docs and watching – among others – this tutorial:

If you have an API, you would have a seperate URL for your database connection and from your main site, you would then fetch the data by requesting specific data with a route like this: https://my-api.com/images/12345
That data request from your main site would usually be in Javascript.

Hi, thank you for your response. Yes, I’ve tried putting the PHP configuration code onto my homepage document and renaming it index.php, and it has worked. I got the echo statement “Connected to database successfully”. But then nothing else from the homepage showed up. All I got was the echo statement. So one question is, how do I fix that? And then how do I start querying data onto the webpages I want?

Thanks for your advice about the API. Admittedly, it looks far more complicated than what I’m trying to do and is likely over my head. Since I got the echo statement to work once, I feel like I’m almost there with this PDO approach. Hopefully I just need to make a few tweaks.

Did you close the php tag?

It depends. In case you want to get specific values from the data base from the input of a form input field, for example, you will probably need an API.

Building an API is complex, indeed.

In case you want to display static data from from your data base, you might want to look into arrays in php. There is a basic php course on Codecademy that also has a chapter about arrays.

So basically what you can do:

  • Have the db connection on top of the file, stored in a variable, something like:
<?php
  $dbh = new PDO('mysql:host=localhost;dbname=basicdb', $user, $pass);
?>
  • Then your html structure.

  • Then somewhere within your html you could have a SQL query like:

<?php foreach($dbh->query('SELECT surname, name from people') as $row) {
  echo '<p>' . $row['surname'] . ' ' . $row['name'] . '</p>';
}?>

I’d advise against having the actual query run within a foreach loop (for performance).

A more memory-friendly approach would be:

$stmt = $dbh->query('SELECT surname, name FROM people');
foreach ($stmt as $row) {    
  echo '<p>' . $row['surname'] . ' ' . $row['name'] . '</p>';
}
1 Like

Thank you both for the advice. I don’t know why I couldn’t get the PDO to work. Instead I used a MySQLi Procedural approach that I found here: PHP MySQL Select Data

At the very least I was able to make the connection and query data in a simple way. So this is a big victory for me. The next step is to get my data to display as a table.

1 Like