foreach and MYSQL confusion

[V]

"while" was easy to understand 

 

I don't know how to echo two variables using foreach.

 

First I have a function

 

function show_posts(MySQLi $connection, $category) {
$posts = array();
$sql = "SELECT * FROM posts WHERE category_id = '$category' ORDER BY post_date DESC";
while ($row = $result->fetch_object()) {
    $posts[$row->post_id] = $row->post_title;
  }
  return $posts;
}

 

And then I echoed the post title but how about the post id (fk) or post date?

 

 

 

$posts = show_posts($connection, $category);

foreach ($posts as $key => $value){ 
    
    	        $post_id = $row["post_id"];
	$post_title = $row["post_title"];
	$post_date = $row["post_date"];

	?>

        
<li <?php if ($post_id == $post) {print $selected;}  ?>><a href="single_post.php?cat=<?php echo $cat; ?>&postID=<?php echo urlencode($post_id); ?>"><?php echo $value; ?></a><br />
<div class="date"><?php echo relativeTime($post_date); ?> </div></li>
        
<?php	}	?>

 

I get an empty postID an incorrect post date. Also I'm not sure if using "while" in the function and "foreach" on the another page that uses the function is good :-\ Foreach is really confusing me..

[ChemicalBliss]

Ok what foreach does is loop through an array while:

  1. Assigning the _value_ of each array item to a variable

  2. Assigning the _keyname_ of each array item to a variable

- Both assignments are optional, eg; "foreach($array){ ... }"

 

Quick example:

$somearray = array(
   0 => array ('post_id'=>2, 'post_title'=>'test post title','post_date'=>'8/8/10')
   1 => array ('post_id'=>5, 'post_title'=>'test post title2','post_date'=>'8/8/10')
);

// The foreach:
foreach($somearray As $key=>$value){
   echo("Key: ".$key."<br />Value:".$value."<hr />"); // This will have 2 iterations.
}

 

Output:

Key: post_id
Value: 2
-------------------------------------------------------------------
Key: post_title
Value: test post title
-------------------------------------------------------------------
Key: post_date
Value: 8/8/10
-------------------------------------------------------------------
Key: post_id
Value: 5
-------------------------------------------------------------------
Key: post_title
Value: test post title2
-------------------------------------------------------------------
Key: post_date
Value: 8/8/10
-------------------------------------------------------------------

 

Hope this helps,

-cb-

[wildteen88]

The problem is you're not setting up the $posts array correctly within your show_posts function. Your're only returning the the post_title field, but in your foreach loop you're trying to uses fields which is not being returned from your function.

 

To sort this out, all you need is a few simple changes

 

First change this

while ($row = $result->fetch_object()) {
    $posts[$row->post_id] = $row->post_title;
  }

to

while ($row = $result->fetch_object()) {
    $posts[] = $row;
  }

 

Now for your foreach loop, you'd do

$posts = show_posts($connection, $category);

foreach ($posts as $row){ 
    
    	        $post_id = $row->post_id;
	$post_title = $row->post_title;
	$post_date = $row->post_date;

	?>

        
<li <?php if ($post_id == $post) {print $selected;}  ?>><a href="single_post.php?cat=<?php echo $cat; ?>&postID=<?php echo urlencode($post_id); ?>"><?php echo $value; ?></a><br />
<div class="date"><?php echo relativeTime($post_date); ?> </div></li>
        
<?php	}	?>

 

Now you should get the correct behaviour you're looking for.

[AbraCadaver]

In your while you only saved the id and title, but you saved the id as the key, so there is only $row->title.  Further, $row is an array of objects, not an array of arrays.  Also, you foreach $posts as $key and $value, but then you try and use $row in the loop?  Try this:

 

while ($row = $result->fetch_object()) {
    $posts[] = $row;
  }

 

Then:

 

foreach ($posts as $post){ 
    $post_id = $post->post_id;
    $post_title = $post->post_title;
    $post_date = $post->post_date;
    // etc...
}

 

[ChemicalBliss]

(Extension to my post above)

Oh and i would store/return a whole result array much like mysql_fetch_assoc() does in increments, eg:

 

function show_posts(MySQLi $connection, $category) {
   $posts = array();
   $sql = "SELECT * FROM posts WHERE category_id = '$category' ORDER BY post_date DESC";
   while ($row = $result->fetch_assoc()) { // Get an associative array of the current row (assoc = with keynames, )
      $posts[] = $row; // Just add the whole row as a new array item
   }
   return $posts;
}

 

But I can't see how your getting any results anyway since your not actually executing the query:

// You need to connect
$mysqli = new mysqli("localhost", "username", "password", "database");

// Execute the query
$result = $mysqli->query("SELECT * FROM posts WHERE category_id = '$category' ORDER BY post_date DESC");

// Save Results as an array
$posts = array();
while ($row = $result->fetch_assoc()) { // Get an associative array of the current row (assoc = with keynames, )
   $posts[] = $row; // Just add the whole row as a new array item
}

// Display results
print_r($posts); // print_r() is useful for outputting raw data from an array. It is most easily viewable in the output source code.

 

Here are a few helpful links:

Connecting with MySQLi Class: http://uk3.php.net/manual/en/mysqli.connect.php

Executing a MySQLi Query: http://uk3.php.net/manual/en/mysqli.query.php

Returning Assoc Arrays: http://uk3.php.net/manual/en/mysqli-result.fetch-assoc.php

The entire manual: http://uk3.php.net/manual/en/index.php

 

-cb-

[V]

Wow! Triple reply

 

@ChemicalBliss, thank you for the detailed and clear explanation! I understand foreach much better

 

@wildteen88,AbraCadaver, both the solutions were correct It works perfectly! Btw, does foreach make loading slower? Maybe something else I added is causing it but when I used "while" directly without any function, navigating from one post to another seemed a little faster.

 

Thanks again!!

[V]

ChemicalBliss thanks for the addition and links!! They're very helpful. I have my DB connection in a header.php, I forgot to mention sorry

[ChemicalBliss]

Use microtime() at the start and end of your script to find the execution time (endtime - starttime = execution time).

php.net/microtime

 

(I would use microtime(TRUE) as it will return a single float as by default it returns an array of integer/float (unix timestamp + millesecond).

 

-cb-

[V]

Thanks for the code! The time varies. It's never the same number. It's

 

1278718880.3978

 

then

 

1278718902.5897

[ChemicalBliss]

No no,

(endtime - starttime = execution time).

 

so:

 

<?php
$start = microtime(TRUE);

... code here

$end = microtime(TRUE);
exit("<hr /> Execution Time: ".($end - $start));
?>

 

-cb-

[V]

Oooh! I see. Thanks for the support! The time seems to be the same using both methods.

[ChemicalBliss]

The time seems to be the same using both methods.

 

Can you ellaborate?

 

You should be getting something like:

0.0015583463

0.0024892455

etc.

 

-cb-