Solved

MySQL multiple row UPDATE is limited

Posted on 2014-07-23
27
162 Views
Last Modified: 2014-08-08
Hi Guys,
This one really takes the cake! Either that or I do.
I have a MySQL table (RECIPIENTS) containing 180 rows, and I am using 2 documents to perform this task.
Doucment One: "Get_and_Change_RECIPIENTS_DATA.php"
Doucment Two: "UpDate_RECIPIENTS_TABLE.php"

Document Two does the actual update.

Here's the problem.
When Recipient_status in all the rows is set to 2 using the radio buttons in Doucment One -- only the first 18 rows (id from 1 to 18) are UPDATED!
Why is that? Why some rows and not others?

Is there something magical about the number 18?
FYI the data in all the columns in each of the 180 rows is identical except for id and Recipient_status and Recipient_f_name. So the data itself should not be a factor.

To see if there was any magic to the numbers I increased the number of rows in the RECIPIENTS table by 10 rows to 190 rows.

Guess what?
Now it UPDATES the first 27 rows  (id from 1 to 27) --  not a single row beyond that!

What is going on here???

For the heck of it I also tried to UPDATE the Recipient_f_name column, but nothing gets updated beyond row 27. So the problem is not with the Recipient_status column

The RECIPIENTS table is structured thusly: `id`, `Recipient_company`, `Recipient_title`, `Recipient_f_name`, `Recipient_eMail`, `Recipient_status`, `Recipient_phone`
But at this point I am only trying to UPDATE Recipient_status.

This is code I have been using for a number of clients, and it has performed flawlessly -- until now.

I have been scratching my head over this for so long I now have a bald spot!
Please help me from going entirely bald!

Thanks,
Sas

Doucment One: "Get_and_Change_RECIPIENTS_DATA.php"
 // GET EXISTING STATUS DATA //
$result = mysql_query("SELECT * FROM RECIPIENTS");
$num_rows = mysql_num_rows($result);

$Invited = mysql_query("SELECT * FROM RECIPIENTS  WHERE Recipient_status='1' ");
$Invited_rows = mysql_num_rows($Invited);

