Adding html,php and mysql queries inside a php variable

FGOD1983 · December 4, 2020, 8:52am

Hey, I’m pretty new to php and I am trying to learn myself php by working on a personal project (website for keeping a collection of all my games).

A bit of background before I tumble into the problem I am facing. My mysql database is made out of several tables. One for the games itself with some info on them like date purchased, date it was released, cover of the game, game name,… And then I have several tables for stuff like Categories and category connect, labels and labelsconnect, … Those are done this way because 1 game can hold several labels, categories, …

What I am doing now is doing a while loop to print out every game of a certain user that is logged in, and inside that while loop I am doing another while loop on the gameID to look for the labels to print those inside the table column of the game it prints.

Now I found myself a tutorial to create a filter with checkboxes (a bit store like) that works with Jquery. That filter seems to work, but the problem I am facing is the $output variable that is used to replace the content with the filtered content.

If I put some text inside, it just displays the text the same amount of times it is finding something that relates to the filter. So that shows me it is working. But What I need to do, is put the whole html table inside that $output variable including the second while loop for the labels and stuff like that. the problems I am facing is that no matter what I try, it just doesn’t finish the load, doesn’t show anything or shows the code instead of the content.

I already tried the normal stuff like putting php inside '. [phpcode] .', a template file and read it with php file_get_contents(), <<<“EOT” and htmlContent function, but nothing seems to work.

Any help is really appreciated!

this is the code for the action.php that is holding the $output variable that is causing issues:

<?php
session_start();

