Jump to content

help with query


digitalgod

Recommended Posts

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?
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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]

?
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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!
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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 :
--------------------------
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.