Jump to content

for each loop for echoing column data


ajetrumpet

Recommended Posts

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 by ajetrumpet
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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();

?>

 

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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>

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.