PHP PDO Updating an array

happypete · March 22, 2011

Hi,

I'm a beginner at PHP and am I'm trying to update an array but cant get it to work:

This is my 'rates' page, There are 6 boxes that are the headings, and 6 boxes per line below for the data, I can get the headings to save but not the data lines below:

rates-edit.php

<?php

error_reporting(E_ALL);
ini_set('display_errors', 2);

     // Include the necessary files
    include_once '../inc/db.inc.php';

    // Open a database connection
    $db = new PDO(DB_INFO, DB_USER, DB_PASS);

  	// Extract details from database
    $sql = "SELECT title1, title2, title3, title4, title5, title6 
  			FROM rates
		WHERE id=1";
		$stmt = $db->prepare($sql);
		$stmt->execute();
		$e = $stmt->fetch(); 

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

?>
<!DOCTYPE html>
<html lang="en">
<head>

   <h3>Rates Table</h3>
  
   <form method="post" action="rates-editupdate.php" enctype="multipart/form-data">
   
   <input type="text" name="title1" maxlength="10" value="<?php echo $e['title1'] ?>" class="rates" />
   <input type="text" name="title2" maxlength="10" value="<?php echo $e['title1'] ?>" class="rates" />
   <input type="text" name="title3" maxlength="10" value="<?php echo $e['title3'] ?>" class="rates" />
   <input type="text" name="title4" maxlength="10" value="<?php echo $e['title4'] ?>" class="rates" />
   <input type="text" name="title5" maxlength="10" value="<?php echo $e['title5'] ?>" class="rates" />
   <input type="text" name="title6" maxlength="10" value="<?php echo $e['title6'] ?>" class="rates" />
   
   <?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
      }
   ?>
   
              <input id="button-upload" class="button" type="submit" name="submit" value="Save Changes" />
              <input id="button-upload" class="button" type="submit" name="submit" value="Add New Row" />
              </form>

</body>
</html>

This is the page that updates the database, I cant get the loop/foreach to work...

rates-editupdate.php

<?php

error_reporting(E_ALL);
ini_set('display_errors', 2);

     // Include the necessary files
    include_once '../inc/db.inc.php';

    // Open a database connection
    $db = new PDO(DB_INFO, DB_USER, DB_PASS);


// Check if coming from a POST command and Save Changes
// THIS BIT WORKS 
    if($_SERVER['REQUEST_METHOD']=='POST'
    && $_POST['submit']=='Save Changes')
{
        $sql = "UPDATE rates
                SET title1=?, title2=?, title3=?, title4=?, title5=?, title6=?
                WHERE id=1
                LIMIT 1";
        $stmt = $db->prepare($sql);
        $stmt->execute(
            array(
                $_POST['title1'],
                $_POST['title2'],
			$_POST['title3'],
			$_POST['title4'],
			$_POST['title5'],
			$_POST['title6']
            )
        );
        $stmt->closeCursor();

	//
	//		THIS IS THE BIT THAT DOES NOT UPDATE:
	//

	$sql = "UPDATE ratestable SET 1=?, 2=?, 3=?, 4=?, 5=?, 6=? WHERE id=?";
		$stmt = $db->prepare($sql);

		if(count($_POST['1']) > 0)
		{
		  foreach($_POST AS $key => $val)
		  {
		    $stmt->execute(array(
							($key),
							($_POST['2'][$key]),
							($_POST['3'][$key]),
							($_POST['4'][$key]),
							($_POST['5'][$key]),
							($_POST['6'][$key]),
							($val)
			)
		  );
		}
		$stmt->closeCursor();
		}


	// once updated return to rates page
	header('Location: rates-edit.php?success=1');
	exit;
}

?>

thanks in advance

darkfreaks · March 22, 2011

instead of using the $_POST method try fetching it like so.....

$stmt= prepare("SELECT * FROM  table");
$stmt->execute();

foreach($stmt as $s) { //code here

}

$s->closeCursor();

darkfreaks · March 23, 2011

the $key => $val only works if you have something like this $employee_age_array[$name]= $number

otherwise use something like i did above

possien · March 23, 2011

I used a different approach that you can use an "include" in any page. It reports the page name in a database so I know what each page is rated as and in my admin I use a graph to average ratings for pages through group queries by page or value. This uses $_GET instead of post. The image in the table is a small star.

<tr>

<td align="center"><h3 class="greenl">Please Rate this page</h3></td>

</tr>

<tr>

<td align="center">Did you find this page and information useful?</td>

</tr>

</table>

<tr>

<td align="center" class="greenl"><a href="<?php echo $_SERVER['PHP_SELF'];?>?rating=1"><img src="images/star.jpg" width="15" height="15" alt="star" /></a></td>

<td align="center" class="greenl"><a href="<?php echo $_SERVER['PHP_SELF'];?>?rating=2"><img src="images/star.jpg" width="15" height="15" alt="star" /></a></td>

<td align="center" class="greenl"><a href="<?php echo $_SERVER['PHP_SELF'];?>?rating=3"><img src="images/star.jpg" width="15" height="15" alt="star" /></a></td>

<td align="center" class="greenl"><a href="<?php echo $_SERVER['PHP_SELF'];?>?rating=4"><img src="images/star.jpg" width="15" height="15" alt="star" /></a></td>

<td align="center" class="greenl"><a href="<?php echo $_SERVER['PHP_SELF'];?>?rating=5"><img src="images/star.jpg" width="15" height="15" alt="star" /></a></td>

</tr>

<td align="center"><a href="<?php echo $_SERVER['PHP_SELF'];?>?rating=1">1</a></td>

<td align="center"><a href="<?php echo $_SERVER['PHP_SELF'];?>?rating=2">2</a></td>

<td align="center"><a href="<?php echo $_SERVER['PHP_SELF'];?>?rating=3">3</a></td>

<td align="center"><a href="<?php echo $_SERVER['PHP_SELF'];?>?rating=4">4</a></td>

<td align="center"><a href="<?php echo $_SERVER['PHP_SELF'];?>?rating=5">5</a></td>

</tr>

</table>

<?php

if(isset($_GET['rating'])){

$good_count = $bad_count = $rating = 0;

$page= $_SERVER['PHP_SELF'];

$req= $_SERVER['REQUEST_URI'];

$parse = parse_url($req,PHP_URL_QUERY);

parse_str($parse);

echo '<p align="center"><strong>Thanks for your input!</strong><br />YOUR RATING: '.$rating.'</p>';

if($rating > 0) {

$good_count = 1;

}

$date = date("Y-m-d H:i:s"); //Get the date and time.

require_once('Connections/org.php');

$dbc = mysqli_connect(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME);

$query = "INSERT INTO results2(page, good_count, rating, date) VALUES('$page', '$good_count', '$rating', '$date')";

mysqli_query($dbc, $query) or die(mysqli_error($dbc));

mysqli_close($dbc);

}

?>

happypete · March 23, 2011

Thanks for the responses, but Ill try and explain myself better.

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???????

happypete · March 24, 2011

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();

Sign In

PHP PDO Updating an array

Recommended Posts

happypete

Link to comment

Share on other sites

darkfreaks

Link to comment

Share on other sites

darkfreaks

Link to comment

Share on other sites

possien

Link to comment

Share on other sites

happypete

Link to comment

Share on other sites

happypete

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information