We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

replace placehoder in template with looped results from query

peterbrowne
peterbrowne asked
on
Medium Priority
623 Views
Last Modified: 2013-12-13
I have a page template with a number of placeholders in the format _PLACEHOLDER_ etc which are being replaced with data from mysql queries using str_replace.  This works fine. However, for a few placeholders they need to be replaced with multiple records, in this case they are comments like you see listed in a blog.  I have tried to loop the results for the comments out and add them to the str_replace, but its not working.  Any ideas?  (yes, I know about template engines, but I don't want to use them...).  See the relevant section of code below...

Thanks for any help!
while ($row_comment = mysql_fetch_assoc($result_comment)){
 
if($display_comments == '0' && $numrows_comment > 0){
	$comment_owner = "<hr /><p>" . $row_comment['first_name'] . ' ' . $row_comment['last_name'] . " said:";
	$display_comment = $row_comment['comment'];
	$comment_date =  'on ' . date("l, F j, Y, g:i a", strtotime($row_comment['comment_date'])) . "<p><hr />";
}
 
$placeholders = array('_PRESENTATION_TITLE_', '_PAGE_TITLE_', '_PAGE_DESCRIPTION_', '_ARTEFACT_', '_ADD_COMMENT_', '_OWNER_', '_COMMENT_', '_DATE_', '_MENU_ITEM_1', '_MENU_ITEM_2', '_MENU_ITEM_3', '_MENU_ITEM_4', '_MENU_ITEM_5', '_MENU_ITEM_6');
 
$placeholders_values = array("$presentation_title", "$page_title", "$page_description", "$artefact", "$add_comment", "$comment_owner", "$display_comment", "$comment_date", "$menu_item_1", "$menu_item_2", "$menu_item_3", "$menu_item_4", "$menu_item_5", "$menu_item_6");
 
$page = str_replace($placeholders, $placeholders_values, $template);
 
}
 
echo $page;

Open in new window

Comment
Watch Question

Most Valuable Expert 2011
Author of the Year 2014

Commented:
What's the symptom that causes you to say it is not working?  Can you show us an example, please?

Author

Commented:
Mmm...the while statement was causing an error...although I can't see where this might be occuring.  The whole code is below with the while loop commented out.  I don't believe this would work anyway...

You can see what I am trying to do at:

http://elearning.ucc.usyd.edu.au/comment/page_1.php
<?php require_once('connection/connect.php');
mysql_select_db($database, $connection);
 
$user = '1';
 
function mysql_fetch_rows($result, $numass=MYSQL_BOTH) {
  $i=0;
  $keys=array_keys(mysql_fetch_array($result, $numass));
  mysql_data_seek($result, 0);
    while ($row = mysql_fetch_array($result, $numass)) {
      foreach ($keys as $speckey) {
        $got[$i][$speckey]=$row[$speckey];
      }
    $i++;
    }
  return $got;
}
 
 
$query = "SELECT * FROM user, user_group, presentation, page, artefact, template, theme WHERE user.group_id = user_group.group_id AND presentation.group_id = user_group.group_id AND page.presentation_id = presentation.presentation_id AND page.artefact_id = artefact.artefact_id AND presentation.template_id = template.template_id AND presentation.theme_id = theme.theme_id AND user.user_id = '$user' ORDER BY page_title ASC";
$result = mysql_query($query, $connection) or die(mysql_error());
 
$records = mysql_fetch_rows($result);
 
$presentation_title = $records[0]['presentation_title'];
$page_title = $records[4]['page_title'];
$page_description = $records[4]['page_description'];
$template = $records[0]['template'];
$page_id = $records[4]['page_id'];
$artefact_path = $records[4]['artefact_path'];
$artefact_width = $records[4]['width'];
$artefact_height = $records[4]['height'];
$display_comments = $records[0]['display_comments'];
$comment_allowed = $records[0]['add_comments'];
 
$comment = $_POST['comment'];
 
if(isset($_POST['submit'])){ 
$query_comment = "INSERT INTO comment (comment_id,comment,comment_date,user_id,page_id)
          VALUES ('','$comment',NOW(),'$user','$page_id')";
$result_comment = mysql_query($query_comment, $connection) or die(mysql_error());
}
 
 
$query_comment = "SELECT * FROM comment, user WHERE user.user_id = comment.user_id AND comment.page_id = '$page_id'";
$result_comment = mysql_query($query_comment, $connection) or die(mysql_error());
$row_comment = mysql_fetch_assoc($result_comment);
$numrows_comment = mysql_num_rows($result_comment); 
 
 
switch($comment_allowed)
{
    case 0:
        $add_comment = "<form id='comment' name='comment' method='post' action=''><table width='100%' border='0' cellpadding='4'>
  <tr>
    <td id='sub_head' colspan='2'>Add a comment on this artifact<p><hr /><p></td>
    </tr>
  <tr>
    <td width='31%'><p><textarea name='comment' id='comment' cols='90' rows='8'></textarea></td>
    <td width='69%' valign='bottom'><input type='submit' name='submit' id='submit' value='Add' /><input name='page_id' type='hidden' value='<?php echo $page_id; ?>' /></td>
  </tr>
    <tr>
    <td colspan='2'><p><hr /><p></td>
    </tr>
</table>
</form>";
        break;
    case 1:
        $add_comment = "<span id='sub_head'>The posting of comments has been disabled for this presentation.</span>";
        break;
}
 
 
 
 
 
if ($records[4]['artefact_type'] == 'JPG' || 'GIF' || 'PNG'){
	$artefact = "<img src='admin/" . $artefact_path . "' width='" . $artefact_width .  "' height='" . $artefact_height . "' />";
}
 
 
if ($records[4]['artefact_type'] == 'MOV'){
	$artefact = "<object width='" . $artefact_width . "' height='" . $artefact_height . "' classid='clsid:02BF25D5-8C17-4B23-BC80-D3488ABDDC6B' codebase='http://www.apple.com/qtactivex/qtplugin.cab'><param name='src' value='admin/" . $artefact_path  . "'><param name='autoplay' value='false' /><embed src='admin/" . $artefact_path  . "' width='" . $artefact_width . "' height='" . $artefact_height . "' autoplay='false' controller='true' pluginspage='http://www.apple.com/quicktime/download/'></embed></object>";
}
 
$page_1 = $records[0]['page_title'];
$page_2 = $records[1]['page_title'];
$page_3 = $records[2]['page_title'];
$page_4 = $records[3]['page_title'];
$page_5 = $records[4]['page_title'];
$page_6 = $records[5]['page_title'];
 
if (!empty($page_1)){
	$menu_item_1 = "<a href='page_1.php'>" . $page_1 . "</a>";
}
 
if (!empty($page_2)){
	$menu_item_2 = "<a href='page_2.php'>" . $page_2 . "</a>";
}
 
if (!empty($page_3)){
	$menu_item_3 = "<a href='page_3.php'>" . $page_3 . "</a>";
}
 
if (!empty($page_4)){
	$menu_item_4 = "<a href='page_4.php'>" . $page_4 . "</a>";
}
 
if (!empty($page_5)){
	$menu_item_5 = "<a href='page_5.php'>" . $page_5 . "</a>";
}
 
if (!empty($page_5)){
	$menu_item_6 = "<a href='page_6.php'>" . $page_6 . "</a>";
}
 
 
 
//while ($row_comment = mysql_fetch_assoc($result_comment)){
 
if($display_comments == '0' && $numrows_comment > 0){
	$display_comment = "<div id='comment_owner'><hr /><p>" . $row_comment['first_name'] . ' ' . $row_comment['last_name'] . " said:</div><div id='comment'>" . $row_comment['comment'] . "</div><div id='comment_date'>on " . date("l, F j, Y, g:i a", strtotime($row_comment['comment_date'])) . "<p><hr /></div>";
}
 
$placeholders = array('_PRESENTATION_TITLE_', '_PAGE_TITLE_', '_PAGE_DESCRIPTION_', '_ARTEFACT_', '_ADD_COMMENT_', '_COMMENT_', '_MENU_ITEM_1', '_MENU_ITEM_2', '_MENU_ITEM_3', '_MENU_ITEM_4', '_MENU_ITEM_5', '_MENU_ITEM_6');
 
$placeholders_values = array("$presentation_title", "$page_title", "$page_description", "$artefact", "$add_comment", "$display_comment", "$menu_item_1", "$menu_item_2", "$menu_item_3", "$menu_item_4", "$menu_item_5", "$menu_item_6");
 
$page = str_replace($placeholders, $placeholders_values, $template);
 
//}
 
echo $page;
?>

Open in new window

Author

Commented:
The while loop is not causing an error now...but I still can't get $display_comment to loop out for the _COMMENT_ placeholder (the comments echo out OK if I replace '$display_comment =' with echo) and display all of the comments, not just the last comment.
<?php require_once('connection/connect.php');
mysql_select_db($database, $connection);
 
$user = '1';
 
function mysql_fetch_rows($result, $numass=MYSQL_BOTH) {
  $i=0;
  $keys=array_keys(mysql_fetch_array($result, $numass));
  mysql_data_seek($result, 0);
    while ($row = mysql_fetch_array($result, $numass)) {
      foreach ($keys as $speckey) {
        $got[$i][$speckey]=$row[$speckey];
      }
    $i++;
    }
  return $got;
}
 
 
$query = "SELECT * FROM user, user_group, presentation, page, artefact, template, theme WHERE user.group_id = user_group.group_id AND presentation.group_id = user_group.group_id AND page.presentation_id = presentation.presentation_id AND page.artefact_id = artefact.artefact_id AND presentation.template_id = template.template_id AND presentation.theme_id = theme.theme_id AND user.user_id = '$user' ORDER BY page_title ASC";
$result = mysql_query($query, $connection) or die(mysql_error());
 
$records = mysql_fetch_rows($result);
 
$presentation_title = $records[0]['presentation_title'];
$page_title = $records[1]['page_title'];
$page_description = $records[1]['page_description'];
$template = $records[0]['template'];
$page_id = $records[1]['page_id'];
$artefact_path = $records[1]['artefact_path'];
$artefact_width = $records[1]['width'];
$artefact_height = $records[1]['height'];
$display_comments = $records[0]['display_comments'];
$comment_allowed = $records[0]['add_comments'];
 
$comment = $_POST['comment'];
 
if(isset($_POST['submit'])){ 
$query_comment = "INSERT INTO comment (comment_id,comment,comment_date,user_id,page_id)
          VALUES ('','$comment',NOW(),'$user','$page_id')";
$result_comment = mysql_query($query_comment, $connection) or die(mysql_error());
}
 
 
$query_comment = "SELECT * FROM comment, user WHERE user.user_id = comment.user_id AND comment.page_id = '$page_id'";
$result_comment = mysql_query($query_comment, $connection) or die(mysql_error());
//$row_comment = mysql_fetch_assoc($result_comment);
$numrows_comment = mysql_num_rows($result_comment); 
 
 
switch($comment_allowed)
{
    case 0:
        $add_comment = "<form id='comment' name='comment' method='post' action=''><table width='100%' border='0' cellpadding='4'>
  <tr>
    <td id='sub_head' colspan='2'>Add a comment on this artifact<p><hr /><p></td>
    </tr>
  <tr>
    <td width='31%'><p><textarea name='comment' id='comment' cols='90' rows='8'></textarea></td>
    <td width='69%' valign='bottom'><input type='submit' name='submit' id='submit' value='Add' /><input name='page_id' type='hidden' value='<?php echo $page_id; ?>' /></td>
  </tr>
    <tr>
    <td colspan='2'><p><hr /><p></td>
    </tr>
</table>
</form>";
        break;
    case 1:
        $add_comment = "<span id='sub_head'>The posting of comments has been disabled for this presentation.</span>";
        break;
}
 
 
 
 
 
if ($records[1]['artefact_type'] == 'JPG' || 'GIF' || 'PNG'){
	$artefact = "<img src='admin/" . $artefact_path . "' width='" . $artefact_width .  "' height='" . $artefact_height . "' />";
}
 
 
if ($records[1]['artefact_type'] == 'MOV'){
	$artefact = "<object width='" . $artefact_width . "' height='" . $artefact_height . "' classid='clsid:02BF25D5-8C17-4B23-BC80-D3488ABDDC6B' codebase='http://www.apple.com/qtactivex/qtplugin.cab'><param name='src' value='admin/" . $artefact_path  . "'><param name='autoplay' value='false' /><embed src='admin/" . $artefact_path  . "' width='" . $artefact_width . "' height='" . $artefact_height . "' autoplay='false' controller='true' pluginspage='http://www.apple.com/quicktime/download/'></embed></object>";
}
 
$page_1 = $records[0]['page_title'];
$page_2 = $records[1]['page_title'];
$page_3 = $records[2]['page_title'];
$page_4 = $records[3]['page_title'];
$page_5 = $records[4]['page_title'];
$page_6 = $records[5]['page_title'];
 
if (!empty($page_1)){
	$menu_item_1 = "<a href='page_1.php'>" . $page_1 . "</a>";
}
 
if (!empty($page_2)){
	$menu_item_2 = "<a href='page_2.php'>" . $page_2 . "</a>";
}
 
if (!empty($page_3)){
	$menu_item_3 = "<a href='page_3.php'>" . $page_3 . "</a>";
}
 
if (!empty($page_4)){
	$menu_item_4 = "<a href='page_4.php'>" . $page_4 . "</a>";
}
 
if (!empty($page_5)){
	$menu_item_5 = "<a href='page_5.php'>" . $page_5 . "</a>";
}
 
if (!empty($page_5)){
	$menu_item_6 = "<a href='page_6.php'>" . $page_6 . "</a>";
}
 
 
 
while ($row_comment = mysql_fetch_assoc($result_comment)){
 
if($display_comments == '0' && $numrows_comment > 0){
	$display_comment = "<div id='comment_owner'><hr /><p>" . $row_comment['first_name'] . ' ' . $row_comment['last_name'] . " said:</div><div id='comment'>" . $row_comment['comment'] . "</div><div id='comment_date'>on " . date("l, F j, Y, g:i a", strtotime($row_comment['comment_date'])) . "<p><hr /></div>";
}
 
$placeholders = array('_PRESENTATION_TITLE_', '_PAGE_TITLE_', '_PAGE_DESCRIPTION_', '_ARTEFACT_', '_ADD_COMMENT_', '_COMMENT_', '_MENU_ITEM_1', '_MENU_ITEM_2', '_MENU_ITEM_3', '_MENU_ITEM_4', '_MENU_ITEM_5', '_MENU_ITEM_6');
 
$placeholders_values = array("$presentation_title", "$page_title", "$page_description", "$artefact", "$add_comment", "$display_comment", "$menu_item_1", "$menu_item_2", "$menu_item_3", "$menu_item_4", "$menu_item_5", "$menu_item_6");
 
$page = str_replace($placeholders, $placeholders_values, $template);
 
}
 
echo $page;
?>

Open in new window

Most Valuable Expert 2011
Author of the Year 2014

Commented:
I read over the code in the snippet above, and here are some suggestions that might be helpful.

You might want to put error_reporting(E_ALL) at the top of your script.  That way, if you rely on an undefined variable you will get a notice and  you can correct the logic so that you are using only known good values.

I could not follow the need for the logic in the function called "mysql_fetch_rows" but I am sure that it violates the rules of function naming conventions to name a local function starting with "mysql_" - this may not cause an error in your script today, but it would be best to use your own prefix and not one that is already assigned.
http://us.php.net/manual/en/userlandnaming.rules.php

The code between lines 25 and 34 seems to draw data from different rows of the same table.  Usually, that indicates that the table should be broken up into separate tables.  I see that $template comes from $records[0] but $page_id comes from $records[1] - this is not a common DB construction.

Also, you might improve the readability of the code by using better alignment and by adding comments.  See the example in the code snippet to replace lines 38 thru 41.

Best regards, ~Ray
// CHECK FOR A SUBMITTED FORM WITH A COMMENT AND UPDATE THE DB
if(isset($_POST['submit']))
{
 
// CHECK TO SEE IF THERE WAS A COMMENT POSTED
   if (!empty($_POST["comment"]))
   {
 
// PREPARE THE COMMENT FOR INSERTION INTO THE DATA BASE
      $comment = mysql_real_eswcape_string($_POST["comment"], $connection);
 
// CREATE THE QUERY STRING AND EXECUTE THE QUERY
      $query_comment  = "INSERT INTO comment ( comment_id,   comment,  comment_date,   user_id,   page_id )
                                      VALUES ( '',         '$comment', NOW(),        '$user',   '$page_id' )";
 
      $result_comment = mysql_query($query_comment, $connection) or die(mysql_error());
   } // END IF
} // END IF
 

Open in new window

Author

Commented:
Hi Ray

Thanks for your comments.  

I understand your point about naming functions with mysql.  However, I found this code at (named function mysql_fetch_rowsarr):

http://au.php.net/mysql_fetch_array

This code is exactly what I needed to access specific records from the join query.  I have attached a text file which has the database structure.  This all works fine.  However, the problem is still with being able to loop out the comments that belong to the page that my code is rendering and have these available in a variable in the str_replace.  I think that I may have to use one of the simpler php template engines to do this, such as HTML Template ITX :

https://wiki.brown.edu/confluence/display/library/HTML+Template+ITX#HTMLTemplateITX-abouttemplateitx




database.txt
Most Valuable Expert 2011
Author of the Year 2014

Commented:
Please describe the issue a little more clearly and post your solution.  Just deleting a question with "Solved it myself..." doesn't do much good for the community.  thank you, ~Ray
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Most Valuable Expert 2011
Author of the Year 2014

Commented:
Looks great, and the MVC model will make the future easier for everyone who encounters this.  Best, ~Ray
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.