How to display a mysql table with php?-CodePudding

I am relatively new to mysql and I have followed a tutorial to get my database to display. I used PHP and I can't find any errors but it doesn't show. Can anyone help?

Thanks

The name of the server is "sql306.iceiy.com"

The name of the rows are "Exercise_Name", "Muscle_Name" and "PPS"

        $servername = "sql306.iceiy.com";
        $username = "icei_32733650";
        $password = "ZoD473049gais";

        $conn = new mysqli($servername, $username, $password);

        // Check connection
        if ($conn->connect_error) {
          echo("Connection failed: " . $conn->connect_error);
        }
        echo "Connected!";

        $sql = "SELECT Exercise_Name, Muscle_Name, PPS, FROM gym_exercises";
if($result = mysqli_query($link, $sql)){
    if(mysqli_num_rows($result) > 0){
        echo "Table";
            echo "<tr>";
                echo "<th>Exercise_Name</th>";
                echo "<th>Muscle_Name</th>";
                echo "<th>PPS</th>";
            echo "</tr>";
        while($row = mysqli_fetch_array($result)){
            echo "<tr>";
                echo "<td>" . $row['Exercise_Name'] . "</td>";
                echo "<td>" . $row['Muscle_Name'] . "</td>";
                echo "<td>" . $row['PPS'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
        // Free result set
        mysqli_free_result($result);
    } else{
        echo "No records matching your query were found.";
    }
} else{
    echo "ERROR:";
}
 
// Close connection
mysqli_close($link);
      ?>

CodePudding user response：

I see several issues with your code:

You need to specify the database in which the relation(s) you are processing exist: $conn = new mysqli($servername, $username, $password, $databaseName);.
You have an extra comma in your select statement.
Since you are using the object-oriented style in connecting, you should continue with that style for consistency. Moreover, I don't see where variable $link has been defined.
You don't have a proper <table> tag.

In the code below I have eliminated doing calls to echo. There is nothing wrong with that per se. It's just a question of what is neater and clearer.

Also, there could be values in the table that could be mistaken for HTML tags if any of the data contains '<' and/or '>'. If so, then you will want to replace these characters with HTML entities, for example htmlentities(row['PPS']).

<?php

$servername = "sql306.iceiy.com";
$username = "icei_32733650";
$password = "ZoD473049gais";
$databasename = "some_database_name";

$conn = new mysqli($servername, $username, $password, $databasename);

// Check connection
if ($conn->connect_error) {
    echo("Connection failed: " . $conn->connect_error);
    return; /* Do not execute remaining code */
}

//echo "Connected!"; /* do not put this out */

$sql = "SELECT Exercise_Name, Muscle_Name, PPS FROM gym_exercises";
if($result = $conn->query($sql)) {
    if($result->num_rows > 0) {
?>
<table>
    <tr>
        <th>Exercise_Name</th>
        <th>Muscle_Name</th>
        <th>PPS</th>
    </tr>
<?php
        while ($row = $result->fetch_array()) {
?>
    <tr>
        <td><?=$row['Exercise_Name']?></td>
        <td><?=$row['Muscle_Name']?></td>
        <td><?=$row['PPS']?></td>
    </tr>
<?php
        }
?>
</table>
<?php
    }
    else {
        echo "No records matching your query were found.";
    }
    // Free result set
    $result->free();
}
else {
    echo "ERROR";
}

// Close connection
$conn->close();