Using PHP variables with mysql data

Charlie_Chalk

Firstly, I'm very VERY new to all this...

I have three tables in a mySQL database:

Table 1 = Artists (4 columns)
Artist_ID ¦ name ¦ Album ¦ Single

Table 2 = Awards (2 columns)
award_ID ¦ award_name

Table 3 = Awards Year (5 columns)
ID ¦ award_year ¦ artist_ID ¦ Awards_ID ¦ winner

What I've done so far, using this quiery

SELECT name, award_name, aw_year
FROM artists, awards, award_year
WHERE award_year.artist_id = artists.artist_id
AND award_year.award_id = awards.award_id
AND award_year.winner = "y"

is output the winners from the list (Artist table has over 40 entries, only 17 are winners).

What I'd like to do now is show the nomines for each award (usually 5 artists) with the winner highlighted. I guess by ordering them alphabetically on the winner column (always y or n) I could get the 'y' entry at the top. I'd like each one to be in it's own table.

The only way I know how to get the different awards on the page is by making a new quiery for each one - something I'm sure is not good.

I'm guessing I could set the award names as some sort of variable, that produces a new table for each award, but I've no idea at all how to do it.

I have looked around on the 'net but I don't really know what to look for.

Any help would be much appreciated!

tomhath

This should give you all the rows. Try it and notice what order they come out. Once it's working you can build the page in php by starting a new html table every time you get a row with a different award_name or aw_year. You can also use php to display just the first five rows and ignore the others (just keep fetching rows but don't put them in the table until you get a new award or year).

SELECT name, award_name, aw_year, winner,
FROM artists, awards, award_year
WHERE award_year.artist_id = artists.artist_id
AND award_year.award_id = awards.award_id
ORDER BY award_name, aw_year DESC, winner DESC, name

Charlie_Chalk

tomhath wrote:
you can build the page in php by starting a new html table every time you get a row with a different award_name or aw_year. You can also use php to display just the first five rows and ignore the others (just keep fetching rows but don't put them in the table until you get a new award or year).

And that's the bit that is making me stomp round the house and growl at the kids :mad: - I just can't do it. I've tried with my very limited knowledge of PHP, but to success.

The only way I can get new tables is by setting up a new db quiery, and I know that's not a good thing to do.

If you can point me in the right direction I would be very grateful!

tomhath

See if you can get it all in one table with a simple example like this. Then come back for help splitting it out into something more fancy:

http://www.ucl.ac.uk/is/mysql/php/

Roger_Ramjet

Very basic loop control that has been asked and answered untold times in these forums. Just do a search and you'll find the answer.

Charlie_Chalk

Thanks Roger. I am a member of many forums across the internet, and often let new users know of topics previously covered that a can be found using the search facility.

I have searched for this paticular subject on many forums, including this one. The problem I have, as I am very new to PHP and mySQL, is knowing the words to use in the search.

I'd never have guessed it was called 'loop control' but now I know I will indeed search for it.

Tom, thanks for your help thus far. I will output the full db onto the page and post back my progress if you are still interested.

Kind regards

Charlie_Chalk

Well, I've progressed to getting the whole lot on screen, you can see the site here , and the code I've used is:

<table width="90%"  border="0" cellpadding="1">
<?php
       $i=0;
            while ($i < $num) {

           $artist_name=mysql_result($result,$i,"name");
           $award_name=mysql_result($result,$i,"award_name");
           $album=mysql_result($result,$i,"album");
          $url=mysql_result($result,$i,"url");
?>
<tr>
    <td><?php echo $artist_name ?></td>
    <td><?php echo $award_name ?></td>
    <td><php echo $album></td>
  </tr>
<?
$i++;
}
?>
</table>

I've tried playing around with variables, and read quite a bit on Loop Control, but it just doesn't seem to stick with me.

I realise this is ABC stuff to most of you, but I would really appreciate it if someone could help me out.

Thanks

Roger_Ramjet

Well the basic principle is:

field 1 is the category, it is output once as a group header.
all records in that category are output until the value in field 1 changes
when it does the new category is output as a group header, etc

Requires that query results be correctly ordered

$SQL = "SELECT field1, field2, field3 FROM table ORDER BY field1, field3";
$result = mysql_query($sql);

// set var to track field1
$curr_f1 = '';

while ($row=mysql_fetch_array($result)) {
   echo '<tr>';
   // check field1
   $f1 = $row['field1'];
   if ($f1 != $cur_f1) {
   // new field1 so output group heading
       echo '<td>' . $f1 . '</td></tr><tr>';
       // record it
       $curr_f1 = $f1;
   }
   // always output rest of record
   echo '<td>' . $row['field2'] . '</td><td>' . $row['field3'] . '</td></tr>';
}

Charlie_Chalk

Roger, thanks very much for the code.

I have spent a frustrating afternoon playing around with it and trying to get it to do what I'm after, but don't seem to be getting much success.

The layout I am after (for each award) is:

<table>
  <tr>
    <td>Award Name </td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>Artist one name</td>
    <td>Artist one album name </td>
  </tr>
  <tr>
    <td>Artist two name</td>
    <td>Artist two album name </td>
  </tr>
  <tr>
    <td>artist three name</td>
    <td>Artist three album name </td>
  </tr>
  <tr>
    <td>artist four name</td>
    <td>Artist four album name </td>
  </tr>
  <tr>
    <td>artist five name</td>
    <td>Artist five album name </td>
  </tr>
</table>

The next award would produce a new table of artist names and corresponding album names.

I'm sure this must be as frustrating for you having to explain what is no doubt a simple PHP process as it is for me not being able to grasp how to get this working.

I would greatley appreciate your help one more time.

Kind regards

tomhath

What Roger posted is pretty much what you want. In English it's something like below. You'll need variables for current award and current year (Roger's $curr_f1, etc). You're actually pretty close. I don't have a working server/database available so I can't give you real code right now.

set the current award name to blank

while there are rows left to read
  if the award from this row is different than the current award 
      (this will always true for the first row)
    echo a new headline element for the award
    set the current award to this one
    set the current year to none
  end if

  if the current year is different than this one
      (this will always be true when you changed awards above)
    echo a new <table> element
    set the current year to this one
  end if
  echo <tr></tr> etc. for this row
end while

Charlie_Chalk

Ha! Cracked it, thanks so much Roger and Tom.

I was tearing my hair out, as your plain English description of what I wanted didn't seem to do what Rogers code did.

Then after a break and a coffee I went over the code one last time and noticed that $cur_f1 should be $curr_f1.

Once the extra r was added - hey presto!

Thanks once again for your help and patience