PHP and MySQL

[big-dog]

Can someone help with deleteing some rows in sql from the php and maybe tell me how to set a textbox to date in mysql with the proper format. I have a form that the user enters the date in a textbox and it seems everyone enters it differant resulting in the out put from the database be jacked up. it probably would be good to have the php echo wrong format try again or something. Ive tried several things in the database and in the php but cant seem to get it.

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT First_Name, First_Class, Second_Class, Race_Date FROM Registration ORDER BY Race_Date DESC";
$result=mysql_query($query);

$num=mysql_numrows($result);

if ($num==0) {
echo "<b>NO Drivers Registered Yet</b><br>";
}else{

}

$mysql="SELECT * FROM Registration DELETE WHERE Race_Date = 0000-00-00";
mysql_close();

echo "<b>Registered Drivers</b><br>";

$i=0;
while ($i < $num) {

$First_Name=mysql_result($result,$i,"First_Name");
$First_Class=mysql_result($result,$i,"First_Class");
$Second_Class=mysql_result($result,$i,"Second_Class");
$Race_Date=mysql_result($result,$i,"Race_Date");

echo "<b>$First_Name</b><br>$Race_Date</b><br>$First_Class<br>$Second_Class<br><hr>";

$i++;
}

?>

[bradgrafelman]

1. When posting PHP code, please use the board's [PHP][/PHP] bbcode tags - they make the code much, much easier to read (as well as aiding the diagnosis of syntax errors). You should edit your post now and add these bbcode tags to help others in reading/analyzing your code.

2. I believe you want to look at the MySQL manual for the DELETE syntax.

[big-dog]

Thats where I came up with what in the php now plus several other versions of it noyjing is working for me

[bradgrafelman]

Did you look at the syntax examples? They give plenty of examples on that page.

I only ask because all of the examples clearly show that the standard syntax is "DELETE FROM ... WHERE ..." but your query starts with a SELECT statement.

[big-dog]

I tried
$mysql="SELECT FROM Registration DELETE WHERE Race_Date = 0000-00-00";
thinking I had to select before I could delete
I also tried this
$mysql="DELETE FROM Registration WHERE DATE_SUB(CURDATE(),INTERVAL 10 DAY) <= Race_Date
but this one I get an Parse error: syntax error, unexpected '>' in /home/public_html/registered.php on line 22
Which is echo "<b>Registered Drivers</b><br>";

and
DELETE * FROM Registration WHERE code = DATE_SUB(CURDATE(),INTERVAL 3 DAY) <= Race_Date
Im just dont get it I know it has to be simple
I spent most of the night jumping back and forth from this and trying to get if/else to work on the second_class

[bradgrafelman]

Here's a simple example... all I'm doing is pasting it right from the linked page:

DELETE FROM somelog WHERE user = 'jcole'

So, basically, all you have teo do is replace the table name, the column name, and the value.

[big-dog]

Im trying to do this from the php file above. I get an error I tried that line last night and again just now. Ive place the line in several differant places in the code and same error.

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
DELETE FROM Registration WHERE Race_Date = '0000-00-00'
$query="SELECT First_Name, First_Class, Second_Class, Race_Date FROM Registration ORDER BY Race_Date DESC";

Parse error: syntax error, unexpected T_STRING in /home/public_html/registered.php on line 8

[bradgrafelman]

I think you might want to search around for some basic PHP and MySQL tutorials.

You can't simply throw a SQL query in the middle of PHP code... put it in a string and use [man]mysql_query/man to query the DB.

$query = 'DELETE FROM whatever WHERE something = \'a value\'';
mysql_query($query);

[big-dog]

I have spent a couple of days looking and trying different things with tuts and docs. I'm a Newbie and just have a hard time with how stuff works together.
Bradgrafelman🆒 you solved it and also with the same line and a little mod solved deleting records older than 15 days
THANKS🙂

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = 'DELETE FROM Registration WHERE Race_Date = \'0000-00-00\''; 
mysql_query($query);
$query = 'DELETE FROM Registration WHERE DATE_SUB(CURDATE(),INTERVAL 15 DAY) <= `Race_Date`'; 
mysql_query($query);
$query="SELECT First_Name, First_Class, Second_Class, Race_Date FROM Registration ORDER BY Race_Date DESC";

$result=mysql_query($query);

$num=mysql_numrows($result);

if ($num==0) {
echo "<b>NO Drivers Registered Yet</b><br>";
}else{

}

mysql_close();

echo "<i><b>Registered Drivers</b></i><br>";

$i=0;
while ($i < $num) {

$First_Name=mysql_result($result,$i,"First_Name");
$First_Class=mysql_result($result,$i,"First_Class");
$Second_Class=mysql_result($result,$i,"Second_Class");
$Race_Date=mysql_result($result,$i,"Race_Date");
echo "<b>$First_Name<br>$Race_Date</b><br>$First_Class<br>$Second_Class</b><hr>";

$i++;
}

?>

[bradgrafelman]

Note that you can combine your two queries into one for efficiency:

DELETE FROM `Registration` WHERE `Race_date` = '0000-00-00' OR DATE_SUB(CURDATE(),INTERVAL 15 DAY) <= `Race_Date`

[big-dog]

I get
Parse error: syntax error, unexpected T_STRING in /home//public_html/registered.php on line 8

doing that

[big-dog]

Hey sence your online can you help me real quick with ifelse or something that will look at $Second_Class if the value is Select One dont show it, if the value is something else show it. tried several things but I think the problem is in the way I have the echo of this stuff at bottom of php I dont know what Im fighting get errors from all the sample tut Ive found.

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = 'DELETE FROM Registration WHERE Race_Date = \'0000-00-00\''; 
mysql_query($query);
$query = 'DELETE FROM Registration WHERE DATE_SUB(CURDATE(),INTERVAL 15 DAY) <= `Race_Date`'; 
mysql_query($query);
$query="SELECT First_Name, First_Class, Second_Class, Race_Date FROM Registration ORDER BY Race_Date DESC";

$result=mysql_query($query);

$num=mysql_numrows($result);

if ($num==0) {
echo "<b>NO Drivers Registered Yet</b><br>";
}else{

}

mysql_close();

echo "<b>Registered Drivers</b><br>";

$i=0;
while ($i < $num) {

$First_Name=mysql_result($result,$i,"First_Name");
$First_Class=mysql_result($result,$i,"First_Class");
$Second_Class=mysql_result($result,$i,"Second_Class");
$Race_Date=mysql_result($result,$i,"Race_Date");
echo "<b>$First_Name<br>$Race_Date</b><br>$First_Class<br>$Second_Class</b><hr>";

$i++;
}

?>

[bradgrafelman]

1. Make sure you escape the proper quotes when you use the query I posted. I didn't escape any quotes - I figured you would automatically do that.

2. You close the connection to the SQL server before you ever retrieve the information. How is PHP supposed to get data from the SQL server after you've terminated the connection?

[big-dog]

Im sorry did I metion newbie
But anyway I thought the

$query = 'DELETE FROM Registration WHERE DATE_SUB(CURDATE(),INTERVAL 15 DAY) <= `Race_Date`'; 
mysql_query($query); 

but it dont I put 2007/01/01 and 2007/01/10 in and it doesnt delete them

$query = 'DELETE FROM Registration WHERE DATE_SUB(CURDATE(),INTERVAL -15 DAY) <= `Race_Date`'; 
mysql_query($query); 

tried a -15 as well