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

Question

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.

aaron-bond · Accepted Answer · 2014-02-03 12:00:01Z

1

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

Instead, use this:

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

edited Feb 3, 2014 at 12:00

answered Feb 3, 2014 at 11:37

aaron-bond

3,3494 gold badges28 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

d.abyss Over a year ago

I've updated the code to reflect this, however still getting the error : Fatal error: Call to a member function bind_param() on a non-object

d.abyss Over a year ago

The change you made in the code breaks the syntax of MySQLi, '?' is the placeholder and the 's' just sets the statement to expect a string. The error must lie within the $sql variable as simply changing the sql variable to "DELETE FROM table_staff WHERE uniqueid = ?" works fine

aaron-bond Over a year ago

@danielsmile Yes, sorry, that was an incorrect fix. From the original error, either $connection isn't an object or $stmt isn't when you're calling bind_param. Use var_dump to find out which it is.

Ben Over a year ago

add ON DUPLICATE KEY UPDATE :)

d.abyss Over a year ago

@Ben will give it a shot now

|

Ben · Accepted Answer · 2014-02-03 11:54:06Z

0

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 = ?

answered Feb 3, 2014 at 11:54

Ben

4016 silver badges13 bronze badges

2 Comments

d.abyss Over a year ago

I use MYSQLi not PDO, the usage is correct as I use this in many other areas of the site. See here codereview.stackexchange.com/questions/40467/…

Ben Over a year ago

Okay. Than it's not a format problem. I guess you have no object/resource?

Collectives™ on Stack Overflow

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

2 Answers 2

11 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related