dynamically populate a form with database data

[esoteric]

Can anyone help get me started on how i can make a menu to display the contents of my sql table, but when clicking them it populates a table form. I know how to echo data on a php page but i dont know how to make it dynamic so each field on the form changes depending on what i clicked on

 

my form

<table width="100%" border="0" cellpadding="0" cellspacing="0">
  <form method="post" action="http://xxx/admin/products/update.php">
  <tr bgcolor="#EDF5FA">
    <td width="131" class="details">ID</td>
    <td width="459"><input type="text" name="prodnum" size="30" />
      <br />
      <span class="details"><em>To get product ID visit the product list page</em></span><em><br />
      </em></td>
  </tr>
  <tr>
    <td class="details">Name</td>
    <td><input type="text" name="name" size="30" /></td>
  </tr>
  <tr bgcolor="#EDF5FA">
    <td class="details">Product ID</td>
    <td><input type="text" name="id" size="15" /></td>
  </tr>
  <tr>
    <td class="details">Category</td>
    <td><select name="cat">
      <option value="Garden">Garden</option>
      <option value="Home">Home</option>
      <option value="Gifts">Gifts</option>
      <option value="Pets">Pets</option>
    </select></td>
  </tr>
  <tr bgcolor="#EDF5FA">
    <td class="details">Version</td>
    <td><span class="details">v.</span>      <input type="text" name="version" size="5" /></td>
  </tr>
  <tr>
    <td class="details">Image Path</td>
    <td><input name="image2" type="text" value="" size="50" /></td>
  </tr>
  <tr bgcolor="#EDF5FA">
    <td class="details">Link Path</td>
    <td><input name="link" type="text" value="" size="50" /></td>
  </tr>
  <tr>
    <td class="details">Description</td>
    <td><textarea name="desc" cols="50" rows="10"></textarea></td>
  </tr>
  <tr bgcolor="#EDF5FA">
    <td class="details">Price</td>
    <td><input type="text" name="price" size="10" /></td>
  </tr>
  <tr>
    <td class="details">Shipping</td>
    <td><input type="text" name="shipping" size="10" /></td>
  </tr>
  <tr bgcolor="#EDF5FA">
    <td class="details">Password</td>
    <td><input type="text" name="pass" size="20" /></td>
  </tr>
  <tr>
    <td> </td>
    <td>
    <p><em class="details"><br />
        <span class="details">To update a product you must fill in every field. If a field is left blank it will save as blank</span></em><br />
<br />
          <input type="submit" value="Update" />
        </p>
      </form>
    
    </td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
  </tr>
</table>

 

The reason im trying to do this is so i can update my products table, at the moment i have to fill in each field and keep referring back to a product list on a different page to fill in every detail again, i just want it to automatically fill in the field and i just change what is needed then hit the submit button.

 

Thanks for any help.

[Psycho]

Well, if you want it truly dynamic then you will need to implement AJAX. But, I would suggest just implementing a PHP solution to start. So, you would have to select a value from the drop down and then submit the selection to get your form with all the values populated. I'll review your form and provide some sample code.

[esoteric]

Thanks for your reply. It doesn't have to be fancy at all, its only for admin eyes anyway. I just need to somehow make a list or menu that then populates the form, that's where i'm struggling because i cant quite figure out how to make it fill in the fields.

 

Any sample code would be much appreciated, thanks for your time.

[Psycho]

OK, to make this easy, create two pages: "select_product.php" and "edit_product.php".

 

On the select_product.php page you would create a small form with nothing but a select list. Do a DB query to get a list of products names and their IDs. use those values to create the select list. That form should have the edit_product.php page as it's action. So, when the user selects a product from the select list and submits the form they will be redirected to the edit_product.php page along with the POST data (i.e. the selected product).

 

Then on the edit_product.php page you would get the selected product from the POST data and do a DB query to get all the values for the product that you want to populate the form with.

$query = "SELECT * FROM products WHERE product_id = $selectedProduct";
$result = mysql_query($query);

$product_data = mysql_fetch_assoc();

 

For simple text inputs you would just set the value using what you retrieved from the DB, but be sure to run it through htmlentiteis().

$prod_name = htmlentities($product_data['name']);

<input type="text" name="prod_name" value="<?php echo $prod_name; ?>" />

 

For radio buttons, checkboxes, or select lists you will need to dynamically check the DB value to determine how the form value should be set

//Dynamically create select options for category
$cat_list = array("Garden", "Home", "Gifts", "Pets");
$cat_options = '';
foreach($cat_list as $cat)
{
    //Dynamically select the option already saved
    $selected = ($cat==$prod_cat) ? ' slected="selected"' : '';
    $cat_options = "<option value=\"{$cat}\"{$selected}>{$cat}</option>\n"
}

    <td><select name="cat">
    <?php echo $cat_options; ?>
    </select></td>

[esoteric]

Thanks for you post, this is what i have so far, the list box populates fine but it doesn't populate the form. I get no errors.

 

select_product.php

<?
xxx

mysql_connect("$host", "$username", "$password") or die( "Unable to connect to database");
mysql_select_db("product_catalogue") or die( "Unable to select database");

