Page 1 of 1

display from one table update to another

Posted: Tue May 26, 2009 9:04 am
by harkly
I am working with 2 tables, images & movements. They are connected with movID field

images
imgID (primary)
name
movid

movements
movid (primary)
name

I have a form that makes updates to the images table, this is working fine. What I want to do is to be able to have a drop down box for the movID, pulling that info from the movements table and updating into the mov_# fields in the images table.

Right now I have the form on one side and the drop downs on the other side of the web page so I don't have to toggle back & forth.

Code:

Code: Select all

<?php
session_start();
?>
 
<!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>
     <link href="stylesheet.css" rel="stylesheet" type="text/css">
     <link rel="stylesheet" media="print" type="text/css" href="../print.css" />
<title>The Art Database - Admin Console</title>
</head>
 
<body>
 
<?php
   include("../library/login.php");
   login();
?>
 
<table width="900" border="0" cellpadding="2" cellspacing="0" align="center">
  <tr>
    <td align=center colspan=6><h3>The Backend</h3></td></tr>
  <tr>
    <td align=center colspan=6><h3><img src=../image/smLine.gif></h3></td></tr>
  <tr>
    <td id="nav" width="600" valign="top" colspan=5><?php include('adminnav.inc.php'); ?></td><td id="status" width="300" valign="top" rowspan=4><?php include('adminstatus.inc.php'); ?>
 
      <br><br><br><h5>Movement Choices<br><br>
<?php
    $result = mysql_query("SELECT * FROM movement order by movName") or die(mysql_error());
    $num_rows=mysql_num_rows($result);
      if ($num_rows != "0")
        {
                echo "<form method='post' action='updateArtist.php'>";
                echo "<select name='movement'>";
            while ($r=mysql_fetch_array($result))
              { 
                $movid=$r["movid"];
                $movName=$r["movName"];
                echo "<option value='$movid_1' selected>$movName - $movid</option>";
              }
                echo "</select>";
                echo "<input type='hidden'>";
                echo "</form>";
        }
 ?>
 
      <br><h5>Medium Choices<br><br>
<?php
    $result = mysql_query("SELECT * FROM medium ORDER BY medName") or die(mysql_error());
    $num_rows=mysql_num_rows($result);
      if ($num_rows != "0")
        {
                echo "<form method='post' action='updateArtist.php'>";
                echo "<select name='medium'>";
            while ($r=mysql_fetch_array($result))
              { 
                $medName=$r["medName"];
                $medid=$r["medid"];
                   echo "<option value='$medid_1' selected>$medName - $medid</option>";
              }
                echo "</select>";
                echo "<input type='hidden'>";
                echo "</form>";
        }
 
 
 
 
 ?> 
      <br><h5>Genre Choices<br><br>
<?php
    $result = mysql_query("SELECT * FROM genre order by genName") or die(mysql_error());
    $num_rows=mysql_num_rows($result);
      if ($num_rows != "0")
        {
                echo "<form method='post' action='updateArtist.php'>";
                echo "<select name='genre'>";
            while ($r=mysql_fetch_array($result))
              { 
                $genid=$r["genid"];
                $genName=$r["genName"];
                echo "<option value='$genid_1' selected>$genName - $genid</option>";
              }
                echo "</select>";
                echo "<input type='hidden'>";
                echo "</form>";
        }
 
 ?> 
      <br><h5>Periods Choices<br><br>
<?php
    $result = mysql_query("SELECT * FROM period order by perName") or die(mysql_error());
    $num_rows=mysql_num_rows($result);
      if ($num_rows != "0")
        {
                echo "<form method='post' action='updateArtist.php'>";
                echo "<select name='period'>";
            while ($r=mysql_fetch_array($result))
              { 
                $perid=$r["perid"];
                $perName=$r["perName"];
                echo "<option value='$perid_1' selected>$perName - $perid</option>";
              }
                echo "</select>";
                echo "<input type='hidden'>";
                echo "</form>";
        }
 ?> 
 
      <br><h5>Museum Choices<br><br>
