for each loop for echoing column data

[ajetrumpet]

i'm doing my best to research this stuff guys, but I keep getting stuck.  My reporting is almost finished, but I'm stuck at the part of my code that's denoted by "//FOR EACH LOOP HERE?"  Can someone help?  in addition to this missing code I need, I also get the error "mysqli_num_rows() expects parameter 1 to be mysqli_resul".  But all these examples on the web use pretty much the same code like this.

<?php

$dbHost = "localhost";
$dbName= "rptDatabase";
$dbUsername = "username";
$dbPassword = "password";

	$conn = mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName);

	if (mysqli_connect_errno()) {
		echo "Failed to connect to MySQL: " . mysqli_connect_error();
								}
	$sql = mysqli_query($conn, "SELECT * FROM tblTraffic ORDER BY ip, date, time"); 
	$report = mysqli_fetch_array($sql); 

echo "<table border='1'>
<tr>
<th>IP ADDRESS</th>
<th>PAGE VISITED</th>
<th>PREVIOUS PAGE</th>
<th>DATE</th>
<th>TIME</th>
</tr>";

	if (mysqli_num_rows($report) > 0) {
		while($row = mysqli_fetch_array($report)) {
	    	echo "<tr>";
	    	//FOR EACH LOOP HERE?
    									 	 	  }
							   		  }
    else {
    	echo "0 results";
		 }

	$conn->close();

?>

 

Edited October 14, 2019 by ajetrumpet

[requinix]

First of all, that code makes my eyes hurt.

<?php

$dbHost = "localhost";
$dbName= "rptDatabase";
$dbUsername = "username";
$dbPassword = "password";

$conn = mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName);

if (mysqli_connect_errno()) {
	echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql = mysqli_query($conn, "SELECT * FROM tblTraffic ORDER BY ip, date, time"); 
$report = mysqli_fetch_array($sql); 

echo "<table border='1'>
	<tr>
		<th>IP ADDRESS</th>
		<th>PAGE VISITED</th>
		<th>PREVIOUS PAGE</th>
		<th>DATE</th>
		<th>TIME</th>
	</tr>";

if (mysqli_num_rows($report) > 0) {
	while($row = mysqli_fetch_array($report)) {
    		echo "<tr>";
    		//FOR EACH LOOP HERE?
	}
} else {
	echo "0 results";
}

$conn->close();

?>

Don't you think that looks a little better? Consistent? Don't have to look all over to see where {s and }s are?

As for the question,

A foreach loop is for going over each value inside an array, one at a time. Is that what you want to do here? Go over each column in the row one at a time?

No, it's not what you want to do. What you want is to go over each row (you're already doing that) and print out values from the row into the table structure you set up.

You've started a new row with that <tr>. Now you need to output each cell (a <td>) with the value inside it (from $row). Try that.

[ajetrumpet]

well I can't find the right thing on the web as an example.  this is not right, but is it close?

	    	foreach($row as $value) {
	    		echo "<td>$value</td>";
}

 

[ajetrumpet]

I found something close:

$fields_num = mysqli_num_fields($sql);

echo "<h1>Table: {tblTraffic}</h1>";
echo "<table border='1'><tr>";
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    $field = mysqli_fetch_field($sql);
    echo "<td>{$field->name}</td>";
}

echo "</tr>\n";
// printing table rows
while($row = mysqli_fetch_row($sql))
{
    echo "<tr>";

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $cell)
        echo "<td>$cell</td>";

    echo "</tr>\n";
}

but I'm getting 1 row printed out with my field names instead of the field content.

[ajetrumpet]

enough searching found me the answer.  this does what I was looking for:

<?php

