Need help cleaning up this code

bschultz · April 6, 2011

I need to pull a list of mp3 file names to create a playlist. I have a column for "priority" which will handle the rotation of the mp3's...so that a priority of 4 will play more often than a priority of 3...and so on.

I have this code (which works) to select a column from the db that matches the selected criteria...AND has a priority of "4". If there are no matches, select a row that has a priority of "3". How can I clean up this code...preferably to remove the need for adding 2's after each variable?

Also, is there a better way of handling this, so that it's ONE select...and then puts the mp3's in the playlist the correct number of times?

<?php

$sql = "SELECT * FROM audio WHERE `client_id` = '$client' AND (`start_date` <= '$nownohour' AND `end_date` >= '$nownohour') AND `$dow` = '1' AND `is_active` = '1' AND (`start_hour` <= '$hournozero' AND `end_hour` >= '$hournozero') AND `priority` = '4' LIMIT 1";
$rs = mysql_query($sql,$dbc);  
$matches = 0; 
while ($row = mysql_fetch_assoc($rs))  {
$matches++; 
echo "$row[title].mp3<br />";
}

if (!$matches) { 
$sql2 = "SELECT * FROM audio WHERE `client_id` = '$client' AND (`start_date` <= '$now' AND `end_date` >= '$now') AND `$dow` = '1' AND `is_active` = '1' AND (`start_hour` <= '$hournozero' AND `end_hour` >= '$hournozero') AND `priority` = '3' LIMIT 1";
$rs2 = mysql_query($sql2,$dbc);  
$matches2 = 0; 
while ($row2 = mysql_fetch_assoc($rs2))  {
$matches2++; 
echo "$row2[title].mp3<br />";
}
}
echo "-------------<br />";
?>

Thanks!

DavidAM · April 6, 2011

Since you have the two query processes separated, there is no need to use different variable names (by adding the '2' to it) for each group. Unless you are going to refer to $sql, $rs, or $row later in the code (and I see no need to), there will be do conflict.

There's a minor difference between the two SQL statements. From looking at it, I'm guessing this was a typo. If that is true, then this would be an excellent candidate for a function.

function getTitle($client, $dow, $priority, $limit) {
  // Calculate the date and hour (or pass them in as parameters)
  $nownohour = date('Y-m-d');  // or whatever
  $hournozero = date('h'); // or whatever

  $sql = "SELECT * FROM audio 
    WHERE `client_id` = '$client' 
    AND (`start_date` <= '$nownohour' AND `end_date` >= '$nownohour') 
    AND `$dow` = '1' AND `is_active` = '1' 
    AND (`start_hour` <= '$hournozero' AND `end_hour` >= '$hournozero') 
    AND `priority` = '$priority' LIMIT $limit";
  $rs = mysql_query($sql);  
  $matches = 0; 
  while ($row = mysql_fetch_assoc($rs))  {
    $matches++; 
    echo "$row[title].mp3<br />";
  }
  return $matches;
}

Note: You have "LIMIT 1" in your queries, which means you will not get more than one row returned. In this case, the "while" loop is completely unnecessary, I left it in since I made the limit a parameter.

Note: I highly recommend NOT using "SELECT *" as it sends ALL of the data from each selected row back to the client. Generally, you should only select the columns that you are going to need.

Now you can call the function in a loop like this:

for ($priority = 4; $priority > 0; $priority--) {
  if (getTitle($client, $dow, $priority, 1) > 0) break;
}

The "break" will cause the loop to exit as soon as the function returns some value greater than zero (i.e. it found a title). Change the "$priority > 0" to "$priority >= 0" if you actually have a priority zero in your database

Disclaimer: This code is untested and provided as educational material only. There is no warranty, expressed or implied on the usability, fitness, correctness, or prettiness of the provided code.

bschultz · April 6, 2011

David,

Thanks for the help. There is a further step to this that I was going to tackle once I got this part working...but...I think that with a function, I'll have to deal with it now.

I need to insert several mp3's into the playlist that ARE NOT in the database....and need them inserted at specific times in the playlist.

Here's what the final playlist needs to look like:

- mp3 from database priority 4 (or 3, if priority 4 does not exist)

- other mp3 #1 (set in a variable)

- mp3 from database priority 4 (or 2, if priority 4 does not exist)

- other mp3 #2 (set in a variable)

- mp3 from database priority 3 (will always exist)

- other mp3 #3 (set in a variable)

- mp3 from database priority 4 (or 1, if priority 4 does not exist)

- other mp3 #4 (set in a variable)

...this will repeat as needed until all "other" mp3's are in the playlist...probably just put those in an array now that I think of it.... I don't care how it handles the priority as long as for every time that priority 1 plays...there will be TWO priority 2's that play...and THREE priority 3's...and if there is a priority 4...PLAY IT FOUR TIMES....

