<p>I'm dealing with a MySQL table that defines the JobName column as UNIQUE.  If somebody tries to save a new Job to the database using a JobName that is already in the database, MySQL throws a warning.</p>

<p>I would like to be able to detect this warning, just like an error, in my PHP script and deal with it appropriately.  Ideally I would like to know what kind of warning MySQL has thrown so that I can branch the code to handle it.</p>

<p>Is this possible?  If not, is it because MySQL doesn't have this ability, PHP doesn't have this ability, or both?</p>

[{'answer_id': 47597, 'author': 'Kyle Cronin', 'author_id': 658, 'author_profile': 'https://Stackoverflow.com/users/658', 'pm_score': 2, 'selected': False, 'text': '<p>First, you should <a href="http://us.php.net/error_reporting" rel="nofollow noreferrer">turn warnings off</a> so that your visitors don\'t see your <strong>MySQL</strong> errors. Second, when you call <a href="http://us.php.net/mysql_query" rel="nofollow noreferrer"><code>mysql_query()</code></a>, you should check to see if it returned false. If it did, call <a href="http://us.php.net/manual/en/function.mysql-errno.php" rel="nofollow noreferrer"><code>mysql_errno()</code></a> to find out what went wrong. Match the number returned to the error codes on <a href="http://dev.mysql.com/doc/refman/4.1/en/error-messages-server.html" rel="nofollow noreferrer">this page</a>.</p>\n<p>It looks like this is the error number you\'re looking for:</p>\n<blockquote>\n<p>Error: 1169 SQLSTATE: 23000 (ER_DUP_UNIQUE)</p>\n<p>Message: Can\'t write, because of unique constraint, to table \'%s\'</p>\n</blockquote>\n'}, {'answer_id': 47606, 'author': 'yukondude', 'author_id': 726, 'author_profile': 'https://Stackoverflow.com/users/726', 'pm_score': 0, 'selected': False, 'text': "<p>Updated to remove the stuff about errno functions which I now realize don't apply in your situation...</p>\n\n<p>One thing in MySQL to be wary of for <code>UPDATE</code> statements: <code>mysqli_affected_rows()</code> will return zero even if the <code>WHERE</code> clause matched rows, but the <code>SET</code> clause didn't actually change the data values. I only mention this because that behaviour caused a bug in a system I once looked at--the programmer used that return value to check for errors after an update, assuming a zero meant that some error had occurred. It just meant that the user didn't change any existing values before clicking the update button.</p>\n\n<p>So I guess using <code>mysqli_affected_rows()</code> can't be relied upon to find such warnings either, unless you have something like an <code>update_time</code> column in your table that will always be assigned a new timestamp value when updated. That sort of workaround seems kinda kludgey though.</p>\n"}, {'answer_id': 47608, 'author': 'Alexander Morland', 'author_id': 4013, 'author_profile': 'https://Stackoverflow.com/users/4013', 'pm_score': 0, 'selected': False, 'text': "<p>depending on what (if any) framework you're using, I suggest you do a query to check for the jobname yourself and create the proper information to user in with the rest of the validations for the form.</p>\n\n<p>Depending on the number of jobnames, you could send the names to the view that contains the form and use javascript to tell use which is taken.</p>\n\n<p>If this doesnt make sense to you, then to sum my view it's this: dont design your program and / or  user to try to do illegal things and catch the errors when they do and handle it then. It is much better, imho, to design your system to not create errors. Keep the errors to actual bugs :)</p>\n"}, {'answer_id': 47662, 'author': 'Ian', 'author_id': 4396, 'author_profile': 'https://Stackoverflow.com/users/4396', 'pm_score': 5, 'selected': True, 'text': '<p>For warnings to be "flagged" to PHP natively would require changes to the mysql/mysqli driver, which is obviously beyond the scope of this question.  Instead you\'re going to have to basically check every query you make on the database for warnings:</p>\n\n<pre><code>$warningCountResult = mysql_query("SELECT @@warning_count");\nif ($warningCountResult) {\n    $warningCount = mysql_fetch_row($warningCountResult );\n    if ($warningCount[0] &gt; 0) {\n        //Have warnings\n        $warningDetailResult = mysql_query("SHOW WARNINGS");\n        if ($warningDetailResult ) {\n            while ($warning = mysql_fetch_assoc($warningDetailResult) {\n                //Process it\n            }\n        }\n    }//Else no warnings\n}\n</code></pre>\n\n<p>Obviously this is going to be hideously expensive to apply en-mass, so you might need to carefully think about when and how warnings may arise (which may lead you to refactor to eliminate them).</p>\n\n<p>For reference, <a href="http://dev.mysql.com/doc/refman/5.0/en/show-warnings.html" rel="nofollow noreferrer">MySQL SHOW WARNINGS</a>    </p>\n\n<p>Of course, you could dispense with the initial query for the <code>SELECT @@warning_count</code>, which would save you a query per execution, but I included it for pedantic completeness.</p>\n'}, {'answer_id': 929065, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>You can detect Unique key violations using mysqli statement error no. The mysqli statement returns error 1062 , that is ER_DUP_ENTRY. You can look for error 1062 and print a suitable error message. If you want to print your column (jobName) also as part of your error message then you should parse the statement error string.</p>\n\n<pre>\n  if($stmt = $mysqli->prepare($sql)){\n            $stmt->bind_param("sss",\n            $name,\n            $identKey,\n            $domain);\n\n\n            $stmt->execute();\n            if($mysqli->affected_rows != 1)  {\n                        //This will return errorno 1062\n                trigger_error(\'mysql error >> \'.$stmt->errno .\'::\' .$stmt->error, E_USER_ERROR);\n                exit(1);\n            }\n            $stmt->close();\n        } else {\n\n            trigger_error(\'mysql error >> \'. $mysqli->errno.\'::\'.$mysqli->error,E_USER_ERROR);\n        }\n</pre>\n'}, {'answer_id': 2029953, 'author': 'tomsv', 'author_id': 246622, 'author_profile': 'https://Stackoverflow.com/users/246622', 'pm_score': 0, 'selected': False, 'text': '<p>It is possible to get the warnings, and in a more efficient way with mysqli than with mysql.</p>\n\n<p>Here is the code suggested on the manual <a href="http://www.php.net/manual/en/mysqli.warning-count.php" rel="nofollow noreferrer">page</a> on php.net for the property mysqli->warning_count:</p>\n\n<pre><code>$mysqli-&gt;query($query);\n\nif ($mysqli-&gt;warning_count) {\n    if ($result = $mysqli-&gt;query("SHOW WARNINGS")) {\n        $row = $result-&gt;fetch_row();\n        printf("%s (%d): %s\\n", $row[0], $row[1], $row[2]);\n        $result-&gt;close();\n    }\n}\n</code></pre>\n'}, {'answer_id': 5058649, 'author': 'AG3', 'author_id': 593260, 'author_profile': 'https://Stackoverflow.com/users/593260', 'pm_score': 0, 'selected': False, 'text': '<p>Note on suppressing warnings: Generally, it is not a good idea to prevent warnings from being displayed since you might be missing something important.  If you absolutely must hide warnings for some reason, you can do it on an individual basis by placing an <code>@</code> sign in front of the statement.  That way you don\'t have to turn off all warning reporting and can limit it to a specific instance.</p>\n\n<p>Example:</p>\n\n<pre><code> // this suppresses warnings that might result if there is no field titled "field" in the result\n $field_value = @mysql_result($result, 0, "field");\n</code></pre>\n'}, {'answer_id': 8032033, 'author': 'Gigi', 'author_id': 1033016, 'author_profile': 'https://Stackoverflow.com/users/1033016', 'pm_score': 2, 'selected': False, 'text': "<pre><code>ini_set('mysql.trace_mode', 1)\n</code></pre>\n\n<p>may be what you are looking for.</p>\n\n<p>The PHP errors can then be handled with a custom PHP error handler, but you can also just turn off displaying php errors as they are usually logged into a log file (depends on your php configuration).</p>\n"}]
['https://Stackoverflow.com/questions/47589', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2056/']