A Quick MySQL Query question.

  • Axe
  • Genius
  • Genius
  • User avatar
  • Posts: 5739
  • Loc: Sub-level 28

Post 3+ Months Ago

Okies,

So, I'm working on my show database, and I've got the basics of MySql figured out to get done what I need to get done...

What I'm attempting to do right now I know I can do with a different MySQL query and some PHP manipulation of the results, but I just know there's a MySQL command that can do what I want in a single line of code..

Here's my current MySQL request..

Code: [ Select ]
SELECT DISTINCT pn_showstate FROM nuke_shows ORDER BY pn_showstate

pn_showstate contains a 2 letter state abbreviation for the location of the show. Using this, I can set it to only display states for which it knows of shows happening - this way, I don't have 20 states with (0) shows listed :)

Then as the loop runs, to find out which states have shows in them, I just call...

Code: [ Select ]
while ($data = mysql_fetch_assoc($result)) {
  $output->Text('<tr bgcolor="#E3EDD9"><td width="5" align="right" nowrap><font face="Verdana">»</font></td><td width="99%"><a href="'.pnModURL('shows', 'user', 'showlist').'&state='.$data['pn_showstate'].'">'.getstate($data['pn_showstate']).'</a> ('.countshows($data['pn_showstate']).')</td></tr>');
 }
  1. while ($data = mysql_fetch_assoc($result)) {
  2.   $output->Text('<tr bgcolor="#E3EDD9"><td width="5" align="right" nowrap><font face="Verdana">»</font></td><td width="99%"><a href="'.pnModURL('shows', 'user', 'showlist').'&state='.$data['pn_showstate'].'">'.getstate($data['pn_showstate']).'</a> ('.countshows($data['pn_showstate']).')</td></tr>');
  3.  }


On my regular request to get the total count of shows in a given state, it calls this function...

Code: [ Select ]
function countshows($state) {
 $sql = 'select count(*) from nuke_shows where pn_showstate = "'.$state.'" and pn_showend > "'.(time() + 86400).'"';
 $result = mysql_query($sql);
 return mysql_result($result, 0);
}
  1. function countshows($state) {
  2.  $sql = 'select count(*) from nuke_shows where pn_showstate = "'.$state.'" and pn_showend > "'.(time() + 86400).'"';
  3.  $result = mysql_query($sql);
  4.  return mysql_result($result, 0);
  5. }

and getstate() simply turns a 2 letter state abbreviation into its full name.

Obviously, I don't want to list shows that have already happened.

As you can see in the countshows() function, there is a field called pn_showend. This is a Unix timestamp of the date the show ends (00:00 on that day). I do want listings for those to show up the entire last day of the show, so I set it to not list them on the following midnight in this function.

Now, let's say that a particular state (Florida, in this example), the database has 20 shows, but 16 of them have already happened.

No problem, the function still sees that there are shows in Florida, and countshows() returns the correct number (4 remaining shows).

But, what if there are 20 shows in the database, and ALL of them have already happened. My first loop is still going to see that there are shows in Florida, but countshows() is going to return 0.

Is there a way to modify this request...

Code: [ Select ]
SELECT DISTINCT pn_showstate FROM nuke_shows ORDER BY pn_showstate

So that it takes into account the...

Code: [ Select ]
where pn_showend > "'.(time() + 86400).'"

bit, and only lists the state if there are still shows that have not yet happend?

Btw, simpy doing...

Code: [ Select ]
$sql = 'SELECT DISTINCT pn_showstate FROM nuke_shows where pn_showend > "'.(time() + 86400).'" ORDER BY pn_showstate';

doesn't work, I knew this already, but pasting for those who might suggest it :)

And this is on MySQL 3.23.56 (I think, just off-hand without checking, I know it's definitely 3.23.something)
  • b_heyer
  • Web Master
  • Web Master
  • User avatar
  • Posts: 4581
  • Loc: Maryland

Post 3+ Months Ago

What happens when you do this:

Code: [ Select ]
$sql = 'SELECT DISTINCT pn_showstate FROM nuke_shows where pn_showend > "'.(time() + 86400).'" ORDER BY pn_showstate';


You said it doesn't work, but what exactly DOESN'T work?
  • Axe
  • Genius
  • Genius
  • User avatar
  • Posts: 5739
  • Loc: Sub-level 28

Post 3+ Months Ago

WOAH!

I just put it back into the code to reproduce the error it gave me and it worked?!??!?

Well, hey, I guess thanks b_heyer, lol :)

I musta made a typo somewhere in there before.
  • b_heyer
  • Web Master
  • Web Master
  • User avatar
  • Posts: 4581
  • Loc: Maryland

Post 3+ Months Ago

I figured it was something like that. That should have produced what you were looking for and I was beyond confused as to what was wrong with it! :-P
  • Axe
  • Genius
  • Genius
  • User avatar
  • Posts: 5739
  • Loc: Sub-level 28

Post 3+ Months Ago

Well, yeah, I figured it'd work too..

You know what it probably is, and I've made this typo a lot today...

I must have skipped the "w" in pn_showstart & pn_showend - it's this dodgy bendy-keyboard, heh. It's one of those you can roll up... I only got it because when I type it's silent, you can't hear a thing. So I can stay up working at night and not disturb Cheri while she's sleeping.

Post Information

  • Total Posts in this topic: 5 posts
  • Users browsing this forum: No registered users and 52 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.