wastedthelight

Wouldn't that grab weeks below 40? and above? I think you want where week > 40 so it gets week 41, change > 41 to get week 42.

You should post the sql you have so far. You'll wanna use this though. WHERE date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) EDIT: Looks like you typed your SQL as I was replying. ha. but yeah I'd switch it around or you'd have to change your week number every week or have an input box so you can "what week do you want to check". However my way will automatically do it for you but you'd only see the last 7 days and couldn't back track. So you could setup so when you submit a song it puts the date (sql format) in a new dat field. I actually do almost exacly what you want with the following code. The music director selects a genre to check (radio station) and then the php fills in the sql query with the correct thing to check. $playlist = mysql_query("SELECT artist, album, section, sectionnumber, date, COUNT(sectionnumber) AS timesplayed FROM playlist WHERE section = '$getgenre' AND sectionnumber > $getcatnumber AND date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY section, sectionnumber ORDER BY section ASC, timesplayed DESC"); The $getgenre is gotten from a form input that tells the script what to get. You'd want to do date instead. Good luck.

I tried to do a sub query with no luck as well. I feel like I'm on the right track but apparently my SQL code is wrong as I get a syntax error: <?php // Make a MySQL Connection $query = "SELECT dj, artist, (SELECT date, COUNT(artist) FROM playlist WHERE date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)) AS total_plays FROM playlist GROUP BY dj"; $result = mysql_query($query) or die(mysql_error()); // Print out result while($row = mysql_fetch_array($result)){ echo $row['dj'] ." played ". $row['total_plays'] ." songs on their last show."; echo "<br />"; } ?>

I'm having a problem figuring out how to do the following. The query should return a list of every DJ in the system and then show how many entries they put in within the last 7 days. Currently what I have is only returning those who actually inputted data in the last 7 days, not including those who didn't and have 0 returned rows. I want it to show even people who have 0. $query = "SELECT dj, COUNT(date) FROM playlist WHERE date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY dj"; The count is on date but doesn't really matter what is there since it just needs to count how many rows were found in the 7 day span....or does it? ha. Thanks everyone.

Wow, okay didn't know that. Thanks for all your help!

Thanks that worked fully this time. So why can't I use Select * instead of listing all the fields out? Why does it seem to screw up everything?

i spoke to soon. When adding the rest of the fields (address, zip, phone...) it screwed everything up. Even taking when it works to changing Select company FROM to Select * From screws it up. This is what I have. It's not out putting anything except autoID and country (id for state adn country for company). <?php $sql = "SELECT * FROM locations ORDER BY state, company"; $res = mysql_query($sql) or die(mysql_error()); $lastState = ''; while (list($state, $company) = mysql_fetch_row($res)) { if ($lastState != $state) // new state ? { if ($lastState != '') { echo '</table><br/>'; // if there was a previous table, close it } echo '<table width="100%" align="left">'; // start new table echo '<tr bgcolor="#c0c0c0"><td><b>';echo $state; echo '</b></td></tr>'; // output new state header $lastState = $state; // reset last state value } echo ' <tr><td>';echo $company; echo '</td> <td>';echo $address1; echo '</td> <td>';echo $address2; echo '</td> <td>';echo $city; echo '</td> <td>';echo $zip; echo '</td> <td>';echo $phone; echo '</td> <td>';echo $cell; echo '</td> <td>';echo $fax; echo '</td> <td>';echo $website; echo '</td></tr>'; // output company name } echo '</table><br/>'; // cloase final table ?>

Thanks! Just had to do a little editing because it was actually showing $state instead of the value state is. but yeah, works perfect thanks for your help Barand!

oh yeah, that's a new HTML table, not mysql

I use to know how to do this but it's been way to long. I would like to make a new table based on State pulled. Michigan ---------- company 1 company 2 company 2 Ohio ----------- company 1 company 2 company 2 and so forth. any help would be appreciated. Thanks.

it already worked perfectly Some improper entries made it pull large numbers that made it look like it was pulling for the last month. my bad. well there's some code example though!

The dates are stored and that's how it's grabbing the last 7 days of music only. That part works as I said. The problem is that it counts not only the 7 days but since the creation of the database. It only displays on screen the last 7 days. but counts over 7 days.

Hello. Need a little help here please. The script should grab only last 7 days of database entries which it does. Also it also needs to count to see how many times a CD was played, which it also does, but gives overall total and not just within that 7 days. Any help getting it to count only last x amount of days? thanks. <?php $query = "SELECT artist, new, section, sectionnumber, date, COUNT(sectionnumber) FROM playlist WHERE new = '0' AND date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY section, sectionnumber ORDER BY section"; $result = mysql_query($query) or die(mysql_error()); echo " <table class=\"listfont\" width=\"700\" border=\"1\" bordercolor=\"#000000\" cellspacing=\"0\" cellpadding=\"0\" align=\"center\"> <tr bgcolor=\"c0c0c0\"> </td> <td>CD</td> <td>Artist</td> <td>Date</td> <td>Times Played</td> </tr> "; while($row = mysql_fetch_array($result)){ echo " <td>". $row['section'] ." ". $row['sectionnumber'] ." </td> <td>". $row['artist'] ."</td> <td>". $row['date'] ."</td> <td>". $row['COUNT(sectionnumber)'] ."</td></tr>"; } echo "</table>"; ?>

How'd you solve this issue? Thanks.

It works! Thanks! Pardon my noobness.