Need Help Updating Mulitple MySQL Fields From One Form

Question

Ootred 0 Newbie Poster

13 Years Ago

I am very new to php and mysql. I have read and re-read many forum posts on this subject, but I just cannot figure this out. I have been at it for almost two weeks now.

This is my hope: Load images and captions from a database into an html form. Captions, if existing, are displayed in a textarea below the picture so that they can be changed/updated/deleted. When user clicks on the Update button, the new/old captions are inserted into the database. All this is so that the images and captions can be displayed on a different website "automatically."

I have used a lot of code from many sources. I got it working on my localhost, but when I went live it no longer worked (live version deleted all the image_filename and description data from the database). I think this is because on the live server register_globals=Off. I need to be able to update just the descriptions (captions) for the images via the database.

Much of this code is sectioned into includes so that I can use it on several webpages/sites.

Please help me. So many of you are such excellent programmers, I am in awe of your abilities.

Here is my code:

<?php ob_start();
  require_once('drfinn_sessionStart.php'); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Update Image Tags</title>
<link rel="stylesheet" type="text/css" href="css/style.css" />
</head>

<body>

<?php
  // Validate user is logged in and set menu
  require_once('imageUpdateLoginValidationCode.php');
  
  // Define database connection constants
  define('DB_HOST', 'localhost');
  define('DB_USER', 'user');
  define('DB_PASSWORD', 'pass');
  define('DB_NAME', 'imgdb');
  
  // Define application standards
  define('FF_IMAGEPATH', 'images/sculpture/');
  $tbl_name = "sculpture";

  // Retrieve the image data from MySQL
  $query = "SELECT * FROM $tbl_name ORDER BY image_filename ASC";
  $result = mysqli_query($dbc, $query);

  // Count table rows
  $count=mysqli_num_rows($result);

?>

  <h3>Update image tags</h3><br />
  <form name="form1" method="post" action="">
    <table width="600" border="1" cellspacing="1" cellpadding="5">
<?php
  // Loop through data to create a table in the form of all images in the table with descriptions
  while($row=mysqli_fetch_array($result)){
?>
      <tr>
	    <td align="center"><?php $id[]=$row['id']; ?><input name="image_filename[]" type="hidden" id="image_filename" value="<? echo $row['image_filename']; ?>"><img src="<?php echo FF_IMAGEPATH;?><?php echo $row['image_filename']; ?>" alt="" /></td></tr>
	  <tr>
	    <td><strong>Description: </strong><textarea name="description[]" id="description" cols="60" rows="2"><? echo $row['description']; ?></textarea></td>
	  </tr>
	  <tr>
	    <td><a href="#update">Go To Update Button</a></td>
	  </tr>
<?php
  }
?>
      <tr>
	    <td align="center"><a name="update"></a><input type="submit" name="submit" value="Update Image Descriptions"></td>
	  </tr>
    </table>
  </form>

<?php
  // Check if button name "Submit" is active, do this 
  if (isset($_POST['submit'])) {
    for($i=0;$i<$count;$i++){
	$query1="UPDATE $tbl_name SET image_filename='$image_filename[$i]', description='$description[$i]' WHERE id='$id[$i]'";
    $result1=mysqli_query($dbc, $query1);
    }
  }
  
  // Redirect back to form
  if($result1){
    header('location:imageUpdate-sculpture.php');
  }
  
  mysqli_close($dbc);
?>
</body>
</html>
<?php ob_flush(); ?>

mysql php

Edited 13 Years Ago by Ootred because: n/a

3 Contributors
8 Replies
392 Views
2 Days Discussion Span
Latest Post 13 Years Ago Latest Post by cereal

megachip04

13 Years Ago

on first glance, your form has no post action...perhaps for some reason you do not need it?

Also, I am unfamiliar with mysqli_query, what is the difference between that and mysql_query?

Edited 13 Years Ago by megachip04 because: n/a

cereal 1,524 Nearly a Senior Poster

13 Years Ago

It doesn't work for two reasons:

1. I don't see any id hidden input
2. image_filename and description (and id) are arrays

Run this example:

<form method="post" action="">
<input type="text" name="id[]" value="9" />
<input type="text" name="image_filename[]" value="apples.jpg" />
<input type="text" name="description[]" value="apples" /><br />

