Displaying User Comments

[doubledee]

I need help getting user comments from my database and displaying them in a format like you'd see in an online newspaper where each user's comments are displayed one after the other like this...

---------------------

By John Doe

8:30 p.m. on Sept 1, 2011

I really liked Debbie's article and encourage others to read it!

 

---------------------

By Sally Peters

11:00 a.m. on Aug 30, 2011

I had the same thing happen to me!!

 

---------------------

 

 

Here is the code I have on my "article.php" page below where I generate my Article...

 

// **********************
// Build Comments Query	*
// **********************

// Build query.
$q = 'SELECT m.first_name, c.created_on, c.body, c.status
	FROM member AS m
	INNER JOIN comment AS c
	ON m.id = c.member_id
	WHERE article_id=?';

// Prepare statement.
$stmt = mysqli_prepare($dbc, $q);

// Bind variable.
mysqli_stmt_bind_param($stmt, 'i', $articleID);

// Execute query.
mysqli_stmt_execute($stmt);

// Store result-set.
mysqli_stmt_store_result($stmt);

// ****************************
// Check for Comments Record.	*
// ****************************
if (mysqli_stmt_num_rows($stmt)>=1){
// Comment in Database.
$commentExists = TRUE;

// Bind result-set to variables.
mysqli_stmt_bind_result($stmt, $firstName, $createdOn, $body, $status);

// Fetch record.
mysqli_stmt_fetch($stmt);
}

 

 

Is it correct that mysqli_stmt_store_result($stmt); stores the entire query results?

 

Where does it do that?  Some hidden MySQL object in memory?

 

How do I get the query results into a format where I can iterate through each record and display it?

 

Thanks,

 

 

 

Debbie

 

[doubledee]

Anyone?

 

 

Debbie

 

 

[mikesta707]

look into the mysqli_xxx_xxx_fetch method, detailed on this page: http://php.net/manual/en/mysqli-stmt.fetch.php

 

The second code example given on that page seems like it accomplishes what you want.

[doubledee]

look into the mysqli_xxx_xxx_fetch method, detailed on this page: http://php.net/manual/en/mysqli-stmt.fetch.php

 

The second code example given on that page seems like it accomplishes what you want.

 

But I don't understand why they use different functions than what I was using before in my other article query.

 

I was able to get things working, but was hoping for someone to help me understand each part of the correct code.

 

 

Debbie

 

 

[cssfreakie]

Have a look at this article. http://devzone.zend.com/article/686

It's a great introduction into prepared statements, explained in a decent manner with some nice examples. If I am correct there is a second part of that one.

[doubledee]

Have a look at this article. http://devzone.zend.com/article/686

It's a great introduction into prepared statements, explained in a decent manner with some nice examples. If I am correct there is a second part of that one.

 

If I post my code here, can you help me figure out what each part is doing?

 

 

Debbie

 

 

[cssfreakie]

Have a look at this article. http://devzone.zend.com/article/686

It's a great introduction into prepared statements, explained in a decent manner with some nice examples. If I am correct there is a second part of that one.

 

If I post my code here, can you help me figure out what each part is doing?

 

 

Debbie

I might if i find some spare time Most of those functions are pretty descriptive though

[gizmola]

In general programming terms a "buffer" is an area of memory that is utilized as a temporary storage location to help with performance by limiting the amount of work required to get one piece of information.  In this case -- fetching one column or one row from the result set.  This might be a good thing, or it might be a bad thing, especially if the result set is very large and you will typically only fetch a few rows, although in that case, you'd probably want to use LIMIT in the query anyways.

 

If you utilize mysqli_stmt_store_result($stmt) then all the result set data is buffered in the php script .  This is only important if you're planning to use mysqli_num_rows(), because it will not return the result number if you do not first call the store_result method. 

 

If you are not going to use mysqli_num_rows(),  then fetching the data unbuffered is probably not going to be any slower. 

 

In summary, the answer to your question is, that store_result() is a mysqli utility function that "buffers" (fetches the result set data into an internal php variable) which will be subsequently used by when fetching data.  If you do not call it, fetching will still work.

[doubledee]

If I post my code here, can you help me figure out what each part is doing?

 

 

Debbie

 

I might if i find some spare time Most of those functions are pretty descriptive though

 

Apparently not descriptive enough...

 

Glad you volunteered!

 

Here is what I have...

 

// **********************
// Build Comments Query	*
// **********************
// Build query.
$q2 = 'SELECT m.first_name, c.created_on, c.body, c.status
	FROM member AS m
	INNER JOIN comment AS c
	ON m.id = c.member_id
	WHERE c.article_id=?';

// Prepare statement.
$stmt2 = mysqli_prepare($dbc, $q2);

 

The line above links the database connection to the query string, right?

 

 

// Bind variable.
mysqli_stmt_bind_param($stmt2, 'i', $articleID);

 

This links/binds my local form variable to the Prepared Statement, right?

 

 

