How to automaticly create a HTML file with content displayed from URL and a MySQL Database entry in PHP

Question

Okay, I recently wanted to create something like a Library, that displays entrys from a MySQL Database as a table. I managed to do it like this:

<?php

$host='localhost';
    $username='root';
    $password='';
    $dbname = "s-lib";
    $conn=mysqli_connect($host,$username,$password,"$dbname");
    if(!$conn)
        {
          die('Could not Connect MySql Server:' .mysql_error());
        }

?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <title>Shadow Library</title>
    <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto|Varela+Round|Open+Sans">
    <link rel="stylesheet" href="https://fonts.googleapis.com/icon?family=Material+Icons">
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script>
    <style type="text/css">
    .bs-example {
        margin: 20px;
    }
    </style>
    <script type="text/javascript">
    $(document).ready(function() {
        $('[data-toggle="tooltip"]').tooltip();
    });
    </script>
</head>

<body>
    <div class="bs-example">
        <div class="container">
            <div class="row">
                <div class="col-md-12">
                    <div class="page-header clearfix">
                        <a href="index.php">
                            <h2 class="pull-left">Shadow-Library</h2>
                        </a>
                    </div>
                    <?php
$result = mysqli_query($conn,"SELECT * FROM titles");
?>
                    <?php
if (mysqli_num_rows($result) > 0) {
?>
                    <table class='table table-bordered table-striped'>
                        <tr>
                            <td><svg xmlns="http://www.w3.org/2000/svg" width="16" height="16" fill="currentColor"
                                    class="bi bi-list-ol" viewBox="0 0 16 16">
                                    <path fill-rule="evenodd"
                                        d="M5 11.5a.5.5 0 0 1 .5-.5h9a.5.5 0 0 1 0 1h-9a.5.5 0 0 1-.5-.5zm0-4a.5.5 0 0 1 .5-.5h9a.5.5 0 0 1 0 1h-9a.5.5 0 0 1-.5-.5zm0-4a.5.5 0 0 1 .5-.5h9a.5.5 0 0 1 0 1h-9a.5.5 0 0 1-.5-.5z" />
                                    <path
                                        d="M1.713 11.865v-.474H2c.217 0 .363-.137.363-.317 0-.185-.158-.31-.361-.31-.223 0-.367.152-.373.31h-.59c.016-.467.373-.787.986-.787.588-.002.954.291.957.703a.595.595 0 0 1-.492.594v.033a.615.615 0 0 1 .569.631c.003.533-.502.8-1.051.8-.656 0-1-.37-1.008-.794h.582c.008.178.186.306.422.309.254 0 .424-.145.422-.35-.002-.195-.155-.348-.414-.348h-.3zm-.004-4.699h-.604v-.035c0-.408.295-.844.958-.844.583 0 .96.326.96.756 0 .389-.257.617-.476.848l-.537.572v.03h1.054V9H1.143v-.395l.957-.99c.138-.142.293-.304.293-.508 0-.18-.147-.32-.342-.32a.33.33 0 0 0-.342.338v.041zM2.564 5h-.635V2.924h-.031l-.598.42v-.567l.629-.443h.635V5z" />
                                </svg> BID</td>
                            <td><svg xmlns="http://www.w3.org/2000/svg" width="16" height="16" fill="currentColor"
                                    class="bi bi-bookmark-plus" viewBox="0 0 16 16">
                                    <path
                                        d="M2 2a2 2 0 0 1 2-2h8a2 2 0 0 1 2 2v13.5a.5.5 0 0 1-.777.416L8 13.101l-5.223 2.815A.5.5 0 0 1 2 15.5V2zm2-1a1 1 0 0 0-1 1v12.566l4.723-2.482a.5.5 0 0 1 .554 0L13 14.566V2a1 1 0 0 0-1-1H4z" />
                                    <path
                                        d="M8 4a.5.5 0 0 1 .5.5V6H10a.5.5 0 0 1 0 1H8.5v1.5a.5.5 0 0 1-1 0V7H6a.5.5 0 0 1 0-1h1.5V4.5A.5.5 0 0 1 8 4z" />
                                </svg> Book Title</td>
                            <td><svg xmlns="http://www.w3.org/2000/svg" width="16" height="16" fill="currentColor"
                                    class="bi bi-person-square" viewBox="0 0 16 16">
                                    <path d="M11 6a3 3 0 1 1-6 0 3 3 0 0 1 6 0z" />
                                    <path
                                        d="M2 0a2 2 0 0 0-2 2v12a2 2 0 0 0 2 2h12a2 2 0 0 0 2-2V2a2 2 0 0 0-2-2H2zm12 1a1 1 0 0 1 1 1v12a1 1 0 0 1-1 1v-1c0-1-1-4-6-4s-6 3-6 4v1a1 1 0 0 1-1-1V2a1 1 0 0 1 1-1h12z" />
                                </svg> Author</td>
                            <td><svg xmlns="http://www.w3.org/2000/svg" width="16" height="16" fill="currentColor"
                                    class="bi bi-calendar" viewBox="0 0 16 16">
                                    <path
                                        d="M3.5 0a.5.5 0 0 1 .5.5V1h8V.5a.5.5 0 0 1 1 0V1h1a2 2 0 0 1 2 2v11a2 2 0 0 1-2 2H2a2 2 0 0 1-2-2V3a2 2 0 0 1 2-2h1V.5a.5.5 0 0 1 .5-.5zM1 4v10a1 1 0 0 0 1 1h12a1 1 0 0 0 1-1V4H1z" />
                                </svg> Year</td>
                            <td><svg xmlns="http://www.w3.org/2000/svg" width="16" height="16" fill="currentColor"
                                    class="bi bi-file-earmark-arrow-down" viewBox="0 0 16 16">
                                    <path
                                        d="M8.5 6.5a.5.5 0 0 0-1 0v3.793L6.354 9.146a.5.5 0 1 0-.708.708l2 2a.5.5 0 0 0 .708 0l2-2a.5.5 0 0 0-.708-.708L8.5 10.293V6.5z" />
                                    <path
                                        d="M14 14V4.5L9.5 0H4a2 2 0 0 0-2 2v12a2 2 0 0 0 2 2h8a2 2 0 0 0 2-2zM9.5 3A1.5 1.5 0 0 0 11 4.5h2V14a1 1 0 0 1-1 1H4a1 1 0 0 1-1-1V2a1 1 0 0 1 1-1h5.5v2z" />
                                </svg> Downloads</td>
                        </tr>
                        <?php
$i=0;
while($row = mysqli_fetch_array($result)) {
?>
                        <tr>
                            <td><?php echo $row["b_id"]; ?></td>
                            <td><?php echo $row["b_title"]; ?></td>
                            <td><?php echo $row["b_author"]; ?></td>
                            <td><?php echo $row["b_year"]; ?></td>
                            <td><a href="<?php echo $row["b_dl"]; ?>" target="_blank">Download</a></td>
                        </tr>
                        <?php
$i++;
}
?>
                    </table>
                    <?php
}
else{
echo "No result found";
}
?>
                </div>
            </div>
        </div>
    </div>
