Using php to access data from a database and outputting the data to HTML

Question

I am creating a blog and I need to create a php file which retrieves data from a database (which I have done). This php file then outputs the data to html(where I need help on). The php and html must be separate files.

The two files are: - index.php : redirects to index.html for displaying blog entries. - index.html: displays blog entries stored in entry files, and redirect the user to login.html if there is not entry.

The echo on the php files outputs the data from the database on the index.php which I don't want. I want to somehow out the data from the database to index.html.

I'm a newbie at coding, so excuse the bad coding.

Thanks in advance.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "addentry";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT id, title, content, date FROM posts";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
         header('location: index.html');   

        echo " " . $row["date"]. "<br>"; 
        echo " " . $row["title"]."<br>";
        echo " ". $row["content"];
        }
    } 
else {
    echo "0 results";
    }
$conn->close();
?>

<?xml version = "1.0" encoding = "utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!-- image.html
     A trivial document
     -->
<html xmlns = "http://www.w3.org/1999/xhtml">


    <head><title> My Blog </title>

    <style type ="text/css">

        body{ 

        position: fixed; 
        overflow:overlay;
        width: 100%;
        top: -20px;
        left: -20px;
        right: -40px;
        bottom: -40px;       


        height: auto;
        background-image:url(image.jpg);
        background-size: cover;



        }
        .container{

            background-color: #ecdad6;          
            padding: 30px;
            width:920px;        
            margin-left: 25%;
            padding-bottom:1000px;
            padding-left:0px;
            border: 2px solid black;
            }

        .links{
            position: absolute;

            padding-right: 135px;
            padding-bottom: 800px;              
            margin-left: 680px;
            margin-right: 100px;
            font-size: 20px;
            word-wrap: break-word;
            top:-3px;


        }


        .blog{
            position: absolute;
            width:678px;
            padding-bottom: 920px;              
            margin-left: 10px;
            font-size: 20px;
            text-align: left;            
            word-wrap: break-word;

            }
        ul li { margin-top: -10px; }

        }

    } 
    </style>
    <body>

        <!--Logo & hyperlinked -->
        <p align = "center"><a href="index.html"><img src = "Logo.jpg"      alt="My logo" width="10%" height="10%"/></a></p> 
        <br/>
        <hr width="50%">

        <div class="container">                         
            <div class="blog"> 
                <form action='index.php' method='get'></form>
                <div class="links">
                    <a href="index.html"> <ul><li>home</li></ul></a> 
                    <a href="login.html"> <ul><li>logIn</li></ul></a> 
                    <a href="entry.html"> <ul><li>add_entry</li></ul></a>                 
                </div>                  
            </div>     

        </div>

    </body>
</html>

<!-- end snippet -->

Having both `index.php` and `index.html` is asking for trouble. — tadman, Mar 20 '17 at 02:26

score 0 · Accepted Answer · edited May 23 '17 at 11:46

You need php to access the data in order to display the results on the page. HTML is not a dynamic language and cannot do this on it's own. One way you could achieve what you are asking is by having the .html file be interpreted by the php parser, though I'm not sure that is really what you are after, but just in case, see the following link:

Using .htaccess to make all .html pages to run as .php files?

This assumes you are using apache as a web server though. IIS has similar functionality. Look at your web server documentation for more detail.

Another option you have is loading dynamic content into your page using AJAX (javascript) and a remote php script. Take a look at http://www.w3resource.com/ajax/working-with-PHP-and-MySQL.php

You may also want to take a look: https://www.w3schools.com/howto/howto_html_include.asp There may be something on that page which could help you as well.

Whilst you haven't specified why you need to redirect from index.php to index.html, I would strongly suggest that you just run with index.php OR index.html.

There is zero need for both that can be determined by the information in your post.

You can print all the html you need using php.

See the following example:

Create header.php

<?xml version = "1.0" encoding = "utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!-- image.html
     A trivial document
     -->
