How can I create dynamic menus and pages driven from database?

Question

I am a newbie here and for php too. I am not new to stackoverflow though :)

I am creating a simple reporting system and I want the menues and pages to be generated from the database.

I saw this video on YouTube and managed to create a menu with the following code.

I have a database table called Reports and columns called rep_id, rep_date, rep_ledit_date, rep_by, department, position, report, and rep_to.

So based on the above method, I created a menu using this code.

<?php 

require ("includes/db.php");

mysqli_select_db($con, '$db_name');

$sql = "SELECT * FROM reports";
$result = mysqli_query($con, $sql) or die (mysqli_error($con));
$represult=mysqli_fetch_assoc($result);
$rep_by=$represult['rep_by'];
$report=$represult['report']; 

?>  
<li> Menu
<ul>
<?php do 
       {
?>
<li><a href="reports.php?rep_id=<?php echo $represult['rep_id']; ?>"> <?php echo $represult['rep_by'] . " (" .   $represult['rep_date'] . ")"; ?></a></li>

<?php 
     } while ($represult=mysqli_fetch_assoc($result));
    //$represult['rep_by'];
    //$represult['report'];
    //$represult['report'] ;
?>
</ul>
</li>

So I created a page called reports.php to see the details of the content in the database. What I wanted was to see the following rep_by (rep_date) as a heading and report as a content.

I might want to use other columns in the content too. So what kind of code the menu and reports.php should have to achieve what I want. What I did was the following and it only outputs the first row when all the menu links are clicked.

 require ("includes/db.php");

 mysqli_select_db($con, '$db_name');

 $sql= "SELECT * FROM reports";
 $result = mysqli_query($con, $sql) or die (mysqli_error($con));
 $represult=mysqli_fetch_assoc($result); 

 <h1> <?php echo $represult['rep_by'] . " (" . $represult['rep_date'] . ")"; ?></a></h1>
 <?php echo $represult['report']; ?>

It looks like you provided the PHP code for menu.php. Are you asking for an example of the PHP code for reports.php? — bloodyKnuckles, May 25 '14 at 22:42
I provided an answer that cleared up a major issue I saw in your code—`$represult=mysqli_fetch_assoc($result)` being used twice—but unclear what the question is? Was this code not working? Is my answer now a solution? Flagged as “Unclear” unless you can edit/amend the question to clarify. — Giacomo1968, May 25 '14 at 22:45
@bloodyKnuckles, I modified it now. The error was made when posting on this forum. The problem is still there on reports.php — wondim, May 26 '14 at 04:13
The explanation is much clearer. You provided the PHP code for reports.php (plural). However your "
@bloodyKnuckles, good observation. That is again my bad in writing it on this forum. I will fix it. My problem is, the pages doesn't show their corresponding report, rather just the first row from the database. — wondim, May 26 '14 at 12:20
Yes, the "WHERE rep_id = ?" part of the query was missing. See the provided report.php example. — bloodyKnuckles, May 26 '14 at 12:54

bloodyKnuckles · Accepted Answer · 2014-05-26T13:42:13.993

1

report.php:

<?php

require ('includes/db.php');

mysqli_select_db($con, $db_name);

$sql= 'SELECT * FROM Reports WHERE rep_id = ?';
$stmt = $con->prepare($sql);
$id = $_GET['rep_id'];
$stmt->bind_param('i', $id);
$stmt->execute();

$stmt->bind_result($rep_id, $rep_date, $rep_ledit_date, $rep_by, $department, $position, $report, $rep_to);
$stmt->fetch();

?>

<h1><?php echo "$rep_by ($rep_date)"; ?></h1>
<?php echo $report; ?>

reports.php (tweaked a bit)

<?php 

require ('includes/db.php');

mysqli_select_db($con, $db_name);

$sql = 'SELECT * FROM Reports';
$stmt = $con->prepare($sql);
$stmt->execute();

$stmt->bind_result($rep_id, $rep_date, $rep_ledit_date, $rep_by, $department, $position, $report, $rep_to);

?>  
Menu
<ul>
<?php while ($stmt->fetch()) { ?>

<li><a href="report.php?rep_id=<?php echo $rep_id; ?>"> <?php echo "$rep_by ($rep_date)"; ?></a></li>

<?php } ?>
</ul>

edited May 26 '14 at 13:42

answered May 26 '14 at 12:50

bloodyKnuckles

11,551
3
29
37

