0

I have a php query to update a MySQL database, see below

$sql=("update hr_payroll set 
payroll_number='$payroll_number', 
tax_code='$tax',
bacs_ref='$bacs_ref',  
pay_frequency='$pay',
last_update_by='$user'
where employee_id='$employee'")or die('Could not connect: Error 23 ' . mysql_error());

If this query is successful a row is inserted into the DB, see below;

if ($conn->query($sql) === TRUE) {
$sql1 = "INSERT INTO audit_hr_employees
(tab, employee, error_type, user, result)
VALUES ('4a', '$employee', 'info', '$user', 'success')";
}

This all works fine.... However if the query is not successful I want to do the same as above but add the error or the query into the database.

Below is a snippet of my code, I want the information to be added to the error_info column.

else
{       
$sql2 = "INSERT INTO audit_hr_employees 
(tab, employee, error_type, user, error_info)
VALUES ('4a', '$employee', 'warning', '$user', '  ')";
}

Is this possible?

Shane
  • 753
  • 3
  • 8
  • 21
  • 1
    This is not a duplicate of that question? – Shane May 10 '16 at 13:41
  • It's very likely that it is a duplicate. When you execute your query, does the database return an error? What is that error? What even is the query that you're executing? (Hint: This looks *wide open* to SQL injection, so you might be executing *anything*.) Note also that you're not even checking what error was generated by the first query. Why not? Recording that there *is* an error is great and all, but recording *what that error was* seems like it would also be pretty useful. – David May 10 '16 at 13:47
  • Seems to be a different question. – user1032531 May 10 '16 at 13:50
  • your question is short on detail, as per the variables if they have values or not and if you're even querying that. If you want to save an error to a database, then you'll need to throw (catch) an exception and use that to write with. – Funk Forty Niner May 10 '16 at 13:52
  • @Fred-ii-: I stand corrected. Learn something new every day :) – David May 10 '16 at 13:56
  • @Fred-ii- Sorry, It's difficult to explain what I am trying to achieve. Which is a keyword? – Shane May 10 '16 at 13:59
  • @David Same here, never ceasing to learn also and makes it all that more exciting ;-) – Funk Forty Niner May 10 '16 at 14:00
  • 1
    @user3092953: It looks like what you're trying to achieve is to store a query which failed. Which isn't itself a bad idea, if I'm being honest. However, you need to also check *why* the query failed. And if this query which is storing the failed query is *also* failing, well, that sounds like a potentially infinite rabbit hole... – David May 10 '16 at 14:01
  • @user3092953 Read up on https://dev.mysql.com/doc/refman/5.5/en/keywords.html you will see keywords and reserved words. You're also going to have to elaborate on your question as per a comment I left. I reopened the question so please give us more detail. – Funk Forty Niner May 10 '16 at 14:01
  • @Fred-ii- - Thank you :-) I have updated the question, does it now make any sort of sense? – Shane May 10 '16 at 14:09
  • @user3092953: `"I want the information to be added to the error_info column."` - What information? Before the edit it looked like you were trying to add the failed query itself. Has that changed? It also looks like you're trying to mix two different ways of interacting with your database (using `mysql_error()` and using `$conn->query()` looks suspect), which won't work at all. (Also note: Defining a string variable in PHP will never result in a SQL error.) The main issue still seems to be that your query is failing but you're not getting the error message from MySQL. – David May 10 '16 at 14:15
  • @David - Ideally I would like to add the failed query to the database – Shane May 10 '16 at 14:19
  • @user3092953: A "query within a query" definitely has the potential for lots of syntax problems if you just directly add it to the string like that. All the more reason to be using prepared statements with query parameters so the database knows to treat the whole "inner query" as just a string to be saved. So you're *definitely* going to want to use prepared statements here and stop directly putting those variables in that string. (As a bonus, it's protection against SQL injection as well.) But, again, you're also going to want to look at the error. Not sure why you still won't do that... – David May 10 '16 at 14:21
  • You are still **as per your other very similiar question** No issueing the query to the database. Do we assume you just left this out for brevity? – RiggsFolly May 10 '16 at 19:08
  • @David Thats just what I said on his other very similiar question, re: the rabbit hole. – RiggsFolly May 10 '16 at 19:09
  • 1
    If you are not absolutely sure that the database failure is not due to the database crashing, all of a sudden its not there any more, you cannot sensibly attempt to write to it again. – RiggsFolly May 10 '16 at 19:10
  • Anyway : what is the possible use of adding a message saying `this query had an error` which it a bit like recording `Woops` without also grabbing the `mysqli_error($con)` and storing that as well – RiggsFolly May 10 '16 at 19:13
  • Why do you think that MYSQL creates a `error log` on a flat file on disk. I suggest its because there are situations that even the developers of MYSQL could not possibly code around in order to ensure they recorded ALL errors where someone would be able to see them regardless of what happens to MYSQL – RiggsFolly May 10 '16 at 19:16

1 Answers1

1

It looks like you're connecting to MySQL via PHP's PDO interface. You can use the errorInfo() function (http://php.net/manual/en/pdo.errorinfo.php) to retrieve the most recent error message and use that in place of your blank string:

$err = $dbh->errorInfo();

$sql2 = "INSERT INTO audit_hr_employees 
(tab, employee, error_type, user, error_info)
VALUES ('4a', '$employee', 'warning', '$user', $err[2])";
kmoser
  • 8,780
  • 3
  • 24
  • 40