Insert is working intermittently...

[jetsettt]

Can anyone help me out with this. I have been struggling on and off it for weeks.

 

Heres the description of the problem.

 

I insert into a MySQL database from a query that is performed using an array that is posted from a page. The incoming array contains selected users (unique id's) that are used to lookup the users telephone numbers in a query and then insert into a DB. This code works intermittently. It will insert the records fine and then on the next attempt it may not? There is not pattern to it working and failing. Does anyone have any idea why this is happening? I have tried a loop using the posted array data count and also a loop based on the count of records brought back from the query but to no avail, it is still intermittently working.

 

The '$stripped_message' data is just a text string.

 

<?php
$date = date('Y-m-d H:i:s');

for ($i=0;$i<count($_POST['recipient']);$i++)  // loop based on count of selected users

$uk_mob_number = "44".substr($row_selected_recipients['User_mobile'], 1); // take off the first digit and replace with 44
$unique = makeRandomstring();  // Create a unique string for each entry	

$values.="('$unique','$date','$stripped_message','$uk_mob_number','sent','$date','$userid'),";
// usleep(50000); // Tried a delay to try to fix, did not work
} // End of loop

$values=substr($values,0,-1); // to remove last comma
$query="INSERT INTO `sms` (`sms_unique`, `sms_sent`, `sms_body`, `sms_to`, `sms_status`, `sms_db_entry_time`, `sms_user`) VALUES $values".";";
?>

[Psycho]

Maybe it is a copy/paste error into your post, but that code is invalid. The for() loop has no opening bracket, but there is a closing one. But, even so the code doesn't really make sense to me. It seems it is simply adding n records all with the same values. Not sure why you would want to add a lot of duplicate records.

 

As to the specific problem (assuming the opening bracket really is there) it could be due to the sms_unique field needing to be unique in the database and your process of generating a random number is actually creating duplicates. Trying to generate non-unique, random values is problematic anyway. Why not just use an auto-incrementing int field?

 

Anyway, add some debugging code so you can see what happens and why. You don't show where you actually run the query. So, your query may be failing.

 

$date = date('Y-m-d H:i:s');

for ($i=0; $i<count($_POST['recipient']); $i++)  // loop based on count of selected users
{
    $uk_mob_number = "44".substr($row_selected_recipients['User_mobile'], 1); // take off the first digit and replace with 44
    $unique = makeRandomstring();  // Create a unique string for each entry	
    $values.="('$unique', '$date', '$stripped_message', '$uk_mob_number', 'sent', '$date', '$userid'),";

} // End of loop

$values=substr($values, 0, -1); // to remove last comma
$query="INSERT INTO `sms`
            (`sms_unique`, `sms_sent`, `sms_body`, `sms_to`, `sms_status`, `sms_db_entry_time`, `sms_user`)
        VALUES $values";
$result = mysql_query($query) or die(mysql_error());

echo "Attempting to create {$_POST['recipient']} records.<br>\n";
echo "Records successfully created: " . mysql_affected_rows() . "<br>\n";
echo "Query ran: {$query}<br>\n";

[jetsettt]

Thanks for your reply, appreciated.

 

The for() loop should have the opening bracket in the code below. The whole function does work as intended (I have included more code to make it all clearer). The problem is that the records are not alway inserted. It is intermittent. I re-submit the page and it works or fails ?!?! I am starting to think that it is an issue with MySQL. I am running version 5.0.75 on Ubuntu server 9.04.

 

Heres more of the code.

 

<?php

// Function to generate a random string for each entry
function makeRandomstring() {
  $salt = "abcdefghijklmnopqrstuvwxyz0123456789";
  srand((double)microtime()*1000000); 
  	$i = 0;
  	while ($i <= 31) {
    		$num = rand() % 33;
    		$tmp = substr($salt, $num, 1);
    		$pass = $pass . $tmp;
    		$i++;
  	}
  	return $pass;
}

$stripped_message = ereg_replace("[^A-Za-z0-9 #!?£$*%&.(){}@;:,=+<>_/-]", "", $_POST['message']); // only allow certain sms valid chars

$userid = $_SESSION['kt_login_id'];  // user

