CODEWITHSUNDEEP
phpmysqlsql
d.abyss
d.abyss

Reputation: 212

PHP/MySQL - Updating an SQL table with data from another table

In a worker shift monitoring/accounting system I've built, there is an option to delete Staff Members. This works fine but I would like to archive shifts for staff members who are deleted.

I looked into it and it seemed the best way to do this would be to update my shift table, before the delete, into another table. I looked up how to do this via another Stack Overflow post however I'm getting an error: fatal error call to member function on a non-object.

Based on what I can find, this error is caused when you try to pass a null value, which has left me confused as the value I'm trying to pass is a GET and is working fine when I test it.

$sql = "
  UPDATE table_archive
     SET table_shift.shift_date = table_archive.shift_date,
         table_shift.start_time = table_archive.start_time,
         table_shift.end_time = table_archive.end_time,
         table_shift.total_hours = table_archive.total_hours,
         table_shift.rate_of_pay = table_archive.rate_of_pay,
         table_shift.uniqueid = table_archive.uniqueid, 
         table_shift.addedBy = table_archive.addedBy,
         table_shift.paidRate = table_archive.paidRate,
         table_shift.totalPaid = table_archive.totalPaid
    FROM table_shift, table_archive
   WHERE table_shift.uniqueid = ?
";

$stmt = $connection->prepare($sql);
$deleteid = htmlentities($_GET['id']);
$stmt->bind_param('s', $deleteid);
$stmt->execute();

I'm stuck as to why this wont pass, the GET cant be a null value as the test delete I'm using at the moment passes the same variable and works fine. mysqli_query($connection,"DELETE FROM table_staff WHERE uniqueid='$deleteid'")

It may be that I'm using the SQL code wrongly or there is some silly thing I've forgotten but this has me stumped. Failing to fix this code, any other suggestions as to how to achieve the intended function are welcome.

Upvotes: 0

Views: 1024

Answers (2)

aaron-bond
aaron-bond

Reputation: 3341

You can't UPDATE FROM. Your syntax is wrong.

Instead, use this:

INSERT INTO table_archive
SELECT * FROM table_shift WHERE table_shift.uniqueid = ?

Upvotes: 1

Ben
Ben

Reputation: 401

Is the use of bindParam correct?

If you use a ? it should look like this:

SELECT ...
WHERE column_name = ?

$sth->bindParam(1, $value_in_php, PDO::PARAM_INT);

If it's not ? but :param_name use this:

SELECT ...
WHERE column_name = :param

$sth->bindParam(':param', $value_in_php, PDO::PARAM_INT);

Your error sounds not like an SQL error, but a PHP error.

And if you want to update the table_archive table the SQL doesn't look correct. It should imho be like this:

UPDATE table_archive
   SET table_archive.shift_date        = table_shift.shift_date
     , to_table_that_should_be_updated = from_table_with_value_to_update
  FROM table_shift
 WHERE table_shift.uniqueid = ?

Upvotes: 0

Related Questions