<?php
    $result = mysql_query("SELECT * FROM museum ORDER BY musName") or die(mysql_error());
    $num_rows=mysql_num_rows($result);
      if ($num_rows != "0")
        {
                echo "<form method='post' action='updateArtist.php'>";
                echo "<select name='museum'>";
            while ($r=mysql_fetch_array($result))
              { 
                $musName=$r["musName"];
                $musid=$r["musid"];
                   echo "<option value='$musName' selected>$musName - $musid</option>";
              }
                echo "</select>";
                echo "<input type='hidden'>";
                echo "</form>";
        }
 
 
 ?> 
     
 
</td>
  <tr>
    <td align=center colspan=5><h3><img src=../image/smLine.gif></h3></td></tr>
  <tr>
    <td id="main" width="500" valign="top">
      <?php
 
mysql_select_db("theartdb"); 
 
$search=$_GET["imgid"];
 
 
//pulls info for Image
 
$result = mysql_query("SELECT * FROM image WHERE imgid = '$search'");
 
  while ($r=mysql_fetch_array($result))
   {    
 
   $imgid=$r["imgid"];
   $artid=$r["artid"];
   $title=$r["title"];
   $dtxt_1 =$r["dtxt_1"];
   $dt=$r["dt"];
   $medid=$r["medid"];
   $size_inch=$r["size_inch"];
   $size_cm=$r["size_cm"];
   $musid=$r["musid"];
   $movid=$r["movid"];
   $genid=$r["genid"];
   $perid=$r["perid"];
   $bio=$r["bio"];
   $keywords=$r["keywords"];
   $notes=$r["notes"];
   $verified=$r["verified"];
 
 
 
//display the form
 
   echo "<table border=0 width=600 bgcolor=f4efd2>";
   echo "<tr><td><img src='../image/$artid/$imgid.jpg' border=0> <br><br></td></tr>\n";
   echo "<tr><td><form method='post' action='updateImage.php'></td></tr>\n";
 
   echo "<tr><td colspan=2><h5>Title --</td></tr><tr><td colspan=6><input type='text' size='70' name='title' value='$title'></td></tr>\n";
 
   echo "<tr><td><h5>Image Id</td><td><h5>Artist Id</td></td></tr>\n";
   echo "<tr><td><input type='text' name='imgid' value='$imgid'><td><input type='text' name='artid' value='$artid'></td></tr>\n";
 
   echo "<tr><td colspan=2><h5>Date Info --</td></tr>\n";
   echo "<tr><td>Date Extra</td><td>Date</td></tr>\n";
   echo "<tr><td><input type='text' name='dtxt_1' value='$dtxt_1'></td><td><input type='text' name='dt' value='$dt'></td></tr>\n";
 
 
   echo "<tr><td colspan=2><h5>Medium --</td></tr><tr><td><input type='text' name='medid' value='$medid'></td></tr>\n";
 
   echo "<tr><td colspan=2><h5>Size --</td></tr>\n";
   echo "<tr><td>Inches</td><td>Centimeters</td></tr>\n";
   echo "<tr><td><input type='text' name='size_inch' value='$size_inch'></td><td><input type='text' name='size_cm' value='$size_cm'></td></tr>\n";
 
   echo "<tr><td bgcolor=f2e9b8 colspan=2><h5>Location --</td></tr>\n";
   echo "<tr><td colspan=2>Museum</td></tr><tr><td><input type='text' name='musid' value='$musid'></td></tr>\n";
 
   echo "<tr><td colspan=2><h5>Movement --</td></tr><tr><td><input type='text' name='movid' value='$movid'></td></tr>\n";
 
echo "<tr><td colspan=2>
 
<label for='movement'>movement</label> 
  <select name='movement' id='movement'> 
   <?php while ( $r = mysql_fetsch_object( $results ) ) { 
    if ( $r->moveid == $movid_1 ) 
        $sel = ' selected=\'selected\''; 
    else 
        $sel = ''; 
 
    echo '<option value=\'{$r->moveid}\' $sel>{$r->movName}</option>\n'; 
} 
 
?> 
  </select> 
 
</td></tr>\n";
 
   echo "<tr><td colspan=2><h5>Genre --</td></tr><tr><td><input type='text' name='genid' value='$genid'></td></tr>\n";
 
   echo "<tr><td bgcolor=f2e9b8 colspan=2><h5>Period --</td></tr><tr><td><input type='text' name='perid' value='$perid'></td></tr>\n";
 
   echo "<tr><td colspan=2><h5>Art Work Info --</td></tr><tr><td colspan=3><textarea rows='5' cols='70' wrap='physical' name='bio' value='$bio'>$bio</textarea></td></tr>\n";
   echo "<tr><td colspan=2><h5>Keywords --</td><tr></tr><td colspan=3><textarea rows='5' cols='70' wrap='physical' name='keywords' value='$keywords'>$keywords</textarea></td></tr>\n";
   echo "<tr><td colspan=2><h5>Notes --</td><tr></tr><td colspan=3><textarea rows='5' cols='70' wrap='physical' name='notes' value='$notes'>$notes</textarea></td></tr>\n";
 
   if ($verified)
      echo "<tr><td colspan=2><h5>Verified &nbsp;<input type='checkbox' name='verified' value='1' checked></td></tr>\n";
   else
      echo "<tr><td colspan=2><h5>Verified &nbsp;<input type='checkbox' name='verified' value='1'></td></tr>\n";
 
   echo "<tr><td><input type='submit' name='button' value='Update'></td></tr>\n";
//   echo "<tr><td><input type='submit' name='button' value='Delete'></td></tr>\n";
   echo "</form>\n";
 
 
}
 
 
?>
 
