How will you approach this sweet problem? (mySQL and PHP)

Question

A database solution.
In a central market for fruit farmers;
Fifty (50) farmers deliver different fruits every month.
Monthly analysis must be made to determine the marginal profit or loss. (see sample tables below)

Question
How do I professionally (using SQL ideal/maximization standard) generate each of the tables below from mySql database using PHP?

See sample table in the link below⇓⇓⇓

https://code.sololearn.com/WuMmdBa6k5jb/?ref=app

Thank you all!

Answer 1

Object-Oriented with PHP/5.6.25 and MySQL/5.7.17 using MySQLi [Dynamic]

Learn more about PHP and the MySQLi Library at PHP.net.

First, start a connection to the database. Do this by making all the string variables needed in order to connect, adjust them to fit your environment, then create a new connection object with new mysqli() and initialize it with the previously made variables as its parameters. Now, check the connection for errors and display a message whether any were found or not.
Like this:

<?php
$servername = "localhost"; $username = "root"; $password = "yourPassword"; $database = "world"; $conn = new mysqli($servername, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully<br>";
?>

Next, make a variable that will hold the query as a string, in this case its a selectstatement with a limit of 100 records to keep the list small. Then, we can execute it by calling the mysqli::query() function from our connection object. Now, it's time to display some data. Start by opening up a <table> tag through echo, then fetch one row at a time in the form of a numerical array with mysqli::fetch_row() which can then be displayed with a simple for loop. mysqli::field_count should be self explanatory. Don't forget to use <td></td>for each value, and also to open and close each row with echo"<tr>" and echo"</tr>. Finally we close the table, and the connection as well with mysqli::close().

<?php
$query = "select * from city limit 100;"; $queryResult = $conn->query($query);
echo "<table>";
while ($queryRow = $queryResult->fetch_row()) {
echo "<tr>"; for($i = 0; $i < $queryResult->field_count; $i++)
{
echo "<td>$queryRow[$i]</td>"; }
echo "</tr>";
}
echo "</table>"; $conn->close();
?>

Any feedback would be much appreciated! Good Luck!