Hi all. I am not sure if this should be in PHP or MySql, but it is more relevant to MySl. I am designing a helpdesk for my department. Currently I am entering data into a form, which submits that data to a MySql table. That table is then displayed on a seperate page with checkboxes so I can "complete" a ticket, which should move it from the current table to a seperate table and then delete the original record. Currently the Complete button for this is successful in deleting the record, but it does not suceed in inserting the record into the second table. I am pretty sure my syntax is messed up, but I am not sure how to fix it.

Here is the code:

` if($_POST['complete']){

    for($i=0;$i<$count;$i++){
        $del_id = $checkbox[$i];
    $sql = "INSERT INTO $tbl_name2 SELECT * FROM $tbl_name WHERE tixnum='$del_id'";
        $sql = "DELETE FROM $tbl_name WHERE tixnum='$del_id'";
    $sql=sprintf($sql,(int)$del_id);
        $result = mysql_query($sql);
    }

if($result){
echo"<meta http-equiv=\"refresh\"content=\"0;URL=completeopen.php\">";
}`

The Database and Tables are defined as such

    <?php
$host="localhost";
$username="root";
$password="";
$db_name="opentix";
$tbl_name="opentix";
$tbl_name2="closedtix";
//Connect and Select
mysql_connect("$host","$username","$password") or die("Cannot Connect");
mysql_select_db("$db_name")or die("Cannot Select Database");

$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);

$count=mysql_num_rows($result);

?>

Why don't you use an enum field, something like:

alter table opentix add completed enum('y','n') default 'n' not null;

from this point when you want to check open tickets just use select * from opentix where completed = 'n' or similar.. anyway to make it work you need to set the rows, change your query to this:

INSERT INTO $tbl_name2 ($tbl_name2.field_1,$tbl_name2.field_2) SELECT $tbl_name.field_1,$tbl_name.field_2 FROM $tbl_name WHERE tixnum='$del_id'

This design is so bad it hurts.
Do not copy records from one table to another to change their status. Just add a status column, as cereal suggested.
Then your code is flawed. You assign some statement text to the $sql variable, but never excecute the INSERT query. Only the DELETE gets executed.
If your departement wants to go productive anytime soon, tell them to install a standard bug tracker like Mantis.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.