Using mySql database data directly into generating articles for my website? (newbie, needs to know where to start))

Question

Hi all I am fairly new to web development, I've made a couple of my own sites over the years with simple html tables and free webhosts.

This time, I have decided to try to host my own site using WAMP server and everything seems to be up-n-running nicely. I've used a html/css template which is fairly simple whilst still having some fancy features included and I've pretty much figured out how to edit that to my tastes.

This is purely for my own fun/learning. I'm making a website which lists information about TV series. So my database will be called "Shows". It will have 6 fields (id, genre, title, imageurl, description, httplink).

What I want to do is generate my own 'article template' which will use all 6 of those data to fill some fields i include in the html (if this is even possible??? I am not sure, but I'm quite experienced in coding games/apps in c#/java and I used databases sometimes in those games so I'm hoping the technique is somewhat similar!?)

So what I currently have is a) a self-hosted website, confirmed available to public already b) html index landing page with css customised to my liking (inside the html I have made one 'section' which holds my article template.

So far, my article template looks like this:

<!--Article One -->
            <section class="wrapper style1">
                <div class="inner">
                    <header class="align-center">
                        <h2>DUMMY TITLE FIELD</h2>
                        <!-- will need to pull these fields including image url from database -->
                        <img src="http://xyz.image.url/images/123.jpg"> 
                        <p>DUMMY DESCRIPTION FIELD, DUMMY DESCRIPTION FIELD, DUMMY DESCRIPTION FIELD, DUMMY DESCRIPTION FIELD, DUMMY DESCRIPTION FIELD, DUMMY DESCRIPTION FIELD, DUMMY DESCRIPTION FIELD</p>
                    </header>
                </div>
            </section>

So , I want the user to be able to sort by genre for example, and I want to automatically fill the articles with all the shows from my database with that genre. And of course this includes creating the above html code on the fly but replacing the debug text with actual variables so that it can change it to the database text (I hope that makes some sense!!)

Really, I'd just like to know the name of this process, so I can google a good current guide (unless of course you can easily type it to me which would be even better for me!), also sorry if this has already been asked here. I've been searching about for all types of html/css/apache/php/mySql tutorials and I'm getting a bit confused.

Really hope someone can help. Many thanks

Answer 1

You are looking for two different solutions. Generating the articles from MySQL requires backend code like Java or PHP, along with the database MySQL. The sorting requires frontend code like javascript. I'll touch on this later.

For some example code (do not copy and paste this), I used PHP to check the database and output your article code in a While loop.

<?php

// Establish how to log in
$servername = "127.0.0.1";
$username = "root";
$password = "yourpasswordhere";
$dbname = "yourdatabasenamehere";

// create the database connection
$db = new mysqli($servername, $username, $password, $dbname);

// if it fails, kill the site.
if (mysqli_connect_error($db)) {
    die("Connection failed: " . mysqli_connect_error($db));
}

// your first query to grab all the article data
$SQL_Query = "SELECT * FROM your_table";
// run the query
$SQL_Run = mysqli_query($db, $SQL_Query);

// while data exists (it makes sure that you have post data, otherwise nothing shows up)
while ($row = mysqli_fetch_assoc($SQL_Run)) {
    echo 
    "<section class='wrapper style1'>
    <div class='inner'>
        <header class='align-center'>
            <h2>" . $row['imageurl'] . "</h2>
            <img src='" . $row['title'] . "'> 
            <p>" . $row['description'] . "</p>
        </header>
    </div>
</section>";
}

// Close the connection
mysqli_close($db);
?>

What this is doing is looping through every entry stored in the database and constructing your article html that you pasted above. Each value is stored in the "$row" variable as it loops through. You can swap out the column names for any data you want.

PHP Walkthrough You must construct a statement and save it into a variable. I usually refer to it as a query in the variable name. Example: $SQL_Query.

Then you must run the query through your database. Example: $SQL_Run

After that, you have two common options. If there is more than one row, you can do a while loop to loop through them all, or if you only return one row (if you are looking for a specific post), you can fetch it and save it into a variable.

---

To add sorting features, you may want to look for a javascript library such as JqueryUI, or ListJS. I personally use ListJS since it is pretty easy to implement http://listjs.com/

---

Answering OP's Question

1) is 'while' loop same as 'for' loop in c# etc?

For Loops and While Loops are different, but work the same way when compared to C# and PHP. While loops run indefinitely until a condition has met, and For loops run for a set time.

2)is the code in your while loop all executed PRIOR to $SQL_Run?

No, $SQL_Run saves all your database information (all the rows and values) into the variable. Then the while loop runs on that variable.

3)is $SQL_Run a function i must write myself somewhere?

$SQL_Run is a user defined variable. mysqli_query() is a built-in php function that requires a database connection and your MySQL Statement

4) how do I access the value of given variable ($row) to parse it into the html document and

You can access it the same way in the example, $row['column name here'].

5) could I use this to stack these html blocks into a pre-existing index.html (or any page for that matter) or do i need to make a new page completely depending on number of returned results?

This code must be part of a php file. Luckily, PHP files work exactly the same as html files. Resave your index.html as index.php and your html will work fine :)

Its also mysqli_fetch_assoc()) function that I don't know about, is this written by us as developers or part of SQL libraries?

It is a builtin function. always use mysqli when running database code as it is secure. https://www.w3schools.com/php/func_mysqli_fetch_assoc.asp

I now see that $SQL_Runis called from within the while loop. I also don't understand what mysqli_query() is exactly.

the mysqli_query() function grabs all the data from your SQL statement and stores it all inside the variable $SQL_Run. Its like saving an excel file of data into a single variable. You can do whatever you want with it after that