@ bloodyKnuckles, Thank you so much. Your solution works perfectly well. You made my day! Now I have to learn about bind_param, bind_result, and their friends as my project will have a lot of them. Where could I find a good info on them? – wondim May 26 '14 at 13:41
@ bloodyKnuckles, how are you doing. I have new issue related to the solution you gave me last time. Would you help me on this please. Than you! http://stackoverflow.com/a/23961453/3674573 – wondim May 31 '14 at 04:02
@ bloodyKnuckles, I am facing a new problem now. I get the following error Fatal error: Call to a member function execute() on a non-object in /home/wt/public_html/view-reports.php on line 237 . It is the same message for the tweaked report.php - which is now view-reports.php. I also see another problem. The variables generated doesn't echo the right corresponding data. For instance, $rep_type echo position. Could you please help me on this. Thank you! Test site url is here. http://ethioserver.com/~wt/view-reports.php?rep_id=144 – wondim Jun 06 '14 at 06:53
Post a new question with the view-reports.php code, particularly around line 237 and in relation to the $stmt variable that you're using to call execute. Somehow the $stmt variable is not getting made into a database object, hence the error, calling execute on a non-object. – bloodyKnuckles Jun 06 '14 at 11:48
Ok thank you! I have posted a new question here. http://stackoverflow.com/q/24086070/3674573 – wondim Jun 06 '14 at 15:58

Giacomo1968 · Answer 2 · 2014-05-26T04:34:48.497

0

Your code is a bit confusing due to the mix of PHP tags and HTML so I have reworked it as 100% PHP like this.

require ("includes/db.php");

mysqli_select_db($con, '$db_name');

$sql = "SELECT * FROM reports";
$result = mysqli_query($con, $sql) or die (mysqli_error($con));

echo '<li>'
   . '<ul>'
   ;

while ($represult = mysqli_fetch_assoc($result)) {
  echo '<li>'
     . '<a href="reports.php?rep_id=' . $represult['rep_id'] . '">'
     . $represult['rep_by']
     . ' (' .   $represult['rep_date'] . ')'
     . '</a>'
     . '</li>'
     ;
} 

echo '</ul>'
   . '</li>'
   ;

The main problem I see in your original code is you are doing this call twice:

$represult=mysqli_fetch_assoc($result);

One right near $result = mysqli_query(…) and the other as the tail end of the do/while loop. Also, unsure of the value of that do/while loop so I refactored it as a simple while loop. And finally with the restructuring to be 100% PHP I did some formatted concatenation to make it easier to read & see the structure of your overall logic.

EDIT: The original poster added additional code saying the problem is it was only returning the first result. The issue—again—is the lack of a while loop. Here is my refactored version of that code:

require ("includes/db.php");

mysqli_select_db($con, '$db_name');

$sql= "SELECT * FROM reports";
$result = mysqli_query($con, $sql) or die (mysqli_error($con));


while ($represult = mysqli_fetch_assoc($result)) {

  echo '<h1>'
     . $represult['rep_by']
     . ' ('
     . $represult['rep_date']
     . ')'
     . '</h1>'
     . $represult['report']
     ;

}

edited May 26 '14 at 04:34

answered May 25 '14 at 22:40

Giacomo1968

25,759
11
71
103

thanks for improving the code. It is much clearer now. But I still have the problem because on report.php, only the first result comes but I want to see different report for every different menu links. I have made an edit on the first page to show what report.php look like with your improved code. – wondim May 26 '14 at 03:53
@andex The code as presented should work to show all reports. Please check the raw HTML output by this code to see if there is a rendering issue. If you found my work to be helpful please up vote it. Thanks and best of luck! – Giacomo1968 May 26 '14 at 04:11
thanks. I made further clarifications on the first post, would you help me in rendering the page called reports.php. – wondim May 26 '14 at 04:17
@andex The problem is similar to the first issue. You are not understanding how `while` loops work with `mysqli_fetch_assoc`. My cleaned up version of you code is in my latest edit. But this is the last edit I will make. If you found my work to be helpful please up vote it. And if it is the answer to the issue, please mark it as such. Thanks and best of luck! – Giacomo1968 May 26 '14 at 04:36
@andex PS: Also you should not edit your question to replace it with another solution. You are making it harder for anyone else to help you. I will revert your change to the code to what it was originally. – Giacomo1968 May 26 '14 at 04:37
thanks again. Sorry for the mistake. It was just to make it clear. The edited answer now shows all reports in the database, I wanted to show, only the one written by the rep_by. – wondim May 26 '14 at 04:54
@andex I have helped you the best I can. No more activity from me. And again, **If you found my work to be helpful please up vote it.** Take care. Farewell. Cheers! – Giacomo1968 May 26 '14 at 04:56
It tried to put up but I don't have the permission to do that as I am a newbie. Your answer is helpful but it didn't solve my problem. What I wanted was to have only report on one page not all. – wondim May 26 '14 at 05:14
@andex An up vote is not choosing an answer as the answer. But it is clear you are clueless on many levels. Best of luck to anyone else coming across this question. – Giacomo1968 May 26 '14 at 05:17

How can I create dynamic menus and pages driven from database?

2 Answers2

Linked