[RESOLVED] Display list of bands that haven't played for over two months :S

middleman666

Hey People,

I need to display a list of bands that haven't played for over two months and I'm having difficulty gettings my head around it because of the multiple tables.

These are my tables:

create table band
(
id int not null primary key auto_increment,
Band text
);

create table show
(
id int not null primary key auto_increment,
Date DATETIME,
Details text
);

create table acts
(
id int not null primary key auto_increment,
ShowID text,
BandID text,
TimeOnstage TIME
);

At the moment, you can create a show and band and then use the acts to tie them together so you know which bands are playing a certain show.

I started coding the query, but I not really sure where I was going with it :S

//Two months ago
$month = date('m')-2;

//Today
$today = date('Y-'.$month.'-d H:i:s' );

  //Prepare band data
  $Bresult = mysql_query("SELECT * FROM band ORDER BY Band ASC");
  while ($Brow= mysql_fetch_array($Bresult)) {
    $BandID = $Brow['id'];
    $Band = $Brow['Band'];

//Get Rid of Slashes (\)
$Band = stripslashes($Band);

//Get Results
$result = mysql_query("SELECT * FROM show WHERE Date < \"$today\" ORDER BY Date ASC");
while ($row= mysql_fetch_array($result)) {

  $ShowID = $row['id'];

  //Prepare act data
  $Aresult = mysql_query("SELECT * FROM acts WHERE BandID = $BandID AND ShowID = $ShowID");
  while ($Arow= mysql_fetch_array($Aresult)) {

    echo $Band.'<br />';

  }

}

  }

Can anyone lend a hand?

Thanks.

middleman666

Ok, had some time to think about this.

I ended up using arrays by creating two arrays of the band ID's of shows that are more than 2 months old and band ID's of shows that are less than 2 months old.

I then compared to two arrays and displayed the band ID's that didn't appear in both of the arrays.

I then convert the band ID to the band name and that's pretty much it.

I haven't ordered the array or removed multiple entries, but it's a start.

<?
//Two months ago
$month = date('m')-2;

//Today
$twomonthsago = date('Y-'.$month.'-d H:i:s' );

 //Get Results
  $result = mysql_query("SELECT * FROM show WHERE Date < \"$twomonthsago\"");
  while ($row= mysql_fetch_array($result)) {
    $ShowID = $row['id'];

//Prepare act data
$Aresult = mysql_query("SELECT * FROM acts WHERE ShowID = $ShowID");
while ($Arow= mysql_fetch_array($Aresult)) {
  $BandID = $Arow['BandID'];

  $BandIDArray1[] = $BandID;

}

  }

 //Get Results
  $result = mysql_query("SELECT * FROM show WHERE Date > \"$twomonthsago\"");
  while ($row= mysql_fetch_array($result)) {
    $ShowID = $row['id'];

//Prepare act data
$Aresult = mysql_query("SELECT * FROM acts WHERE ShowID = $ShowID");
while ($Arow= mysql_fetch_array($Aresult)) {
  $BandID = $Arow['BandID'];

  $BandIDArray2[] = $BandID;

}

  }

  //List Results
  $i=0; 
  while($i<count($BandIDArray1)) { 

if (!in_array($BandIDArray1[$i], $BandIDArray2)) {
  $BandID = $BandIDArray1[$i]; 

  $Bresult = mysql_query("SELECT * FROM band WHERE id = $BandID");
  $Brow= mysql_fetch_array($Bresult);
    $BandID = $Brow['id'];
    $Band = $Brow['Band'];
    $Link = $Brow['Link'];

    //Get Rid of Slashes (\)
    $Band = stripslashes($Band);

    echo '<a href="'.$Link.'" target="_blank">'.$Band.'</a><br />';

}

$i++; 
  } 


?>