[MySQLi PHP] Compare variable to sql row

[vman315]

I am trying to build a shoutcast radio logger. I have it down and working, only my table looks like this 
I want to have a check to compare the last song to the current song as to not record the same song twice as it is still playing.
 
My table is made as such 

CREATE TABLE  `db`.`table` (
`pId` INT( 11 ) NOT NULL AUTO_INCREMENT ,
 `SongTitle` CHAR( 255 ) DEFAULT NULL ,
 `playedDate` DATE DEFAULT NULL ,
 `playedTime` TIME DEFAULT NULL ,
PRIMARY KEY (  `pId` )
)

My php script as such

<?php 
/*Date and Time*/
$datetime = new DateTime();
$timestamp = $datetime->format('H:i:s');
$datestamp = $datetime->format('Y-m-d');
/*Get song*/
$song = file_get_contents('http://radio.ponyvillelive.com:8008/currentsong?sid=1');
/*SQL Connect*/
$con = mysqli_connect("localhost","root","password","db");
/*Get the highest pID*/
$maxpId = mysqli_query($con,"SELECT MAX(pId) FROM `table`");
$maxpId = mysqli_fetch_array($maxpId, MYSQL_BOTH);
$maxpId = $maxpId[0];
/*Run query*/
mysqli_query($con,"INSERT INTO table (songtitle, playeddate, playedtime)
VALUES ('$song','$datestamp','$timestamp')");
/*Close Connection*/
mysql_close($con);
/*Echo variables*/
echo 'Song: '."$song".'<br>Date: '."$datestamp".'<br>Time: '."$timestamp".'<br>pId: '."$maxpId";
?>

I have looked at this http://forums.phpfreaks.com/topic/221701-compare-mysql-row-value-to-php-variable/ and tried using it however it seems I am missing something.

 

Basicly take the highest pId ($maxpId) from the table, comapare $song to that row's songtitle, then

if same {echo "Song is already in for this play. Nothing recorded";} else {echo "Song was recorded."; mysqli_query($con,"INSERT INTO table (songtitle, playeddate, playedtime) VALUES ('$song','$datestamp','$timestamp')");}

[Ch0cu3r]

You'll want to modify the SongTitle database field so its a unique/primary key. Then you can change your INSERT query to an INSERT IGNORE query, MySQL will then ignore any duplicate entries with the same song title

 

This may help you further.

http://www.tutorialspoint.com/mysql/mysql-handling-duplicates.htm

Edited September 24, 2013 by Ch0cu3r

[DavidAM]

You'll want to modify the SongTitle database field so its a unique/primary key. ...

That would certainly prevent "record(ing) the same song twice "; however, I suspect the OP means "same song twice in a row".

 

You need to get the title of the last song recorded:

SELECT SongTitle FROM `table` WHERE pId = (SELECT MAX(pId) FROM `table`)

However, I would not recommend using the pId for this. I would combine the playedDate and playedTime into a single DATETIME field, and use playedDateTime = (SELECT MAX(playedDateTime).

 

Furthermore, if this is a continuously running job, I would just save the "last song" in a PHP variable for immediate comparison instead of going back to the database repeatedly. To prevent duplicates when the script starts (say after a reboot or whatever), load the last song from the database at startup.

 

Oh, and give your table a meaningful name. "Table" is just not helpful, and creates the need to use the backticks whenever you query it.