Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : PHP and MySQL: Better way to print query results?

[Yeedog]

Hey all.

I have a table where a concertID, setID and venueID is stored. Now when i output i seem to be going about it a very clunky way as there could be more then one venueName. This is to reflect changes of a venue's name over the course of the years.

so an entry could be like:

1 1 3
1 1 5

there is the same concert and set it's just associated with 2 venue names. It could be more then 2 and there could be only 1 name.

I'm printing out all the concerts on a page but i don't want a concert repeated multiple times with just a different venueName. I want that alternate venueName attached to the first printing of that specific concert on the page.

I do have a working script but it seems very blah the way i tried to figure out if the next thing i go was the same show with an alternate name or not and then work it into the table structure.

here is a simple script i was just playing with.

PHP Code:

    $DBName     = "dmb_concerts";
    $TableName1     = "concert_taper_source";
    $TableName2     = "concert_set_venue";

    $TableName3     = "concert_info";
    $TableName4     = "taper_info";
    $TableName5     = "source_info";
    $TableName6     = "venue_info";
    $TableName7     = "set_info";


    $Link = mysql_connect ($Host, $User, $Password);    
    
    $Query = "SELECT  * FROM (($TableName3
            left join $TableName2 ON $TableName3.concertID = $TableName2.concertID)
            left join $TableName7 ON $TableName2.setID = $TableName7.setID)
            left join $TableName6 ON $TableName2.venueID = $TableName6.venueID";

    $Result = mysql_db_query ($DBName, $Query, $Link);

    $temp1 = 123;
    $temp2 = 456;    

        print("<table align='center' border='1'>");
    while ($row = mysql_fetch_array($Result)) 
    {
        if($temp1 != $row[concertID])
        {
            if($temp2 == 1)
            {
                print("</tr>");
                $temp2 = 123;
            }

            print("
                <tr>
                    <td>$row[date]</td>
                    <td>$row[artist]</td>
                    <td>$row[venueName]</td>
            ");
            
            $temp1 = $row[concertID];
            $temp2 = 1;
        }
        else
            print("<td>$row[venueName]</td>");

    }
        print("</table>"); 


as you can see it's very blah the way i tried to see if the next thing i got was the same concert with an alternate name. I used temp vars and stuff.

Is there a better way to accomplish this?? Bad database layout?


Thanks!

[Burhan]

I think you need to split up your query.

Something like this :

PHP Code:

$query   = "SELECT `venue_id`,`venueName` FROM `venue_info`";
$results = mysql_query($query);
if (!$results) { die($query."<br />".mysql_error()); }

//Now, lets fetch each venue and get the information for that venue

while($row = mysql_fetch_assoc($results))
{
   echo '<table border="0">';
   echo '<tr><td colspan="3">Details for'.$row['venueName'].'</td></td>';
   echo '<tr><td>Artist Name</td><td>Date</td><td>Set</td></tr>';
   
    // -- Get all events at a particular venue

   $results2 = mysql_query("SELECT concertId, setId FROM concert_info where venueId = ".$row['venue_id']);
   
    // -- Do a while, print out information for each concert and
    // -- set for each venue
}
?> 


[Yeedog]

Originally posted by fyrestrtr
I think you need to split up your query.

Something like this :

PHP Code:

$query   = "SELECT `venue_id`,`venueName` FROM `venue_info`";
$results = mysql_query($query);
if (!$results) { die($query."<br />".mysql_error()); }

//Now, lets fetch each venue and get the information for that venue

while($row = mysql_fetch_assoc($results))
{
   echo '<table border="0">';
   echo '<tr><td colspan="3">Details for'.$row['venueName'].'</td></td>';
   echo '<tr><td>Artist Name</td><td>Date</td><td>Set</td></tr>';
   
    // -- Get all events at a particular venue

   $results2 = mysql_query("SELECT concertId, setId FROM concert_info where venueId = ".$row['venue_id']);
   
    // -- Do a while, print out information for each concert and
    // -- set for each venue
}
?> 


hmm i might be misunderstanding your code, but I don't believe it's doing what i want.

Your code goes through each venue and then prints out information for each venue but it doesn't make the association that a concert at venue name X with concertID 1 and setID 1 and show at venue name Y with concertID 1 and setID 1 is really the same show.

What I want is to have the print out to be something like

ConcertID info -- setID info @ venue name y (x)

thus it says the venuename is y but in parenthesis it has an alternate name it can go by within the database. In my orginal code it's ordering by date which is what I want it to do when it's all said and done. The date is stored in the Concert_info table.


so an examaple of an actual print out of data from my orginal code is:

PHP Code:

2004-07-20      Dave Matthews Band      Blockbuster Sony Music Entertainment Center      Tweeter Center at the Waterfront
2004-07-21     Dave Matthews Band     Blockbuster Sony Music Entertainment Center     Tweeter Center at the Waterfront
2004-07-23     Dave Matthews Band     Verizon Wireless Virginia Beach Amphitheater
2004-07-24     Dave Matthews Band     Verizon Wireless Amphitheatre
2004-07-26     Dave Matthews Band     Alltel Pavillion at Walnut Creek
2004-07-27     Dave Matthews Band     Lakewood Amphitheatre     Coca Cola Amphitheatre    HiFi Buys Amphitheatre
2004-07-29     Dave Matthews Band     Tampa Bay Amphitheatre
2004-07-31     Dave Matthews Band     Coral Sky Amphitheatre     Mars Music Amphitheatre    Sound Advice Amphitheatre
2004-08-01     Dave Matthews Band     Coral Sky Amphitheatre     Mars Music Amphitheatre    Sound Advice Amphitheatre 


Thus there should be only one print out of information for each concert even tho a concert is listed multiple times in my venue_info table with a differnt venue name attached to it.

So while i did have an answer i feel there could be alot better way to solve this then the way i'm doing it. Thus i'm posting looking for opinions.

Thanks all

[Dan L]

Don't even query the venue info, then!

[Burhan]

In your sample printout, what is the set name? Because all I see is venues?

[Yeedog]

Originally posted by DanX
Don't even query the venue info, then!

How do i get the venue name if i don't query to get venue info?

[Yeedog]

Originally posted by fyrestrtr
In your sample printout, what is the set name? Because all I see is venues?

thanks for all the help.

In the example the setName is the artist. My example is only showing one artist (Dave Matthews Band) but there would be many different artist

The setID is in a table that stores the artist for a given set.
The date would come from the concert_info table.
The rest is venue names.

Hope that helps.


EDIT:

here is what the dbase looks like with the tables in question...

http://yee.is-useless.org/t.jpg

[hiryuu]

Unless there's a reason to show previous venue names, you should probably have a way to indicate the active venue name, and only use that one.

[Yeedog]

Originally posted by hiryuu
Unless there's a reason to show previous venue names, you should probably have a way to indicate the active venue name, and only use that one.

yeah i guess i'll just have to do something like that...