maybe i'm completely misunderstanding what you're saying stein. i agree that order by and group by are completely independent

but what i was originally say is if you have the following data set

regdate fname lname company
1/1/09  joe   smith tww
1/2/09  joe   smith tww

3/11/2009 11:41:35 AM

In recreating your table, I get the opposite. I get the 01-01-2009 row.

mysql> SELECT * FROM test2;
+------------+-------+-------+---------+
| regdate    | fname | lname | company |
+------------+-------+-------+---------+
| 2009-01-01 | b     | s     | tww     |
| 2009-01-02 | b     | s     | tww     |
+------------+-------+-------+---------+
2 rows in set (0.01 sec)


mysql> select *, count(*) from test2 group by fname, lname, company order by regdate asc;
+------------+-------+-------+---------+----------+
| regdate    | fname | lname | company | count(*) |
+------------+-------+-------+---------+----------+
| 2009-01-01 | b     | s     | tww     | 2        |
+------------+-------+-------+---------+----------+
1 row in set (0.00 sec)


mysql> select *, count(*) from test2 group by fname, lname, company order by regdate desc;
+------------+-------+-------+---------+----------+
| regdate    | fname | lname | company | count(*) |
+------------+-------+-------+---------+----------+
| 2009-01-01 | b     | s     | tww     | 2        |
+------------+-------+-------+---------+----------+
1 row in set (0.01 sec)

3/11/2009 11:42:58 AM

yeah you're right. so did i. not sure how i misunderstood how that worked

my second suggestion with the subquery worked though

select *, (select max(regdate) from quag qq where qq.lname=q.lname and qq.fname=q.fname and qq.company=q.company)
from quag q
group by fname, lname, company

[Edited on March 11, 2009 at 11:49 AM. Reason : .]

3/11/2009 11:48:04 AM

GAH

n00bs

3/11/2009 11:49:43 AM

In fairness, it really is non-intuitive.

Meanwhile, you can do things like:

mysql> select *, GROUP_CONCAT(regdate ORDER BY regdate DESC), count(*) from test2 group by fname, lname, company order by regdate desc;
+------------+-------+-------+---------+---------------------------------------------+----------+
| regdate    | fname | lname | company | GROUP_CONCAT(regdate ORDER BY regdate DESC) | count(*) |
+------------+-------+-------+---------+---------------------------------------------+----------+
| 2009-01-01 | b     | s     | tww     | 2009-01-02,2009-01-01                       | 2        |
+------------+-------+-------+---------+---------------------------------------------+----------+
1 row in set (0.00 sec)

3/11/2009 11:52:09 AM

Quote :

"Question: does your table have a unique identifier for each row? Like an auto_increment field?"

yepper

Quote :

"my second suggestion with the subquery worked though"

it's not working for me...here are the two queries, verbatim, i've tried that don't conk out, and they both return the exact same thing (the older entry comes up while the newer does not, as we've noted):

SELECT *, COUNT(*)
FROM $table
GROUP BY LNAME, FNAME, COMPANY

SELECT *, (SELECT max(REGDATE) FROM $table table2 WHERE table2.LNAME=table1.LNAME AND table2.FNAME=table1.FNAME AND table2.COMPANY=table1.COMPANY)
FROM $table table1
GROUP BY LNAME, FNAME, COMPANY

i don't know if it matters, and i don't see how it could, but the REGDATE field is just the standard php now() function

[Edited on March 11, 2009 at 2:56 PM. Reason : *shrug*]

3/11/2009 2:53:00 PM

SELECT * FROM table a WHERE a.id IN (SELECT MAX(b.id) FROM table b GROUP BY b.lname, b.fname, b.company)

Should do what you want (under the assumption that the greater the ID, the closer the regdate)

I don't have a prompt up to check it, but that should solve your problem.

[Edited on March 11, 2009 at 3:38 PM. Reason : .]

[Edited on March 11, 2009 at 3:38 PM. Reason : .]

3/11/2009 3:37:32 PM

^ that gave me the results i'm looking for! i tweaked it to be this, and it's still putting out the same results (albeit different order due to the order by)...any reason i couldn't or shouldn't have reduced the query? since i get what you were doing with it, i just replaced the ID with REGDATE, since that's what i (technically) was looking for

SELECT * FROM $table WHERE REGDATE IN (SELECT MAX(REGDATE) FROM $table GROUP BY LNAME, FNAME, COMPANY) ORDER BY LNAME