if (isset($_SESSION['userId'])) {

    if (isset($_POST['action'])) {

        $userId = $_SESSION['userId'];
        $username = $_SESSION['userUid'];

        require '../includes/dbh.inc.php';

        $filter = "";

        if (isset($_POST['labels'])) {
            $labels = implode("','", $_POST['labels']);
            $filter .= "AND labelname IN('" . $labels . "')";
        }
        if (isset($_POST['category'])) {
            $category = implode("','", $_POST['category']);
            $filter .= "AND categoryname IN('" . $category . "')";
        }
        if (isset($_POST['platform'])) {
            $platform = implode("','", $_POST['platform']);
            $filter .= "AND platform IN('" . $platform . "')";
        }
        if (isset($_POST['gamename'])) {
            $gamename = implode("','", $_POST['gamename']);
            $filter .= "AND gamename IN('" . $gamename . "')";
        }


        $sql = "SELECT DISTINCT games.gameID,games.idUsers,games.gamename,games.comments,games.dateofpurchase,games.lentoutto,games.gameinfo,games.price,games.pictureurl,games.finished,games.rating,games.releasedate, GROUP_CONCAT(DISTINCT labels.labelname ORDER BY labels.labelname) FROM games INNER JOIN gamelabels ON gamelabels.gameID=games.gameID INNER JOIN labels ON labels.labelID=gamelabels.labelID WHERE labelname !='' AND idUsers = '".$userId."' $filter GROUP BY games.gameID ORDER BY gamename";

        $result = $conn->query($sql);
        $output = '<table id="table">
                        <tr>
                            <th width="22%">Name:</th>
                            <th colspan="2">Info:</th>
                        </tr>
                        <tr>';

        $filtertemplate = file_get_contents('filter.tpl');

        if($result->num_rows>0){
            while($row=$result->fetch_assoc()){
                $output .= <<<"EOT"

                

                $gamemodequery = "SELECT * FROM gamemodesconnect INNER JOIN games ON gamemodesconnect.gameID=games.gameID INNER JOIN gamemode ON gamemode.gamemodeID=gamemodesconnect.gamemodeID WHERE gamemodesconnect.gameID='".$row['gameID']."' ORDER BY gamemode ASC";
                $platformquery = "SELECT * FROM platformconnect INNER JOIN games ON platformconnect.gameID=games.gameID INNER JOIN platform ON platform.platformID=platformconnect.platformID WHERE platformconnect.gameID='".$row['gameID']."' ORDER BY platform ASC";
                $labelquery = "SELECT * FROM gamelabels INNER JOIN games ON gamelabels.gameID=games.gameID INNER JOIN labels ON labels.labelID=gamelabels.labelID WHERE gamelabels.gameID='".$row['gameID']."' ORDER BY labelname ASC";
                $categoryquery = "SELECT * FROM categoryconnect INNER JOIN games ON categoryconnect.gameID=games.gameID INNER JOIN category ON category.categoryID=categoryconnect.gamescategory WHERE categoryconnect.gameID='".$row['gameID']."' ORDER BY categoryname ASC";


            <td>
                <b style="color: #000; padding: 0px 0px 10px 10px;"><?php echo $row['gamename'] ?>:</b><br/><br/>
                <img src="<?php echo $row['pictureurl'] ?>" alt="" style="display:block; width:93%; height:auto;margin: 0px 0px 10px 10px" />
            </td>
            <td class="noBorder text" style="width: 35%">
                <b>Release date:</b> <?php $releaseformat = strtotime($row['releasedate']); $formattedrelease = date('d-m-Y', $releaseformat); echo $formattedrelease ?> <br/>
                <b>Date of purchase:</b> <?php $purchaseformat = strtotime($row['dateofpurchase']); $formattedpurchase = date('d-m-Y', $purchaseformat); echo $formattedpurchase ?><br/>                <b>Purchase price:</b> <?php echo $row['price'] ?><br/>
                <b class='rowlabel'>Label: </b>
                <?php
                $resultlabellist = mysqli_query($conn, $labelquery);
                while($reslabel = mysqli_fetch_array($resultlabellist)) {
                    echo "<span class=\"label\">".$reslabel['labelname']."</span>";
                }
                ?>
                <br/>
                <b class='rowlabel'>Genre: </b>
                <?php
                $resultcategorylist = mysqli_query($conn,$categoryquery);
                while($rescategory = mysqli_fetch_array($resultcategorylist)) {
                    echo "<span class=\"label\">".$rescategory['categoryname']."</span>";
                }
                ?>
                <br/>
                <b class='rowlabel'>Platform: </b>
                <?php
                $resultplatformlist = mysqli_query($conn,$platformquery);
                while($resplatform = mysqli_fetch_array($resultplatformlist)) {
                    echo "<span class=\"label\">".$resplatform['platform']."</span>";
                }
                ?>
                <br/>
                <b class='rowlabel'>Gamemode: </b>
                <?php
                $resultplatformlist = mysqli_query($conn,$gamemodequery);
                while($resgamemode = mysqli_fetch_array($resultplatformlist)) {
                    echo "<span class=\"label\">".$resgamemode['gamemode']."</span>";
                }
                ?>

                <b class="rowlabel">Lent out to: </b><span class="gameinfo"><?php echo $row['lentoutto'] ?> </span><br/>

                <svg version="1.1" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" style="display: none;">
                    <symbol id="okey" width="2em" height="2em" viewBox="0 0 16 16" class="bi bi-check-circle" fill="rgb(0, 204, 79)"><path fill-rule="evenodd" d="M8 15A7 7 0 1 0 8 1a7 7 0 0 0 0 14zm0 1A8 8 0 1 0 8 0a8 8 0 0 0 0 16z"/><path fill-rule="evenodd" d="M10.97 4.97a.75.75 0 0 1 1.071 1.05l-3.992 4.99a.75.75 0 0 1-1.08.02L4.324 8.384a.75.75 0 1 1 1.06-1.06l2.094 2.093 3.473-4.425a.236.236 0 0 1 .02-.022z"/></symbol>
                    <symbol id="no" width="2em" height="2em" viewBox="0 0 16 16" class="bi bi-dash-circle" fill="rgb(239, 42, 16)"><path fill-rule="evenodd" d="M8 15A7 7 0 1 0 8 1a7 7 0 0 0 0 14zm0 1A8 8 0 1 0 8 0a8 8 0 0 0 0 16z"/><path fill-rule="evenodd" d="M4 8a.5.5 0 0 1 .5-.5h7a.5.5 0 0 1 0 1h-7A.5.5 0 0 1 4 8z"/></symbol>
                </svg>
                <?php
                $resfinished = $row['finished'];
                if ($resfinished == 1) {
                    $finishedstatus = '<svg aria-hidden="true" focusable="false" style="margin-right: 2px; width: 32px; height: 32px"><use xlink:href="#okey"/></svg>';
                } elseif ($resfinished == 0) {
                    $finishedstatus = '<svg aria-hidden="true" focusable="false" style="margin-right: 2px; width: 32px; height: 32px"><use xlink:href="#no"/></svg>';
                }
                ?>
                <b class="rowlabelicon">Finished: </b><span class="gameinfo"> <?php echo $finishedstatus ?></span><br>
                <b class="rowlabelicon">Rating:</b>
                <span class="gameinfo">
                        <div class="Stars" style="--rating: <?php echo $row['rating'] ?>" aria-label="Rating of this product is <?php echo $row['rating'] ?>out of 5."></div>
                    </span>
                <br/><br/>
            </td>
            <td class="text">
                <b>Game info: </b><?php echo $row['gameinfo'] ?>
                <br/><br/>
                <b>Comments: </b><?php echo $row['comments'] ?>
                <br/><br/>
            </td>
        </tr>

        <?php }
        mysqli_close($conn);
        ?>

    </table>
EOT;
            }
        }
        else {
            $output = "<h3>No entries found</h3>";
        }
        echo $output;
    }

}
?>