<html xmlns = "http://www.w3.org/1999/xhtml">


    <head><title> My Blog </title>

    <style type ="text/css">

        body{ 

        position: fixed; 
        overflow:overlay;
        width: 100%;
        top: -20px;
        left: -20px;
        right: -40px;
        bottom: -40px;       


        height: auto;
        background-image:url(image.jpg);
        background-size: cover;



        }
        .container{

            background-color: #ecdad6;          
            padding: 30px;
            width:920px;        
            margin-left: 25%;
            padding-bottom:1000px;
            padding-left:0px;
            border: 2px solid black;
            }

        .links{
            position: absolute;

            padding-right: 135px;
            padding-bottom: 800px;              
            margin-left: 680px;
            margin-right: 100px;
            font-size: 20px;
            word-wrap: break-word;
            top:-3px;


        }


        .blog{
            position: absolute;
            width:678px;
            padding-bottom: 920px;              
            margin-left: 10px;
            font-size: 20px;
            text-align: left;            
            word-wrap: break-word;

            }
        ul li { margin-top: -10px; }

        }

    } 
    </style>
    <body>

        <!--Logo & hyperlinked -->
        <p align = "center"><a href="index.html"><img src = "Logo.jpg"      alt="My logo" width="10%" height="10%"/></a></p> 
        <br/>
        <hr width="50%">

        <div class="container">                         
            <div class="blog"> 
                <form action='index.php' method='get'></form>
                <div class="links">
                    <a href="index.html"> <ul><li>home</li></ul></a> 
                    <a href="login.html"> <ul><li>logIn</li></ul></a> 
                    <a href="entry.html"> <ul><li>add_entry</li></ul></a>                 
                </div>                  
            </div>

Create footer.php

        </div>

    </body>
</html>

<!-- end snippet -->

Modify your index.php file as follows:

<?php

include("header.php");

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "addentry";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT id, title, content, date FROM posts";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
         header('location: index.html');   

        echo " " . $row["date"]. "<br>"; 
        echo " " . $row["title"]."<br>";
        echo " ". $row["content"];
        }
    } 
else {
    echo "0 results";
    }
$conn->close();

include("footer.php);
?>

Now you just call your index.php file, the html header and footer are included in the php file.

That's a good answer, but be very careful when linking to w3schools. It's done tremendous damage with it's misinformed advice and out-of-date approaches to solving problems. The official PHP documentation is usually considerably better, and sites like [PHP the Right Way](http://www.phptherightway.com/) are a good introduction to the overall picture. — tadman, Mar 20 '17 at 02:36
For example, those links recommend using the obsolete and deleted `mysql_query` interface, and it does so without any SQL escaping whatsoever, which is extremely bad. — tadman, Mar 20 '17 at 02:39
This is for my coursework. I am required to create an index.php which directs to index.html to view the blog entries. The index.html displays the blog entries. I understand that php is required in the html document in order to display the data which is fine. So in other words, the index.php retrieves the data from the database in file. The index.html displays the data from another file. They must be separate documents. — Timmay696969, Mar 20 '17 at 02:46
agreed, @tadman, however the OP clearly has the mysqli interface method in the php code they posted, so already has the appropriate (& current) database connector to handle both inline and ajax driven php requests. The code they posted does not appear to be vulnerable from sql injection attacks either. The question was not about data or application security, which is a completely separate though important and related topic. — Majickal, Mar 20 '17 at 02:51
I'm talking specifically to the first link which extremely old code and wickedly out of date advice. With jQuery and PDO none of that is a problem. With `mysql_query` and plain JavaScript you get yourself into trouble *very* quickly. People tend to take sites like that as authoritative and that creates serious problems. — tadman, Mar 20 '17 at 02:56
@Timmay696969, in that case i think maybe what they are really asking you to do is rewrite the index.html file. To explain further: index.php retrieves the header and footer content and the content from the database and writes that data to index.html - http://php.net/manual/en/function.file-put-contents.php there are a few ways you can do this, output buffering is one: http://stackoverflow.com/questions/2832010/what-is-output-buffering — Majickal, Mar 20 '17 at 03:01
@Timmay696969 If @Majickal's interpretation is correct, which I'd tend to agree with, then that's basically emitting a statically cached page. `index.html` on its own is incapable of running any code. It's just sent to the client as-is. — tadman, Mar 20 '17 at 03:02
@Majickal Thats exactly what I am looking for. But I am confused as to why header.php and footer.php is needed. what exactly would it do to benefit my situation? — Timmay696969, Mar 20 '17 at 03:25
@Timmay696969, would just allow you to easily include the header and footer html in the buffer (as discussed in buffer output previously) so you don't have to stuff around with escaping characters or do something like `$html .= ""; ` mission for each line of html, then write $html to the file, instead you just write the buffer to the file. Does that make sense? Mind you if you see a way where you don't use that method, you should do that, whatever works for you mate :) — Majickal, Mar 20 '17 at 05:53

Using php to access data from a database and outputting the data to HTML

Thanks in advance.

1 Answers1