My question is how do I go about displaying data from a table, selecting it and updating the related info in another table??

Any related sites that I can look at? I have googled but I don't think I am phrasing it right.

Re: display from one table update to another

Posted: Tue May 26, 2009 4:20 pm
by McInfo
Here is an example that demonstrates using a list of host organizations from one database table to update the hosts for events stored in a second table. You can update multiple events with a single form submission.

The "test_100791" database contains two tables:

Code: Select all

EXPLAIN `host`
# +--------+---------------------+------+-----+---------+----------------+
# | Field  | Type                | Null | Key | Default | Extra          |
# +--------+---------------------+------+-----+---------+----------------+
# | h_id   | tinyint(3) unsigned | NO   | PRI |         | auto_increment |
# | h_name | varchar(50)         | NO   |     |         |                |
# +--------+---------------------+------+-----+---------+----------------+
 
SELECT * FROM `host`
# +------+---------------------+
# | h_id | h_name              |
# +------+---------------------+
# |    1 | Wilson Catering     |
# |    2 | Popular Bands, Inc. |
# |    3 | Grand Stand, LLC    |
# +------+---------------------+
 
EXPLAIN `event`
# +------------+----------------------+------+-----+---------+----------------+
# | Field      | Type                 | Null | Key | Default | Extra          |
# +------------+----------------------+------+-----+---------+----------------+
# | e_id       | smallint(5) unsigned | NO   | PRI |         | auto_increment |
# | e_name     | varchar(50)          | NO   |     |         |                |
# | e_datetime | datetime             | NO   |     |         |                |
# | h_id       | tinyint(3) unsigned  | NO   |     |         |                |
# +------------+----------------------+------+-----+---------+----------------+
 
SELECT * FROM `event`
# +------+----------------------+---------------------+------+
# | e_id | e_name               | e_datetime          | h_id |
# +------+----------------------+---------------------+------+
# |    1 | Lunch on the Lawn    | 2009-06-01 12:00:00 |    1 |
# |    2 | A Concert with Cindy | 2009-06-15 20:00:00 |    3 |
# +------+----------------------+---------------------+------+
I tried to make the example rudimentary, so it is lacking some error handling.

example.php

Code: Select all

<?php
$dbc = mysql_connect('localhost', 'root', '');
mysql_select_db('test_100791', $dbc);
 
