a lil mysql help? (using phpMyAdmin) - GoFuckYourself.com

galleryseek · 12-05-2003, 02:27 PM

i'm trying to delete all the rows with "December 4, 2003" in the Date field... but when i run the below code, it just refreshes to the page, but doesn't delete anything:

DELETE FROM `crtgpcache` WHERE Date='December 4, 2003'

what should i change it to so it'll delete all the damn rows?

thx

Lane · 12-05-2003, 02:49 PM

what type is the field, date or datetime?

and try using '2003-12-04'

StuartD · 12-05-2003, 04:13 PM

If your code is done properly really, it should be done in mktime() dates.

Inwhich case... the dates in the database might look like this "1070524800"

So your query should look like "WHERE Date = '1070524800'

otherwise, as Lane said, it could be a date field which would be done as "2003-12-4" or something like that.

galleryseek · 12-05-2003, 11:19 PM

its a VarChar field... and i KNOW the data within hundreds of these fields, the Date field = "December 4, 2003".. i want them deleted! and my query in my initial post is not working.

KobyBoy · 12-05-2003, 11:27 PM

Do a SELECT COUNT(*) FROM crtgpcache WHERE Date = "December 4, 2003" This should return the number of rows that satsify your query. If nothing gets returned then you know there is nothing wrong with your DELETE query but rather there are no rows in the database that satisfy your condition.

galleryseek · 12-05-2003, 11:29 PM

Quote:

Originally posted by KobyBoy
Do a SELECT COUNT(*) FROM crtgpcache WHERE Date = "December 4, 2003" This should return the number of rows that satsify your query. If nothing gets returned then you know there is nothing wrong with your DELETE query but rather there are no rows in the database that satisfy your condition.

thanks, figured it out from that.. there was a damn space in each date entry at the beginning.

gornyhuy · 12-05-2003, 11:30 PM

Quote:

Originally posted by galleryseek

thanks, figured it out from that.. there was a damn space in each date entry at the beginning.

Happens to my clients all the time, in mysql and also in excel...

KobyBoy · 12-05-2003, 11:39 PM

Yeah, whitespaces could mess things up. Whenever I put something into the database and I know it's not supposed to have a space in the beginning or at the end (like dates, IP addresses, etc...) I use PHP's trim() function (http://www.php.net/manual/en/function.trim.php) to remove whitespace(s) from the beginning and the end of the string. This saves my butt all the time.

12-05-2003, 02:27 PM	#1
galleryseek Confirmed User Industry Role: Join Date: Mar 2002 Posts: 8,234	a lil mysql help? (using phpMyAdmin) i'm trying to delete all the rows with "December 4, 2003" in the Date field... but when i run the below code, it just refreshes to the page, but doesn't delete anything: DELETE FROM `crtgpcache` WHERE Date='December 4, 2003' what should i change it to so it'll delete all the damn rows? thx

12-05-2003, 02:49 PM	#2
Lane Will code for food... Join Date: Apr 2001 Location: Buckeye, AZ Posts: 8,496	what type is the field, date or datetime? and try using '2003-12-04' __________________

12-05-2003, 04:13 PM	#3
StuartD Sofa King Band Join Date: Jul 2002 Location: Outside the box Posts: 29,903	If your code is done properly really, it should be done in mktime() dates. Inwhich case... the dates in the database might look like this "1070524800" So your query should look like "WHERE Date = '1070524800' otherwise, as Lane said, it could be a date field which would be done as "2003-12-4" or something like that. __________________ This is me on facebook This is me on twitter

12-05-2003, 11:19 PM	#4
galleryseek Confirmed User Industry Role: Join Date: Mar 2002 Posts: 8,234	its a VarChar field... and i KNOW the data within hundreds of these fields, the Date field = "December 4, 2003".. i want them deleted! and my query in my initial post is not working.

12-05-2003, 11:27 PM	#5
KobyBoy Confirmed User Join Date: Jul 2003 Location: Purgatory Posts: 201	Do a SELECT COUNT(*) FROM crtgpcache WHERE Date = "December 4, 2003" This should return the number of rows that satsify your query. If nothing gets returned then you know there is nothing wrong with your DELETE query but rather there are no rows in the database that satisfy your condition.

12-05-2003, 11:39 PM	#8
KobyBoy Confirmed User Join Date: Jul 2003 Location: Purgatory Posts: 201	Yeah, whitespaces could mess things up. Whenever I put something into the database and I know it's not supposed to have a space in the beginning or at the end (like dates, IP addresses, etc...) I use PHP's trim() function (http://www.php.net/manual/en/function.trim.php) to remove whitespace(s) from the beginning and the end of the string. This saves my butt all the time.