ALSO...I need a way to update the database column "last_play" with the current timestamp of when that particular title was put into a playlist...and select the mp3 that played the longest ago. (fairly simple order by in the select...but don't quite know how to update the database after it inserts it into the playlist).

DavidAM · April 6, 2011

I thought there might be more to it. So if I understand, you want to mix one-to-one your, let's call them "sponsored", songs with your database songs. One from the database, one from the sponsor list, one from the database, one from the sponsor list, etc.; until all of the sponsored songs are played. I have a couple of questions:

1) The example you gave will always play priority 4 songs if there are a significant number of them in the database. Even if you limit the query to songs NOT played today, if there are enough priority 4 songs, it will never fail over to priority 3, 2, or 1. Is this correct, or does your example need to extend into the other levels (i.e. 4 or 3, S, 4 or 2, S, 3, S, 4 or 1, S, 3 or 2, S, 2, S, 3 or 1, S, 2 or 1, S, 1)? Or do you just want it MORE likely that a priority 4 will play?

2) The query from your original post does nothing to randomize the songs. It will likely return the same songs in the same order when run at the same time of the same DOW. Even if we exclude songs by the last_played date, the order will be the same in a week or two. In fact, the query in the function I suggested, will likely return the same song every time it is called for level 4.

3) Your example shows all priority 4 songs played BEFORE any priority 3 songs. I guess that is what "priority" means, but do you really want it that way? Or do you want to randomly mix the songs in the list without regard to priority as long as there are more higher priority songs than lower?

There are a couple of ways to tackle this, we just need to be sure of the requirements so we don't paint ourselves into a corner. We can tackle the UPDATE once we figure out the SELECT.

bschultz · April 6, 2011

Yes, priority 4 needs to play all the time. The songs are actually the variable defined elements...and the "commercials" are in the database. Songs won't change very often...and they can run 24-7 (easy to define in a variable) while the ads can only run during a particular hour (lunch time ads...breakfast ads...etc...). Priority 4 (in the database...commercial) is an ad that will only be used on the day of a "big" sale (not used very often at all...but need to account for it when it is needed)...where that is about the only ad that is played. Thus, it shows up a lot more often than any other priority. On a "regular" day, priority 3 (daypart specific) plays more than priority 2 (monthly special) or 1 (generic ad)...and priority 4 wouldn't play at all...because it wouldn't exist in the database (unless it's a big sale day.

Make sense?

I know that the query I wrote will likely return the same ads all the time...I hadn't written in the check to "last_play" since I didn't know how to update that part of the DB.

Thanks again for the help!!!!!

DavidAM · April 7, 2011

I was kind of bored, so I knocked this out for you. I think it satisfies the requirements as stated. I did not run it to test, and some of the column names in the queries may be off, but I think you can fix that. Post back if you have any questions. I tried to document it with comments, so it should be understandable. (Famous last words, right :intoxicated: )

I've made some assumptions:

[*]The audio table has a unique numeric identifier which I have called ID

[*]The priority column is numeric

[*]There is a last_played column and it has the datetime datatype

[*]You have already sanitized all of the inputs

[*]I did not include some error checking that should probably be added: for instance, the getCommercial() function returns false if it can't find anything, the calling code does not check for this

// ** CREATE SOME DUMMY DATA FOR TESTING
// Just fill the "songs" array with 10 songs
$songs = array();
for ($i = 1; $i <= 10; $i++) {
$songs[] = 'Song ' . $i;
}

// values for other variables needed
$client = 8;
$dow = 1;
// ** END DUMMY DATA FOR TESTING

/* array of the priority we want, and the fail-over priorities (in order of prefernce)
if what we want is not available
NOTE: the way the getCommercial() function is written, you could extend each
nested array, to multiple failovers (as I did with the third one)
You could also provide a single value (as in the second array here) if
you are sure it will exist. */
$priorities = array( array(4, 3), array(3), array(4, 2, 3), array(4, 1) );

// collect the songs/commercials in an array to output later
$playlist = array();

// walk the song list and merge in commercials
/* NOTE: We carry an index into the priorities array because we will cycle
through it multiple times. */
$priInd = 0;
$priCnt = count($priorities);
foreach ($songs as $title) {
$playlist[] = $title;
$playlist[] = getCommercial($client, $dow, $priorities[$priInd++]);
if ($priInd >= $priCnt) $priInd = 0;
}

print_r($playlist);

// ** DONE ** //

// ** FUNCTOINS ** //