thanks, all

3/12/2009 8:09:49 AM

Because selecting the regdate in the subquery doesn't actually give you the results you want.

mysql> SELECT * FROM test2 ORDER BY id;
+----+------------+-------+-------+---------+
| id | regdate    | fname | lname | company |
+----+------------+-------+-------+---------+
| 1  | 2009-01-01 | b     | s     | tww     |
| 2  | 2009-01-01 | c     | t     | tww1    |
| 3  | 2009-01-01 | d     | u     | tww2    |
| 4  | 2009-01-02 | b     | s     | tww     |
| 5  | 2009-01-03 | d     | u     | tww2    |
+----+------------+-------+-------+---------+

mysql> SELECT * FROM test2 a WHERE a.id IN (SELECT MAX(b.id) FROM test2 b GROUP BY b.lname, b.fname, b.company);
+----+------------+-------+-------+---------+
| id | regdate    | fname | lname | company |
+----+------------+-------+-------+---------+
| 4  | 2009-01-02 | b     | s     | tww     |
| 5  | 2009-01-03 | d     | u     | tww2    |
| 2  | 2009-01-01 | c     | t     | tww1    |
+----+------------+-------+-------+---------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM test2 WHERE REGDATE IN (SELECT MAX(REGDATE) FROM test2 GROUP BY LNAME, FNAME, COMPANY) ORDER BY LNAME;
+----+------------+-------+-------+---------+
| id | regdate    | fname | lname | company |
+----+------------+-------+-------+---------+
| 1  | 2009-01-01 | b     | s     | tww     |
| 4  | 2009-01-02 | b     | s     | tww     |
| 2  | 2009-01-01 | c     | t     | tww1    |
| 5  | 2009-01-03 | d     | u     | tww2    |
| 3  | 2009-01-01 | d     | u     | tww2    |
+----+------------+-------+-------+---------+
5 rows in set (0.00 sec)

3/12/2009 8:26:47 AM

well, now i am confused, then...because it's giving me the correct output (no duplicates and everything valid is coming through)...any idea why? i see what you mean...but now i don't understand why i'm getting the "correct" results (i've got 11 entries so far, one of which is a duplicate...only 10 are coming through, and it's the correct one)

>.<

[Edited on March 12, 2009 at 8:32 AM. Reason : .]

3/12/2009 8:30:48 AM

Does the outdated duplicate fall on a day where another record exists?

[Edited on March 12, 2009 at 8:34 AM. Reason : .]

3/12/2009 8:33:29 AM

it does...8 of the 11 current records fall on the same date...the content of REGDATE is the standard PHP now() output, so here is what the duplicate records' REGDATE looks like:

2009-03-10 23:48:57
2009-03-10 23:49:38

there are 6 others before them, all with 2009-03-10, but with different times (obviously)

3/12/2009 9:49:06 AM

To some extent that would explain why yours is working. I'd forgot that you said you were using a UNIX timestamp rather than a DATE field. The reason it's working is because the odds that two people have submitted your form at the exact same second is slim.

Now, with that said, unless you've got regdate keyed, you'll probably see some speed increase (in larger tables) by selecting based off ID instead of REGDATE.

3/12/2009 10:44:11 AM

^ yeah, that would make sense...also, since it's not even really comparing the dates by themselves, but only dates in cases where the last name, first name, and company ALSO match, the odds of having the exact same set of data at the exact same time would be pretty low (then again, look at tww and the multiple posts glitch)...so i'll probably change it to ID, since, really, it's a safer bet

now i'm trying to make it more complex...so in this same table, there are fields for attendance to various events...i want to be able to get totals for each event attended without caring specifically who's attending, but kicking out the duplicate from before...here's a sample of what the table looks like

+----+---------------------+-------+-------+---------+-----------+-------+--------+
| id |       regdate       | lname | fname | company | breakfast | lunch | dinner |
+----+---------------------+-------+-------+---------+-----------+-------+--------+
| 1  | 2009-03-10 20:15:34 | smith | alexa |   abc   |     1     |   1   |    0   |
| 2  | 2009-03-10 21:26:35 | brown | jason |   cde   |     1     |   1   |    0   |
| 3  | 2009-03-10 22:37:36 | green | jenny |   efg   |     1     |   1   |    1   |
| 4  | 2009-03-10 23:48:57 | black | roger |   ghi   |     0     |   1   |    1   |
| 5  | 2009-03-10 23:49:38 | black | roger |   ghi   |     1     |   1   |    1   |
+----+---------------------+-------+-------+---------+-----------+-------+--------+

