SQL query not returning rows to php-CodePudding

I really just need to figure out the flaw in my query. I'm pretty new to both PHP and SQL. When I run the site, I get no errors but am unable to access any data from the database.

I've been staring at this for a few hours now and can't figure it out. I do know that in my shell, any variable within a string is printing fine, as seen when I echo it out. My only thought is that a semicolon is needed in the queries, but anywhere else I've looked doesn't have anything for that.

<html>
    <?php
            $con = mysqli_connect("localhost", "someuser", "password123");
            if ($con->connect_error){
                    echo "Bad Connection to Database";
            }
            @$data = $_REQUEST["data"];
            $data = htmlspecialchars($data);
            $countries = array();

            $codequery =  "select code from nations where name rlike '$data'";

            $sqlcodes = mysqli_query($con, $codequery);
            if(!empty($sqlcodes) && $sqlcodes->num_rows > 0){
                    while($row = $sqlcodes->fetch_assoc()) {
                            $countries[$row] = $row["code"];
                    }
            }

            ?>

    <form>
            Enter a partial country name<br>
            <input type="text" id="data" name="data">
            <?php
                    echo "<br><br>User request for $data:<br>";
                    foreach (array_unique($countries) as $c){
                            $name = mysqli_query($con, 'select name from nations where code = "$countries[$c]"');
                            $pop = mysqli_query($con, 'select pop from nations where code = "$countries[$c]"');
                            $area = mysqli_query($con, 'select area from nations where code = "$countries[$c]"');
                            $tempupper = strtoupper($countries[$c]);
                            $map = '<img src="https://www.worldometers.info/img/maps_c/'.$tempupper.'"-map.gif>';
                            $flag = 'img src="https://www.worldometers.info/img/flags/small/tn_'.$countries[$c].'-flag.gif>"';
                            echo "<br>";
                            echo '<table border="5"> <tr><th>User Input:</th><th>$data</th></tr> <tr><th>Population:</th><th>$pop</th></tr> <tr><th>Area:</th><th>$area</th></tr> <tr><th>Map:</th><th>';
                    }
                ?>
            </textarea><br>
            <input type=submit>

            <?php
                mysqli_close($con);
            ?>
    </form>

</html>

CodePudding user response：

As noted in the comments to your question, there are a lot of issues with your code from a logic and structure standpoint. I added comments to many of the rows to explain what's happening. The output may not be exactly what you are looking for but you can alter it as needed.

<html>
    
<?php
$con = mysqli_connect("localhost", "someuser", "password123");
if ($con->connect_error) {
    echo "Bad Connection to Database";
}

$data = $_REQUEST["data"]; // get data from form submission

// Use prepared statement to avoid SQL injection
$stmt = $con->prepare("select code, name, pop, area from nations where name rlike ?"); // retrieve all needed fields in one query
$stmt->bind_param("s", $data); // the string submitted through the form will be used in the query
$stmt->execute();
$queryResult = $stmt->get_result();
$resultsArray = $queryResult->fetch_all(MYSQLI_ASSOC); // Result of the database query are stored in $resultsArray
$stmt->close();

mysqli_close($con);
?>

<body>
<!-- Create form to collect country name search -->
<form>
    Enter a partial country name<br>
    <input type="text" id="data" name="data">
    <input type=submit>
</form>

<?php 

// If user input is provided, show request and results
if ($data) {
    echo "<br><br>User request for " . $data . "<br>";

    // If $resultsArray exist, loop through the query results and display info for each record
    if (isset($resultsArray)) {
        foreach ($resultsArray as $values) {
            echo "<table border=5><tr>";
            echo "<th>User Input:</th><th>" . $data . "</th></tr>";
            echo "<tr><th>Population:</th><th>" . $values["pop"] . "</th></tr>";
            echo "<tr><th>Area:</th><th>" . $values["area"] . "</th></tr> ";
            echo "<tr><th>Map:</th><th><img src=https://www.worldometers.info/img/maps_c/" .
                strtoupper($values["code"]) .
                "-map.gif></th></tr>";
            echo "<tr><th>Flag:</th><th><img src=https://www.worldometers.info/img/flags/small/tn_" .
                $values["code"] .
                "-flag.gif></th></tr>";
            echo "</table>";
            echo "<br>";
        }
    }
} 

?>

</body>
</html>