PHP PDO Updating the database with a loop?/foreach?

[happypete]

 

I can get the info from the database fine with this:

 

$sql2 = "SELECT 1, 2, 3, 4, 5, 6
		FROM ratestable
		ORDER BY id ASC";
		$stmt2 = $db->prepare($sql2);
		$stmt2->execute();
		$e2 = $stmt->fetch();

 

and display it with this:

 

 <?php
  while($e2 = $stmt2->fetch())
  {
?>
   <input type="text" name="1" maxlength="10" value="<?php echo $e2['1'] ?>" class="rates" />
   <input type="text" name="2" maxlength="10" value="<?php echo $e2['2'] ?>" class="rates" />
   <input type="text" name="3" maxlength="10" value="<?php echo $e2['3'] ?>" class="rates" />
   <input type="text" name="4" maxlength="10" value="<?php echo $e2['4'] ?>" class="rates" />
   <input type="text" name="5" maxlength="10" value="<?php echo $e2['5'] ?>" class="rates" />
   <input type="text" name="6" maxlength="10" value="<?php echo $e2['6'] ?>" class="rates" />
   <?php
      }
   ?>

 

How do I update it to the database?

 

$sql = "UPDATE rates
                SET title1=?, title2=?, title3=?, title4=?, title5=?, title6=?
THE CODE HERE IS WHERE I AM STUCK....

 

and that's as far as I can get, need to use an array or foreach???????

[will35010]

http://w3schools.com/sql/sql_update.asp

[happypete]

http://w3schools.com/sql/sql_update.asp

 

Thanks, but that link didn't help at all. I can update an entry in a database if it was a single entry it would be something like this:

 

$sql = "UPDATE ratestable
	     SET 1=?, 2=?, 3=?, 4=?, 5=?, 6=? 
	     WHERE id=1";
	     $stmt = $db->prepare($sql);
	     foreach($stmt as $key) { 
	     $stmt->execute(array(
							$_POST['1'],
							$_POST['2'],
							$_POST['3'],
							$_POST['4'],
							$_POST['5'],
							$_POST['6'],
			)
		  );
		}
		$stmt->closeCursor();

 

 

but what I can't do is update multiple entries of the same table at the same time...

 

This code below prints out the data for EACH ID in the 'ratestable' and makes it editable, so I need to be able to update multiple rows at a time, that is what I'm having the trouble with... so I'm thinking it's going to have a loop, foreach or array or something.....??????

 

 <?php
  while($e2 = $stmt2->fetch())
  {
?>
   <input type="text" name="1" maxlength="10" value="<?php echo $e2['1'] ?>" class="rates" />
   <input type="text" name="2" maxlength="10" value="<?php echo $e2['2'] ?>" class="rates" />
   <input type="text" name="3" maxlength="10" value="<?php echo $e2['3'] ?>" class="rates" />
   <input type="text" name="4" maxlength="10" value="<?php echo $e2['4'] ?>" class="rates" />
   <input type="text" name="5" maxlength="10" value="<?php echo $e2['5'] ?>" class="rates" />
   <input type="text" name="6" maxlength="10" value="<?php echo $e2['6'] ?>" class="rates" />
   <?php
      }
   ?>

 

 

 

[will35010]

You would have to put the results in a form and submit any changes to update. Here is a decent tutorial on that http://www.freewebmasterhelp.com/tutorials/phpmysql/7.

 

Example:

 

Step 1: Retrieve Data into form for user to edit with value attribute set with initial value

Step 2: Submit form with any changes

Step 3: Write code to update those changes in the db

 

I hope this helps!

[happypete]

You would have to put the results in a form and submit any changes to update. Here is a decent tutorial on that http://www.freewebmasterhelp.com/tutorials/phpmysql/7.

 

Example:

 

Step 1: Retrieve Data into form for user to edit with value attribute set with initial value

Step 2: Submit form with any changes

Step 3: Write code to update those changes in the db

 

I hope this helps!

 

 

 

1: So yes my results are in a form as per the code above....

2: Yes sent it to this page: rates-editupdate.php ( I know I got this bit right because I have simple update code written on my other pages of the site)