</body>

</html>

I am done with that, however, I now also created Book IDs, because I want to create the viewing of them like this: example.com/index.php?bid=[BookID] So, my MySQL Structure looks like this:

s-lib
- b_id [BookID]
- b_title [Book Title]
- b_author [Book Author]
- b_year [Book Release Year]
- b_dl [Book Download Link]
- b_descr [Book Description]
- b_cover [Book Cover Image (as a link to the file)]

Now, I want to have when you click on a title in the list, it opens it. I have no idea how to do it and Googling brought me to nothing since my english is not that great, that I would know how to properly Google it (sorry).

What I want to have when you enter the ?bid=[BookID] is, it should generate a page with the title, author, release year, download link, description and cover shown.

How can I do that?

Edit 1: After reading the two answers already, I figured they don't show the URL. What I am looking for/trying to achieve is, that you can open a book right from the URL, for example when I open example.com/index.php?bid=3 that it displays the Book with the b_id [BookID] 3. Also, that it loads all the other stuff like b_title and so and displays it. Since I'm not very familiar with JavaScript in general, I have no idea how to use Ajax (Also, it would be completely okay for the site to always reload, I don't care about Bandwith effeciency).

milenmk · Accepted Answer · 2021-08-21T21:45:22.457

Just tested it and it needed some adjustments. This is what I've got and it's working when you click on the ID number:

<form method="post">
    <table>
        <tr>
            <td><td><input type="button" name="book_id_1" value="<?php echo $row["b_id"]; ?>" onclick='loadScript(this.value)' /></td></td>
            <td>TITLE</td>
            <td>AUTHOR</td>
            <td>YEAR</td>
            <td><a href="<?php echo $row["b_dl"]; ?>" target="_blank">Download</a></td>
        </tr>
        <script>
            function loadScript(str) {
                if (str == "") {
                    document.getElementById("para").innerHTML = "";
                    return;
                } else {
                    if (window.XMLHttpRequest) {
                        // code for IE7+, Firefox, Chrome, Opera, Safari
                        xmlhttp = new XMLHttpRequest();
                    } else {
                        // code for IE6, IE5
                        xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
                    }
                    xmlhttp.onreadystatechange = function() {
                        if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
                            document.getElementById("para").innerHTML = xmlhttp.responseText;
                        }
                    }
                    xmlhttp.open("GET", "test2.php?q=" + str, true);
                    xmlhttp.send();
                }
            }
        </script>
    </table>
