Creating website via SQL table and PHP object

Question

I wanted to try keeping the content of a website (mostly) in a SQL table and have made the basic template for the body. Set the $pageName and include this template.

class Page {
  public $content = [];
  public $heading;
}

$page = new Page();

$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
/* check connection*/
if ($conn->connect_errno) {
  die("Failed to connect with MySQL: " . $conn->connect_errno);
};

$sql = "SELECT type, heading1, content1, style FROM $table WHERE page = '$pageName' ORDER BY ord";
$result = mysqli_query($conn, $sql);

while($row = mysqli_fetch_array($result, MYSQL_ASSOC)) {
        switch($row['type']){
          case "content":
            $page->content[] = array("content" => '<p>' . $row['content1'] . '</p>', "heading" => "<h3>" . $row['heading1'] . "</h3>", "style" => $row['style']);
            break;
          case "heading":
            $page->heading = "<h1>" . $row['heading1'] . "</h1>";

            if ($row['content1']) {
              $page->heading .= "<h2>" . $row['content1'] . "</h2>";
            }
        }
    }

  mysqli_close($conn);
 ?>

<header>
  <?php echo $page->heading; ?>
</header>


<?php
for ($i=0; $i < count($page->content); $i++) {
  echo "<section class='content ".$page->content[$i]["style"]."'>";
  echo $page->content[$i]["heading"];
  echo $page->content[$i]["content"];
  echo "</section>";
}
?>

I'm planning on adding more types than content and heading as needed. I will also add includes such as HTML head/meta, navbar, and footer.

I just wondered what people thought of this (besides "why?").

Answer 1

It's simple. It looks nice enough.

But please, for the love of little Bobby Tables, bind your variables: http://www.wikihow.com/Prevent-SQL-Injection-in-PHP . That would work out to:

$stmt->bind_param("s", $pageName);

once you've created a prepared statement.

Also, consider using a numeric identifier which maps to a pageName, similar to how StackExchange maps 174740 to "creating-website-via-sql-table-and-php-object".

Wrap very long lines, please. After commas in your array() would be a good place.

You didn't show your schema. I hope you have a compound unique key on (page_name, ord). Perhaps it's your PK.

Sometimes HTML tags aren't perfectly nested, and such errors might become stored in your database. I don't know what the PHP equivalent of python BeautifulSoup is, but at the DB->web interface your loop has an opportunity to ensure only well-formed HTML is sent to clients.

Answer 2

It would be a little easier to read if you use a foreach loop instead:

foreach ($page->content as $i => $content) {
  echo "<section class='content ".$content["style"]."'>";
  echo $content["heading"];
  echo $content["content"];
  echo "</section>";
}

And you could also simplify it a bit for yourself by embedding the php code into html:

<?php foreach ($page->content as $i => $content) { ?>
    <section class='content <?= $content["style"]; ?>'>
        <?= $content["heading"] . $content["content"]; ?>
    </section>
<?php endforeach; ?>

Regarding fetching the data from mysql. PDO has a great functionality to fetch data directly into user defined classes. Here's a good example. It can also help you with SQL injections protection. Your code would then transform into something like this:

class Page {
    public $heading;
    public $content;
    public $style;
}

try {
    // connect & prepare
    $dbh = new PDO("mysql:dbname={$dbName};host={$dbHost}", $dbuser, $dbpass);
    $stmt = $dbh->prepare('YOUR QUERY HERE');
    $stmt->bind(':page', $pageName);
    $stmt->execute();

    // fetch data
    $pages = $sth->fetchAll(PDO::FETCH_CLASS, "Page");

    // output
    ...  
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

After the fetch, the $pages variable will be populated with Page classes instances. There is one drowback, though: all properties have to be public.