0
\$\begingroup\$

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?").

\$\endgroup\$
1
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$
0
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.