</form>

<div id="para"></div>

and test2.php

<?php

$book_id = intval($_GET['q']);

print '<tr><td>Result: ' . $book_id . '</tr></td>';

Amend your code based on this.

Further, If you change the onclick='loadScript(this.value)' to onmouseover='loadScript(this.value)', info will be loaded when the mouse is over the book id and you can style it in a popup or something.

milenmk · Answer 2 · 2021-08-21T20:50:54.413

One possible solution is to use an ajax script (you can find a lot of examples here). When clicking on the ID, Its value should be passed to an external file where you will use it in another SQL query to get the information based on the id. Or, you can use something like that:

if (mysqli_num_rows($result) > 0) {
?>
    <form method='post'>
        <table class='table table-bordered table-striped'>

            <!-- Your header content here -->

            <?PHP

            $i=0;

            while ($row = mysqli_fetch_array($result)) {
            ?>
                <tr>
                    <td><?php echo $row["b_id"]; ?></td>
                    <input type="hidden" name="book_id_<?PHP print $i; ?>" value="<?php print $row["b_id"]; ?>" />
                    <td><?php echo $row["b_title"]; ?></td>
                    <td><?php echo $row["b_author"]; ?></td>
                    <td><?php echo $row["b_year"]; ?></td>
                    <td><a href="<?php echo $row["b_dl"]; ?>" target="_blank">Download</a></td>
                    <td><input class="button" type="submit" name="submit_<?PHP print $i; ?>" value="MORE INFO"></td>
                </tr>
                <?php
                if (isset($_POST['submit_<?PHP print $i; ?>'])) {
                    $book_id = $_POST['book_id_<?PHP print $i; ?>'];

                    //SQL query and result content here

                    $i++;
                }
                ?>
        </table>
    </form>
<?php
            }
        }

However, to avoid having content messed up, the result should also be wrapped with <tr></tr> tag and you can have another <table> ... </table> within it.

Also, this approach reloads the page, so I highly recommend using ajax. It could be something like that:

                <tr>
                    <td><?php echo $row["b_id"]; ?></td>
                    <input type="hidden" name="book_id_<?PHP print $i; ?>" value="<?php print $row["b_id"]; ?>" />
                    <td><?php echo $row["b_title"]; ?></td>
                    <td><?php echo $row["b_author"]; ?></td>
                    <td><?php echo $row["b_year"]; ?></td>
                    <td><a href="<?php echo $row["b_dl"]; ?>" target="_blank">Download</a></td>
                    <td><input class="button" type="submit" name="submit_<?PHP print $i; ?>" value="More info" onclick='loadScript(this.value)'>></td>
                </tr>
                <div id ="para"></div>

File "ajax.js" loaded in the header with the following content:

function loadScript(str) {
    if (str == "") {
        document.getElementById("para").innerHTML = "";
        return;
    } else { 
        if (window.XMLHttpRequest) {
            // code for IE7+, Firefox, Chrome, Opera, Safari
            xmlhttp = new XMLHttpRequest();
        } else {
            // code for IE6, IE5
            xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
        }
        xmlhttp.onreadystatechange = function() {
            if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
                document.getElementById("para").innerHTML = xmlhttp.responseText;
            }
        }
        xmlhttp.open("GET","script.php?q="+str,true);
        xmlhttp.send();
    }
}

Where script.php is the external file that contains the SQL query for the info and result content like:

<?PHP

$book_id = intval($_GET['q']);

// SQL query

<tr>
    <table>
        ///RESULT CONTENT
    </table>
</tr>

Note that the code may require some adjustments. Anyway, there is plenty of AJAX example here you cans search for. — milenmk, Aug 21 '21 at 20:56
Okay, I implemented the things you just mentioned, and it works almost fine. When I click the button, the content for just a few moments and then reloads the page, making the content not seen anymore. Since I'm not very familiar with JavaScript, Ajax and PHP, I have no clue how to fix this. — Moonstone, Aug 21 '21 at 21:06
If you use the second example with AJAX, there shouldn't be a page reload. Here is an example that may help you: https://stackoverflow.com/questions/16754246/onclick-send-to-ajax — milenmk, Aug 21 '21 at 21:10

How to automaticly create a HTML file with content displayed from URL and a MySQL Database entry in PHP

2 Answers2