Php page not displaying MySQL data although it works in PphpMyAdmin

[ElPed]

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");


however, the following code displays the headers but does not display the data when inserted into a php page:

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 in advance

[Max Height]

I doubt

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

works as is in phpMyAdmin

[ElPed]

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>";
?>

and it works a treat.

[Raju Gautam]

Try:

error_reporting(E_ALL | E_STRICT);
ini_set('display_errors', 1);

$sql = "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";
$result = mysql_query($sql) or die(mysql_error()); 

You must see some errors if it has any, otherwise the code should display the data.

[ElPed]

Thanks Raju but it seems the fault was in the naming of the original tables where both the competition and course tables had a field called "name".

I renamed them to be unique and the output is as I required

Image2.jpg

Thanks for your help.

[Raju Gautam]

Thanks Raju but it seems the fault was in the naming of the original tables where both the competition and course tables had a field called "name".

I renamed them to be unique and the output is as I required

Image2.jpg

Thanks for your help.

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!

[r937]

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

in my opinion this is an unfortunate decision

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

in my opinion

[ElPed]

Thank you very much for your reply.

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.

Thanks again.