// Set $selected as -0 if null so db query does not break. If there is a value continue.
$selected = "-0";
if (isset($_POST['recipient'])) {
$selected = $_POST['recipient'];
}
if (is_array($selected)){    // If $selected is an array continue with adding quotes

foreach($selected as $selected_quoted) {
        $quoted[] = '\''.$selected_quoted.'\''; // loop and encapsulate in single quotes to work with the IN() function. 
    }

$selected = implode(",", $quoted); // implode it as a comma seperated string eg 23,34,39
} else {
$selected = $selected; // no array -> print single value
}
mysql_select_db($database_scheme, $scheme);
$query_selected_recipients = "SELECT Users.User_mobile, Users.User_first_name, Users.User_last_name, Users.User_level, Users.User_random_key FROM Users WHERE Users.User_random_key IN ($selected) AND Users.User_mobile REGEXP '^[0-9]{11}$' ORDER BY Users.User_last_name ASC";
$selected_recipients = mysql_query($query_selected_recipients, $scheme) or die(mysql_error());
$row_selected_recipients = mysql_fetch_assoc($selected_recipients);
$totalRows_selected_recipients = mysql_num_rows($selected_recipients);

$date = date('Y-m-d H:i:s');

for ($i=0; $i<count($_POST['recipient']); $i++)  // loop based on count of selected users
{
    $uk_mob_number = "44".substr($row_selected_recipients['User_mobile'], 1); // take off the first digit and replace with 44
    $unique = makeRandomstring();  // Create a unique string for each entry

$values.="('$unique', '$date', '$stripped_message', '$uk_mob_number', 'sent', '$date', '$userid'),";

} // End of loop

$values=substr($values, 0, -1); // to remove last comma
$query="INSERT INTO `sms`
            (`sms_unique`, `sms_sent`, `sms_body`, `sms_to`, `sms_status`, `sms_db_entry_time`, `sms_user`)
        VALUES $values";
$result = mysql_query($query) or die(mysql_error());

$result=mysql_query($query, $sms_db); // Run the query

?>

[litebearer]

Aside...

http://php.net/manual/en/function.ereg-replace.php

[Psycho]

The for() loop should have the opening bracket in the code below. The whole function does work as intended (I have included more code to make it all clearer). The problem is that the records are not alway inserted. It is intermittent. I re-submit the page and it works or fails ?!?! I am starting to think that it is an issue with MySQL. I am running version 5.0.75 on Ubuntu server 9.04.

 

In other words, you did not implement the debugging code I suggested to give you more information to try and identify the issue. So, what more do you expect from me when you fail to at least attempt what I have provided?

[jetsettt]

There is no debugging output apart from the fact that the insert does not complete intermittently. Nothing changes on the page. What else can I look out for? 

[jetsettt]

When I echo the output everything looks good apart from the final query code.

echo $result=mysql_query($query, $sms_db);

I should get '1' for a completed INSERT query but it sometime returns nothing. I don't know what else to do?

[xyph]

Did you look at the additions Psycho made to your code? Have you tried to implement those?

 

Have you checked for possible MySQL errors after executing your query? There's a function designed to assist with that specifically. You'll have to check the manual and find it, though.

[Psycho]

There is no debugging output apart from the fact that the insert does not complete intermittently. Nothing changes on the page. What else can I look out for? 

 

FAIL!

 

I provided a few lines which would tell you:

1. How many records your code should attempt to create based upon the value of $_POST['recipient']

2. How many records were actually created

3. The text of the query that was actually run

 

I also showed that you should be using a check to see if the query failed. And, if so, echo the error to the page, at least for a dev environment.

[jetsettt]

I have indeed FAILED!.

 

I missed the code that you put and I have now seen and included it, thank you.

 

Here is the output which points to the insert query as not completing.

 

Attempting to create Array records.

Records successfully created: 3

Query ran: INSERT INTO `sms` (`sms_unique`, `sms_sent`, `sms_body`, `sms_to`, `sms_status`, `sms_db_entry_time`, `sms_user`) VALUES ('skft2pzzrdxapfz63l1z6hj6rs00rova','2012-03-05 18:32:45','TEST MESSAGE!','440000000000','sent','2012-03-05 18:32:45','1753'),('c0fb1srn4amcpv4mwnpy4y141wpx3nm5','2012-03-05 18:32:45','TEST MESSAGE!','440000000000','sent','2012-03-05 18:32:45','1753'),('aymueugqagdsccy6h2gnso11mlm3mg5k','2012-03-05 18:32:45','TEST MESSAGE!','440000000000','sent','2012-03-05 18:32:45','1753');

 

 

 