3/12/2009 11:15:42 AM

I only read your last message, but given that table, you could do this:

mysql> SELECT sum(a.breakfast), sum(a.lunch), sum(a.dinner)
    ->   FROM regs a
    ->     INNER JOIN (
    ->       SELECT x.lname, x.fname, x.company, max(x.regdate) as regdate
    ->         FROM regs x
    ->         GROUP BY x.lname, x.fname, x.company
    ->       ) b
    ->       ON (a.lname = b.lname AND a.fname = b.fname AND a.company = b.company AND a.regdate = b.regdate)
    -> ;
+------------------+--------------+---------------+
| sum(a.breakfast) | sum(a.lunch) | sum(a.dinner) |
+------------------+--------------+---------------+
|                4 |            4 |             2 |
+------------------+--------------+---------------+

SELECT sum(a.breakfast), sum(a.lunch), sum(a.dinner)
  FROM regs a
  WHERE a.id IN (
      SELECT first_value(x.id)
          OVER (
            PARTITION BY x.lname, x.fname, x.company
            ORDER BY x.regdate DESC)
          AS id
        FROM regs x
      )

DATA regs;
  SET regs;
  BY company fname lname DESCENDING regdate;
  IF NOT first.lname THEN delete;
RUN;

3/12/2009 7:44:19 PM

i don't know what i'm missing, but i'm getting nothing from that query...i tweaked it only with some AS operators, but even removing them does nothing...not that i'm getting 0's...just getting nothing at all...my query is as follows:

SELECT sum(t1.breakfast) AS breakfast_total, sum(t1.lunch) AS lunch_total, sum(t1.dinner) AS dinner_total
FROM $table t1
INNER JOIN (
    SELECT x.lname, x.fname, x.company, max(x.regdate)
    FROM $table x
    GROUP BY x.lname, x.fname, x.company
) t2
ON (t1.lname=t2.lname AND t1.fname=t2.fname AND t1.company=t2.company AND t1.regdate=t2.regdate)

$query = "see above";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
echo "<li>".$row['breakfast_total']." for breakfast</li>";

3/13/2009 9:06:50 AM

After playing with it a bit:

My Table

mysql> SELECT * FROM test2;
+----+------------+-------+-------+---------+-----------+-------+--------+
| id | regdate    | fname | lname | company | breakfast | lunch | dinner |
+----+------------+-------+-------+---------+-----------+-------+--------+
| 1  | 2009-01-01 | b     | s     | tww     | 2         | 10    | 1      |
| 4  | 2009-01-02 | b     | s     | tww     | 4         | 3     | 8      |
| 5  | 2009-01-03 | d     | u     | tww2    | 6         | 7     | 5      |
| 2  | 2009-01-01 | c     | t     | tww1    | 8         | 5     | 4      |
| 3  | 2009-01-01 | d     | u     | tww2    | 10        | 1     | 8      |
+----+------------+-------+-------+---------+-----------+-------+--------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test2 a WHERE a.id IN (SELECT MAX(b.id) FROM test2 b GROUP BY b.lname, b.fname, b.company);                                              
+----+------------+-------+-------+---------+-----------+-------+--------+
| id | regdate    | fname | lname | company | breakfast | lunch | dinner |
+----+------------+-------+-------+---------+-----------+-------+--------+
| 4  | 2009-01-02 | b     | s     | tww     | 4         | 3     | 8      |
| 5  | 2009-01-03 | d     | u     | tww2    | 6         | 7     | 5      |
| 2  | 2009-01-01 | c     | t     | tww1    | 8         | 5     | 4      |
+----+------------+-------+-------+---------+-----------+-------+--------+
3 rows in set (0.00 sec)

mysql> SET @b :=0, @l := 0, @d := 0;
SELECT id, regdate,fname,lname, company, @b:=@b+breakfast, @l:=@l+lunch, @d:=@d+dinner FROM test2 a
WHERE a.id IN (SELECT MAX(b.id) FROM test2 b GROUP BY b.lname, b.fname, b.company);
SELECT @b breakfast, @l lunch, @d dinner;
Query OK, 0 rows affected (0.00 sec)

