PHP Script Review - need solution!

[jjfletcher90]

I have a database in MySql and runs fine - yet the server I am on is a Windows running MySql 5.0 and not 5.1.6 - unable to run cron job with this version and also windows i understand does not support.... So I have created a PHP script that I hope will provide the same results - but when I run it, the data in my database does not delete as intended... Could you folks review my script and possibly any othe thoughts on how to achieve this function...

 

<?php
$db = mysql_connect(credentials removed);
if (!$db)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("genpublic", $db);

mysql_query("DELETE FROM users
WHERE `signup_date` <DATE_SUB(CURDATE(), INTERVAL 1 DAY)'");

mysql_close($db);
?>

 

Best Regards,

 

John

 

MOD EDIT: DB credentials edited out

[AyKay47]

what PHP errors do you receive?

have you debugged your mysql_query?

[Pikachu2000]

I've removed your DB credentials from your post, but you should change your username/password nonetheless.

[jjfletcher90]

I recieve the following errors:

 

SQL query:

 

mysql_query(

 

"DELETE FROM users

WHERE `signup_date` <DATE_SUB(CURDATE(), INTERVAL 1 DAY)"

)

 

MySQL said: 

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql_query("DELETE FROM users

WHERE `signup_date` <DATE_SUB(CURDATE(), INTERV' at line 1

 

 

[Pikachu2000]

Judging from the error message, it appears you've nested 2 mysql_query() functions somehow, like mysql_query( mysql_query("SELECT . . . ) ). Is that a possibility? Is the code you posted the actual code you're using?

[AyKay47]

appears that you are querying a query here...

 

Edit: as pikachu has already stated

[jjfletcher90]

This is all in testing stages - trying to get the code to delete a record in the database after a set amount of time has passed:

 

the code I am testing is:

 

<?php
$db = mysql_connect("credentials removed");
if (!$db)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("databasename", $db);

mysql_query("DELETE FROM users
WHERE `signup_date` <DATE_SUB(CURDATE(), INTERVAL 1 DAY)");

mysql_close($db);
?>

[Pikachu2000]

That code can't possibly return the error you posted. It doesn't even contain a call to mysql_error() after the query.

[AyKay47]

yeah this one has me confused..your syntax is not incorrect here, and the error indicates that you are querying a query, i d not see how that error is coming from the code you provided..

[jjfletcher90]

I ran this from within phpmyadmin:

 

mysql_query("DELETE FROM users

WHERE `signup_date` <DATE_SUB(CURDATE(), INTERVAL 1 DAY)");

 

and received the error - when I run the PHP from a browser it does nothing - no error nor message at all

[Pikachu2000]

Well, that explains a lot; I wish you'd said that to begin with. You only enter the actual query string itself in phpMyAdmin, not the call to mysql_query(). Try it that way, and see what happens.

 

In other words, in phpMyAdmin, only paste this

DELETE FROM users WHERE `signup_date` <DATE_SUB(CURDATE(), INTERVAL 1 DAY)

[jjfletcher90]

Error

There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

 

ERROR: Unclosed quote @ 75

STR: "

SQL: DELETE FROM users

WHERE `signup_date` <DATE_SUB(CURDATE(), INTERVAL 1 DAY)");

 

 

SQL query:

 

DELETE FROM users WHERE `signup_date` <DATE_SUB(CURDATE(), INTERVAL 1 DAY)");

 

MySQL said: 

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '")' at line 2

 

 

[jjfletcher90]

removered the " enclosed and re-ran the test:

 

Error

SQL query:

 

DELETE FROM users WHERE `signup_date` < DATE_SUB( CURDATE( ) , INTERVAL 1 DAY )

 

)

 

MySQL said: 

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

 

[jjfletcher90]

MySql version is 5.0 (Goddady) - this error seems to be referring to the version of the database not the code???

[Pikachu2000]

No, it executes fine for me locally. Copy/paste the actual query string into phpMyAdmin, don't retype it. See what happens.

 

DELETE FROM users WHERE `signup_date` <DATE_SUB(CURDATE(), INTERVAL 1 DAY)

[jjfletcher90]

Deleted rows: 0 (Query took 0.0004 sec)

 

SQL query:

DELETE FROM users WHERE `signup_date` < DATE_SUB( CURDATE( ) , INTERVAL 1 DAY )

 

checked to see if the sample data was deleted from the table and it is still there - huh?

 

Looks like progress to me - at least no errors!

 

No to actually deleted the record when requested...

[jjfletcher90]

These are the columns in the table that I am using - am I calling on the right column to delete the requested record based on lapsed time?

 

userid | first_name | last_name | email_address | username | password | info | user_level | signup_date | last_login | activated

[Pikachu2000]

If you're trying to delete any record that has a date in that field of more than 1 day ago, yes. Note that the date would need to be more than one day ago, so it won't delete a record with yesterday's date. Just to make sure, the signup_date field is a DATE type field, right?

[jjfletcher90]

changed the test:

 

SELECT * FROM `users` WHERE `signup_date` < DATE_SUB( CURDATE( ) , INTERVAL 1 DAY )

 

and received this result:

 

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0100 sec)

 

[Pikachu2000]

You're going to need to post some of the data from that particular field so we can see what's going on with it. Post the values this query returns.

 

SELECT signup_date FROM users ORDER BY signup_date DESC LIMIT 10

[jjfletcher90]

I ran this from within phpmyadmin:

 

and the record deleted... I went in and edited the date to reflect 2 days ago and then it deleted... So.... 

 

When I run the PHP from the browser this is the error recieved:

 

Parse error: syntax error, unexpected $end in test.php on line 14

 

This is the modified code:

<?php
$db = mysql_connect("removed credentials");
if (!$db)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("databasename", $db);

mysql_query("DELETE FROM users WHERE `signup_date` <DATE_SUB(CURDATE(), INTERVAL 1 DAY);

mysql_close($db);
?>

 

Thanks for helping me Guys - your awsome!

 

[AyKay47]

you forgot the closing quotation in your sql

 

mysql_query("DELETE FROM users WHERE `signup_date` <DATE_SUB(CURDATE(), INTERVAL 1 DAY");mysql_close($db);?>

[jjfletcher90]

Showing rows 0 - 0 (1 total, Query took 0.0047 sec)

SQL query:

SELECT signup_date

FROM users

ORDER BY signup_date DESC

LIMIT 10

[Pikachu2000]

Your call to mysql_query() isn't properly closed. It's really better practice not to embed the query string within the query execution, as doing so makes it impossible to echo the query string if you need to do any debugging.

 

mysql_select_db("databasename", $db);
$query = "DELETE FROM users WHERE `signup_date` <DATE_SUB(CURDATE(), INTERVAL 1 DAY)";
mysql_query( $query );
// etc.

[jjfletcher90]

I'm just getting darn right angry now... this is the new error after all the changes:

 

Parse error: syntax error, unexpected $end in test.php on line 13

 

<?php
$db = mysql_connect("removed credentials");
if (!$db)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("databasename", $db);

mysql_query("DELETE FROM users WHERE `signup_date` <DATE_SUB(CURDATE(), INTERVAL 1 DAY)

mysql_close($db);
?>