making an editable database table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

making an editable database table

Post by mohson »

Im still experiencing problems with this - In lehmans terms what I
want to do is this :

page (1) has a form that you fill in when you
want to add a record. page (2) displays all the records in a table,
I want to be able to click on a particular record i.e their personID (as a
hyperlink) which then loads that record in the original group of text
boxes,( i.e form) from where it was originally entered.

Any help please??

here is the code for when I display records:

Code: Select all

// config-------------------------------------
$host = "****"; //your database host
$user = "*****"; // your database user name
$pass = "*****"; // your database password
$db = "contact_management_system"; // your database name

$filename = "people.html"; // name of this file
$option = array (5, 10, 20, 50, 100, 200);
$default = 10; // default number of records per page
$action = $_SERVERї'PHP_SELF']; // if this doesn't work, enter the filename
$query = "SELECT *, DATE_FORMAT(`dateoflastcontact`, '%d/%m/%y') 
AS `dateoflastcontact`, DATE_FORMAT(`datecontactagain`, '%d/%m/%y') 
AS `datecontactagain` FROM `people` ORDER BY `firstname`";

// database query. Enter your query here


// end config---------------------------------

	
$opt_cnt = count ($option);

$go = $_GETї'go'];
// paranoid
if ($go == "") {
$go = $default;
}
elseif (!in_array ($go, $option)) {
$go = $default;
}
elseif (!is_numeric ($go)) {
$go = $default;
}
$nol = $go;
$limit = "0, $nol";
$count = 1; 

echo "<form name="form1" id="form1" method="get" action="$action">\r\n";
echo "<select name="go" id="go">\r\n";

