How to update database using ID values & data from 2 equal php arrays

[worldcomingtoanend]

I have 2 equal arrays.  1 has ID values for the database table and the other has the corresponding update data.  The array lengths keep changing as new data at different times is updated.  However they will always be equal.  How do I go about updating my mysql database table by extracting each ID from the array and then use that to commit the necessary update with the corresponding data in another array.  Below is what I have tried but It only updates the last element and nothing else:

$arraysIDs; //array containing database table IDs
$arrayVALUES; // array containing update data

for ($x = 0; $x < count($arraysIDs); $x++){
     $element = "UPDATE TableToUpdate SET
	newUpdateData = '$arrayVALUES[$x]'
	WHERE ID = $arraysIDs[$x])";
						
}

	

Thank you for your help.

[ginerjm]

You didn't REALLY use the above code? It couldn't do what you want.

 

Also - problem with your approach is you have to be absolutely sure that the two arrays are in sync and that item x in one array does in fact correspond to item x in the other array. A better approach might be to use an associative array such as

 

$items[$id] = $value;

 

Now you can simply code

foreach($items as $k=>$v)
{
// do your code to use $k as the id and $v as the value for each pair
}

Edited January 18, 2016 by ginerjm

[worldcomingtoanend]

Thanks ginerjm, i tried what u said but it gives me the same exact result as my first attempt.  Only the last element gets updated.  This is what I did:

$arrayIDs; //array containing database table IDs
$arrayVALUES; // array containing update data
$combine = array_combine($arrayIDs, $arrayVALUES);
foreach($combine as $key => $value)){
     $element = "UPDATE TableToUpdate SET
	newUpdateData = '$value'
	WHERE ID = '$key'";
						
}

[ginerjm]

OK - please explain the first two lines to me:

$arrayIDs; //array containing database table IDs

$arrayVALUES; // array containing update data

 

What do you think those lines are doing for you? They do nothing for me!

 

Now this line:

 

$combine = array_combine($arrayIDs, $arrayVALUES);

 

The same issue that I mentioned earlier here. How do you know that the items in each array are EXACTLY corresponding? If you can truly ensure that they do match then you can stick with your original approach, but if not this new approach isn't any better.

 

 

To finish with your code, this is your query. Where do you execute it and how do you know what the results are:

foreach($combine as $key => $value)){
$element = "UPDATE TableToUpdate SET
newUpdateData = '$value'
WHERE ID = '$key'";

 

Your loop builds a query using the items in the array. But you never run the query. At least not in this code snippet. Care to share?

[worldcomingtoanend]

The reason why I included this:

$arrayIDs; //array containing database table IDs
$arrayVALUES; // array containing update data

is because I do not have problems with that part of the code.  Thats why i put comments to indicate its an array getting values from elsewhere.  I have no problem with that part at all and both my arrays are and will always be equal with corresponding ID and values.   Below is more info regarding your questions

$arrayIDs =$_POST['eid'];  //get ID values(whic come as an array from posted form
$arrayVALUES = $_POST['tDescription'];  // get VALUES which come as an array from posted form
$combine = array_combine($arrayIDs, $arrayVALUES); 
print_r($combine); //print to prove that each array element is EXACTLY corresponding.  I honestly hv no problem with this part.

 

I dont understand when u say the query never runs.  As far as I know the query is running without any problem but its only updating the last element of the array. I hv not included here the database connections.  Do you want to see the entire code with the database connections? I hv no problem with that since I have already made dozens and dozens of connections.  The only hiccup is what I hv stated. Also If the array size is 1, that is, if there is 1 element then it gets updated without any problem.  From my research its got to do with something involving for loops and updating mysql tables.

Edited January 18, 2016 by worldcomingtoanend

[mac_gyver]

your code that is executing the sql query statement is not inside the loop. it's after the end of the loop. therefore it is only executing the sql query statement that was formed in the last pass through the loop.

 

wouldn't you have to execute the sql query statement inside the loop?

[ginerjm]

The code as you posted it doesn't work. Typing the name of the array as a one word sentence doesn't do anything. To post it in the forum with no explanation is just plain improper since it is meaningless.

 

Ok - so you have to input element sets using arrays to collect their values. What happens when the user enters an id in one element but enters no value for it? Then he enters another id and its matching value? Your array will be off by one place since the un-entered value element will not arrive at the server so your two arrays will be out of sync. Of course I'm sure that you are validating all of your input, arent' you?

 

And as I said before and Mac_gyver as added to, you aren't running any queries in this code that you have showed us. Perhaps you want to share the rest of your code with us as I also said before?

[worldcomingtoanend]

concerning the IDs, they are not manually input. They are set as hidden since they are being fetched from the database.  The only thing a user does is update changes on  each form input box and when they click on update changes the new values in each input box is sent to the update page in an array together with a corresponding array containing the database IDs for each input box which technically is a row.   

 

Let me look at few things concerning the queries and will get back.  Otherwise thanks so far for your inputs.

Edited January 18, 2016 by worldcomingtoanend

[Barand]

Use the id as the index for the array of form items. For example

<input type='text' name='description[$id]' value='$description' >

Then when you process the POST data

foreach ($_POST['description'] as $id => $description) {
      // update description where id is $id
}

[worldcomingtoanend]

You guys you are just awesome.  I double checked my queries and i noticed that I was putting my code on top of the if statement that does the query. I must hv messed up unwittingly since i got stuck for a long time and kept experimenting with lots of code. So i simply had to take the closing bracket of the foreach loop and wrapped it around to make the if statement inside and voila everything updated as I needed.  here is what i did:

$arrayIDs; //array containing database table IDs
$arrayVALUES; // array containing update data
$combine = array_combine($arrayIDs, $arrayVALUES);

foreach($combine as $key => $value)) {
$element = "UPDATE TableToUpdate SET
    newUpdateData = '$value'
    WHERE ID = '$key'";

      if($connection->query($element)===TRUE){
	 echo "voila u hv updated";				
      }
                        
}

Thanks ginerjm, Macgyver, Barand for your patience and advice.

[ginerjm]

You still need to sanitize your input values before putting them in a non-prepared query.

[worldcomingtoanend]

By sanitize I guess u mean doing proper validation?

[Barand]

Sanitize - protect from sql injection attacks.

 

Best way is use prepared queries. With a mysqli connection ($mysqli) you would

$element = "UPDATE TableToUpdate SET
    newUpdateData = ?
    WHERE ID = ?";
$stmt = $mysqli->prepare($element);
$stmt->bind_param('si', $value, $key);

foreach($combine as $key => $value) {
    $stmt->execute();                   
}

[worldcomingtoanend]

Thanks Barand, shall implement your suggestion.  Cheers