help with query

[digitalgod]

hey guys,

I'm trying to figure out how to build a query.

I have a table named list and in it there's "club" and "date" and "name" what I want is to display a list ordered by the name of the club and then ordered by the date. But there could more than 1 entry that has the same club name and date.

so let's say I have

club2 | 2006-08-23  | John
club1 | 2006-08-24  | Jen
club1 | 2006-08-24  | Sarah
club1 | 2006-08-24  | Chris
club3 | 2006-08-24  | Louis

so it would display

club2 2006-08-23
club1 2006-08-24
club3 2006-08-24

and if I click on club 1 I can see all the names. The 2nd part I can easily do it with by doing $_GET but I'm not too sure how to do the first one.

any ideas?

[jdforsythe]

try using DISTINCT like so:

[code]SELECT DISTINCT club, date FROM list GROUP BY club ORDER BY date ASC[/code]

hope that helps!

jeremy

EDIT:

i just reread your question.  make that:
[code]SELECT DISTINCT club, date FROM list GROUP BY club ORDER BY club ASC, date ASC[/code]

[digitalgod]

thanks, I'll try that now, but will that still let me see the names? or will I only be able to echo the club and date?

[digitalgod]

nope, I can't echo the name, anyone it can select everything but just have a DISTINCT club?

[jdforsythe]

i was under the impression that you only wanted the club names and unique dates, not the people's names.

let's say you have the data in "list" like this:

club1 | 08-24-2006 | john
club1 | 08-24-2006 | jeb
club1 | 08-25-2006 | jane
club1 | 08-25-2006 | jimbo
club2 | 08-24-2006 | amy
club2 | 08-24-2006 | anne
club2 | 08-25-2006 | andrea
club2 | 08-25-2006 | andy

if you:
[code]SELECT DISTINCT club,date FROM list ORDER BY club ASC, date ASC[/code]
(take out the group by) you will get this:

club1 | 08-24-2006
club1 | 08-25-2006
club2 | 08-24-2006
club2 | 08-25-2006

DISTINCT selects only unique club-date combinations.  if you add the name field in, then it will only exclude entries where the club, date, and name are all the same.  so in the example table above it will return ALL entries since none of them are exact duplicates.

i'm trying to figure out exactly what you want this to do.

okay i think i might understand.  something like this(?):
[code]
<?php
$q = "SELECT DISTINCT club,date FROM list ORDER BY club ASC, date ASC";
$p = mysql_query($q) or die('Error: ' . mysql_error());
while ($result = mysql_fetch_row($p)) {
  $club = $result[0];
  $date = $result[1];

  echo "<a href=\"second_query.php?club=$club&amp;date=$date\">$club on $date</a><br />";
}
?>
[/code]

and the second_query.php like this:
[code]
<?php
$club = $_GET['club'];
$date = $_GET['date'];

$q = "SELECT name FROM list WHERE (club = '$club') AND (date = '$date')";
...
?>
[/code]

if you see what i'm getting at.

[jdforsythe]

or are you going for something like this:

[table]
[tr]
[td]club[/td][td]date[/td][td]name[/td]
[/tr]

[tr]
[td]club 1[/td][td]08-24-2006[/td][td]john[/td]
[/tr]

[tr]
[td][/td][td][/td][td]james[/td]
[/tr]

[tr]
[td][/td][/tr][tr][td]08-25-2006[/td][td]anne[/td]
[/tr]

[tr]
[td][/td][td][/td][td]andrea[/td]
[/tr]

[tr]
[td]club 1[/td][td]08-24-2006[/td][td]john[/td]
[/tr]

[/table]

?

[digitalgod]

well what I'm trying to do is have the first query display the the club names and the date. Then when you click on that it will display all the names for that club on that date.

makes any sense? So should I keep the first query you gave me and on the second page have a new query that will display all the names for that club on that night?

[b]*edit*

actually I also want it to display the number of names in the first query[/b]

[jdforsythe]

try the example php code in reply #4

if there are multiple records for the same club-date:
club1 | 08-24-2006 | john
club1 | 08-24-2006 | james
club1 | 08-25-2006 | jim

it will return:
club1 | 08-24-2006
club1 | 08-25-2006

the first php code makes a list of links so that if you click on the club-date it sends you to the second php code.

the second set of code gets all names from the table which match the club and date you clicked on.

i'm pretty sure this is what you're asking.  here's a complete version of the code so you can test it and see if it's what you need:

first_query.php:
[code]
<html><body>
<?php

mysql_connect($DB_HOST, $DB_LOGIN, $DB_PASSWORD) or die('Error :' . mysql_error());
mysql_select_db($DB_NAME) or die('Error :' . mysql_error());

$q = "SELECT DISTINCT club,date FROM list ORDER BY club ASC, date ASC";
$p = mysql_query($q) or die('Error: ' . mysql_error());
while ($result = mysql_fetch_row($p)) {
  $club = $result[0];
  $date = $result[1];

  echo "<a href=\"second_query.php?club=$club&amp;date=$date\">$club on $date</a><br />";
}
?>
</body></html>
[/code]

and second_query.php:
[code]
<html><body>
<?php