+----+------------+-------+-------+---------+------------------+--------------+---------------+
| id | regdate    | fname | lname | company | @b:=@b+breakfast | @l:=@l+lunch | @d:=@d+dinner |
+----+------------+-------+-------+---------+------------------+--------------+---------------+
| 4  | 2009-01-02 | b     | s     | tww     | 4                | 3            | 8             |
| 5  | 2009-01-03 | d     | u     | tww2    | 10               | 10           | 13            |
| 2  | 2009-01-01 | c     | t     | tww1    | 18               | 15           | 17            |
+----+------------+-------+-------+---------+------------------+--------------+---------------+
3 rows in set (0.00 sec)

+-----------+-------+--------+
| breakfast | lunch | dinner |
+-----------+-------+--------+
| 18        | 15    | 17     |
+-----------+-------+--------+
1 row in set (0.00 sec)

3/13/2009 9:40:08 AM

Since we are on topic of queries and results,

is there an easier way to write php result driven things?

I can create forums, write to the database, but displaying the data correct is sometimes a problem. Does dream weaver or some other sort of program, connect to your database, you show it the form, the query and it can help you display the results in html?

3/13/2009 10:17:23 AM

This should be easy, but it's late and my brain is frozen.

I have a table listing daily test scores. Four columns: the day of the test, the name of the test-taker, their score, and their score's rank.

> SELECT * FROM scores;

+---------+------+-------+------+
| Day     | Name | Score | Rank |
+---------+------+-------+------+
| Monday  | Jim  | 8     | 3    |
| Monday  | Bob  | 8     | 3    |
| Monday  | Sam  | 9     | 2    |
| Monday  | Joe  | 10    | 1    |
| Tuesday | Jim  | 3     | 3    |
| Tuesday | Bob  | 8     | 2    |
| Tuesday | Sam  | 10    | 1    |
| Tuesday | Joe  | 8     | 2    |
+---------+------+-------+------+

+---------+----------------+--------------+
| Day     | Score_of_Eight | Ranked_First |
+---------+----------------+--------------+
| Monday  | Jim, Bob       | Joe          |
| Tuesday | Bob, Joe       | Sam          |
+---------+----------------+--------------+

3/24/2009 1:32:41 AM

The only difficult part should be the second column in your results table. This should help:
http://stackoverflow.com/questions/122942/

3/24/2009 2:02:08 AM

Questionably efficient, but:

SELECT s.Day, 
(SELECT GROUP_CONCAT(Name SEPARATOR ', ') FROM scores s2 WHERE s.Day=s2.Day AND s2.Score=8 GROUP BY s2.Day) Score_of_Eight,
s.Name Ranked_First
FROM scores s
WHERE s.Rank=1

3/24/2009 10:27:10 AM

BACK TO THE NERDERY WITH YOU

3/24/2009 11:53:04 AM

My old thread died, so I decided that I'll ask my question in this thread instead of making another one. It's not really a MySQL question. It's actually a question about how to sort MySQL results using PHP. If there is another thread I should use, please let me know.

I have a database that looks like this:

+----+------+------+
| id | type | desc |
+----+------+------+
| 1  | opt1 | foo1 |
| 2  | opt1 | bar1 |
| 3  | opt2 | foo2 |
| 4  | opt2 | bar2 |
| 5  | opt3 | foo3 |
| 6  | opt3 | bar3 |
+----+------+------+

SELECT * FROM options

<select name="type">
    <option value="" selected="selected">Please choose...</option>
    <?php while($row = mysql_fetch_array($result)){echo "<option value=\"".$row['id']."\">".$row['type'];} ?>
</select>

3/24/2009 12:24:39 PM

Any suggestions?

3/25/2009 9:17:02 AM

You can make three different queries or you can make the one query and load all the options into different arrays.

3/25/2009 9:52:58 AM

$lol = mysql_query(SELECT * FROM options);
while($L = mysql_fetch_object($lol)) $teehee[$L->type][$L->id] = $L->desc;

foreach($laff as $t=>$teehee) {
  echo '<select name="type-'.$t.'"><option value="" selected="selected">Please choose...</option>';
  foreach($teehee[$t] as $i=>$guffaw) {
    echo '<option value="'.$i.'">'.$guffaw.'</option>';
  }
  echo '</select>';
}

