deleting rows...

Dan39

I am trying to delete rows from the table albums if in the row album is blank, so I tryed using this...

		$query = 'DELETE FROM albums WHERE album = ""';
		mysql_query($query) or die("couldnt delete dude <br />" . mysql_error());

and it did not work... none of the rows where album was blank were deleted...

harmor

I think the way to do this is make a while loop and delete records where album is blank.

$query = mysql_query("SELECT * FROM albums WHERE albumid ")or die(mysql_error());
    while($row = mysql_fetch_array($query))
{
   if (empty($row[album]))
    {
         mysql_query("DELETE FROM albums WHERE albumid=$row[albumid]");
    }
}

SQL_Maestro

this code just kills not-empty records!!!

If the rows in table really matter as an empty strings, the original code should work properly. Probably the white spaces or NULL there are stored?

you can try to use following code:

DELETE FROM albums WHERE !LENGTH(TRIM(IFNULL(album,'')))

Sxooter

First thing I see if you are using the wrong quote character. In SQL, " " quotes go around identifiers, like table names and such (except in MySQL, where some idiot originally decided to use ` to do that.. urg, but newer versions work correctly, at least if you set some run time flags I think. double urg). The correct character to quote textual data is the ' character.

HOWEVER, the problem may not be that they are blank. The problem is likely that they are NULL. NULL does not equal blank. NULL, in basic terms, means "there's a value, we just don't know what it is". you can delete records with NULL fields with:

delete from sometable where somefield IS NULL.

Note that = NULL won't work, because nothing equals NULL, not even another NULL. for example, is my hair color the same as yours? We don't know. It might be, it might not be, so the answer to the quest is my hair color (unknown, hence NULL) equal to your hair color (also unknown, hence, also unknown, hence also NULL) is unkown (hence, NULL)

Null is a tricky concept at first, especially to many programmers who deal with mostly black and white answers.

csn

NULL can be a biotch! For example, I want to create a unique constraint on a table:

UNIQUE (name, site_id, parent_id)

But parent_id can be NULL. So, this constraint allows:

abc|1|null
abc|1|null
def|1|null
def|1|null
...

Know how to make this work, Sxooter?

I also occasionally get bit by NULL in where clauses with AND's.

Dan39

its still not working when i say its NULL, but lemme explain the situation more....
i am adding a big list of things to the database from a textarea and using the explode function to seperatly add each line into the db... so if someone has a blank line it adds like nothing, or NULL, or i dunno maybe just a new line character... so i am trying to remove those...

i tried

mysql_query("DELETE FROM albums WHERE album IS NULL") or die(mysql_error());

and that didnt work

Sxooter

OK, if you're inserting '' then you're not inserting a NULL. you can insert a NULL in one of two ways, normally. Either use the NULL keyword unquoted, or leave the field out of the list of fields to be inserted into:

create table a (b int, c text);
insert into a (b) values (1); -- inserts NULL into c
insert into a (c) values ('abc'); -- inserts NULL into b
insert into a (a,b) values (12,NULL);
insert into a (a,b) values (NULL,'more text');

Otherwise it's blank, OR a variable number of spaces. If it's three spaces, you can find out how many with some kind of regular expression matching. I know MySQL has pattern matching, I just don't know the syntax off the top of my head. You can use length(fieldname) to see how long they are if you're interested, but all you need is the regular expression to delete them. Make sure you backup your data first in case you wind up deleting it all.