$dbHost = "localhost";
$dbName= "rptDatabase";
$dbUsername = "username";
$dbPassword = "password";

	$conn = mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName);

	if (mysqli_connect_errno()) {
		echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
	$sql = mysqli_query($conn, "SELECT * FROM tblTraffic ORDER BY ip, date, time");
	$column_count = 5;
	$i = 1;

echo "<table border=1 width=100%>
<tr>
<th width=20%>IP ADDRESS</th>
<th width=20%>PAGE VISITED</th>
<th width=20%>PREVIOUS PAGE</th>
<th width=20%>DATE</th>
<th width=20%>TIME</th>
</tr>";

$fields_num = mysqli_num_fields($sql);

echo "<table border=0 width=100%><tr>";

// printing table rows
while($row = mysqli_fetch_array($sql)) {
	if ($i == 1) { echo '<tr width=100%>'; }

		echo "<td width=20% align='center'>".$row[1]."</td>";
		echo "<td width=20% align='center'>".$row[2]."</td>";
		if(empty($row[3])) { echo "<td width=20% align='center'>"."N/A"."</td>"; }
		else { echo "<td width=20% align='center'>".$row[3]."</td>"; }
		echo "<td width=20% align='center'>".$row[4]."</td>";
		echo "<td width=20% align='center'>".$row[5]."</td>";

			$i++;
		echo '</tr>';
}

echo '</table>';

	$conn->close();

?>

 

[requinix]

That also happens to be what I was trying to get you to do, so, 👍

[Barand]

You are creating variables that you never use ($column_count, $fields_num)

Your HTML markup for the table is wrong

• You have two table opening tags.
• You should have a <tr> tag at start of every row, not just when $i==1.

Use CSS styles instead of deprecated html attributes (eg width, align)

You don't need the $conn->close(); - that happens anyway at the end of the script.

Do not use "SELECT * ...". Specify the columns you want. (The table structure could be changed in future). I also told you earlier that dates should be stored as yyyy-mm-dd format. Same goes for times (hh:mm or hh:mm:ss). If you don't then you can't use them to order your data.

mysql> SELECT time12
            , time24 
       FROM tbtime 
       ORDER BY time12;
+--------+--------+
| time12 | time24 |
+--------+--------+
| 2:00AM | 02:00  |
| 2:00PM | 14:00  |
| 4:00AM | 04:00  |
| 4:00PM | 16:00  |
| 6:00PM | 18:00  |
| 8:00AM | 08:00  |
| 8:00PM | 20:00  |
+--------+--------+

You can reformat them for human benefit.

<?php
$dbHost = "localhost";
$dbName= "rptDatabase";
$dbUsername = "username";
$dbPassword = "password";

    mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
    $conn = mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName);
    
    $sql = mysqli_query($conn, "SELECT ip
                                     , page
                                     , CASE WHEN referrer = ''
                                            THEN 'N/A'
                                            ELSE referrer
                                       END as referrer     
                                     , DATE_FORMAT(date, '%m/%d/%y') as formatdate
                                     , TIME_FORMAT(time, '%l:%i%p') as formattime
                                FROM tblTraffic 
                                ORDER BY ip, date, time");


?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Example</title>
<style type="text/css">
    table  { font-family: verdana, sans-serif; font-size: 10pt; width: 100%; border-collapse: collapse; }
    th, td { width: 20%; text-align: center; padding: 8px; }
    th     { background-color: #396; color: #FFF; }
</style>
</head>
<body>
    <table border='1'>
        <tr>
            <th>IP ADDRESS</th>
            <th>PAGE VISITED</th>
            <th>PREVIOUS PAGE</th>
            <th>DATE</th>
            <th>TIME</th>
        </tr>
        
        <?php
            // printing table rows
            while($row = mysqli_fetch_row($sql)) {
                echo '<tr>'; 
                foreach ($row as $col) {
                    echo "<td>$col</td>";
                }
                echo '</tr>';
            }
            
        ?>
    </table>
</body>
</html>

 

Edited October 14, 2019 by Barand

[ajetrumpet]

thanks guys.  I will improve my script.

[Psycho]

I would add one more recommendation. Don't intermix PHP logic and HTML. Put all of your logic at the start of the script and assign the output to variables. Then output those variables int eh applicable areas of the HTML. As you build more complexity you will need to create scripts for specific purpose and include them with other scripts (instead of copy/pasting the same code all over the place). Example

<?php

//Connect to DB
//Execute query
$output = "";
while($row = mysqli_fetch_array($sql)) {
    $output .= //some code to create html output
}

?>
<html>
<body>
Here is my output:<br>
<?php echo $output; ?>
</body>
</html>

 

[ajetrumpet]

3 minutes ago, Psycho said:

 As you build more complexity you will need to create scripts for specific purpose and include them with other scripts (instead of copy/pasting the same code all over the place).

this is a static file and its a stand alone report.  but my script to capture the traffic data and throwing it into the database is a script that is included in all pages on my website.

[Psycho]

Just now, ajetrumpet said:

this is a static file and its a stand alone report.  but my script to capture the traffic data and throwing it into the database is a script that is included in all pages on my website.

Are the lines to connect to the DB included in this script? They shouldn't be. You should have a single script/file to connect to the DB. That way if there is ever a need to change the connection info for the database server it only has to be done one and in a file that is appropriately named (e.g. connectDb.php). What if you aren't around when the change has to be made? How are they to know all the places where the connection info needs to be changed. If you are already doing that for the most part, you're ahead of where many people are.

[ajetrumpet]

no I'm not doing this, but I rarely work with other people.  I am the sole developer for this small business.  but i will keep that in mind.  when you work for entrepreneurs a lot of times the "traditionality" and coding standards sometimes get missed cuz the business owners don't really understand the tech that their using.

[Barand]

31 minutes ago, Psycho said:

Put all of your logic at the start of the script and assign the output to variables

… but bear this in mind