?>

<form name="form" method="POST" action="../products/edit_product.php">
<select name="ProductName" id="ProductName">

<?php 
$sql="SELECT * FROM Products";
$result =mysql_query($sql);
while ($data=mysql_fetch_assoc($result)){
?>
<option value ="<?php echo $data['ProductName'] ?>" ><?php echo $data['ProductName'] ?></option>
<?php } ?>
</select>
<input type="submit" value="submit">
</form>

 

edit_product.php

<?
xxxx

$connection = mysql_connect("$host", "$username", "$password") or die( "Unable to connect to database");
$db = mysql_select_db("product_catalogue") or die( "Unable to select database"); 

$data = 'SELECT * FROM Products WHERE ProductName = "'.$data.'"';
  $query = mysql_query($data) or die("Couldn't execute query. ". mysql_error());
  $product_data = mysql_fetch_assoc($query);
  
  
$id = htmlentities($product_data['ID']);
$name = htmlentities($product_data['ProductName']);
$productid = htmlentities($product_data['ProductId']);
$category = htmlentities($product_data['Category']);
$version = htmlentities($product_data['ProductVersion']);
$imagepath = htmlentities($product_data['ProductImage']);
$linkpath = htmlentities($product_data['ProductLink']);
$description = htmlentities($product_data['ProductDescription']);
$price = htmlentities($product_data['ProductPrice']);
$shipping = htmlentities($product_data['ShippingPrice']);
?>

<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <form method="post" action="http://xxxxx/admin/products/update.php">
    <tr bgcolor="#EDF5FA">
      <td width="131" class="details">ID</td>
      <td width="459"><input type="text" name="prodnum" size="30" value="<?php echo $id; ?>"/></td>
    </tr>
    <tr>
      <td class="details">Name</td>
      <td><input type="text" name="name" size="30" value="<?php echo $name; ?>" /></td>
    </tr>
    <tr bgcolor="#EDF5FA">
      <td class="details">Product ID</td>
      <td><input type="text" name="id" size="15" value="<?php echo $productid; ?>" /></td>
    </tr>
    <tr>
      <td class="details">Category</td>
      <td><input type="text" name="id" size="15" value="<?php echo $category; ?>" /></td>
    </tr>
    <tr bgcolor="#EDF5FA">
      <td class="details">Version</td>
      <td><span class="details">v.</span>      <input type="text" name="version" size="5" value="<?php echo $version; ?>" /></td>
    </tr>
    <tr>
      <td class="details">Image Path</td>
      <td><input name="image2" type="text" size="50" value="<?php echo $imagepath; ?>" /></td>
    </tr>
    <tr bgcolor="#EDF5FA">
      <td class="details">Link Path</td>
      <td><input name="link" type="text" size="50" value="<?php echo $linkpath; ?>" /></td>
    </tr>
    <tr>
      <td class="details">Description</td>
      <td><textarea name="desc" cols="50" rows="10" value="<?php echo $description; ?>"></textarea></td>
    </tr>
    <tr bgcolor="#EDF5FA">
      <td class="details">Price</td>
      <td><input type="text" name="price" size="10" value="<?php echo $price; ?>" /></td>
    </tr>
    <tr>
      <td class="details">Shipping</td>
      <td><input type="text" name="shipping" size="10" value="<?php echo $shipping; ?>" /></td>
    </tr>
    <tr>
      <td> </td>
      <td><p>
        <input type="submit" value="Update" />
        </p>
    </form>
    
    </td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
  </tr>
</table>

 

I probably have done something right, any ideas? Thank you.

[synking]

From what i can see if this is you're whole code you don't

 

assign the post variables from the first form.

 

and you are also

$data = 'SELECT * FROM Products WHERE ProductName = "'.$data.'"';

calling a variable in the $data variable but it is never assigned anything but the query change that and assign the post data and you should be ok.

[Psycho]

Yeah, but don't use $data to define $data. The only time that makes sense is if you are modifying a value to be of the same type. Anyway, I'm surprised you didn't get any errors.

 

Also, do your products not have a unique ID? You should be passing THAT value and not the product name. And, when you populate the form you should have a hidden field for the product ID. If you use the product name as the key and the user changes the name how do you know what record to update!?

 

So, change your select list so the ID is the value

<?php
$sql="SELECT ProductID, ProductName  FROM Products";
$result =mysql_query($sql);
$productOptions = '';
while ($data=mysql_fetch_assoc($result))
{
    $productOptions .= "<option value =\"{$data['ProductID']}\">{$data['ProductName']}</option>\n";
}
?>
<select name="ProductID" id="ProductID">
<?php echo $productOptions; ?>
</select>

 

Then change your form script to actually use the value that is passed

$productID = intval($_POST['productID']);
$query = "SELECT * FROM Products WHERE ProductID = {$productID}";
$result = mysql_query($query) or die("Couldn't execute query. ". mysql_error());
$product_data = mysql_fetch_assoc($result);

 

If you still get an empty form, do a print_r($_POST) and echo your query to the page to make sure they are what you expect.