// Execute query.
mysqli_stmt_execute($stmt2);

 

Runs the Prepared Statement using the bound variables, right?

 

 

// Store result-set.
mysqli_stmt_store_result($stmt2);

 

I'm guessing this stores the entire query results set in some humongous variable/object in memory??

 

Not sure the exact format of this "data structure" if that's what it is?! 

 

 

// ****************************
// Check for Comments Record.	*
// ****************************
if (mysqli_stmt_num_rows($stmt2)>=1){
// Comment in Database.
$commentExists = TRUE;

// Bind result-set to variables.
mysqli_stmt_bind_result($stmt2, $firstName, $createdOn, $body, $status);
}

 

Last line somehow associates each field in the query result set to the variables above, right?

 

Again, I'm having a hard time visualizing what the "data structure" might look like or exactly how this works, especially if my SELECT returns 1,000 rows... 

 

 

<!-- MEMBER COMMENTS -->
<?php
// Fetch Comment record.
while (mysqli_stmt_fetch($stmt2)){
echo '<div class="comment">';
echo		'<p class="commentAuthor">By ' . $firstName . '</p>';
echo   '<p class="commentDate">' . date('g:ia', strtotime($createdOn)) . ' on ' . date('M j, Y', strtotime($createdOn)) . '</p>';
echo	$body;
echo '</div>';
}
?>

 

I'm guess that mysqli_stmt_fetch() grabs a ROW (??) from the results set created in mysqli_stmt_store_result();, right??

 

Please explain this more!

 

If you follow the (Procedural) example here: http://www.php.net/manual/en/mysqli-stmt.fetch.php

 

You will see that they don't follow the same sequence of events that I do and that confused me...

 

 

Debbie

 

 

[doubledee]

In general programming terms a "buffer" is an area of memory that is utilized as a temporary storage location to help with performance by limiting the amount of work required to get one piece of information.  In this case -- fetching one column or one row from the result set.  This might be a good thing, or it might be a bad thing, especially if the result set is very large and you will typically only fetch a few rows, although in that case, you'd probably want to use LIMIT in the query anyways.

 

If you utilize mysqli_stmt_store_result($stmt) then all the result set data is buffered in the php script .  This is only important if you're planning to use mysqli_num_rows(), because it will not return the result number if you do not first call the store_result method. 

 

So I need it for code like this, right?

 

// Execute query.
mysqli_stmt_execute($stmt2);

// Store result-set.
mysqli_stmt_store_result($stmt2);

// ****************************
// Check for Comments Record.	*
// ****************************
if (mysqli_stmt_num_rows($stmt2)>=1){
// Comment in Database.
$commentExists = TRUE;

 

 

 

If you are not going to use mysqli_num_rows(),  then fetching the data unbuffered is probably not going to be any slower. 

 

In summary, the answer to your question is, that store_result() is a mysqli utility function that "buffers" (fetches the result set data into an internal php variable) which will be subsequently used by when fetching data.  If you do not call it, fetching will still work.

 

Okay.

 

Feel free to comment on my last post that came after you made this one!

 

 

 

Debbie

 

 

[gizmola]

Your description is accurate, only it is important to note that store_result does not need to be called.  If it is, yes it stores it in a variable, but not one that is accessible from php in anyway.  It's memory used by the mysqli client routines invisibly.

 

If you do want to use the num_rows, then you do need to use buffered mode.

 

As for "bind" variables, I think it's helpful if you think about them as a contract between your script and mysqli.  "Binding" a variable simply sets things up so that the php variable name is "bound" to a particular column.  After you do that, everytime you fetch the client will put the value of the column for that fetched row into the bound variable.  This can be a really confusing concept for people use to the original mysql api.  It is also not something you *have* to use as there are routines like mysqli_fetch_row or mysqli_fetch_assoc you can use instead.

[doubledee]

Your description is accurate, only it is important to note that store_result does not need to be called.  If it is, yes it stores it in a variable, but not one that is accessible from php in anyway.  It's memory used by the mysqli client routines invisibly.

 

If you do want to use the num_rows, then you do need to use buffered mode.

 

As for "bind" variables, I think it's helpful if you think about them as a contract between your script and mysqli.  "Binding" a variable simply sets things up so that the php variable name is "bound" to a particular column.  After you do that, everytime you fetch the client will put the value of the column for that fetched row into the bound variable.  This can be a really confusing concept for people use to the original mysql api.  It is also not something you *have* to use as there are routines like mysqli_fetch_row or mysqli_fetch_assoc you can use instead.

 

Okay, so I think I'm *slowly* gtting this tuff down.

 

But how did my code look?

 

(The code above is basically all of the code I used to pull my comments out if the database and display them one after another below the corresponding article.)

 

My code seems to run okay - so far - but you just never know?!

 

 

Debbie

 

 

[gizmola]

The code looks clear and functional. The important thing is that you understand it, and that you'll be able to maintain it and update it as needed.