/* Get a commercial from the database.
$priority is an array - the first element is the priority we want;
the remaining elements is/are the priority we will take if the priority
we want is not available (in order of preference). We build a CASE
statement to generate a sort order based on the order in this array */
function getCommercial($client, $dow, $priorities) {

// Calculate the date and hour (or pass them in as parameters)
$nownohour = date('Y-m-d'); // or whatever
$hournozero = date('h'); // or whatever

/* convert the priorities array to a comma-separated list for the IN phrase
and a CASE statement for the sort order */
$priIN = implode(',', $priorities);
$priCASE = 'CASE `priority` ';
foreach ($priorities as $key => $value) {
$priCASE .= ' WHEN ' . $value . ' THEN ' . $key;
}
$priCASE .= ' END AS priSort';

// NOTE: we capture the unique ID of the song for the update
// NOTE: I added a condition to exclude any commercials played in the last 10 minutes
$sql = "SELECT ID, title, $priCASE FROM audio
WHERE `client_id` = '$client'
AND (`start_date` <= '$nownohour' AND `end_date` >= '$nownohour')
AND `$dow` = '1' AND `is_active` = '1'
AND (`start_hour` <= '$hournozero' AND `end_hour` >= '$hournozero')
AND `priority` IN ($priIN)
AND DATE(last_played) < DATE_SUB(NOW(), INTERVAL 10 MINUTE)
ORDER BY priSort, last_played
LIMIT 1";

// Return false if we don't find anything at all
$title = false;
$rs = mysql_query($sql);
if (! $rs === false) {
// we only want one row, so no while loop
if ($row = mysql_fetch_assoc($rs)) {
$id = $row['ID'];
$title = $row['title'];
$sqlUp = "UPDATE audio SET last_played = NOW() WHERE ID = $id";
mysql_query($sqlUp);
}
}

return $title;
}

The tricky part is the priorities. We create a multi-dimensional array of priorities and fail-overs, in the order that we want to request them. We pass the elements (which are, themselves, arrays) in to getCommercial() in sequence. In the function we build an IN phrase (i.e. AND priority IN (4, 3)) to select the ones we want and we build a CASE statement for the sort order (i.e. CASE priority WHEN 4 THEN 0 WHEN 3 THEN 1 END AS priSort ... ORDER BY priSort) -- So the query will find priority 4's and 3's that satisfy the parameters and then return the 4's first, then the 3's. But we put a LIMIT of 1 on the query, so we will get the first row (which will be a 4 if one was found).

bschultz · April 7, 2011

All of your assumptions are correct...except that the ID field is actually called row_number. Thanks a bunch for the help. I'll give it a whirl tomorrow.

This is what happens when you're a radio announcer that dabbles in php. The boss finds out, and tells you to find a way to make something work...and I don't know how!

To be honest, what you wrote makes very little sense to me...well, each part makes sense...just not how they all work together...but it helps me learn. Thanks again, and I'm sure I'll be back tomorrow with more questions.

bschultz · April 7, 2011

DavidAM...

Now I was bored...so I started messing with this tonight. Here's the updated code (with the changes of ID is now row_number...and the songs in the array...and adding the db connect and username and password to the function).

<?php



// ** CREATE SOME DUMMY DATA FOR TESTING
// Just fill the "songs" array with 10 songs
$songs = array('01.mp3', '02.mp3', '03.mp3', '04.mp3', '05.mp3', '06.mp3', '07.mp3', '08.mp3', '09.mp3', '10.mp3');
for ($i = 1; $i <= 10; $i++) {
	$songs[] = 'Song ' . $i;
}

// values for other variables needed
$client = 4;



// ** END DUMMY DATA FOR TESTING

/* array of the priority we want, and the fail-over priorities (in order of prefernce)
if what we want is not available 
NOTE: the way the getCommercial() function is written, you could extend each
		nested array, to multiple failovers (as I did with the third one)
	You could also provide a single value (as in the second array here) if
		you are sure it will exist. */
$priorities = array( array(4, 3), array(3), array(4, 2, 3), array(4, 1) );

// collect the songs/commercials in an array to output later
$playlist = array();

// walk the song list and merge in commercials
/* NOTE: We carry an index into the priorities array because we will cycle
through it multiple times. */
$priInd = 0;
$priCnt = count($priorities);
foreach ($songs as $title) {
$playlist[] = $title;
$playlist[] = getCommercial($client, $dow, $priorities[$priInd++]);
if ($priInd >= $priCnt) $priInd = 0;
}

print_r($playlist);

// ** DONE ** //

// ** FUNCTOINS ** //

/* Get a commercial from the database. 
	$priority is an array - the first element is the priority we want;
		the remaining elements is/are the priority we will take if the priority
		we want is not available (in order of preference). We build a CASE 
		statement to generate a sort order based on the order in this array */