$Increment = 0;
while($getlist3=mysql_fetch_assoc($getlist2))
{
if($getlist3[Recipient_status] =='1')
{
  $SendInvitation='Yes';
  $invited='1';
           $Invited_YES='CHECKED';
           $Invited_NO='';
}
else if($getlist3[Recipient_status] =='2')
{
  $SendInvitation='No';
  $invited='2';
           $Invited_YES='';
           $Invited_NO='CHECKED';
 }
 // ALTER STATUS OF EXISTING DATA //
echo" <form name='saveORchoose' enctype='multipart/form-data' method='post' id='saveORchoose'>

<input type='radio' style='background-color: {$BG};'  name='ud_Recipient_status[ {$getlist3['id']} ]' value='1' $Invited_YES />Yes&nbsp;&nbsp;&nbsp;&nbsp;
<input type='radio' style='background-color: {$BG};'  name='ud_Recipient_status[ {$getlist3['id']} ]' value='2' $Invited_NO />No&nbsp;&nbsp;

<input type='TEXT'  name='Recipient_id' id='Recipient_id' value='$getlist3[id]'>
";

$Increment++;
echo"
<input type='submit' name='S'  class='button NxtGREEN'  value='Save Changes' onclick='saveORchoose.action=\"UpDate_RECIPIENTS_TABLE.php\";' style='width: 300px; height:22px;  FONT-SIZE:12px; font-weight:normal; font-family:Arial;'/>
</form>
";

Open in new window

Doucment Two: "UpDate_RECIPIENTS_TABLE.php"
 /////////////////////BEGIN UPDATING RECIPIENT'S STATUS ////////////////////////////
$ud_Recipient_status_LC=array();
if( !isset($_POST['ud_Recipient_status']) )
{
	echo "<hr>No ud_Recipient_status detected<hr>";
}
else
{
$ud_Recipient_status = $_POST['ud_Recipient_status'];
}
 $udrs_count = count($ud_Recipient_status);
foreach($ud_Recipient_status as $id => $status)
{
    $id=trim($id);
    $Recipient_status_SQL="UPDATE `RECIPIENTS` SET `Recipient_status`='{$status}'  WHERE `id`='{$id}' ";
        if(mysql_query($Recipient_status_SQL))
        {
 //SUCCESS
			$Success++;
		}
		else
		{
			echo('FAILED TO UPDATE THE RECIPIENT STATUS!'.mysql_error()); //FAILED
			$Fail++;
		}
}
//etc.
/////////////////////END UPDATING RECIPIENT'S STATUS ////////////////////////////

Open in new window

0
Comment
Question by:sasnaktiv
  • 16
  • 10
27 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40215451
Try printing out the value of $Recipient_status_SQL.  What does the WHERE clause contain?  Is that string present in the id field of the rows of the database?
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 40215514
Okay Ray,
Thanks for diving into this.
As you suggested I printed out the values of Recipient_status_SQL
It all seems correct except that it is stopping at id 18.
Sas

Recipient_status_SQL=UPDATE `RECIPIENTS` SET `Recipient_status`='2' WHERE `id`='1'
Recipient_status_SQL=UPDATE `RECIPIENTS` SET `Recipient_status`='2' WHERE `id`='3'
Recipient_status_SQL=UPDATE `RECIPIENTS` SET `Recipient_status`='2' WHERE `id`='4'
Recipient_status_SQL=UPDATE `RECIPIENTS` SET `Recipient_status`='2' WHERE `id`='5'
Recipient_status_SQL=UPDATE `RECIPIENTS` SET `Recipient_status`='2' WHERE `id`='6'
Recipient_status_SQL=UPDATE `RECIPIENTS` SET `Recipient_status`='2' WHERE `id`='7'
Recipient_status_SQL=UPDATE `RECIPIENTS` SET `Recipient_status`='2' WHERE `id`='8'
Recipient_status_SQL=UPDATE `RECIPIENTS` SET `Recipient_status`='2' WHERE `id`='9'
Recipient_status_SQL=UPDATE `RECIPIENTS` SET `Recipient_status`='2' WHERE `id`='10'
Recipient_status_SQL=UPDATE `RECIPIENTS` SET `Recipient_status`='2' WHERE `id`='11'
Recipient_status_SQL=UPDATE `RECIPIENTS` SET `Recipient_status`='2' WHERE `id`='12'
Recipient_status_SQL=UPDATE `RECIPIENTS` SET `Recipient_status`='2' WHERE `id`='13'
Recipient_status_SQL=UPDATE `RECIPIENTS` SET `Recipient_status`='2' WHERE `id`='14'
Recipient_status_SQL=UPDATE `RECIPIENTS` SET `Recipient_status`='2' WHERE `id`='15'
Recipient_status_SQL=UPDATE `RECIPIENTS` SET `Recipient_status`='2' WHERE `id`='16'
Recipient_status_SQL=UPDATE `RECIPIENTS` SET `Recipient_status`='2' WHERE `id`='17'
Recipient_status_SQL=UPDATE `RECIPIENTS` SET `Recipient_status`='2' WHERE `id`='18'
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40215569
Well, rather than guess, I think it would be best to visualize the data.  Please add var_dump($_POST) to the top of the second script.  Use "view source" in your browser and copy/paste the results here.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40215574
Also, be sure to add error_reporting(E_ALL) to the top of all of the scripts.  We want to be sure we're not relying on an undefined variable.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40215577
And I also think we are going to need to see the REAL and complete scripts.  The first script posted with the question fails with a PHP parse error.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 40215635
Okay Ray,
I did not get any errors.
This may or may not be what you are looking for, but the following is what I get from var_dump($_POST) : (I made some changes for securtiy0

array(42) { ["Recipient_id"]=> array(1) { [17]=> string(8) " 18   *" } ["id"]=> string(1) " " ["ud_Recipient_title"]=> array(18) { [1]=> string(3) "Dr." [2]=> string(3) "Mr." [3]=> string(3) "Mr." [4]=> string(3) "Mr." [5]=> string(3) "Mr." [6]=> string(3) "Mr." [7]=> string(3) "Mr." [8]=> string(3) "Mr." [9]=> string(3) "Mr." [10]=> string(3) "Mr." [11]=> string(3) "Mr." [12]=> string(3) "Mr." [13]=> string(3) "Mr." [14]=> string(3) "Mr." [15]=> string(3) "Mr." [16]=> string(3) "Mr." [17]=> string(3) "Mr." [18]=> string(3) "Mr." } ["ud_Recipient_f_name"]=> array(18) { [1]=> string(16) "First Customer 1" [2]=> string(13) "Bounce Back 2" [3]=> string(16) "Rotation Round 3" [4]=> string(16) "Rotation Round 4" [5]=> string(16) "Rotation Round 5" [6]=> string(16) "Rotation Round 6" [7]=> string(16) "Rotation Round 7" [8]=> string(16) "Rotation Round 8" [9]=> string(16) "Rotation Round 9" [10]=> string(17) "Rotation Round 10" [11]=> string(17) "Rotation Round 11" [12]=> string(17) "Rotation Round 12" [13]=> string(17) "Rotation Round 13" [14]=> string(17) "Rotation Round 14" [15]=> string(17) "Rotation Round 15" [16]=> string(17) "Rotation Round 16" [17]=> string(17) "Rotation Round 17" [18]=> string(17) "Rotation Round 18" } ["ud_Recipient_phone"]=> array(18) { [1]=> string(0) "" [2]=> string(0) "" [3]=> string(0) "" [4]=> string(0) "" [5]=> string(0) "" [6]=> string(0) "" [7]=> string(0) "" [8]=> string(0) "" [9]=> string(0) "" [10]=> string(0) "" [11]=> string(0) "" [12]=> string(0) "" [13]=> string(0) "" [14]=> string(0) "" [15]=> string(0) "" [16]=> string(0) "" [17]=> string(0) "" [18]=> string(0) "" } ["ud_Recipient_eMail"]=> array(18) { [1]=> string(25) "sas@dualcorporation.com" [2]=> string(26) "4sas@dualcorporation.com" [3]=> string(25) "sas@dualcorporation.com" [4]=> string(25) "sas@dualcorporation.com" [5]=> string(25) "sas@dualcorporation.com" [6]=> string(25) "sas@dualcorporation.com" [7]=> string(25) "sas@dualcorporation.com" [8]=> string(25) "sas@dualcorporation.com" [9]=> string(25) "sas@dualcorporation.com" [10]=> string(25) "sas@dualcorporation.com" [11]=> string(25) "sas@dualcorporation.com" [12]=> string(25) "sas@dualcorporation.com" [13]=> string(25) "sas@dualcorporation.com" [14]=> string(25) "sas@dualcorporation.com" [15]=> string(25) "sas@dualcorporation.com" [16]=> string(25) "sas@dualcorporation.com" [17]=> string(25) "sas@dualcorporation.com" [18]=> string(25) "sas@dualcorporation.com" } ["ud_Recipient_zip"]=> array(18) { [1]=> string(5) "10022" [2]=> string(5) "10022" [3]=> string(5) "10022" [4]=> string(5) "10022" [5]=> string(5) "10022" [6]=> string(5) "10022" [7]=> string(5) "10022" [8]=> string(5) "10022" [9]=> string(5) "10022" [10]=> string(5) "10022" [11]=> string(5) "10022" [12]=> string(5) "10022" [13]=> string(5) "10022" [14]=> string(5) "10022" [15]=> string(5) "10022" [16]=> string(5) "10022" [17]=> string(5) "10022" [18]=> string(5) "10022" } ["ud_Recipient_status"]=> array(17) { [" 1 "]=> string(1) "2" [" 3 "]=> string(1) "2" [" 4 "]=> string(1) "2" [" 5 "]=> string(1) "2" [" 6 "]=> string(1) "2" [" 7 "]=> string(1) "2" [" 8 "]=> string(1) "2" [" 9 "]=> string(1) "2" [" 10 "]=> string(1) "2" [" 11 "]=> string(1) "2" [" 12 "]=> string(1) "2" [" 13 "]=> string(1) "2" [" 14 "]=> string(1) "2" [" 15 "]=> string(1) "2" [" 16 "]=> string(1) "2" [" 17 "]=> string(1) "2" [" 18 "]=> string(1) "2" } ["Recipient_type"]=> string(6) "Patron" ["aTable"]=> string(24) "1sastrxinteractivecom1" ["aTitle"]=> string(3) "Mr." ["aFname"]=> string(6) "Yourpal" ["aMname"]=> string(0) "" ["aLname"]=> string(5) "TestA" ["account_name"]=> string(9) "Your Pal 1" ["aEmail"]=> string(0) "" ["admin_eMail"]=> string(0) "" ["P"]=> string(1) "a" ["wrapperURL"]=> string(13) "2127532001510" ["expiration_date"]=> string(0) "" ["A_Key"]=> string(23) "1sastrxinteractivecom" ["GoLearnPromo"]=> string(1) "1" ["FromMenuPromo"]=> string(0) "" ["D_TableName"]=> string(0) "" ["ManagerPin"]=> string(0) "" ["A_phone"]=> string(1) " " ["Recipient_eMail"]=> string(0) "" ["FromManagerMenu"]=> string(1) "1" ["mFname"]=> string(0) "" ["mMname"]=> string(0) "" ["mLname"]=> string(0) "" ["rTable"]=> string(24) "RECIPIENTS" ["Recipient_eMail"]=> string(25) "sas@dualcorporation.com" ["Recipient_company"]=> string(0) "" ["LIMIT_start"]=> string(1) "0" ["ud_LIMIT_start"]=> string(1) "4" ["ud_RotationROUND"]=> string(1) "4" ["LIMIT_end"]=> string(1) "4" ["FromWhere"]=> string(26) "M_ManageRecipientList.php" ["DoWhat"]=> string(8) "SendMail" ["AvailableCOUNT"]=> string(1) "3" ["Invited_rows"]=> string(3) "168" }
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40215677
I did not get any errors.
But you also did not post the complete script.  Are you sure you're testing the right version of the script?  Can we please see the whole thing?  Thanks.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40215680
Here is the var_dump() output, reformatted a bit to make it more readable.  If you use "view source" then copy/paste into the code snippet, it preserves the formatting.  Please do that, going forward.

Is this what you expected to post to the action script?  I don't really understand the "Recipient_id" array.  Why does it have a key of "17" and that string of data?

array(42) 
{ ["Recipient_id"]=> array(1) 
  { [17]=> string(8) " 18   *" 
  } 
  ["id"]=> string(1) " " 
  ["ud_Recipient_title"]=> array(18) 
  { [1]=> string(3) "Dr." 
    [2]=> string(3) "Mr." 
    [3]=> string(3) "Mr." 
    [4]=> string(3) "Mr." 
    [5]=> string(3) "Mr." 
    [6]=> string(3) "Mr." 
    [7]=> string(3) "Mr." 
    [8]=> string(3) "Mr." 
    [9]=> string(3) "Mr." 
    [10]=> string(3) "Mr." 
    [11]=> string(3) "Mr." 
    [12]=> string(3) "Mr." 
    [13]=> string(3) "Mr." 
    [14]=> string(3) "Mr." 
    [15]=> string(3) "Mr." 
    [16]=> string(3) "Mr." 
    [17]=> string(3) "Mr." 
    [18]=> string(3) "Mr." 
  } 
  ["ud_Recipient_f_name"]=> array(18) 
  { [1]=> string(16) "First Customer 1" 
    [2]=> string(13) "Bounce Back 2" [3]=> string(16) "Rotation Round 3" [4]=> string(16) "Rotation Round 4" [5]=> string(16) "Rotation Round 5" [6]=> string(16) "Rotation Round 6" [7]=> string(16) "Rotation Round 7" [8]=> string(16) "Rotation Round 8" [9]=> string(16) "Rotation Round 9" [10]=> string(17) "Rotation Round 10" [11]=> string(17) "Rotation Round 11" [12]=> string(17) "Rotation Round 12" [13]=> string(17) "Rotation Round 13" [14]=> string(17) "Rotation Round 14" [15]=> string(17) "Rotation Round 15" [16]=> string(17) "Rotation Round 16" [17]=> string(17) "Rotation Round 17" 
    [18]=> string(17) "Rotation Round 18" 
  } 
  ["ud_Recipient_phone"]=> array(18) 
  { [1]=> string(0) "" 
    [2]=> string(0) "" [3]=> string(0) "" [4]=> string(0) "" [5]=> string(0) "" [6]=> string(0) "" [7]=> string(0) "" [8]=> string(0) "" [9]=> string(0) "" [10]=> string(0) "" [11]=> string(0) "" [12]=> string(0) "" [13]=> string(0) "" [14]=> string(0) "" [15]=> string(0) "" [16]=> string(0) "" [17]=> string(0) "" 
    [18]=> string(0) "" 
  } 
  ["ud_Recipient_eMail"]=> array(18) 
  { [1]=> string(25) "sas@dualcorporation.com" 
    [2]=> string(26) "4sas@dualcorporation.com" [3]=> string(25) "sas@dualcorporation.com" [4]=> string(25) "sas@dualcorporation.com" [5]=> string(25) "sas@dualcorporation.com" [6]=> string(25) "sas@dualcorporation.com" [7]=> string(25) "sas@dualcorporation.com" [8]=> string(25) "sas@dualcorporation.com" [9]=> string(25) "sas@dualcorporation.com" [10]=> string(25) "sas@dualcorporation.com" [11]=> string(25) "sas@dualcorporation.com" [12]=> string(25) "sas@dualcorporation.com" [13]=> string(25) "sas@dualcorporation.com" [14]=> string(25) "sas@dualcorporation.com" [15]=> string(25) "sas@dualcorporation.com" [16]=> string(25) "sas@dualcorporation.com" [17]=> string(25) "sas@dualcorporation.com" 
    [18]=> string(25) "sas@dualcorporation.com" 
  } 
  ["ud_Recipient_zip"]=> array(18) 
  { [1]=> string(5) "10022" 
    [2]=> string(5) "10022" [3]=> string(5) "10022" [4]=> string(5) "10022" [5]=> string(5) "10022" [6]=> string(5) "10022" [7]=> string(5) "10022" [8]=> string(5) "10022" [9]=> string(5) "10022" [10]=> string(5) "10022" [11]=> string(5) "10022" [12]=> string(5) "10022" [13]=> string(5) "10022" [14]=> string(5) "10022" [15]=> string(5) "10022" [16]=> string(5) "10022" [17]=> string(5) "10022" 
    [18]=> string(5) "10022" 
  } 
  ["ud_Recipient_status"]=> array(17) 
  { [" 1 "]=> string(1) "2" 
    [" 3 "]=> string(1) "2" 
    [" 4 "]=> string(1) "2" 
    [" 5 "]=> string(1) "2" 
    [" 6 "]=> string(1) "2" 
    [" 7 "]=> string(1) "2" 
    [" 8 "]=> string(1) "2" 
    [" 9 "]=> string(1) "2" 
    [" 10 "]=> string(1) "2" 
    [" 11 "]=> string(1) "2" 
    [" 12 "]=> string(1) "2" 
    [" 13 "]=> string(1) "2" 
    [" 14 "]=> string(1) "2" 
    [" 15 "]=> string(1) "2" 
    [" 16 "]=> string(1) "2" 
    [" 17 "]=> string(1) "2" 
    [" 18 "]=> string(1) "2" 
  } 
  ["Recipient_type"]=> string(6) "Patron" 
  ["aTable"]=> string(24) "1sastrxinteractivecom1" 
  ["aTitle"]=> string(3) "Mr." 
  ["aFname"]=> string(6) "Yourpal" 
  ["aMname"]=> string(0) "" 
  ["aLname"]=> string(5) "TestA" 
  ["account_name"]=> string(9) "Your Pal 1" 
  ["aEmail"]=> string(0) "" 
  ["admin_eMail"]=> string(0) "" 
  ["P"]=> string(1) "a" 
  ["wrapperURL"]=> string(13) "2127532001510" 
  ["expiration_date"]=> string(0) "" 
  ["A_Key"]=> string(23) "1sastrxinteractivecom" 
  ["GoLearnPromo"]=> string(1) "1" 
  ["FromMenuPromo"]=> string(0) "" 
  ["D_TableName"]=> string(0) "" 
  ["ManagerPin"]=> string(0) "" 
  ["A_phone"]=> string(1) " " 
  ["Recipient_eMail"]=> string(0) "" 
  ["FromManagerMenu"]=> string(1) "1" 
  ["mFname"]=> string(0) "" 
  ["mMname"]=> string(0) "" 
  ["mLname"]=> string(0) "" 
  ["rTable"]=> string(24) "RECIPIENTS" 
  ["Recipient_eMail"]=> string(25) "sas@dualcorporation.com" 
  ["Recipient_company"]=> string(0) "" 
  ["LIMIT_start"]=> string(1) "0" 
  ["ud_LIMIT_start"]=> string(1) "4" 
  ["ud_RotationROUND"]=> string(1) "4" 
  ["LIMIT_end"]=> string(1) "4" 
  ["FromWhere"]=> string(26) "M_ManageRecipientList.php" 
  ["DoWhat"]=> string(8) "SendMail" 
  ["AvailableCOUNT"]=> string(1) "3" 
  ["Invited_rows"]=> string(3) "168" 
} 

Open in new window

0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 40215713
Yes Ray,
Those are the values I expected, but I did not expect it to stop at 18!
Notice that the "Invited_rows" is 168.
That number (168) is correct since 4 rows should not have been updated and they are not included in "Invited_rows"
So, the total of the full table is correct at 190.

PS: I see what you mean by "View Source"

Now, below is the script:
<?php
error_reporting(E_ALL);
ini_set('display_errors', 0);

include "../../_db/db_BizMail.inc";
/////////////////////BEGIN UPDATING RECIPIENT'S STATUS ////////////////////////////
$ud_Recipient_status_LC=array();
if( !isset($_POST['ud_Recipient_status']) )
{
	echo "<hr>No ud_Recipient_status detected<hr>";
}
else
{
$ud_Recipient_status = $_POST['ud_Recipient_status'];
}
 $udrs_count = count($ud_Recipient_status);
foreach($ud_Recipient_status as $id => $status)
{
    $id=trim($id);
    $Recipient_status_SQL="UPDATE `RECIPIENT` SET `Recipient_status`='{$status}'  WHERE `id`='{$id}' ";
        if(mysql_query($Recipient_status_SQL))
        {
 //SUCCESS
			$Success++;
		}
		else
		{
			echo('FAILED TO UPDATE THE RECIPIENT STATUS!'.mysql_error()); //FAILED
			$Fail++;
		}
}
//etc.
/////////////////////END UPDATING RECIPIENT'S STATUS ////////////////////////////
var_dump($_POST)
?>

Open in new window

0
 
LVL 58

Expert Comment

by:Gary
ID: 40215826
How is the data being posted?
Are you sure all these fields are within the FORM?
The only thing that makes sense is that the fields are not in the form or whatever you are using, maybe js, is not grabbing all the fields.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 40215847
Hi Gary,
Of course the fields are within the FORM. If the fields were not in the form they would not UPDATE at all. But only a portion is being UPDATED. And the only one I'm concerned about at the moment is the "Recipient_status".

PS: I like your icon.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40215903
Here is the contents of $ud_Recipient_status

  ["ud_Recipient_status"]=> array(17) 
  { [" 1 "]=> string(1) "2" 
    [" 3 "]=> string(1) "2" 
    [" 4 "]=> string(1) "2" 
    [" 5 "]=> string(1) "2" 
    [" 6 "]=> string(1) "2" 
    [" 7 "]=> string(1) "2" 
    [" 8 "]=> string(1) "2" 
    [" 9 "]=> string(1) "2" 
    [" 10 "]=> string(1) "2" 
    [" 11 "]=> string(1) "2" 
    [" 12 "]=> string(1) "2" 
    [" 13 "]=> string(1) "2" 
    [" 14 "]=> string(1) "2" 
    [" 15 "]=> string(1) "2" 
    [" 16 "]=> string(1) "2" 
    [" 17 "]=> string(1) "2" 
    [" 18 "]=> string(1) "2" 
  } 

Open in new window

In the action script we find this:

foreach($ud_Recipient_status as $id => $status)

Open in new window

That suggests two important things.  First, there are only seventeen rows that can get updated -- that is just what the code permits, nothing more.  Second, the $id value is not a number, but a blank concatenated to a number concatenated to a blank.  This is probably corrected by the trim() function, but I wonder why the code that generated the post request created the data in this way.  That is what we call a code smell.  And it demands further investigation to be sure it's not a lurking problem.

Two recommendations:  FIrst, stop the process and hire a professional programmer to write this code for you.  It will not cost you much money at all, and you can get a professional result very fast with something as simple as this. Second, if you want to learn programming, take a class at a college or some other formal study exercise, where you can gradually move forward into progressive learning.  Trial and error is just not a very effective way to teach yourself anything as potentially complicated as computer programming.

If you want to post the CREATE TABLE statements for this application and post the test data, I'll try to show you how you might go about this.  The existing code has a lot of unnecessary moving parts that are probably distractions to the central objectives.  If we can start with the data only, and none of the unworking code, we will probably be days ahead!

If you want to learn this stuff, here are some learning resources that may be helpful.  Be prepared to spend some time and depth of thought, and detailed practice, if you want to someday own these skills as your own.  It's not rocket science, but it's also not something you can pick up over a few weeks.

How to get started with PHP (the right way):
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

Things to avoid (like the plague):
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12293-AntiPHPatterns-and-AntiPHPractices.html

The general design pattern for table maintenance scripts:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12335-PHP-and-MySQLi-Table-Maintenance.html
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 40215928
Okay Ray a few of questions.
First your statement:
 "First, there are only seventeen rows that can get updated -- that is just what the code permits, nothing more. "
Why is that? What's limiting the code to 17?
Why don't I have this problem with other tables that have many more rows than 17?

Your second statement:  
"...stop the process and hire a professional programmer to write this code for you.  It will not cost you much money at all, and you can get a professional result very fast with something as simple as this.
Any recommendations?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40215955
only seventeen rows that can get updated
This happens because the FORM script sends an array to the ACTION script that has only seventeen elements.  It's evident in the data posted here.  Given other data, there may be variants.  If you can post the CREATE TABLE statements and the data to load the tables we might be able to find an alternative.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 40216713
Good morning Ray,

Can you show me exactly where it is evident that the ...
ACTION script that has only seventeen elements.  It's evident in the data posted here.

Also why don't I have the identical problem with other websites I've created using the identical code?

Thanks,
Sas
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40216735
only seventeen elements
Please look at the output of var_dump() here.

why don't I have the identical problem with other websites I've created using the identical code?
No idea at all.  Probably there is a data-dependent component and this data is different.  

I can't do much with the code fragments posted in this question; the first one doesn't even pass the PHP parser.  So let's not look at code that doesn't work.  But once we can see the CREATE TABLE statements and the test data set to populate these tables we can start to make progress.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 40216928
Hi Ray,
As usual, I'm puzzled.
In the first place, where is it getting these values from: [17]=> string(8) " 18   *"
array(42) 
{ ["Recipient_id"]=> array(1) 
  { [17]=> string(8) " 18   *" 
  } 
  ["id"]=> string(1) " " 

Open in new window


Here's the create table script you asked for:
mysql_query("CREATE TABLE $Recipient_TableName(
id INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(id),

Recipient_title VARCHAR(5),
Recipient_f_name VARCHAR(60),
Recipient_phone VARCHAR(20), 
Recipient_eMail VARCHAR(90),
Recipient_zip VARCHAR(5),
Recipient_status VARCHAR(1),
Recipient_type VARCHAR(90))")
or die("L-1814<P>".mysql_error());  

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40216942
Great - I can start to work with that.  Can we please get the test data to load into the table, too?
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 40217407
Here's the test data Ray,
I've only presented 5 rows beginning with id 16 and ending with id 19.
They were all created by duplicating id 3.

This is id 3:
3,Mr.,Round 3 ,2120000000,sas@dualcorporation.com,10022,2,patron,

ids 16 - 19:
16,Mr.,Rotation Round 16 ,2120000000,sas@dualcorporation.com,10022,2,patron,
17,Mr.,Rotation Round 17 ,2120000000,sas@dualcorporation.com,10022,2,patron,
18,Mr.,Rotation Round 18 ,2120000000,sas@dualcorporation.com,10022,2,patron,
19,Mr.,Round 3 ,2120000000,sas@dualcorporation.com,10022,2,patron,
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40217456
With only 5 rows and all of them having a "2" in the status field, this won't lead us to much of a test.  While I create some test data for you, please do two things.  First, please post the complete and true copy of the first PHP script that was at the top of this question (the one that failed the PHP parser because it was incomplete).  Second, please read this article to see why test data is important and why we use it to make our development faster and more accurate.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_7830-A-Quick-Tour-of-Test-Driven-Development.html
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 40217551
Here are two scripts that demonstrate the essential moving parts.  The first script creates and loads a temporary table and shows how to query the table to find the rows that are candidates for updates.  

Note the test data names given to the rows in the table (line 60).  By using different names with the row number and the original status, we can follow this information through the logic of the script.

After the table is loaded, the moving parts start at line 108.  We run a query to find the existing rows with status=1 and create radio buttons that allow us to request a change in the status.  Why two buttons for each row?  Because if you have only one radio button and you change your mind after you set it, there is no way to un-set it if there is only one button.

Once the radio buttons have been prepared in a variable, we create the form (line 125).

<?php // demo/temp_sas1.php

// SEE http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28482438.html

ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';

// SHOW THE REQUEST
var_dump($_POST);

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE recipients
( r_id     INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, r_f_name VARCHAR(60) NOT NULL DEFAULT ''
, r_status VARCHAR(1)  NOT NULL DEFAULT ''
)
"
;

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// OUR TEST DATA FOR THE TABLE
$test_data_arrays = array
( array( 'r_f_name' => 'NAME01_1', 'r_status' => '1')
, array( 'r_f_name' => 'NAME02_2', 'r_status' => '2')
, array( 'r_f_name' => 'NAME03_1', 'r_status' => '1')
, array( 'r_f_name' => 'NAME04_2', 'r_status' => '2')
, array( 'r_f_name' => 'NAME05_2', 'r_status' => '2')
, array( 'r_f_name' => 'NAME06_1', 'r_status' => '1')
, array( 'r_f_name' => 'NAME07_1', 'r_status' => '1')
, array( 'r_f_name' => 'NAME08_1', 'r_status' => '1')
, array( 'r_f_name' => 'NAME09_1', 'r_status' => '1')
)
;
// LOADING OUR DATA INTO THE TABLE
foreach ($test_data_arrays as $person)
{
    // ESCAPE THE DATA FOR SAFE USE IN A QUERY
    $safe_r_f_name  = $mysqli->real_escape_string($person['r_f_name']);
    $safe_r_status  = $mysqli->real_escape_string($person['r_status']);

    // CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
    $sql = "INSERT INTO recipients ( r_f_name, r_status ) VALUES ( '$safe_r_f_name', '$safe_r_status' )";

    // RUN THE QUERY TO INSERT THE ROW
    $res = $mysqli->query($sql);

    // IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$res)
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }


    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    $r_id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_r_f_name $safe_r_status</b> WITH AUTO_INCREMENT ID = $r_id" . PHP_EOL;
}
echo PHP_EOL;

// DEMONSTRATE HOW TO PRODUCE A FORM THAT HAS ALL THE ROWS WITH STATUS=1 AND RADIO BUTTONS
$radios = NULL;

// QUERY GETS THE CANDIDATES WITH STATUS=1 FOR UPDATE TO STATUS=2
$sql = "SELECT r_id, r_f_name, r_status FROM recipients WHERE r_status = '1' ORDER BY r_id";
$res = $mysqli->query($sql);
while ($row = $res->fetch_object())
{
    // USE HEREDOC NOTATION TO CREATE EACH INPUT CONTROL
    $radio = <<<RADIO
    $row->r_f_name Status=1 <input type="radio" name="r_ids[$row->r_id]" value="1" checked/> Status=2 <input type="radio" name="r_ids[$row->r_id]" value="2" />
RADIO;

    // COLLECT THE INPUT CONTROLS HERE
    $radios .= PHP_EOL . $radio;
}

// GENERATE THE FORM
$form = <<<FORM
<form method="post" action="temp_sas2.php">
$radios
<input type="submit" />
</form>
FORM;

echo $form;

Open in new window

0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 40217566
Now on to the second script.  Please be thoughtful about security and Accept Only Known Good Values.  I deliberately omitted the security checks here so that I could show just the logic needed to create the queries that would update the table.  This script does not actually run those queries, it just creates them and prints them.  In order to run them, I would have to create a permanent table just for this E-E question and if I did that for every E-E question that needed to show a PHP+MySQL example, I would have too many tables to deal with!

<?php // demo/temp_sas2.php

// SEE http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28482438.html

ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';

// SHOW THE REQUEST
var_dump($_POST);

// SHOW HOW TO SET UP THE QUERIES THAT WOULD MAKE THE APPROPRIATE UPDATES
foreach ($_POST['r_ids'] as $r_id => $r_status)
{
    $sql = "UPDATE recipients SET r_status = '$r_status' WHERE r_id = '$r_id' LIMIT 1";
    echo PHP_EOL . $sql;
}

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40217572
And finally, there is this: I see you're still working in the MySQL Extension, which is deprecated and will be removed from PHP.  You might want to get a head start on learning and using a different extension.  This article can help you understand why PHP is doing away with MySQL support and what you must do to keep your scripts running.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Best of luck with your project, ~Ray
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 40217664
Yes Ray,
The server is using PHP5.2
If I were to set it to 5.4 I would have to rewrite hundreds of php documents that run this entire website. And I don't have the financial resources to pay someone to do that.
I have no problems sticking with PHP5.2
After all, any thing that is deprecated and removed from PHP is only going to be removed from PHP5.4+ without having any effect on PHP5.2 or earlier versions -- Am I correct?
So even if my code is not mysqli it should continue to function properly with PHP5.2, -- correct?
Do you still need the code to the ACTION script?
Sas
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40217761
So even if my code is not mysqli it should continue to function properly with PHP5.2, -- correct?
That's the wrong question.  PHP 5.2 is not supported any more, at all, not even for security fixes.  What that means is that if (when) a hacker identifies a deadly security hole in PHP 5.2 and begins to attack PHP web sites, you won't have the option to survive any more.  Your site will be a sitting duck.  Consider the risk/reward analysis.  The logic is similar to saying, "I know my tires are worn out and I really should replace them, but as long as they're not flat, I don't have to do anything."  The risk is a blowout at highway speed.  I don't even know how to profile the reward.

I recently began a project with a non-profit here in the DC suburbs.  They have a PHP 5.2 web site that is sporting the twin security risks of magic quotes and register globals.  After looking at their code, I realized that the site cannot be remediated at all.  They relied on PHP suppression of Notice messages and collected a variety of anti-practices into their code over the years.  The result?  They will have to rewrite the site entirely because they can no longer make incremental changes.  So what is the value of following best-practices?  In their case, it's going to run into six figures, or their site is simply going to go down one day.  Conference registration, membership, dues, member communications -- all of it will be lost.

That's why professional programmers are always learning and advancing the state of the art.  The non-profit wanted to make a small membership change, or at least they thought it was small.  They have two types of membership and want to add a third.  Should be easy -- just change the member object to add another option, right?  But here is the problem with that.  There is no member object -- everything is written in PHP procedural code.  And all of the PHP code that touched the member data base table assumed that there would be only two types of membership.  I counted 408 code points that needed to change.

The MySQL extension virtually guarantees that you cannot take advantage of object-oriented design and programming.  In my opinion, that's too big a downside to tolerate.  So my advice would be to set a hard-and-fast coding standard.  From this day forward you must never write another mysql_query() statement again.  It's just a bad idea from a bygone era, and one you don't have to carry forward.
0
 
LVL 1

Author Closing Comment

by:sasnaktiv
ID: 40249390
Ray,
Thanks for the attention and advice.
Sas
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40249432
Thanks for the points! ~Ray
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how can I dected if URL has variable? 7 23
update field on focusout 15 23
PHP string issue 5 17
Wordpress Body Class 5 12
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now