Problem with removing a SQL row

Hi ya peepz,

Been trolling the net for hours trying to find a solution to my problem.
I am currently running a SHOUTcast server on my home PC and I have a SQL database installed so that all the tracks that are played are logged for future reference.

The problme I have is when no-one is connected, now and again the track title will get logged as blank and then the last track is logged again, so I start to get a bit of spam, you can see it here:

http://dz.game-host.org/radio/index.php

basically the page refreshes every 30 seconds and I am trying to get a bit of code that looks for a blank field in “songTitle” and then deletes that row automatically, I can do it manually but thats a pain as I want it to run maintainance free and this is the last problem I face (I hope!)

I have tried

[code]//mysql connection settings
$connection = mysql_connect($castithost,$castituser,$castitpass)
or die(“Couldn’t connect.”);

$db = mysql_select_db($castitdb,$connection) or die (“couldnt select database”);

//SQL Statement
$sql = 'SELECT * FROM ‘.$ctbl_prefix.‘songinfo ORDER BY time DESC limit 0,’.$sngs.’ ';

//Delete all rows that have blank track titles
$sqldel = 'DELETE * FROM '.$ctbl_prefix.'songinfo WHERE songTitle = “” ';

//Execute sql query and show results
$sql_result = mysql_query($sql,$connection,$sqldel) or die (“could not execute the query”);[/code]

Unfortunatly I’m no coder and its driving me insane, so I would appriciate any help that you could offer.

Thanks.

Edd

I would first check to see if the query is returning an error or if it is just not deleting any rows.

http://us2.php.net/manual/en/function.mysql-query.php

Additionally, you should make sure that the value stored in the database is not “NULL”, because I don’t believe that checking if songTitle = “” is the same as checking IS NULL.

http://dev.mysql.com/doc/refman/4.1/en/ … -null.html

You should also check the line that contains the DELETE query.

//Delete all rows that have blank track titles
$sqldel = 'DELETE * FROM '.$ctbl_prefix.'songinfo WHERE songTitle = "" '; 

I do believe it should NOT have the Asterisk in the query. That type of query would imply that you could selectively delete items in a record, ( sort of like DELETE username FROM table WHERE id = 123 ). The DELETE is an ALL OR NOTHING query. The entire record or none of it. So the * is not needed.

Your code (or at least the query part) should be as follows:

//Delete all rows that have blank track titles
$sqldel = 'DELETE FROM '.$ctbl_prefix.'songinfo WHERE songTitle = "" '; 

also as Monk points out you might want to check for a NULL as well which is NOT the same thing as “” (an empty string). Your query for that could be as follows:

//Delete all rows that have blank track titles
$sqldel = 'DELETE  FROM '.$ctbl_prefix.'songinfo WHERE songTitle = "" OR songTitle IS NULL'; 

If you know it will always be an empty string ( “” ) OR if it will always be a NULL, you should only check for that, as it would slow down your query to check for both.

Hi Guys,

Thanks for you infomation, I have just had another fiddle and got it working :slight_smile:

[code]//Delete all rows that have blank track titles
$sqldel = 'DELETE FROM '.$ctbl_prefix.‘songinfo WHERE songTitle = “” OR songTitle IS NULL’;
mysql_query($sqldel);

//Execute sql query and show results
$sql_result = mysql_query($sql,$connection) or die (“could not execute the query”);[/code]

so just pulled out the $sqldel from the Execute query bit and bingo its all working.

Thanks for the help, really do appriciate it!

Thanks for you help on the pervious problem, basically another problem has arrouse :stuck_out_tongue:

Thanks for any help you maybe able to assist with :)

Edd

Sponsor our Newsletter | Privacy Policy | Terms of Service