2. PHPRunner General questions
  3. deleting a record
This topic is locked

deleting a record

 		3/18/2007 7:56:49 PM
PHPRunner General questions
M
 mord3th author

Hello all,

I am trying to 'move' a record into a "Finished" table. Kinda like an archive. I have a field with radio buttons and the sql determines what to move if finished=yes. It copies the record into the new table,but now i need to delete the old one and im stumped.
Alexey admin 3/19/2007

Hi,
use Deletesql statement.

Here is the info on using it.

http://www.webcheatsheet.com/sql/interacti.../sql_delete.php

The SQL command should look like:

delete from tablename where finished='Yes'
M
 		mord3th author 3/19/2007

thats what im am using but i get the error

mysql_query(): supplied argument is not a valid MySQL-Link resource


heres teh code im using (best i can remember)

global ($conn, $strTableName)

$strInsert="INSERT INTO finished (id, name, phone, email) VALUES (id, name, phone, email) WHERE finished='Yes'";

$strDelete="DELETE FROM ".$strTableName"; WHERE finished='Yes'";

db_exec($strInsert, $strDelete, $conn)


it seems like $strDelete is declared wrong or that there is a delete function declared somewhere else. I get an error everytime.
J
 		Jane 3/20/2007

There are some syntax errors in your code.

I suppose you use this code in the Before record updated event. In this case you can use $values array with all field values of edited record.

global $conn, $strTableName;

$strInsert="INSERT INTO finished (id, name, phone, email) VALUES (".$values["id"].",'".$values["name"]."','".$values["phone"]."','".$values["email"]."') WHERE finished='Yes'";

$strDelete="DELETE FROM ".$strTableName." WHERE finished='Yes'";

db_exec($strInsert, $conn);

db_exec($strDelete, $conn);
M
 		mord3th author 3/21/2007

There are some syntax errors in your code.

I suppose you use this code in the Before record updated event. In this case you can use $values array with all field values of edited record.


you sir, are a genius. I'll try it out later when i get out of class.

It looks like exactly what i was trying to do <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=16424&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
M
 		mord3th author 3/21/2007

well i ended up tweaking it abit and heres what i got

global $conn, $strTableName;
$strSQLInsert = "INSERT INTO finished (id, name, phone, email, box, problem, finished) SELECT id, name, phone, email, box, problem, finished FROM ".$strTableName." where finished='Yes'";

$strDelete = "DELETE FROM ".$strTableName." WHERE finished = 'Yes'";

db_exec($strSQLInsert, $conn);

db_exec($strDelete, $conn);


Copies the old record into new db and deletes record from old db. However, i cant seem to copy ALL the fields from the old record. i get an error when i try to add the 'update' field (its a text field, but i have no problem copying the other 2 text field before it)
if you need to look at it ive provided a password

irongryphonlabs.com/login.php

User: test

pass: test
Alexey admin 3/22/2007

Hi,
UPDATE is a reserved SQL word

You may have problems using it as a field name.
Try to enclose it in wrappers in your code.

If your database is MySQL use

`update`

instead of just

update
M
 		mord3th author 3/22/2007

that did the trick

thanks so much for your help <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=16454&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />