SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Php page not displaying MySQL data although it works in PphpMyAdmin

    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

  2. #2
    Non-Member Max Height's Avatar
    Join Date
    Dec 2011
    Posts
    303
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)
    I doubt

    Code:
    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

  3. #3
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try:
    PHP Code:
    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.
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  5. #5
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Attached Images Attached Images

  6. #6
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ElPed View Post
    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.
    Code sql:
    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!
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ElPed View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •