Page 1 of 1

select & delete mysql queries

Posted: Fri Jul 18, 2003 10:55 pm
by superman
i've try to insert and delete record at the same page, but it only can insert and can't delete the record.
when i click submit button from the previous form, the record is insert into table B and delete from the table A.
is the code correct?

Code: Select all

<? $Connect = mysql_connect("localhost","root","");
                mysql_select_db("mw");
				$TodayDate = strftime("%Y-%m-%d"); 	
				$TodayTime = strftime("%H:%M:%S"); 	
				$result=mysql_query("insert into LeaveApplication1 (StaffID, LeaveType, JobTitle, FromDate, ToDate, Duration, Reason, TodayDate, TodayTime) values ('$StaffID', '$LeaveType', '$JobTitle', '$FromDate', '$ToDate', '$Duration', '$Reason', '$TodayDate')");

				$result=mysql_query("delete from LeaveApplication where StaffID='$StaffID' and LeaveType='$LeaveType' and JobTitle='$JobTitle' and FromDate='$FromDate' and ToDate='$FromDate' and Duration='$Duration' and Reason='$Reason' and TodayDate='$TodayDate' and TodayTime='$TodayTime'");
				 mysql_close();
			?>

Simple stuff

Posted: Mon Jul 21, 2003 6:31 am
by gjb79
I know it sounds dumb and there's a good chance you've already checked, but it is easy to over look.

Log into mysql and make sure the account your using in the script to log into it has permissions set to delete. Could save you a lot of trouble if it is the case.

One more thing

Posted: Mon Jul 21, 2003 6:33 am
by gjb79
If your inserting and deleting from the same table, you need these two lines to be identical as far as the table name:

"insert into LeaveApplication1"

"delete from LeaveApplication"

In your script you are missing the 1 in LeaveApplication in the delete code.

Re: select & delete mysql queries

Posted: Mon Jul 21, 2003 3:16 pm
by xisle
I would recommend deleting based on the record id (primary key)
for the table. With this many 'where' conditions, the delete could
easily get overlooked.

Code: Select all

"delete from LeaveApplication where id='99999'";

...

Posted: Mon Jul 21, 2003 3:29 pm
by kettle_drum
You may also not want to connect to the mysql server as root.

Maybe also try DELETE * FROM table WHERE id = '1'

Re: ...

Posted: Mon Jul 21, 2003 3:44 pm
by Celtis
kettle_drum wrote:You may also not want to connect to the mysql server as root.
Good advice, and make sure that you change the root password (obviously change "password" to "YOUR PASSWORD!"):

Code: Select all

Use mysql; 
Update user set password=PASSWORD("password") where user='root';
kettle_drum wrote:Maybe also try DELETE * FROM table WHERE id = '1'
Delete * won't work in mysql, you just use delete instead :)