3/25/2009 10:33:21 AM

from here on out, i'm using $lol instead of a regular lol

thxu bigman

3/25/2009 11:05:04 AM

^^ I used the code you posted, but changed around the variables so that I could better understand what was going on. I also used brackets for the while loop - don't you need those? What I have is listed below, but it's not doing anything at all. It's not breaking, either, though.

<?php
$query = "SELECT * FROM options";
$result = mysql_query($query);
while($group = mysql_fetch_object($result)) {
    $filtered[$group->type][$group->id] = $group->desc;
    foreach($option as $type=>$filtered) {
        echo '<select name="'.$type.'"><option value="" selected="selected">Please select...</option>';
        foreach($filtered[$type] as $id=>$desc) {
            echo '<option value="'.$id.'">'.$desc.'</option>';
        }
        echo '</select>';
    }
}
?>

<select name="opt1">
    <option value="" selected="selected">Please select...</option>
    <?php populate drop down menu with all IDs/DESCs of those in type "opt1" ?>
</select>

<?php populate radio buttons with all IDs/DESCs of those in type "opt2" ?>

<ul>
    <?php populate check boxes with all IDs/DESCs of those in type "opt3" ?>
</ul>

3/25/2009 12:11:28 PM

If you can point me in the direction of a good tutorial, I'd appreciate that, too. I make no money doing this - it's extraneous to my full-time job - so my time is a bit limited. Otherwise, I would try to teach myself this in a more structured manner. I also learn fairly well by looking at something done correctly and then breaking it down into its individual parts, which is why I ask. Is what I'd like to do just not feasible? Or is it just very inefficient?

3/25/2009 2:55:07 PM

the while loop is only for that one line to load up the array with all the variables needed

... and i messed up some other crap too

$lol = mysql_query(SELECT * FROM options);
while($L = mysql_fetch_object($lol)) {$teehee[$L->type][$L->id] = $L->desc;}

foreach($teehee as $t=>$laff) {
  echo '<select name="'.$t.'"><option value="" selected="selected">Please choose...</option>';
  foreach($laff as $i=>$guffaw) {
    echo '<option value="'.$i.'">'.$guffaw.'</option>';
  }
  echo '</select>';
}

3/25/2009 3:17:54 PM

Quote :

"If you can point me in the direction of a good tutorial, I'd appreciate that, too"

Can't go wrong with a good book.

http://www.amazon.com/PHP-MySQL-Development-Luke-Welling/dp/0672317842 is one of the best.

3/25/2009 3:26:23 PM

^^ That works - thanks! It's a pretty neat way of getting them all the display. My question, though, is how do I only show one type of option? I am trying to store all of the options in the database instead of coding them directly on each page because there are so many, they will exist on about 20 pages, and they'll be changing on a pretty regular basis. Not all of them are drop down lists. Some are check boxes and some are radio buttons. For example, how do I show "opt2" as radio buttons and "opt3" as check boxes? As it is right now, it prints out multiple drop down menus all right next to each other. Not only would I like to be able to separate them, but I'd like to be able to change the code so that it displays the correct type of selection (drop down, radio buttons, check boxes).

I assume it would be some kind of if statement? I tried something like

if($laff= "opt2")

3/26/2009 8:31:29 AM

Any suggestions? The book comes next week sometime, and I'll try to be a bit less annoying.

3/26/2009 1:10:44 PM

if you want to do it per option, you'd do like

$lol = mysql_query(SELECT * FROM options);
while($L = mysql_fetch_object($lol)) {$teehee[$L->type][$L->id] = $L->desc;}

echo '<select name="opt1"><option value="" selected="selected">Please choose...</option>';
foreach($teehee['opt1'] as $i=>$guffaw) {
  echo '<option value="'.$i.'">'.$guffaw.'</option>';
}
echo '</select><br />';

foreach($teehee['opt2'] as $i=>$guffaw) {
  echo '<legend>'.$guffaw.': <input type="radio" name="opt2" value="'.$i.'" /></legend>';
}
echo '<br />';

foreach($teehee['opt3'] as $i=>$guffaw) {
  echo '<legend>'.$guffaw.': <input type="input" name="opt3-'.$i.'" /></legend>';
}

$lol = mysql_query(SELECT * FROM options WHERE type='opt1');

3/26/2009 1:20:54 PM