for ($i = 0; $i <= $opt_cnt; $i ++) &#123;
if ($option&#1111;$i] == $go) &#123;
echo "<option value="".$option&#1111;$i]."" selected="selected">".$option&#1111;$i]."</option>\r\n";
&#125; else &#123;
echo "<option value="".$option&#1111;$i]."">".$option&#1111;$i]."</option>\r\n";
&#125;
&#125;

echo "</select>\r\n";
echo "<input type="submit" name="Submit" id="Submit" value="Go" />\r\n";
echo "</form>\r\n";

$connection = mysql_connect ($host, $user, $pass) or die ("Unable to connect");
mysql_select_db ($db) or die ("Unable to select database $db");



// control query------------------------------
/* this query checks how many records you have in your table.
I created this query so we could be able to check if user is
trying to append number larger than the number of records
to the query string.*/
$off_sql = mysql_query ("$query") or die ("Error in query: $off_sql".mysql_error());
$off_pag = ceil (mysql_num_rows($off_sql) / $nol);
//-------------------------------------------- 


$off = $_GET&#1111;'offset'];
//paranoid
if (get_magic_quotes_gpc() == 0) &#123;
$off = addslashes ($off);
&#125;
if (!is_numeric ($off)) &#123;
$off = 1;
&#125;
// this checks if user is trying to put something stupid in query string
if ($off > $off_pag) &#123;
$off = 1;
&#125;

if ($off == "1") &#123;
$limit = "0, $nol";
&#125;
elseif ($off <> "") &#123;
for ($i = 0; $i <= ($off - 1) * $nol; $i ++) &#123;
$limit = "$i, $nol";
$count = $i + 1;
&#125;
&#125; 




// Query to extract records from database.
$sql = mysql_query ("$query LIMIT $limit") or die ("Error in query: $sql".mysql_error()); 



// Define your colors for the alternating rows

$color1 = "#ADD8E6";$color2 = "#E0FFFF";
$color = $color2;echo 

"<table width="50%" border="0" cellpadding="2" cellspacing="2">

    <tr>
		<td><b><small>RecNo</small></b></td>
		<td><b><small>ID</small></b></td>
		<td><b><small>Title</small></b></td>
		<td><b><small>First Name</small></b></td>
		<td><b><small>Surname</small></b></td>
		<td><b><small>Organisation</small></b></td>
		<td><b><center><small>Role</small></center></b></td>
		<td><b><small>Address(1)</small></b></td>
		<td><b><small>Address(2)</small></b></td>
		<td><b><small>City</small></b></td>
		<td><b><small>Post Code</small></b></td>
		<td><b><small>Telephone</small></b></td>
		<td><b><small>Mobile</small></b></td>
		<td><b><small>Fax</small></b></td>
		<td><b><small>Last Contact</small></b></td>
		<td><b><small>Contact Again</small></b></td>
		<td><b><small>Notes</small></b></td>";





while ($row = mysql_fetch_object($sql)) 






&#123;($color==$color2)? $color = $color1 : $color = $color2;


echo "<tr bgcolor="$color"><td>".$count . '</td><td> ' . $row->person_id .'</td><td>'.        
	
	$row->salutation .'</td><td>'.         
	
	'<a href=mailto:'.$row->email.'>'.$row->firstname .'</a></td><td>'.
	'<a href=mailto:'.$row->email.'>'.$row->surname .'</a></td><td>'.       
 	
	$row->organisation.'</td><td>'.   
	$row->role.'</td><td>'.       
 	$row->address1 .'</td><td>'.        
	$row->address2 .'</td><td>'.       
 	$row->city .'</td><td>'.       
 	$row->postcode .'</td><td>'.        
	$row->telephone .'</td><td>'.        
	$row->mobile .'</td><td>'.        
	$row->fax .'</td><td>'.        
	$row->dateoflastcontact.'</td><td>'.        
	$row->datecontactagain  .'</td><td>'.          
	$row->notes .'</td></tr>';

$count += 1;
&#125;

echo "</table>"; 

echo "<br /><br />\r\n";
if ($off <> 1) &#123;
$prev = $off - 1;
echo "&#1111; < <a href="$filename?offset=$prev&go=$go">prev</a> ] \r\n";
&#125;
for ($i = 1; $i <= $off_pag; $i ++) &#123;
if ($i == $off) &#123;
echo "&#1111;<b> $i </b>] \r\n";
&#125; else &#123;
echo "&#1111; <a href="$filename?offset=$i&go=$go">$i</a> ] \r\n";
&#125;
&#125;
if ($off < $off_pag) &#123;
$next = $off + 1;
echo "&#1111; <a href="$filename?offset=$next&go=$go">next</a> > ] \r\n";
&#125;

echo "<br /><br />\r\n";
echo "Page $off of $off_pag<br />\r\n";



?>
Here is the code for the original form where a record is entered - and where I hopefully want to be able to load the selected record for editing.

Code: Select all

?>

<form method="post" action="processpeople.html">
<table width="100%"  border="0">
    <tr> 
      <td width="17%"><font face="Times New Roman, Times, serif"><strong>Salutation</strong></font></td>
      <td width="27%"><font face="Times New Roman, Times, serif"> 
        <select name="salutation" style="color: #000000; 
			background-color: #ADD8E6">
          <option>Mr</option>
          <option>Mrs</option>
		  <option>Ms</option>
          <option>Miss</option>
		  <option>Dr</option>
        </select>
		
        </font></td>
      <td width="27%"><font face="Times New Roman, Times, serif"><strong>Telephone</strong></font></td>
      <td width="29%"><input name="telephone" type="text"style="color: #000000; 
background-color: #ADD8E6" size="11" maxlength="20">
</td>
    </tr>
    <tr> 
      <td><font face="Times New Roman, Times, serif"><strong>First Name</strong></font></td>
      <td><font face="Times New Roman, Times, serif"> 
        <input name="firstname" type="text"  size="20"style="color: #000000; 
		background-color: #ADD8E6">
        </font></td>
      <td><font face="Times New Roman, Times, serif"><strong>Mobile</strong></font></td>
      <td><input name="mobile" type="text"style="color: #000000; 
			background-color: #ADD8E6" size="20" maxlength="20"></td>
    </tr>
    <tr> 
      <td><font face="Times New Roman, Times, serif"><strong>Surname</strong></font></td>
      <td><font face="Times New Roman, Times, serif"> 
        <input name="surname" type="text" size="15"style="color: #000000; 
			background-color: #ADD8E6">
        </font></td>
     <td><font face="Times New Roman, Times, serif"><strong>Fax</strong></font></td>
      <td><font face="Times New Roman, Times, serif"> 
        <input name="fax" type="text"style="color: #000000; 
			background-color: #ADD8E6" size="20" maxlength="20">
        </font></td>
    <tr> 
      <td><font face="Times New Roman, Times, serif"><strong>Organisation</strong></font></td>
      <td><font face="Times New Roman, Times, serif"> 
        <input name="organisation" type="text" size="20"style="color: #000000; 
			background-color: #ADD8E6">
      <td><font face="Times New Roman, Times, serif"><strong>E-mail</strong></font></td>
      <td><font face="Times New Roman, Times, serif"> 
        <input name="email" type="text" size="15"style="color: #000000; 
			background-color: #ADD8E6">
        </font></td>
    </tr>
    <tr> 
      <td><font face="Times New Roman, Times, serif"><strong>Role</strong></font></td>
      <td><font face="Times New Roman, Times, serif"> 
        <input name="role" type="text" size="25"style="color: #000000; 
		background-color: #ADD8E6">
        </font></td>
      <td><font face="Times New Roman, Times, serif"><strong>Date of Last Contact</strong></font></td>
      <td><font face="Times New Roman, Times, serif"> 
        <input name="dateoflastcontact" type="text" value="yyyy-mm-dd" size="10"style="color: #000000; 
		background-color: #ADD8E6">
        </font></td>
    </tr>
    <tr> 
      <td><font face="Times New Roman, Times, serif"><strong>Address(1)</strong></font></td>
      <td><font face="Times New Roman, Times, serif"> 
        <input name="address1" type="text"  size="20" style="color: #000000; 
		background-color: #ADD8E6">
        </font></td>
      <td><font face="Times New Roman, Times, serif"><strong>Date Contact Again</strong></font></td>
      <td><font face="Times New Roman, Times, serif"> 
        <input name="datecontactagain" type="text" value="yyyy-mm-dd "size="10"style="color: #000000; 
		background-color: #ADD8E6">
        </font></td>
    </tr>
    <tr> 
      <td><font face="Times New Roman, Times, serif"><strong>Address(2)</strong></font></td>
      <td><font face="Times New Roman, Times, serif"> 
        <input name="address2" type="text"  size="20"style="color: #000000; 
		background-color: #ADD8E6">
        </font></td>
      <td><font face="Times New Roman, Times, serif"><strong>Notes</strong></font></td>
      <td><textarea name="notes"style="color: #000000; 
		background-color: #ADD8E6"></textarea></td>
    </tr>
    <tr> 
      <td><font face="Times New Roman, Times, serif"><strong>City</strong></font></td>
      <td><font face="Times New Roman, Times, serif"> 
        <input name="city" type="text"  size="20"style="color: #000000; 
		background-color: #ADD8E6">
        </font></td>
      <td><font face="Times New Roman, Times, serif">&nbsp;</font></td>
      <td>&nbsp;</td>
    </tr>
    <tr> 
      <td height="43"><font face="Times New Roman, Times, serif"><strong>Post 
        Code</strong></font></td>
      <td><font face="Times New Roman, Times, serif"> 
        <input name="postcode" type="text" size="7"style="color: #000000; 
			background-color: #ADD8E6">
        </font></td>
      <td><font face="Times New Roman, Times, serif">&nbsp;</font></td>
      <td>&nbsp;</td>
    </tr>
  </table>
<input type="submit" name="submit" value = "Enter Information"> 
</form>
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Generally you need to do this:

1-) Get the primary key of a record (you need this to identify the row) in the list script

