PHP results layout question

Graham_Eire

Hi all,

I'm new to the site and to PHP so if I'm asking somthing simple sorry and by the way I'm following a book at the moment so if my code is sloppy sorry.

I want to emulate a Excel workbook that is used to list scheduled training dates and comments for trainers in the following layout.

I've got two tables linked by trainer.id I want to display a table with the trainer's ID across the first row of the table and then get the linked schedule data for each trainer to display in a column below the row for each trainer.

I've two queries the first extracts the id's for each trainer from the trainer table and the second extracts the date and comments from the schedule table

Try as I might I can't get the data to link to the correct columns it just creates rows under the first column.

Any help greatly appricated.

Here's my code

<?php

@ $db = new mysqli('localhost', '*******', '*****', 'test');

/* check connection */
if (mysqli_connect_errno()) {

echo 'Sorry cant connect to the database';
exit();
}
else

 echo 'connected';


$query="SELECT id FROM trainer";

$result=$db->query($query);


echo '<p>Number of records:';
echo $result->num_rows;
echo '</p>';


$query2="SELECT s.Sdate, s.Comments from Scheule s, trainer t where s.trainer = t.id ORDER BY Sdate "; 

$result2=$db->query($query2);

echo '<p>Number of records:';
echo $result2->num_rows;
echo '</p>';


if ($result->num_rows)

 {

echo '<table border="2">';
echo '<tr>';
while ($row = $result->fetch_assoc())
{
echo '<th>';
echo $row['id'];
echo '</th>';
}
echo '</tr>';

if ($result2->num_rows)
{
	while ($row2 = $result2->fetch_assoc())

{
echo '<tr>';
echo '<td>';
echo $row2["Sdate"];
echo '<br/>';
echo $row2["Comments"];
echo '</td>';
echo '</tr>';
}

echo '</table>';
}
}

$result->free();

$result2->free();

$db->close();

?>

naskar

Alright , I understand what you're tring to do but why are you using two tables? Is it because of the way you want the layout to be presented?

I may be able to help you, But i am confused as to how you want the layout to look, could you please explain further

Graham_Eire

Hi Naskar,

Sorry when I mentioned two tables I meant two database tables linked by trainer.id they are trainer and schedule.

For the display I want to use just one html table.

The first row will list all of the trainer's IDs in individual cells then I want the related data to display in rows below the cell containing the trainer's id in one column for each trainer like in the Excel spreadsheet.

<Table>

Graham_Eire

Sorry the last part of the previous message should have has this hit the submit button as wasn't watching what I was doing.

I'm not sure if this is correct but below is what I think the table HTML would look like when generated by PHP

Thanks for the reply mate

   <table border="1">
              <tr> <td> id </td> <td> id </td> </tr>
               <tr> <td> s.Sdate <br/> s.Comment </td> <td> s.Sdate <br/> s.Comment </td> </tr>
               </table>

NogDog

I suspect you can do this with one query instead of two, but I would need a better understanding of the DB tables involved and their relationship (e.g.: could there be more than one row in the Schedule table for a given trainer ID in the trainer table?). It might be useful to show us the two tables' definitions. (You could do a SHOW CREATE TABLE table_name on each table and copy-and-paste the results here.)

PS: I changed your

 tags to [code=php] tags to make it more readable.

Graham_Eire

Here is my Trainers table

Field  | Type      |  Null | Key | Default  | Extra       |

-------+----------+------+-----+---------+----------------+
ID | int(10) | NO | PRI | NULL | auto_increment |
Fname | char(10) | NO | | NULL | |
Lname | char(10) | NO | | NULL | |

And here is the Schedule table

Thaks again for the help

Graham

naskar

Hmm im trying an approach where it sets up a loop , and outside the loop are the <tr><td> elements so that for X items it sets all boxes side by side , will find out a definite working method and get back to you

Graham_Eire

Thanks mate owe you one.

naskar

$trainers;
$dates;
$comments;

$result = mysql_query("SELECT ... FROM ...");
while($row = mysql_fetch_assoc($result)) {
    $trainers .= '<td>'.$row['trainer'].'</td>';
    $dates .= '<td>'.$row['dates'].'</td>';
    $comments .= '<td>'.$row['comments'].'</td>';
}