phdr · December 4, 2020, 4:50pm

Unless you are using an actual templating engine/script that supports putting php code into the template, none of that will work. $filtertemplate isn’t being used in the posted code anyways, so, we cannot provide any help with problems with it.

The Heredoc syntax you are using in the posted code, the <<<"EOT" … EOT; are for building strings. The only ‘php’ you can put inside the string are variables. Where you have the closing EOT; now, is after the end of the while(){} loop and is also after the end of the </table> tag.

For where you are at, simply reorganize your code, by removing any of the php ‘business logic’, that knowns how to query for and retrieve data, from inside the html makeup, to be before the start of the html makeup, then just use simple php code inside the html makeup to test/use/loop over the data retrieved by the business logic.

As to the ‘dependent’ queries you want to execute inside of the main loop. Don’t. Executing queries inside a loop is a huge performance problem, due to the communications between php and the database server. In general, you would just join these with the main query.

Here’s a laundry list of points for the current code -

Since you are determining what will be displayed on the page, you should be using GET method forms/links, not a POST method form.
You should not try to detect if the submit button isset, since there are cases where it won’t be. If you are using a post method form, detect if the $_SERVER[‘REQUEST_METHOD’] == ‘POST’
Only store the user id in a session variable. Query on each page request to get any other user data. This will insure that if any of the user data is ever edited, the change will take effect on the very next page request.
Don’t copy variables to other variables for no reason. Just use the original variables.
Don’t put external, unknown, dynamic values directly into an sql query. Use a prepared query instead. Switching to the much simpler PDO database extension will greatly simplify using prepared queries.
Don’t use escaped double-quotes in the html markup. Just use single-quotes.
Since php destroys all resources used on a page when your script ends, just let php close the database connection for you.
Conditional logic ‘fail’ code is often much shorter than the ‘success’ code. If you switch the conditional test and put the ‘fail’ code first, it results in easier to read code.
The current else {…} branch, with the ‘No entries found’ output, is part of the if (isset($_POST['action'])) { logic, not the if($result->num_rows>0){ logic, and there’s an extra } at the end of the posted code.
If you use table alias names in your queries, it will make writing and reading the queries easier.

FGOD1983 · December 6, 2020, 10:33am

The template is removed from the code but I left the variabele in there to keep testing with of all else fails.

The problem I was having with inner joins on the labels was that it was not styling the labels per found label, that is why I started using the while loop inside the main loop. That way I was able to give styling to each found value.

The reason I was saying post is because the tutorial on the jQuery filter was using that. But it is a good idea to try to do it with get. But I would have no idea on how to use the get function inside jQuery to return the results from action.php

Good point on the isset for the submit button. I was unaware of this way of detecting it.

Yeah I guess it is indeed better to use the session useruid instead of a php variable inside the code/mysql queries.

On the matter of PDO, I’m converting my code already on some parts to this, but I wanted to get the code itself working first before converting it.