0

I have the following insert which I suspected might be failing frequently. I just tested it manually by making a small series of http requests manually from my browser to execute it, making sure there couldn't have been any overlapping requests, and it failed once. To be clear, by "fail" I mean the data was not inserted. There was however no error given of any kind.

Here is the query:

$sql = "INSERT INTO `$dbname`.`$file" . "_data` (`offerid`, `source`, `keyword`, `views`) VALUES ($offerid,'$source','$keyword',1)";
if(!mysql_query($sql)){
 errorlogger();
}

Seems awfully simple to me, I can't imagine why it would fail. Any ideas?

EDIT: Was a code typo on my part, question resolved.

JVC
  • 793
  • 3
  • 8
  • 21
  • here ``$file" . "_data`` – Sayed Aug 03 '15 at 20:32
  • If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Aug 03 '15 at 20:32
  • 2
    The OP would be getting a syntax error if that were the case @phplover – Jay Blanchard Aug 03 '15 at 20:33
  • 3
    you are not getting `mysql_error()` info. If either of your `$source` or `$keyword` values contain single quotes your query will fail. – Sean Aug 03 '15 at 20:34
  • 1
    Add error checking, such as `or die(mysql_error())` to your queries. Or you can find the issues in your current error logs. – Jay Blanchard Aug 03 '15 at 20:34
  • 1
    if `offerid` is `AUTO_INCREMENT` then you don't have to set it in the query – Sayed Aug 03 '15 at 20:35
  • 1
    `mysql_error()` will probably tell you you've got an error, but if you `echo` or `var_dump` your `$sql` you will probably spot the error right away. – GolezTrol Aug 03 '15 at 20:35
  • @JayBlanchard He has error checking with `if(!mysql_query($sql))`. – Barmar Aug 03 '15 at 20:37
  • What does `errorlogger()` do? Does it log `mysql_error()`? If it does, the log should have the reason for the failure. – Barmar Aug 03 '15 at 20:38
  • I see that @Barmar, just don't know what he is doing with it. – Jay Blanchard Aug 03 '15 at 20:40
  • Thanks all. errorlogger does log mysql_error, and there is nothing there. No error is generated at all. Good point about the single quotes, however I was testing with the keyword "test-link" so, not an issue there. offerid is not auto increment and should not be, which is why it's defined. Anyone have any other ideas? I'm still stumped. Also to reiterate, it doesn't fail always. Only sometimes. In my test, once out of 20 times. – JVC Aug 03 '15 at 22:11
  • 1.) Try to set the DB name into the DB instance. 2.) Are you sure that $file is the right string? 3.) $offerid,'$source','$keyword' - these are the strings of the varible names, not the variables. They should be concatenated. – koredalin Aug 03 '15 at 22:19
  • 1) I'm sure the dbname is correct because as I mentioned, the query works most of the time. But I can try that anyway just to see. 1) yes $file is definitely the right string. 3) Not sure what you're suggesting, I'm inserting strings contained in those variables. Why would concatenation matter? The query is in double quotes so the variables are converted to their contents at execution, and this aspect works perfectly fine (except for those occasions when the insert fails) – JVC Aug 03 '15 at 22:24

2 Answers2

1

Turns out this was operator error. My logic path did an insert if the data to be entered was new, and an update if it was already present. I had a subtle typo in my update query, so it would fail but not consistently. This gave me the illusion that it was a problem with the insert, but it was not.

JVC
  • 793
  • 3
  • 8
  • 21
0

Try this. If it do not works - execute the query into the console for testing, or into the PhpMyAdmin.

$sql = "INSERT INTO `" . $dbname . "`.`" . $file . "_data` (`offerid`, `source`, `keyword`, `views`) 
    VALUES (" . $offerid . ",'" . $source . "','" . $keyword . "',1)";
koredalin
  • 446
  • 3
  • 11
  • Made no difference at all from a web browser. 20 executions, one failure to insert. However from phpmyadmin the query does not seem to fail, at least not after 40 executions. – JVC Aug 03 '15 at 22:32
  • I have made a new change. Try it now. – koredalin Aug 03 '15 at 22:33
  • I believe I have an idea where the problem lies now, and if I'm right, it's nothing to do with the original insert. I need to experiment a little and will post my results. – JVC Aug 03 '15 at 22:37
  • Try another mysql error dump. It depends on you DB driver (mysql, mysqli, PDO). – koredalin Aug 03 '15 at 22:40
  • Nope, the problem was with a different query that would execute only if the data was already present (update vs. insert). I had a subtly-wrong field name. Looks to be working fine now... operator error! – JVC Aug 03 '15 at 22:44