$sql_updates = array();
if (isset($_POST['event']) && is_array($_POST['event'])) {
    foreach ($_POST['event'] as $id => $pe) {
        if (isset($pe['old_host'])
        &&  isset($pe['new_host'])
        &&  $pe['old_host'] != $pe['new_host']
        &&  validId($id)
        &&  validId($pe['new_host'])
        ) {
            $sql_updates[] = "
                UPDATE `event`
                SET `h_id` = '{$pe['new_host']}'
                WHERE `e_id` = '{$id}'
                LIMIT 1
            ";
        }
    }
    foreach ($sql_updates as $sql) {
        mysql_query($sql, $dbc);
    }
}
 
$events = queryRows('
    SELECT `e_id`       AS `id`
         , `e_name`     AS `name`
         , `e_datetime` AS `date`
         , `h_id`       AS `host`
    FROM `event`
');
 
$hosts = queryRows('
    SELECT `h_id`   AS `id`
         , `h_name` AS `name`
    FROM `host`
');
 
mysql_close($dbc);
?>
 
<form method="post" action="<?php echo basename(__FILE__); ?>">
 <table cellspacing="1" cellpadding="3" border="1">
  <thead>
   <tr>
    <th>ID</th>
    <th>Event Name</th>
    <th>Date/Time</th>
    <th>Hosted By</th>
   </tr>
  </thead>
  <tbody>
   <?php
   foreach ($events as $e) {
       printf('<tr><td>%s</td><td>%s</td><td>%s</td><td>%s%s</td></tr>'
             , $e['id']
             , $e['name']
             , $e['date']
             , htmlSelectBox('event['.$e['id'].'][new_host]', $hosts, $e['host'])
             , htmlHiddenInput('event['.$e['id'].'][old_host]', $e['host']));
   }
   ?>
  </tbody>
  <tfoot>
   <tr>
    <td colspan="4" style="text-align: center;">
     <input type="reset" value="Reset" />
     <input type="submit" value="Update" />
    </td>
   </tr>
  </tfoot>
 </table>
</form>
 
<pre>$_POST => <?php print_r($_POST); ?></pre>
<pre>$sql_updates => <?php print_r($sql_updates); ?></pre>
 
<?php
/**
 * Determines whether the test input is a valid ID
 *
 * @param $test A string to test
 * @return bool
 */
function validId ($test)
{
    if (preg_match('/[^0-9]/', $test)) {
        return false;
    }
    return true;
}
 
/**
 * Retuns an associative array containing the rows returned by the query
 *
 * @param $sql A query that returns one or more records
 * @return array
 */
function queryRows ($sql)
{
    global $dbc;
    $res = mysql_query($sql, $dbc);
    $rows = array();
    while ($row = mysql_fetch_assoc($res)) {
        $rows[] = $row;
    }
    return $rows;
}
 
/**
 * Returns an HTML select box
 *
 * @param $name The value of the name attribute
 * @param $options An array of arrays with 'id' and 'name' indexes
 * @param $selected_option The id of the selected option
 * @return string
 */
function htmlSelectBox ($name, $options, $selected_option = '')
{
    $html = '<select name="'.$name.'">';
    foreach ($options as $opt) {
        $selected = ($opt['id'] == $selected_option)
                  ? ' selected="selected"' : '';
        $html .= sprintf('<option value="%s"%s>%s</option>'
                        , $opt['id'], $selected, $opt['name']);
    }
    $html .= '</select>';
    return $html;
}
 
/**
 * Returns an HTML hidden input
 *
 * @param $name The value of the name attribute
 * @param $value The value of the value attribute
 * @return string
 */
function htmlHiddenInput ($name, $value)
{
    return sprintf('<input type="hidden" name="%s" value="%s" />'
                  , $name, $value);
}
?>
The condition on line 10 prevents an event from being updated if its host was not changed.

Lines 11 and 12 supplement the need for mysql_real_escape_string(). The validId() function ensures that the given string consists of only digits.

Lines 27-33 and 35-39 fetch all events and all hosts. In the HTML table, the events are listed in rows. Each row contains a <select> that lists all hosts. Next to each <select> is a hidden input that remembers the original host value for the event in that row.

Lines 77 and 78 are for debugging.

Edit: This post was recovered from search engine cache.