Problem with removing a SQL row


#1

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


#2

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


#3

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.


#4

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!


#5

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