<input type="text" name="id[]" value="10" />
<input type="text" name="image_filename[]" value="peaches.jpg" />
<input type="text" name="description[]" value="peaches" /><br />

<input type="text" name="id[]" value="11" />
<input type="text" name="image_filename[]" value="oranges.jpg" />
<input type="text" name="description[]" value="oranges" /><br />
<input type="submit" value="submit" name="submit" />
</form>

<?php
if(isset($_POST['submit']))
{
	echo '<pre> id[] ';
	print_r($_POST['id']);

	echo 'image_filename[] ';
	print_r($_POST['image_filename']);

	echo 'description[] ';
	print_r($_POST['description']);

	$map = array_map(null, $_POST['id'], $_POST['image_filename'], $_POST['description']);
	echo 'result ';
	print_r($map);
	echo '</pre>';
}
?>

You can process the resulting array with a foreach() loop and do the update queries. And so you should do in your script. Bye :)

Edited 13 Years Ago by cereal because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Ootred 0 Newbie Poster · Answer 1 · 2011-08-03T08:16:58+00:00

Thanks for pointing out the missing post action. I too thought that action was a necessary part of the post process, but I copied that out of another script and it worked fine on localhost, so I honestly just forgot that it was missing.

As far as MySQL and MySQLi, the "i" is for "improved." Or at least thats what the book said that I am muddling through trying to teach myself php since the college I am attending does not seem to have a professor to teach it to me... So I use MySQLi. But I have noticed that all the code I review trying to figure this out only uses MySQL. Since I am only using a relational database, I may try to switch to MySQL and see if that helps. Honestly, I said all that just to say I do not know the difference for sure.

Ootred 0 Newbie Poster · Answer 2 · 2011-08-03T09:31:00+00:00

Ok, somethings were omitted from the last code I posted. I have tried some ideas and other code I found on other websites, but still to no avail. When I click the update button, the image_filename and description fields are deleted (or it might be better to say that nothing is inserted so the fields are now empty). Any and all help is appreciated.

Here is the code I am using now:

<?php ob_start();
  require_once('sessionStart.php'); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Update Image Tags</title>
<link rel="stylesheet" type="text/css" href="css/style.css" />
</head>

<body>

<?php
  // Validate user is logged in and set navigation menu
  require_once('imageUpdateLoginValidationCode.php');
  
  // Define database connection constants
  define('DB_HOST', 'localhost');
  define('DB_USER', 'user');
  define('DB_PASSWORD', 'pass');
  define('DB_NAME', 'imgdb');
  
  // Define application standards
  define('FF_IMAGEPATH', 'images/drHelp/2010/');
  $tbl_name = "images";
  
  // Connect to the database 
  $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)or die('cannot connect to database');
  
  // Retrieve the image data from MySQL
  $query = "SELECT * FROM $tbl_name ORDER BY image_filename ASC";
  $result = mysqli_query($dbc, $query);

  // Count table rows
  $count=mysqli_num_rows($result);
  
  // Check if button name "Submit" is active, do this 
  if (isset($_POST['submit'])) {
    for($i=0;$i<$count;$i++){
	// Define variables from $_POST array
	$id = mysqli_real_escape_string($dbc, trim($_POST['id']));
	$image_filename = mysqli_real_escape_string($dbc, trim($_POST['image_filename']));
	$description = mysqli_real_escape_string($dbc, trim($_POST['description']));
	// Update database
	$query1="UPDATE $tbl_name SET image_filename='$image_filename[$i]', description='$description[$i]' WHERE id='$id[$i]'";
    $result1=mysqli_query($dbc, $query1);
    }
  }
?>

  <h3>Update image tags</h3><br />
  <form name="form1" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
    <table width="600" border="1" cellspacing="1" cellpadding="5">
<?php
  // Loop through data to create a table in the form of all images in the table with descriptions
  while($row=mysqli_fetch_array($result)){
?>
      <tr>
	    <td align="center"><?php $id[]=$row['id']; ?><input name="image_filename[]" type="hidden" value="<? echo $row['image_filename']; ?>"><img src="<?php echo FF_IMAGEPATH;?><?php echo $row['image_filename']; ?>.jpg" alt="" /></td></tr>
	  <tr>
	    <td><strong>Description: </strong><textarea name="description[]" cols="60" rows="2"><? echo $row['description']; ?></textarea></td>
	  </tr>
	  <tr>
	    <td><a href="#update">Go To Update Button</a></td>
	  </tr>
<?php
  }
