How can I create dynamic menus and pages driven fr

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']; ?>

Answer 1

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']
     ;

}

Answer 2

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>