Back To Programming / Scripting / Coding Forum

A Quick MySQL Query question.

  • Axe
  • Genius
  • Genius
  • User avatar
  • Joined: Jan 07, 2004
  • Posts: 5744
  • Loc: Sub-level 28
  • Status: Offline

Post January 22nd, 2004, 2:15 pm

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)
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post January 22nd, 2004, 2:15 pm

  • b_heyer
  • Web Master
  • Web Master
  • User avatar
  • Joined: Jun 15, 2003
  • Posts: 4583
  • Loc: Maryland
  • Status: Offline

Post January 22nd, 2004, 2:21 pm

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?
Pixel Acres V2
  • Axe
  • Genius
  • Genius
  • User avatar
  • Joined: Jan 07, 2004
  • Posts: 5744
  • Loc: Sub-level 28
  • Status: Offline

Post January 22nd, 2004, 2:29 pm

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
  • Joined: Jun 15, 2003
  • Posts: 4583
  • Loc: Maryland
  • Status: Offline

Post January 22nd, 2004, 3:29 pm

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
Pixel Acres V2
  • Axe
  • Genius
  • Genius
  • User avatar
  • Joined: Jan 07, 2004
  • Posts: 5744
  • Loc: Sub-level 28
  • Status: Offline

Post January 22nd, 2004, 3:36 pm

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.

Page 1 of 1

Post Information

  • Total Posts in this topic: 5 posts
  • Users browsing this forum: No registered users and 135 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
 
cron
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.