0

I use the same PHP code for all of my SQL requests on this project. However, the one I am writing up to update my tables is throwing back errors. Here is the section where I post the headers and write up the SQL request:

$query = $conn->prepare('UPDATE personnel AS p
            SET p.firstName = ?, p.lastName = ?, p.jobTitle = ?, 
                p.email = ?, l.name = ?, d.name = ?
                INNER JOIN department AS d ON p.departmentID=d.id
                INNER JOIN location AS l ON d.locationID=l.id
            WHERE p.id = ?');
$id = $_POST['one'];
$fName = $_POST['two'];
$lName = $_POST['three'];
$job = $_POST['four'];
$email = $_POST['five'];
$dep = $_POST['six'];
$loc = $_POST['seven'];
$query->bind_param("sssssss", $fName, $lName, $job, $email, 
                              $loc, $dep, $id);
$query->execute();

I have also tried entering bind param as "ssssssi" even though the number is in string format. I entered all of the POST headers as variables; it doesn't seem to like me throwing them straight into the bind_param.

Here are my console and network responses: (name, email, etc are fictional)

My PHP headers

My console response

My PHP response

SynDuck
  • 37
  • 3
  • 12
  • 2
    `FROM` in queries is for select, not update. See https://dev.mysql.com/doc/refman/8.0/en/update.html – aynber Nov 08 '21 at 16:46
  • 2
    Not sure what you are expecting in `$result = $query->get_result();` after an UPDATE Query? – RiggsFolly Nov 08 '21 at 16:49
  • Look at the raw response from the server. The error message is there as a HTML code – Dharman Nov 08 '21 at 16:52
  • 1
    ___Problem___ You have shown us a javscript error about a badly formed json and a PHP coded query and __nothing about how or WHAT you return the response to the Ajax call__ – RiggsFolly Nov 08 '21 at 16:53
  • 1
    Check the response from the server, and you'll see the error. I'm going to **guess** that you're getting an error about `call to member execute on bool` because your prepare statement failed. – aynber Nov 08 '21 at 16:54
  • I think JOIN goes before SET, but your new error message should point it out. Do you have mysqli error reporting enabled? Can you see the error? – Dharman Nov 08 '21 at 17:11
  • I didn't originally use "from". I added it after seeing this: https://stackoverflow.com/questions/9588423/sql-server-inner-join-when-updating. I've removed it now. I've also removed the $result line since the variable is not used in this file. The images are from the console log and the PHP files network response. What do I need to do to show more details about the error? – SynDuck Nov 08 '21 at 17:17
  • I had it that way around as well, Dharman. Before I changed it according to the stackoverflow answer in my previous comment. – SynDuck Nov 08 '21 at 17:21
  • The answer you linked is for SQL server. I don't think MySQL has a similar syntax – Dharman Nov 08 '21 at 17:23
  • 1
    This should help you [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) – Dharman Nov 08 '21 at 17:24
  • Thanks for the lead! I'll have a proper look through it when I'm back on my computer and give an update. – SynDuck Nov 08 '21 at 17:55
  • It took a while for me to get a proper error message, sorry. I currently get Fatal error: Call to a member function bind_param() on boolean. From what I've looked up, this usually happens when a table or header doesn't exist but this is not the case here. – SynDuck Nov 11 '21 at 11:38
  • That's not the proper error message. That message only tells you that you haven't enabled mysqli error reporting. Read the link above and add `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` to enable it – Dharman Nov 11 '21 at 11:39
  • Fatal error: Uncaught mysqli_sql_exception: Unknown column 'test' in 'field list'. I take it the problem is with the way I wrote the SQL as it's looking for 'test' rather than entering it. – SynDuck Nov 11 '21 at 12:05
  • Where is `test` coming from? I can't see it in the code in your question – Dharman Nov 11 '21 at 12:05
  • Right. Sorry. One of the things I tried yesterday was making the code smaller and simpler to see if it would help me find the issue. The code used is: $query = $conn->prepare('UPDATE personnel SET jobTitle = test WHERE id = ?'); $id = 1; $query->bind_param("i", $id); $query->execute(); – SynDuck Nov 11 '21 at 12:10
  • Then that error has nothing to do with your question. Please edit the question to get it into shape if you still face some problem. At the moment it's unclear what the issue is. Please note that these comments are getting long and they might be deleted by a moderator soon. If your question is meant to be helpful to others, it must clearly show what the issue you are trying to solve is. – Dharman Nov 11 '21 at 12:12

0 Answers0