Check if mysql DB has info in a field

big-dog

I need to know how to check if a field in mysql has information in it and if it doesnt then that row gets deleted.

Piranha

DELETE FROM table WHERE column IS NULL

big-dog

I tried

DELETE FROM Comment WHERE Testimonials IS NULL
and
DELETE FROM Comment WHERE Testimonials IS NULL

and it didnt delete any thing. didnt give me error either. what am I doing wrong

Piranha

Nothing. All your rows have information in them. Of course, it may be an empty string they have, if it is then you should delete where something = "". Just be careful and try it out on the development server first, it will completely remove the rows.

big-dog

this deleted all rows not just the rows where Testimonials was empty
DELETE FROM Comment WHERE Testimonials = ""
All my rows will have info in them except I have 2 colums that either 1 or the other will have info. I need to just delete the 1 that doesnt have info in the certain colum.

bradgrafelman

Just as a side note - if you want to see what rows will be deleted rather than just deleting them, change the "DELETE" statement to a "SELECT *" one.

I don't understand why the above query wouldn't work, but maybe try something like this:

DELETE FROM `Comment` WHERE LENGTH(`Testimonials`) = 0;

EDIT: And if that doesn't work, are you sure there isn't just a space or something in those columns? Try this:

SELECT *, LENGTH(`Testimonials`) FROM `Comment`

Verify that the lengths for the rows that you think don't have any data in that column are actually 0.

big-dog

bradgrafelman
I tried your solution

DELETE FROM `Comment` WHERE LENGTH(`Testimonial`) = 0;

and it worked on the DB.
But I put that in my php webpage and it doesnt work.
Basicly what Im trying to do with this page is pull the rows where Testimonial has info and display it. If Testimonial' is blank or null then it doesnt display any thing. can someone see where im messed up at?

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$sql = 'DELETE FROM `Comment` WHERE LENGTH(`Testimonial`) = 0';
$sql = 'SELECT Testimonial , First_Name , Last_Name , Company , City , State , Date FROM Comment ORDER BY Date DESC LIMIT 0, 30 '; 
$result=mysql_query($sql); 

while($data = mysql_fetch_assoc($result)) { 
    echo "<b>{$data['Testimonial']}<br>{$data['First_Name']} {$data['Last_Name']} {$data['Company']} {$data['City']} {$data['State']}<br><hr>"; 
}

mysql_close();

?>

big-dog

OK
I got it worked out I think. I forgot mysql_query($sql); after the delete line.
As always THANKS for all of your guys help.

bradgrafelman

big-dig wrote:
I got it worked out I think. I forgot mysql_query($sql); after the delete line.

I'm wondering if that wasn't your problem with previous suggestions, as they should have worked.

By the way - the string localhost should be quoted (as all strings should) in your mysql_connect() call.

Don't forget to mark this thread resolved.

big-dog

In the prier attemps I was in mysql DB, it wasnt till it worked there I put it in the php page and tried it. But its still working 🙂
Can you tell me why I need to quote the localhost line? Trying to learn is why I ask.

bradgrafelman

big-dog wrote:
Can you tell me why I need to quote the localhost line?

Because you're giving it the [man]string[/man] "localhost". Without the quotes, you're telling PHP to look for a constant ([man]language.constants[/man]) named localhost. Failing to find such a constant, PHP will generate an E_NOTICE level error and then assume 'localhost' is a string.