?>
      <tr>
	    <td align="center"><a name="update"></a><input type="submit" name="submit" value="Update Image Descriptions"></td>
	  </tr>
    </table>
  </form>

<?php
  mysqli_close($dbc);
?>
</body>
</html>
<?php ob_flush(); ?>

Ootred 0 Newbie Poster · Answer 3 · 2011-08-03T22:17:25+00:00

Thank you for the example cereal, and for pointing out that I forgot to assign the id to a hidden value. That helped me understand arrays and what is happening with my code a bit more, but I am still confused.
I have updated my code with your example. I can now get my arrays to print to screen and see how they are nested and listed. However, I cannot figure out how to extract the values to variables that I can then insert into a database. I have tried several things this mornign including foreach loops, for loops, extract(), list(), and multiple combinations there of.
Here is what I have updated (except for assigning id to a hidden input), leaving some code at the beginning and end as markers:

// Check if button name "Submit" is active, do this 
  if (isset($_POST['submit'])) {
    // Process POST inputs to an array map
    $map = array_map(null, $_POST['id'], $_POST['image_filename'], $_POST['description']);
	
	// Show the array map onscreen to see the POST data (to be removed later when code actually works) And I DO see the updated descriptions here in the array
	echo '<pre>';
	echo 'result ';
	print_r($map);
	echo '</pre>';	  
	
	// Assign array values to variables so they can be inserted into the database with a loop
	// The following code is wrong and does not assign values to variables. This is where I need help
    foreach($map as $value){
	  // Define variables from $_POST array
	  // I am pretty sure this is not really the way to do this
	  $id = mysqli_real_escape_string($dbc, trim($value));
	  $image_filename = mysqli_real_escape_string($dbc, trim($value));
	  $description = mysqli_real_escape_string($dbc, trim($value));
	  // Update database (I really only need to update the description)
	  $query1="UPDATE $tbl_name SET description='$description' WHERE id='$id'";
      $result1=mysqli_query($dbc, $query1);
	}
  }
?>

  <h3>Update image tags</h3><br />

Could you please show me how to extract the values into variables to be used for updating the database? Thank you.

cereal 1,524 Nearly a Senior Poster Featured Poster · Answer 4 · 2011-08-03T23:11:09+00:00

You have to do just few changes:

foreach($map as $key => $value){
	  // Define variables from $_POST array
	  // I am pretty sure this is not really the way to do this
	  $id = mysqli_real_escape_string($dbc, trim($value[0]));
	  $image_filename = mysqli_real_escape_string($dbc, trim($value[1]));
	  $description = mysqli_real_escape_string($dbc, trim($value[2]));
	  // Update database (I really only need to update the description)
	  $query1="UPDATE $tbl_name SET description='$description' WHERE id='$id'";
          $result1=mysqli_query($dbc, $query1);
}

Now it should work, at least I hope so :D
With bidimensional arrays you need to use the second form of the foreach() loop (check the link at the end of my post), because in your example $value is the first level, but you still need to get the value of the second array.

I'm assuming that you're getting an array like in my example:

result Array
(
    [0] => Array
        (
            [0] => 9
            [1] => apples.jpg
            [2] => apples
        )

    [1] => Array
        (
            [0] => 10
            [1] => peaches.jpg
            [2] => peaches
        )
    . . .
)

- http://php.net/manual/en/control-structures.foreach.php

Ootred 0 Newbie Poster · Answer 5 · 2011-08-04T20:44:53+00:00

Thank you SO much cereal. That did it! You make is seem so easy. I knew I was missing something obvious. But like the wise man once said: "Everything is obvious, once someone shows you."

p.s. Your assumption was correct. That is what the array looked like.

cereal 1,524 Nearly a Senior Poster Featured Poster · Answer 6 · 2011-08-04T21:24:28+00:00

cereal 1,524 Nearly a Senior Poster

13 Years Ago

Perfect, you are welcome! ;)