function getCommercial($client, $dow, $priorities) {
$dbc = mysql_pconnect('xxx', 'xxx', 'xxx');  
mysql_select_db('popaudio',$dbc); 
// Calculate the date and hour (or pass them in as parameters)
$nownohour = date('Y-m-d');  // or whatever
$hournozero = date('h'); // or whatever
    $now = date('Y-m-d');
    $dow = date('l');
    $hour = date('G');	
/* convert the priorities array to a comma-separated list for the IN phrase
	and a CASE statement for the sort order */
$priIN = implode(',', $priorities);
$priCASE = 'CASE `priority` ';
foreach ($priorities as $key => $value) {
	$priCASE .= ' WHEN ' . $value . ' THEN ' . $key;
}
$priCASE .= ' END AS priSort';

// NOTE: we capture the unique ID of the song for the update 
// NOTE: I added a condition to exclude any commercials played in the last 10 minutes
$sql = "SELECT row_number, title, $priCASE FROM audio
		WHERE `client_id` = '$client' 
		AND (`start_date` <= '$now' AND `end_date` >= '$now') 
		AND `$dow` = '1' AND `is_active` = '1' 
		AND (`start_hour` <= '$hour' AND `end_hour` >= '$hour') 
		AND `priority` IN ($priIN) 
		AND DATE(last_played) < DATE_SUB(NOW(), INTERVAL 10 MINUTE)
		ORDER BY priSort, last_play
		LIMIT 1";

// Return false if we don't find anything at all
$title = false;
$rs = mysql_query($sql);
if (! $rs === false) {
	// we only want one row, so no while loop
	if ($row = mysql_fetch_assoc($rs)) {
		$id = $row['row_number'];
		$title = $row['title'];
		$sqlUp = "UPDATE audio SET last_play = NOW() WHERE row_number = $id";
		mysql_query($sqlUp);
	}
}

return $title;
}
?>

Here's what I get:

Array ( [0] => 01.mp3 [1] => [2] => 02.mp3 [3] => [4] => 03.mp3 [5] => [6] => 04.mp3 [7] => [8] => 05.mp3 [9] => [10] => 06.mp3 [11] => [12] => 07.mp3 [13] => [14] => 08.mp3 [15] => [16] => 09.mp3 [17] => [18] => 10.mp3 [19] => [20] => Song 1 [21] => [22] => Song 2 [23] => [24] => Song 3 [25] => [26] => Song 4 [27] => [28] => Song 5 [29] => [30] => Song 6 [31] => [32] => Song 7 [33] => [34] => Song 8 [35] => [36] => Song 9 [37] => [38] => Song 10 [39] => )

The array order doesn't make any sense to me...and none of the "commercial" names are in there. Did I miss something?

DavidAM · April 7, 2011

Looks like all of the even numbers in the array (which should be the commercials) are blank. Actually, they are probably false, which means the getCommercial() function failed to find anything.

Do you have error reporting turned on? And did you get any errors?

Not related to the problem, but since you put in your song titles, you can take out the for loop immediately below it. so the start of the that script would look like:

 // Turn on error reporting for development
error_reporting(E_ALL);
ini_set('display_errors', 1);

// ** CREATE SOME DUMMY DATA FOR TESTING
// Just fill the "songs" array with 10 songs
$songs = array('01.mp3', '02.mp3', '03.mp3', '04.mp3', '05.mp3', '06.mp3', '07.mp3', '08.mp3', '09.mp3', '10.mp3');
/* WE DON'T NEED THIS FOR LOOP ANYMORE
for ($i = 1; $i <= 10; $i++) {
	$songs[] = 'Song ' . $i;
}
*/

To find out why the query is failing, we need to change the function just a little. Where we execute the query, change the IF statement and output the query and mysql_error() message:

	// Return false if we don't find anything at all