mysql_connect($DB_HOST, $DB_LOGIN, $DB_PASSWORD) or die('Error :' . mysql_error());
mysql_select_db($DB_NAME) or die('Error :' . mysql_error());

$club = $_GET['club'];
$date = $_GET['date'];

$q = "SELECT name FROM list WHERE (club = '$club') AND (date = '$date')";
$p = mysql_query($q) or die('Error: ' . mysql_error());

echo "<table>";
echo "<tr><td>club</td><td>date</td><td>name</td></tr>";

while ($result = mysql_fetch_row($p)) {
  echo "<tr><td>$club</td><td>$date</td><td>$result[0]</td></tr>";
}

echo "</table>";

?>

[/code]

just replace $DB_HOST, $DB_LOGIN, $DB_PASSWORD, and $DB_NAME with the values you need and try to run the script.  i think this will do what you're looking to do, or set you on the right track.

jeremy

[digitalgod]

yeah I already had the second part figured out and as for the first part I did this and it works perfectly for now (gotta test it some more with more entries)

[code]
<?php
$today = date("Y-m-d");
$query = mysql_query("SELECT DISTINCT club, date FROM list WHERE date = '". $today . "' GROUP BY club ORDER BY club ASC, date ASC") or die(mysql_error());

while ($row = mysql_fetch_array($_SESSION['guestlist_result'])) {
        $count= mysql_query("SELECT name FROM list WHERE club = '".$row['club']."' AND date = '".$row['date']."'") or die(mysql_error());
        $count_result = mysql_num_rows($count);
        echo $row['club'] . ' #: ' . $count_result;
}
?>
[/code]

thanks for your help!

[fenway]

It would be more efficient to simply issue a COUNT(*) query, assuming these are MyISAM tables.

[digitalgod]

back again, I have another question

that first query was to see lists that have been added "today", now I want to be able to see the archive.

I want to select everything from listarchive and group everything by date, ordered from today to the very first entry, and in each day it will show all the clubs (ordered alphabeticaly) that had a list... dunno if I'm clear so here's an example

I want to be able to see something like this

2006-08-28
---------------------
Club 1  |  Number of people :
Club 5  |  Number of people :
--------------------------

2006-08-27
---------------------
Club 2  |  Number of people :
Club 5  |  Number of people :
--------------------------

etc

I'm really having a hard time figuring how to create a query that will select and group everything like that. Can someone give me a push in the right direction?

[digitalgod]

ok almost there, this displays everything almost perfectly, only problem is that it always displays the date on top of each club even if it's the same date/

[code]
<?php
$result = mysql_query("SELECT DISTINCT date, club FROM listarchive ORDER BY date ASC, date ASC") or die(mysql_error());
while ($row = mysql_fetch_array($result)) {

echo '<div style="margin-left:8px; margin-top:10px; margin-right:8px; height:1px; clear:both; background-image:url(images/dot.jpg) "><img src="images/spacer.gif"></div>
<div style="margin-top:8px; margin-bottom:5px "><strong>'.date("D jS M y", mktime(0,0,0,$m,$d,$y)).' </strong></div>
<div style="margin-left:10px; margin-top:10px; clear:both "><img src="images/square1.jpg" align="absmiddle" style="margin-right:5px "><strong class="light_gray"><a href="admin.php?a=guestlist&action=archive&club='.$row['club'].'&date='.$row['date'].'">'.$row['club'].'</strong></a></div>';
echo '<div style="margin-left:10px; margin-top:10px; clear:both ">People on the list: ' . $friends_count.'</div>';
}
?>
[/code]

and it displays

2006-08-28
---------------------
Club 1   |  Number of people :
--------------------------

2006-08-28
---------------------
Club 5   |  Number of people :
--------------------------

2006-08-27
---------------------
Club 2   |  Number of people :
--------------------------

2006-08-27
---------------------
Club 5   |  Number of people :
--------------------------

[fenway]

You'll have to check if the last one is the same as the current one...

[digitalgod]

hmm that's what I thought but I was sure there was a better way of doing this..

so something like this I guess?
[code]
<?php
$result = mysql_query("SELECT DISTINCT date, club FROM listarchive ORDER BY date ASC, date ASC") or die(mysql_error());
$prevDate = "";
while ($row = mysql_fetch_array($result)) {
$currDate = $row['date'];
echo '<div style="margin-left:8px; margin-top:10px; margin-right:8px; height:1px; clear:both; background-image:url(images/dot.jpg) "><img src="images/spacer.gif"></div>';
if ($currDate != $prevDate) {
echo '<div style="margin-top:8px; margin-bottom:5px "><strong>'.date("D jS M y", mktime(0,0,0,$m,$d,$y)).' </strong></div>';
}
echo '<div style="margin-left:10px; margin-top:10px; clear:both "><img src="images/square1.jpg" align="absmiddle" style="margin-right:5px "><strong class="light_gray"><a href="admin.php?a=guestlist&action=archive&club='.$row['club'].'&date='.$row['date'].'">'.$row['club'].'</strong></a></div>';
echo '<div style="margin-left:10px; margin-top:10px; clear:both ">People on the list: ' . $friends_count.'</div>';
$prevDate = $row['date'];
}
?>
[/code]

[fenway]

Pretty much... you have to "keep track" no matter how you implement your solution.