2-) Pass that primary key to the edit script



I don't like to provide the primary key in my url's so i usually end up with a list script that has rows, and each row has a select box. In a session a store the select box id => primary key mapping...

If someone selects such a box, and hits the post button, the script will look at the selected box, stuff the primary key in $_SESSION['selection'] and redirect to edit.php

In edit.php i then look in $_SESSION['selection'] to find the primary key of the record. If the editting is done, or no selection was made, i redirect back to the list script.
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post by mohson »

do you know of any good tutorials or working examples og this topic?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Ok, here are some snippets i once used....

LIST The code that generates rows with a select box

Code: Select all

<form action="<?php echo $_SERVER&#1111;'PHP_SELF']; ?>" method="post">

<table id="list">
<?php
  echo "<tr>";
  echo "<th>Select</th>";
  foreach($properties as $property)
  &#123;
    echo "<th><a href='&#123;$_SERVER&#1111;'PHP_SELF']&#125;?order_column=&#123;$property&#1111;'column']&#125;'>&#123;$property&#1111;'name']&#125;</a></th>";
  &#125;
  echo "<th>&nbsp;</th>";
  echo "</tr>";
?>

<?php
  $i = 0;
  foreach($rows as $row)
  &#123;
    $lookup = array_shift($lookups);
    echo "<tr>";
    echo "<td class='select'><input type='checkbox' name='select&#1111;]' value='$i' /></td>";
    foreach($properties as $property)
    &#123;
        if (isset($lookup&#1111;$property&#1111;'column']]))
        &#123;
            echo "<td class='property'>&#123;$lookup&#1111;$property&#1111;'column']]&#125;</td>";
        &#125;
        else
        &#123;
            echo "<td class='property'>&#123;$row&#1111;$property&#1111;'column']]&#125;</td>";
        &#125;
    &#125;
    echo "<td>&nbsp;</td>";
    echo "</tr>";
    ++$i;
  &#125;
?>

</table>
<table id="actionbar">
<tr>
<?php
foreach($scripts as $script)
&#123;
    echo "<td class='action'><input type='submit' name='script' value='&#123;$script&#1111;'name']&#125;' /></td>";
&#125;
foreach($actions as $action)
&#123;

    echo "<td class='action'><input type='submit' name='action' value='&#123;$action&#1111;'name']&#125;' /></td>";
&#125;
?>
<td>&nbsp;</td>
</tr>

LIST The code that is executed when someone selects something from the list

Code: Select all

if (isset($_POST&#1111;'script']))
&#123;
    foreach($scripts as $script)
    &#123;
        if ($script&#1111;'name'] == $_POST&#1111;'script'])
        &#123;
            if (isset($_POST&#1111;'select']))
            &#123;
                $selections = array();
                $rows = $variables&#1111;'rows'];
                // loop through all selected rows
                foreach($_POST&#1111;'select'] as $select)
                &#123;
                    $pairs = array();

                    // only need the pkey values
                    foreach($rows&#1111;$select] as $key => $val)
                    &#123;
                        $data_properties = $data->getProperties();
                        foreach($data_properties as $data_property)
                        &#123;
                            if ($data_property&#1111;'name'] == $key && isset($data_property&#1111;'pkey']) && $data_property&#1111;'pkey'] == 'y')
                            &#123;
                                $pairs&#1111;$key] = $val;
                            &#125;
                        &#125;
                    &#125;
                    $selections&#1111;] = $pairs;
                &#125;
                $variables&#1111;'selections']&#1111;$class] = $selections;
            &#125;
            unset($variables&#1111;'rows']);
            unset($variables&#1111;'search']&#1111;$class]);
            scriptSave($variables);
            scriptNext($script&#1111;'script_id']);
        &#125;
    &#125;
&#125;
DETAIL The code that is executed when a selection was made

Code: Select all

// get a selection
$pairs = array_shift($variables&#1111;'selections']&#1111;$class]);
array_unshift($variables&#1111;'selections']&#1111;$class], $pairs);

if (isset($_POST&#1111;'action']))
&#123;
    if ($_POST&#1111;'action'] == 'Cancel')
    &#123;
        unset($variables&#1111;'selections']&#1111;$class]);
        scriptSave($variables);
        scriptPrevious();
    &#125;

    if ($_POST&#1111;'action'] == 'Update')
    &#123;
        // replace some stuff
        foreach($properties as $property)
        &#123;
            $data_properties = $data->getProperties();
            foreach($data_properties as $data_property)
            &#123;
                if ($data_property&#1111;'name'] == $property&#1111;'column'] && (!isset($data_property&#1111;'pkey']) || $data_property&#1111;'pkey'] != 'y') && (!isset($data_property&#1111;'noedit']) || $data_property&#1111;'noedit'] != 'y'))
                &#123;
                    $pairs&#1111;$property&#1111;'column']] = $_POST&#1111;$property&#1111;'column']];
                &#125;
            &#125;
        &#125;
        $data->update($pairs);

        $pairs = array_shift($variables&#1111;'selections']&#1111;$class]);

        if (isset($preselections))
        &#123;
            unset($variables&#1111;'selections']&#1111;$class]);
            scriptSave($variables);
            scriptPrevious();
        &#125;

        if (count($variables&#1111;'selections']&#1111;$class]) > 0)
        &#123;
            $pairs = array_shift($variables&#1111;'selections']&#1111;$class]);
            array_unshift($variables&#1111;'selections']&#1111;$class], $pairs);
            scriptSave($variables);
        &#125;
        else
        &#123;
            unset($variables&#1111;'selections']&#1111;$class]);
            scriptSave($variables);
            scriptPrevious();
        &#125;
    &#125;
&#125;
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Please choose a better title to threads than 'Help'
Post Reply