[xyph]

The script is saying it successfully added 3 rows. I don't see how you came to your conclusion.

[jetsettt]

I am coming to that conclusion because if I echo 

$result=mysql_query($query, $sms_db);

it is not outputting '1' as expected if it works. Nothing is output when it fails.

 

ini_set('display_errors',1);
error_reporting(-1);

 

Is outputting...

 

Warning: mysql_free_result(): 48 is not a valid MySQL result resource in /home/site.test/public_html/sms_sending_complete.php on line 236

 

[xyph]

You should read my first reply to this thread. Check for errors.

 

Also:

Trying to generate non-unique, random values is problematic anyway. Why not just use an auto-incrementing int field?

[Psycho]

I am coming to that conclusion because if I echo 

$result=mysql_query($query, $sms_db);

it is not outputting '1' as expected if it works. Nothing is output when it fails.

 

ini_set('display_errors',1);
error_reporting(-1);

 

Is outputting...

 

Warning: mysql_free_result(): 48 is not a valid MySQL result resource in /home/site.test/public_html/sms_sending_complete.php on line 236

 

 

And why are you not checking for the error. You stated in the previous post that you were getting the confirmation record that three records were added. But, was that only when the script succeeds? If you get that message when the INSERTs fail, then the error is NOT in that query, but in another one. I already provided an example of some debugging code you can add to your script. You simply need to do the same thing for any branches in logic where a problem could exists. Output the value of variables, errors, etc. so you can determine where the problem is. The only thing I left out in my previous code was I used the value of $_POST['recipient'] in the debug code instead of the count. But, I did include a reference to using some logic to test for a DB error.

 

The last code you posted included two instances of mysql_query() using the same $query.

$result = mysql_query($query) or die(mysql_error());

 

$result=mysql_query($query, $sms_db); // Run the query

Don't go just slapping code in without knowing what you are doing. If there IS a unique field int eh DB (which you still didn't asnwer) the second query will always fail because you are trying to INSERT the same records twice!

[jetsettt]

I am checking for the error and the error seems to occur when the INSERT query is ran at

$result=mysql_query($query, $sms_db);

 

The code that assembles the data ready for the INSERT works everytime and collects the data.

$query="INSERT INTO `sms` (`sms_unique`, `sms_sent`, `sms_body`, `sms_to`, `sms_status`, `sms_db_entry_time`, `sms_user`,`sms_type`, `sms_processed`) VALUES $values".";";

 

... it just does not insert everytime when the query is run. The debugging code you posted does not seem to check if the INSERT completes successfully, which is my problem.

 

The last bit of code I pasted was a pasting mistake, sorry for the confusion. It should not have had two  instances of mysql_query()

$result = mysql_query($query) or die(mysql_error());

$result=mysql_query($query, $sms_db); // Run the query

 

There is a unique, incrementing field in the table (sms_id). The sms_unique field in the query is only used for unique url calling, so should not cause any DB inserting problems.

 

Thanks for your help, I appreciate it but I don't think this is going anywhere. I have tried to explain though.

[jetsettt]

I have finally sorted this. I could kick myself.

 

All that was need was the adding of the database name to the query. I had two DB connections and mysql_query() would choose to either run or not on each page load.

 

By setting the full DATABASE.TABLE info for the INSERT query ensured that mysql_query() works every time as it knows exactly which DB to insert to.

 

I changed the code from...

 

$query="INSERT INTO `sms`
            (`sms_unique`, `sms_sent`, `sms_body`, `sms_to`, `sms_status`, `sms_db_entry_time`, `sms_user`)
        VALUES $values";

 

to...

 

$query="INSERT INTO smsdatabase1.sms
            (`sms_unique`, `sms_sent`, `sms_body`, `sms_to`, `sms_status`, `sms_db_entry_time`, `sms_user`)
        VALUES $values";

 

Thanks to Pyscho who put me on the right road with the diagnostic code.

 

$result = mysql_query($query, $sms_db) or die ("Query failed: " . mysql_error() . " Actual query: " . $query);

 

mysql_error() showed me that it could not find the DB. It was then possible to quickly identify the problem with the code.