Can anyone please throw some light on my problem? I’ve been working through the Sitepoint “Build Your Own Database Driven Web Site Using PHP & MySQL” book and have hit a snag:-
The following works as expected in the database using PHPMyAdmin:
$result = mysql_query (“select competition.name, competition.competitiondate, competition.starttime, course.name from competition inner join competitioncourse on competition.id=competitionid inner join course on courseid=course.id”);
[B]
however, the following code displays the headers but does not display the data when inserted into a php page:[/B]
echo “<table border=‘0’ align=‘center’>”;
echo “<tr><font size=‘2’><b> <th width = ‘300’>Name</th> <th width=‘100’>Date</th> <th width=‘80’>Time</th> <th width=‘100’>Course</th></b></tr>”;
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo “<tr><font size=‘2’><td align=‘center’>”;
echo $row[‘competition.name’];
echo “</td><td>”;
echo $row[‘competition.competitiondate’];
echo “</td><td align=‘center’>”;
echo $row[‘competition.starttime’];
echo “</td><td align=‘center’>”;
echo $row[‘course.name’];
echo “</td></tr>”;
}
echo “</table>”;
?>
Thanks but I can guarantee that it certainly does!
Anyway I have sorted it. I have revisited the nameing convention in the tables to make them more unambiguous i.e. I have renamed competition.name as competition.competitionname and course.name as course.coursename
$result = mysql_query (“select competition.competitionname, competition.competitiondate, competition.starttime, course.coursename from competition inner join competitioncourse on competition.id=competitionid inner join course on courseid=course.id”);
The display is now:
echo “<table border=‘0’ align=‘center’>”;
echo “<tr><font size=‘2’><b> <th width = ‘300’>Name</th> <th width=‘100’>Date</th> <th width=‘80’>Time</th> <th width=‘100’>Course</th></b></tr>”;
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo “<tr><font size=‘2’><td align=‘center’>”;
echo $row[‘competitionname’];
echo “</td><td>”;
echo $row[‘competitiondate’];
echo “</td><td align=‘center’>”;
echo $row[‘starttime’];
echo “</td><td align=‘center’>”;
echo $row[‘coursename’];
echo “</td></tr>”;
}
echo “</table>”;
?>
Okay no problem. Glad to know you found the cause and fixed it. In such case you can even create alias of the fields.
SELECT
competition.name,
competition.competitiondate,
competition.starttime,
course.name AS course_name
FROM
competition
INNER JOIN competitioncourse ON competition.id=competitionid
INNER JOIN course ON courseid=course.id
See ‘course.name AS course_name’ for aliasing the column!
select competition.competitionname, competition.competitiondate, competition.starttime, course.coursename …
repeating the table name at the start of the column name is counter-productive, adding noise where clarity is more important
I have your book but I was working through Kevins book first. This is a College Project and although I have done a little in Access, I am fairly new to Php. I am trying to work my way through it, trying to get a little help where I can.
I have just joined the Forum and was extremely impressed by the speed of the replies and the willingness of people to lend support.
I am certain there are better ways of doing this and I will try the suggestions from the other experienced posters. I’ll try and work my way through this but hopefully be able to come back when I get stuck.