3: 'Write code to update those changes' This is where I need help.. I don't know how to write the code to update the database for each for the ID's.. If it was easy to find I would have found it by now as I have been searching the web since yesterday, hence the fact I'm asking for help on this forum...

 

The link sent me to a simple 'update a database' and a loop, so I look at the loop and cant figure how to write the code based on my example:

 

so somewhere in the code below I will need a loop and a count????

 

$sql = "UPDATE ratestable
	     SET 1=?, 2=?, 3=?, 4=?, 5=?, 6=? 
	     WHERE id=1";
	     $stmt = $db->prepare($sql);
	     foreach($stmt as $key) { 
	     $stmt->execute(array(
							$_POST['1'],
							$_POST['2'],
							$_POST['3'],
							$_POST['4'],
							$_POST['5'],
							$_POST['6'],
			)
		  );
		}
		$stmt->closeCursor();

 

 

 

 

[happypete]

updated the code to this:

 

 

$sql2 = "SELECT id, 1, 2, 3, 4, 5, 6
		FROM ratestable
		ORDER BY id ASC";
		$stmt2 = $db->prepare($sql2);
		$stmt2->execute();
		$e2 = $stmt->fetch();

 

 

 

<?php
  while($e2 = $stmt2->fetch())
  {
?>
<input type="hidden" name="id" value="<?php echo $e2['id']; ?>" />
   <input type="text" name="1" maxlength="10" value="<?php echo $e2['1'] ?>" class="rates" />
   <input type="text" name="2" maxlength="10" value="<?php echo $e2['2'] ?>" class="rates" />
   <input type="text" name="3" maxlength="10" value="<?php echo $e2['3'] ?>" class="rates" />
   <input type="text" name="4" maxlength="10" value="<?php echo $e2['4'] ?>" class="rates" />
   <input type="text" name="5" maxlength="10" value="<?php echo $e2['5'] ?>" class="rates" />
   <input type="text" name="6" maxlength="10" value="<?php echo $e2['6'] ?>" class="rates" />
   <?php
      }
   ?>

 

Can get it to update the last last ID but not all rows:

 

$sql = "UPDATE ratestable
				SET 1=?, 2=?, 3=?, 4=?, 5=?, 6=? 
				WHERE id=?";
		$stmt = $db->prepare($sql);
		$stmt->execute(array(
							$_POST['1'],
							$_POST['2'],
							$_POST['3'],
							$_POST['4'],
							$_POST['5'],
							$_POST['6'],
							$_POST['id'],
							));
		$stmt->closeCursor();

 

[happypete]

sorted it

 

Here is the working code:

 

<input type="text" name="dates[<?php echo $e2['id']; ?>]" maxlength="20" value="<?php echo $e2['dates']; ?>" class="rates" />
   <input type="text" name="night[<?php echo $e2['id']; ?>]" maxlength="20" value="<?php echo $e2['night']; ?>" class="rates" />
   <input type="text" name="week[<?php echo $e2['id']; ?>]" maxlength="20" value="<?php echo $e2['week']; ?>" class="rates" />
   <input type="text" name="month[<?php echo $e2['id']; ?>]" maxlength="20" value="<?php echo $e2['month']; ?>" class="rates" />
   <input type="text" name="min[<?php echo $e2['id']; ?>]" maxlength="20" value="<?php echo $e2['min']; ?>" class="rates" />
   <input type="text" name="rank[<?php echo $e2['id']; ?>]" maxlength="20" value="<?php echo $e2['rank']; ?>" class="rates" />

 

 

$sql3 = "UPDATE ratestable
				SET dates=?, night=?, week=?, month=?, min=?, rank=? 
				WHERE id=?";
		$stmt3 = $db->prepare($sql3);
		if(count($_POST['rank']) > 0)
		{
		  foreach($_POST['rank'] AS $key => $val)
		  {
			  $stmt3->execute(array(
								   $_POST['dates'][$key],
								   $_POST['night'][$key],
								   $_POST['week'][$key],
								   $_POST['month'][$key],
								   $_POST['min'][$key],
								   $val, 
								   $key 
								   
								   ));
		  }}
		$stmt3->closeCursor();