$title = false;
$rs = mysql_query($sql);
// Change this IF and show any error
if ($rs === false) {
	echo "Query Failed: $sql <BR />" . mysql_error();
} else {
	// we only want one row, so no while loop
	if ($row = mysql_fetch_assoc($rs)) {

we might as well add a check on the UPDATE statement too, just to be sure:

			$sqlUp = "UPDATE audio SET last_play = NOW() WHERE row_number = $id";
		if (! mysql_query($sqlUp)) {
			echo "Update Failed: $sql <BR />" . mysql_error();
		}

bschultz · April 7, 2011

I was getting a bunch of "undeclared variable" errors...even though the variables were defined. So, I declared them again...and am getting the same results now.

Array ( [0] => 01.mp3 [1] => [2] => 02.mp3 [3] => [4] => 03.mp3 [5] => [6] => 04.mp3 [7] => [8] => 05.mp3 [9] => [10] => 06.mp3 [11] => [12] => 07.mp3 [13] => [14] => 08.mp3 [15] => [16] => 09.mp3 [17] => [18] => 10.mp3 [19] => )

<?php

$client = 4;
$now = date('Y-m-d');
$dow = date('l');
$hour = date('G');


// Turn on error reporting for development
error_reporting(E_ALL);
ini_set('display_errors', 1);

// ** CREATE SOME DUMMY DATA FOR TESTING
// Just fill the "songs" array with 10 songs
$songs = array('01.mp3', '02.mp3', '03.mp3', '04.mp3', '05.mp3', '06.mp3', '07.mp3', '08.mp3', '09.mp3', '10.mp3');
/* WE DON'T NEED THIS FOR LOOP ANYMORE
for ($i = 1; $i <= 10; $i++) {
	$songs[] = 'Song ' . $i;
}

// values for other variables needed




// ** END DUMMY DATA FOR TESTING

/* array of the priority we want, and the fail-over priorities (in order of prefernce)
if what we want is not available 
NOTE: the way the getCommercial() function is written, you could extend each
		nested array, to multiple failovers (as I did with the third one)
	You could also provide a single value (as in the second array here) if
		you are sure it will exist. */
$priorities = array( array(4, 3), array(3), array(4, 2, 3), array(4, 1) );

// collect the songs/commercials in an array to output later
$playlist = array();

// walk the song list and merge in commercials
/* NOTE: We carry an index into the priorities array because we will cycle
through it multiple times. */
$priInd = 0;
$priCnt = count($priorities);
foreach ($songs as $title) {
$playlist[] = $title;
$playlist[] = getCommercial($client, $dow, $priorities[$priInd++]);
if ($priInd >= $priCnt) $priInd = 0;
}

print_r($playlist);

// ** DONE ** //

// ** FUNCTOINS ** //

/* Get a commercial from the database. 
	$priority is an array - the first element is the priority we want;
		the remaining elements is/are the priority we will take if the priority
		we want is not available (in order of preference). We build a CASE 
		statement to generate a sort order based on the order in this array */
function getCommercial($client, $dow, $priorities) {
$dbc = mysql_pconnect('xxx', 'xxx', 'xxx');  
mysql_select_db('popaudio',$dbc); 
$client = 4;
$now = date('Y-m-d');
$dow = date('l');
$hour = date('G');
// Calculate the date and hour (or pass them in as parameters)
$nownohour = date('Y-m-d');  // or whatever
$hournozero = date('h'); // or whatever

/* convert the priorities array to a comma-separated list for the IN phrase
	and a CASE statement for the sort order */
$priIN = implode(',', $priorities);
$priCASE = 'CASE `priority` ';
foreach ($priorities as $key => $value) {
	$priCASE .= ' WHEN ' . $value . ' THEN ' . $key;
}
$priCASE .= ' END AS priSort';

// NOTE: we capture the unique ID of the song for the update 
// NOTE: I added a condition to exclude any commercials played in the last 10 minutes
$sql = "SELECT row_number, title, $priCASE FROM audio
		WHERE `client_id` = '$client' 
		AND (`start_date` <= '$now' AND `end_date` >= '$now') 
		AND `$dow` = '1' AND `is_active` = '1' 
		AND (`start_hour` <= '$hour' AND `end_hour` >= '$hour') 
		AND `priority` IN ($priIN) 
		AND DATE(last_play) < DATE_SUB(NOW(), INTERVAL 10 MINUTE)
		ORDER BY priSort, last_play
		LIMIT 1";

// Return false if we don't find anything at all
$title = false;
$rs = mysql_query($sql);
// Change this IF and show any error
if ($rs === false) {
	echo "Query Failed: $sql <BR />" . mysql_error();
} else {
	// we only want one row, so no while loop
	if ($row = mysql_fetch_assoc($rs)) {
       $id = $row['row_number'];
		$sqlUp = "UPDATE audio SET last_play = NOW() WHERE row_number = $id";
		if (! mysql_query($sqlUp)) {
			echo "Update Failed: $sql <BR />" . mysql_error();
		}
} }

return $title;
}
?>

DavidAM · April 7, 2011

Yeah, variables defined OUTSIDE of a function are not available INSIDE the function unless you pass them as parameters. We can add the $dow and $now and $hour to the function parameter list once we get things working. Or if you are taking them straight from the current date, we can use the appropriate SQL functions. But let's see what's wrong with the query first.

If you didn't get any "Query Failed" messages, then it would appear that the query is not returning any rows. So let's take a look at the query:

	$sql = "SELECT row_number, title, $priCASE FROM audio
		WHERE `client_id` = '$client' 
		AND (`start_date` <= '$now' AND `end_date` >= '$now') 
		AND `$dow` = '1' AND `is_active` = '1' 
		AND (`start_hour` <= '$hour' AND `end_hour` >= '$hour') 
		AND `priority` IN ($priIN) 
		AND DATE(last_play) < DATE_SUB(NOW(), INTERVAL 10 MINUTE)
		ORDER BY priSort, last_play
		LIMIT 1";

	echo $sql . '<BR />';

Are you sure you have data in the database that matches the criteria?

Add that echo statement so we can see the query (you'll take it out when we get it working). You can copy the query and run it directly against the database to see if you get anything. Post it here and I'll take a look. I wondered if I had setup that CASE statement correctly, but it sounds like you didn't get any error messages about the query.

I added the condition for DATE(last_play) < 10 minutes ago. We might want to take that out, at least until we get it working. Multiple test runs, one after the other, can likely happen in less than 10 minutes and you might exclude everything. Since we order by last_play, we're going to get the oldest one anyway - except that the sort is within the priority, so higher priorities will repeat more often. That is, if you only have 1 priority 4, it will come up every time you ask for a priority 4 and never fail over; with the 10 minute limit in there, you would not be repeating the same commercial after every song.

bschultz · April 7, 2011

Here is one of the selects as it "echoed"

SELECT row_number, title, CASE `priority` WHEN 4 THEN 0 WHEN 3 THEN 1 END AS priSort FROM audio WHERE `client_id` = '4' AND (`start_date` <= '2011-04-07' AND `end_date` >= '2011-04-07') AND `Thursday` = '1' AND `is_active` = '1' AND (`start_hour` <= '12' AND `end_hour` >= '12') AND `priority` IN (4,3) AND DATE(last_play) < DATE_SUB(NOW(), INTERVAL 10 MINUTE) ORDER BY priSort, last_play LIMIT 1

It actually looks ok to me...but I don't know what priSort does...

DavidAM · April 7, 2011

Yeah, it looks OK to me, too.

SELECT row_number, title, 
CASE `priority` WHEN 4 THEN 0 WHEN 3 THEN 1 END AS priSort 
FROM audio 
WHERE `client_id` = '4' 
AND (`start_date` <= '2011-04-07' AND `end_date` >= '2011-04-07') 
AND `Thursday` = '1' 
AND `is_active` = '1' 
AND (`start_hour` <= '12' AND `end_hour` >= '12') 
AND `priority` IN (4,3) 
AND DATE(last_play) < DATE_SUB(NOW(), INTERVAL 10 MINUTE) 
ORDER BY priSort, last_play 
LIMIT 1

Questions:

AND (`start_date` <= '2011-04-07' AND `end_date` >= '2011-04-07')

are start_date and end_date actually defined with the DATE or DATETIME datatype? If not, they should be.

You actually have columns named after the days of the week? I think you would get an sql error if not, but I may as well ask.

AND (`start_hour` <= '12' AND `end_hour` >= '12')

start_hour and end_hour have the INTEGER datatype?

AND DATE(last_play) < DATE_SUB(NOW(), INTERVAL 10 MINUTE)

last_play has the DATETIME data type?

Are you sure there is data in the database that satisfies ALL of those requirements?

Can you run this directly against your database, using mySqlAdmin or something to see what happens. Maybe pull the AND's out one at a time until you find the one that is breaking it?

priSort (a long-winded explanation)

You stated a while back that you wanted specific priorities with specific failovers if the requested priority was not available.

Here's what the final playlist needs to look like:

- mp3 from database priority 4 (or 3, if priority 4 does not exist)

- other mp3 #1 (set in a variable)

- mp3 from database priority 4 (or 2, if priority 4 does not exist)

- other mp3 #2 (set in a variable)

- mp3 from database priority 3 (will always exist)

- other mp3 #3 (set in a variable)

- mp3 from database priority 4 (or 1, if priority 4 does not exist)

- other mp3 #4 (set in a variable)

So I build this array of priority requests with failovers (oops, I got the second and third ones swapped, but hopefully, you'll understand it well enough to make adjustments as needed).

$priorities = array( 
array(4, 3), 	// Get a priority 4. If not available, get a priority 3
array(3), 	// Get a priority 3 (will always exist)
array(4, 2, 3), // Get a priority 4. If not available, get a priority 2
		// I added priority 3 here to show we could use more than one
		// failover - so, if priority 2 is not available get a priority 3
array(4, 1) 	// Get a priority 4. If not available, get a priority 1
);

While walking the list of songs, we use the $priInd to walk this list of priorities. We increment the index ($priInd++) - the postfix ++ (after the $priInd) means use the current value and then increment (by one). We then test to see if $priInd has reached the end of the $priorities list, and if so, we set it to zero (back to the beginning of the list). So we are passing one of the four priority/failover arrays to the getCommercial function.

The first time this function is called, $priorities INSIDE THE FUNCTION will be an array containing 4 and 3. The second time, it is an array with one element which is 3. The third time, it is 4, 2, and 3. Next it gets 4 and 1. Then the next time, we have restarted the list, so it gets 4 and 3 again.

We do two things with this array. We use it to build the IN phrase to restrict the SELECT to just those priorities. This is done with the implode() function. You see that in the query you pasted here as AND `priority` IN (4,3).

The other thing we do is build a CASE statement so we can sort the values we get back. Why? We wanted to do this with a single query, so we used the IN phrase to SELECT multiple priorities. But we need those priorities in a specific order. Well, based on your requirements, we could have just sorted on priority DESCENDING and we would always get the higher priority first. But I tend to try and make the code as generic as possible. In the array -- array(4,3) -- the first element has index 0 (zero) and the second element has index 1 (one). So we write this case statement: CASE `priority` WHEN 4 THEN 0 WHEN 3 THEN 1 END AS priSort which says, (for each row that we retrieve) if the priority is 4 return the value 0 (zero), if the priority is 3, return the value 1 (one) -- we limited the query to 4 and 3, so we don't need to worry about anything else. So, we are returning a new column "AS priSort" with either a 0 or a 1 value. The "AS priSort" is just giving this new column a name so we can refer to it, it's called an ALIAS. Fortunately, mySql lets us use aliases in the ORDER BY. So we ORDER BY priSort. Since 0 comes before 1 (the values assigned to priSort), then the 4's will come BEFORE the 3's (in this case). If there are no 4's, then we only get 3's, so we can just use the first row returned.

The beauty of this arrangement is that we can define a priority/failover of array(2, 4, 1, 3) and get the rows back in that order -- any 2's that exist will be first, then any 4's that exist, then any 1's that exist, then any 3's.

I hope that clears it up a bit. I know its complex, but it is an elegant (if I may say so) single-query solution to the problem.

DavidAM · April 7, 2011

HOLD THE PRESSES:

There seems to be a line of code missing in the function. We need to assign the value from the query to the $title variable:

	$title = false;
$rs = mysql_query($sql);
// Change this IF and show any error
if ($rs === false) {
	echo "Query Failed: $sql <BR />" . mysql_error();
} else {
	// we only want one row, so no while loop
	if ($row = mysql_fetch_assoc($rs)) {
		$id = $row['row_number'];

// SOMEHOW THIS LINE GOT LOST
		$title = $row['title'];
// THAT ONE JUST ABOVE HERE

		$sqlUp = "UPDATE audio SET last_play = NOW() WHERE row_number = $id";
		if (! mysql_query($sqlUp)) {
			echo "Update Failed: $sql <BR />" . mysql_error();
		}
	} 
}

return $title;

bschultz · April 7, 2011

$title = $row['title']; did the trick...I have ads!

But, here's the array:

Array ( [0] => 01.mp3 [1] => [2] => 02.mp3 [3] => [4] => 03.mp3 [5] => cencfish [6] => 04.mp3 [7] => cenccha [8] => 05.mp3 [9] => [10] => 06.mp3 [11] => [12] => 07.mp3 [13] => cencglv1 [14] => 08.mp3 [15] => cencfuel [16] => 09.mp3 [17] => [18] => 10.mp3 [19] => )

It has three songs from the song array at once...then an add...then song 5 , 6 and 7 are next to each other...then an add...and song 9 and 10 are next to each other. How do we get an ad from the DB between each of the songs?

Now, I did just notice another problem...the music is in the same order all the time (which makes sense, since it's an array. Any way to make that random? I can probably figure that part out...just use RAND() on the results, correct?

Also, the playlist needs to have each item on a separate line...not in the array format it's in now. How would I take each element of the final array and put it like this:

item 1

item 2

item 3

...and so on?

You have been a HUGE help on this! I appreciate it a lot!

DavidAM · April 7, 2011

First the easy stuff:

1) You might want to add ".mp3" to the title assignment: $title = $row['title'] . '.mp3'; - you were doing that in your original code.

2) use shuffle($songs); to randomly rearrange the elements of the array.

$songs = array('01.mp3', '02.mp3', '03.mp3', '04.mp3', '05.mp3', '06.mp3', '07.mp3', '08.mp3', '09.mp3', '10.mp3');
shuffle($songs);

3) use a foreach loop to walk the array and output the results:

foreach ($playlist as $title) {
  echo $title . '<BR />';
}

On the missing commercials: It appears that the first two priority/failovers are not returning anything. In the last code you posted, we had:

$priorities = array( array(4, 3), array(3), array(4, 2, 3), array(4, 1) );

So if the first two are finding nothing and the last two are finding songs; that would indicate that there are no 4's or 3' being found (unless you changed the order or values of this array).

Array (
[0] => 01.mp3 
[1] => 				// array(4, 3)
[2] => 02.mp3 
[3] => 				// array(3)
[4] => 03.mp3 
[5] => cencfish 		// array(4, 2, 3)
[6] => 04.mp3 
[7] => cenccha 			// array(4, 1)
[8] => 05.mp3 
[9] => 				// array(4, 3)
[10] => 06.mp3 
[11] => 			// array(3)
[12] => 07.mp3 
[13] => cencglv1		// array(4, 2, 3)
[14] => 08.mp3 
[15] => cencfuel 		// array(4, 1)
[16] => 09.mp3 
[17] => 			// array(4, 3)
[18] => 10.mp3 
[19] => 			// array(3)
)

Have a look at the data in the database, especially the 4's and 3's. Something is preventing them from being returned.

bschultz · April 8, 2011

As of right now, there are NO priority 4's (for testing), one priority 3, six priority 2's and five priority 1's...but the only priority 3 is a Monday only commercial.

So, I changed one of the ads that was showing up before (cencdeli) to a priority three...and there it is...AMAZING! Sorry about that!

If anybody wants the final version...here it is.

<?php

$client = 4;
$now = date('Y-m-d');
$dow = date('l');
$hour = date('G');


// Turn on error reporting for development
error_reporting(E_ALL);
ini_set('display_errors', 1);

// ** CREATE SOME DUMMY DATA FOR TESTING
// Just fill the "songs" array with 10 songs
$songs = array('01.mp3', '02.mp3', '03.mp3', '04.mp3', '05.mp3', '06.mp3', '07.mp3', '08.mp3', '09.mp3', '10.mp3');

shuffle($songs);

/* array of the priority we want, and the fail-over priorities (in order of prefernce)
if what we want is not available 
NOTE: the way the getCommercial() function is written, you could extend each
		nested array, to multiple failovers (as I did with the third one)
	You could also provide a single value (as in the second array here) if
		you are sure it will exist. */
$priorities = array( array(4, 3), array(3), array(4, 2, 3), array(4, 1) );

// collect the songs/commercials in an array to output later
$playlist = array();

// walk the song list and merge in commercials
/* NOTE: We carry an index into the priorities array because we will cycle
through it multiple times. */
$priInd = 0;
$priCnt = count($priorities);
foreach ($songs as $title) {
$playlist[] = $title;
$playlist[] = getCommercial($client, $dow, $priorities[$priInd++]);
if ($priInd >= $priCnt) $priInd = 0;
}

//print_r($playlist);           I think this echo's the songs...but not sure
foreach ($playlist as $title) {
  echo $title . '<BR />';
}

// ** DONE ** //

// ** FUNCTOINS ** //

/* Get a commercial from the database. 
	$priority is an array - the first element is the priority we want;
		the remaining elements is/are the priority we will take if the priority
		we want is not available (in order of preference). We build a CASE 
		statement to generate a sort order based on the order in this array */
function getCommercial($client, $dow, $priorities) {
$dbc = mysql_pconnect('xxx', 'xxx', 'xxx');  
mysql_select_db('popaudio',$dbc); 
$client = 4;
$now = date('Y-m-d');
$dow = date('l');
$hour = date('G');
// Calculate the date and hour (or pass them in as parameters)
$nownohour = date('Y-m-d');  // or whatever
$hournozero = date('h'); // or whatever

/* convert the priorities array to a comma-separated list for the IN phrase
	and a CASE statement for the sort order */
$priIN = implode(',', $priorities);
$priCASE = 'CASE `priority` ';
foreach ($priorities as $key => $value) {
	$priCASE .= ' WHEN ' . $value . ' THEN ' . $key;
}
$priCASE .= ' END AS priSort';

// NOTE: we capture the unique ID of the song for the update 
// NOTE: I added a condition to exclude any commercials played in the last 10 minutes
$sql = "SELECT row_number, title, $priCASE FROM audio
		WHERE `client_id` = '$client' 
		AND (`start_date` <= '$now' AND `end_date` >= '$now') 
		AND `$dow` = '1' AND `is_active` = '1' 
		AND (`start_hour` <= '$hour' AND `end_hour` >= '$hour') 
		AND `priority` IN ($priIN) 
		AND DATE(last_play) < DATE_SUB(NOW(), INTERVAL 10 MINUTE)
		ORDER BY priSort, last_play
		LIMIT 1";
// echo $sql . '<BR />';
// Return false if we don't find anything at all
$title = false;
$rs = mysql_query($sql);
// Change this IF and show any error
if ($rs === false) {
	echo "Query Failed: $sql <BR />" . mysql_error();
} else {
	// we only want one row, so no while loop
	if ($row = mysql_fetch_assoc($rs)) {
       $id = $row['row_number'];

$title = $row['title'] . '.mp3';

		$sqlUp = "UPDATE audio SET last_play = NOW() WHERE row_number = $id";
		if (! mysql_query($sqlUp)) {
			echo "Update Failed: $sql <BR />" . mysql_error();


		} 
} }

 return $title;
{  

}
}

?>

David, thanks again!

bschultz · June 21, 2011

I've now been asked by my bosses to add a feature! Why can't they ever think of EVERYTHING at once?

So, the code above has this order:

song

commercial

song

commercial

etc...

Now, they want this:

song

feature (this is new)

commercial

song

feature (this is new)

commercial

etc...

How can I add more rotations of songs...without messing with the order of the commercials and AND features?

Sign In

Need help cleaning up this code

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information