echo '<table>';

echo '<tr>';
    echo $trainers;
echo '</tr>';

echo '<tr>';
    echo $dates;
echo '</tr>';

echo '<tr>';
    echo $comments;
echo '</tr>';

echo '</table>';

Hi, please let me know if this works.. My pc is having problems running PHP these past 2 days

Graham_Eire

Hi Naskar,

I tried your code as written above and got the following result.


connected
Number of records:4


Notice: Undefined variable: trainers in C:\Program Files\Apache Group\Apache2\htdocs\testtrainer8.php on line 32

Notice: Undefined variable: dates in C:\Program Files\Apache Group\Apache2\htdocs\testtrainer8.php on line 33

Notice: Undefined variable: comments in C:\Program Files\Apache Group\Apache2\htdocs\testtrainer8.php on line 34
1                                                       1                                        1 2 
2007-08-31                                      2007-09-01                        2007-09-01 
This is an old entry for Graham        This is the first for Graham This is the second for Graham

So I took out the periods after $trainer . = '<td>' so it read as $trainer = '<td>'
and got the following result which is the correct layout but only for one trainer I need a column for each trainer.


connected
Number of records:4

2 
2007-09-01 
This is the first for Maria

Graham_Eire

Hi Nascar

I think I need the two queries so I went back to the php code and ammeded it to the following

<?php

@ $db = new mysqli('localhost', '**', '**', 'test');

/* check connection */
if (mysqli_connect_errno()) {

echo 'Sorry cant connect to the database';
exit();
}
else

 echo 'connected';

$query="SELECT id FROM trainer";

$result=$db->query($query);


echo '<p>Number of records:';
echo $result->num_rows;
echo '</p>';


$query2="SELECT s.Sdate, s.Comments from Scheule s, trainer t where s.trainer = t.id ORDER BY Sdate, id "; 

$result2=$db->query($query2);

echo '<p>Number of records:';
echo $result2->num_rows;
echo '</p>';


if ($result->num_rows)

 {

 echo '<table border="1">';
    echo '<tr>';

while ($row = $result->fetch_assoc())
	{ 
    $trainid = $row['id'];

  echo '<th>';
        echo $trainid; 
	echo '</th>';
    }


while ($row2 = $result2->fetch_assoc())

{
    $date = $row2['Sdate']; 
    $comments = $row2['Comments']; 


 echo '<tr>';	    
   {	

echo '<td>';
     echo $date;
echo '<br />';
       echo $comments; 
echo '</td>';

}

echo '</tr>';   
echo '</table>';


}

}





$result2->free();

$db->close();

?>

which is giving me the following layout

connected
Number of records:3

Number of records:4

1 2 3 
2007-08-31
This is an old entry for Graham 
2007-09-01
This is the first for Graham2007-09-01
This is the second for Graham2007-09-01
This is the first for Maria

This is close to the actual layout that I need but I can't get the related data to go under the correct headings.

Any ideas mate??

Thanks again,

Graham

ahundiak

// Start by attaching all the schedule for a trainer to the trainer
$query2="SELECT t.id, s.Sdate, s.Comments from Scheule s, trainer t where s.trainer = t.id ORDER BY Sdate, id ";

$result2=$db->query($query2); 
$records = array();
while ($row = $result->fetch_assoc()) {
    $records[$row['id']][] = $row;
}
print_r($records);

// Now one pass for printing the trainer info
foreach($records as $id => $schedules)
{
    echo $id . ' ';
}
// And then another pass for the schedule
foreach($records as $id => $schedules)
{
    foreach($schedules as $schedule) {
        echo $schedule['Sdate'] . ' ' . $schedule['Comments'];
}

I know this is not quite what you asked for. It's not clear to me why the date should get it's own row in your table. What happens if different trainers have different number of comments for a given date?

But I hope you can see how using an array with the trainer id as an index allows you to group the schedule records for a given trainer together.

Graham_Eire

Thanks ahundiak I'll give that a try.

The reason I'm trying to give each date a row with it's comment in the same row is that on any one day (date) a trainer can be scheduled to train two different clients at different times, hence in my mind they would be two different records.

Hope this makes sense.

Graham