PHP/HTML Form scrolling through records/adding removing data

nealios · November 7, 2007

Hello im new to PHP. I am creating an admin system for my plumbing business.

I can display my data in a HTML but i was wondering how i would display it in a form view. I want the data to be displayed in a text field so that i can scroll through records individually and add and edit data. (Similar to Access)

Any help would be appreciated! Many thanks,

My current code for reference is

<?php

$con = mysql_connect('localhost:8889', 'root', 'root');

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db("Ocklynge", $con);

$result = mysql_query("SELECT * FROM Customer");

echo "<table border='1'>

<tr>

<th>CustomerID</th>

<th>Title</th>

<th>FirstName</th>

<th>Surname</th>

<th>Address1</th>

<th>Address2</th>

<th>County</th>

<th>Postcode</th>

<th>Telephone</th>

<th>Mobile</th>

<th>Email</th>

<th>AdditionalInfo</th>

</tr>";

while($row = mysql_fetch_array($result))

{

echo "<tr>";

echo "<td>" . $row['CustomerID'] . "</td>";

echo "<td>" . $row['Title'] . "</td>";

echo "<td>" . $row['FirstName'] . "</td>";

echo "<td>" . $row['Surname'] . "</td>";

echo "<td>" . $row['Address1'] . "</td>";

echo "<td>" . $row['Address2'] . "</td>";

echo "<td>" . $row['Town'] . "</td>";

echo "<td>" . $row['County'] . "</td>";

echo "<td>" . $row['Postcode'] . "</td>";

echo "<td>" . $row['Telephone'] . "</td>";

echo "<td>" . $row['Mobile'] . "</td>";

echo "<td>" . $row['Email'] . "</td>";

echo "<td>" . $row['AdditionalInfo'] . "</td>";

echo "</tr>";

}

echo "</table>";

mysql_close($con);

?>

PHP_PhREEEk · November 8, 2007

The ability to add and/or edit data versus just displaying it is quite a bit more overhead programming. Instead of updating any individual field in a record, you should update an entire record. So you'd have to put your table into a FORM with a Submit button, then update a record by pressing the Submit button. If you are the only one to use this FORM, and therefore don't require any security or validation, it wouldn't be too hard. If this FORM was on a public server, you'd be in for quite a bit of data validation.

As you have it now, it appears that each record would span horizontally, which then looks like you'd have to constantly scroll left and right. That would get annoying quickly, so you might want to span vertically instead. You'd have a better chance of viewing an entire record onscreen. At any rate, you have to get all of these variables into a FORM to do what you want.

PhREEEk

nealios · November 8, 2007

Thanks for your reply.

Any tips of how i would actually code it? I have no idea?

PHP_PhREEEk · November 8, 2007

The tip I gave should have gotten you started... there's no database stuff necessary at all, to start.

You need to get all of a single record broken down into individual FORM fields. Have you not done any work with HTML Forms?

PhREEEk

nealios · November 8, 2007

Ive had little experience with any of this. Ive been using W3 schools as a start which is quite useful.

Since i first posted i have used HTML form and the POST variable to add stuff to my database.

What i want to do is display what i have in the database in a form rather than a HTML table. That way i can hopefully add buttons to scroll through records.

Thanks again

PHP_PhREEEk · November 8, 2007

I'll put something together for you.

Please use phpMyAdmin or similar and do a database dump. I need STRUCTURE and at least one valid record. Save as .sql file and copy/paste it into BBCode CODE tags here. I'll start on the Form structure right now while you're doing that.

PhREEEk

nealios · November 8, 2007

Many thanks i really appreciate your help.

-- phpMyAdmin SQL Dump
-- version 2.7.0-pl2
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Nov 08, 2007 at 06:06 PM
-- Server version: 5.0.19
-- PHP Version: 5.1.6
-- 
-- Database: `Ocklynge`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `Customer`
-- 

CREATE TABLE `Customer` (
  `CustomerID` int(20) NOT NULL auto_increment,
  `Title` varchar(5) default NULL,
  `FirstName` varchar(20) default NULL,
  `Surname` varchar(20) default NULL,
  `Address1` varchar(25) default NULL,
  `Address2` varchar(25) default NULL,
  `Town` varchar(25) default NULL,
  `County` varchar(25) default NULL,
  `Postcode` varchar( default NULL,
  `Telephone` varchar(15) default NULL,
  `Mobile` varchar(15) default NULL,
  `Email` varchar(35) default NULL,
  `AdditionalInfo` varchar(50) default NULL,
  PRIMARY KEY  (`CustomerID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- 
-- Dumping data for table `Customer`
-- 

INSERT INTO `Customer` VALUES (1, 'Mr', 'Tom', 'Neal', '42 Street', NULL, 'Eastbourne', 'East Sussex', 'BN22 7HE', '01323643266', '07764777378', 'nealios@hotmail.com', 'website');
INSERT INTO `Customer` VALUES (2, 'miss', 'harriet', 'coombs', '10 street', 'stonecross', 'pevensy', 'east sussex', 'bn21 7ep', '01323566665', '07763717546', 'hcoombs@hotmail.com', 'website');
INSERT INTO `Customer` VALUES (3, '', '', '', '', '', '', '', '', '', '', '', '');

PHP_PhREEEk · November 8, 2007

ok, thanks... it imported fine. Give me a few hours.

PhREEEk

PHP_PhREEEk · November 8, 2007

This has quickly turned into a 2 page process, so it'll be awhile

I have the modification form about 95% complete. You will be able to modify a record (any individual field), create a new record, or delete one.

Right now I'm working on the page that will view records for selection to be modified.

It's probably getting late where you are (I'm guessing UK). I probably won't have a working set of scripts (they need to sync) until your morrow in the morning.

Cheers,

PhREEEk

nealios · November 8, 2007

Yeah i am in the UK, please do not put yourself out too much. As long as i have the solution that is the main thing.

Thanks again!

PHP_PhREEEk · November 9, 2007

This is just about finished...

I'm finishing up testing right now, then it'll take me awhile to post up the info...

Since this is a public forum, some might want to use this and/or modify it for their needs, and others might want to download it just to learn a few things. Given that, I'll need to post up how to get the right DB initialized and how the script works.

Anyways, I'll be back within the hour or so to start posting the code.

PhREEEk

nealios · November 9, 2007

Thank you!

I really appreciate your hard work!

PHP_PhREEEk · November 9, 2007

Ok, so here we go...

@nealios - You will need to start fresh with a new database and everything. I modified the database quite a bit, especially removing any and all capital letters in database, table, or field names. In coding, you get tired of chasing down bugs that end up a stoopid capital letter error... so you learn to code in lower-case whenever possible. Read the instructions at the top of the first script, and you'll be good to go (don't forget to add your non-default MySQL port!). AdditionalInfo field has been increased to 500 chars max, so it is now more or less a notepad for jotting down whatever about that contact.

@anyone else - This is currently in a UK format. If you want US format, let me know... Any ideas, features, whatever, let me know. Can you make it 'pertier'? I'm not big colors and stuff... if you are, send me ideas.

Features:

Create New Records

Modify Existing Records

Delete Unwanted Records

Re-Sync All Records

Sort Records By Any Field (in LIST view)

Modifiable CSS Text Imbedded in Script (You customize)

Save as dbadmin.php - READ the comments THOROUGHLY!

<?php
// WARNING - This script is NOT SAFE for a public server! It has not been
// thoroughly secured from SQL injection or other vulnerabilities. YOU HAVE
// BEEN WARNED, and YOU USE AT YOUR OWN RISK!!

#############################################################################
## First, create a database of any name. Then, if necessary, create a User
## (with a password!) and give that User permissions on the new database.
## Add the server, port, database name, User, and password below.
## NOTES: port can usually be left blank. Only define a port if you have
## MySQL configured to run on a non-default port (other than 3306). Define
## these same values for the customers.php script, then run the customers.php
## script one time to have it generate a blank record to start with.
#############################################################################
$sql_server = 'localhost'; // You can usually leave this alone
$sql_port = '';
$sql_dbname = 'example'; // Put whatever your database name is (use lower case)
$sql_user   = 'root'; // MySQL User
$sql_pass   = 'secret'; // MySQL User password

#############################################################################
## Next, using phpMyAdmin or similar, copy the following text to the SQL
## input box and execute to build the table.
## ( Copy/Paste everything between /* and */ )
## Define the same variables above over to customers.php. Run customers.php
## once, and a blank 'first record' will be created, which you can then
## edit your first contact's information to, and select MODIFY to save it.
## To create a new record, load any record by clicking the CID field, and it
## will be loaded into the record editor. Edit the fields with the new
## information, and then select NEW. After adding and deleting many records,
## there will exist gaps in the CustomerID (CID) numerical order. Selecting
## SYNCHRONIZE from the record editor will re-number all records, fixing
## any gaps. The Database is auto-OPTIMIZED every time a record is deleted.
#############################################################################
/*
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
  `cid` int(20) NOT NULL auto_increment,
  `title` varchar(5) default NULL,
  `first_name` varchar(20) default NULL,
  `surname` varchar(20) default NULL,
  `address1` varchar(25) default NULL,
  `address2` varchar(25) default NULL,
  `town` varchar(25) default NULL,
  `county` varchar(25) default NULL,
  `postcode` varchar( default NULL,
  `telephone` varchar(15) default NULL,
  `mobile` varchar(15) default NULL,
  `email` varchar(35) default NULL,
  `additional_info` varchar(500) default NULL,
  PRIMARY KEY  (`cid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
*/

#############################################################################
## No further user modifications are needed below here. Changes to code
## from here down will only alter actual script behavior and output.
#############################################################################

/* SCRIPT PROPER */
// Pack database variables
$db = array("name"   => $sql_dbname,
            "user"   => $sql_user,
            "pass"   => $sql_pass,
            "server" => $sql_server,
            "port"   => $sql_port
           );

// We need a record number, or this script won't run
if( isset($_GET['cid']) && is_numeric($_GET['cid']) ) {
    $cid = intval($_GET['cid']);
} elseif ( isset($_POST['cid']) && is_numeric($_POST['cid']) ) {
    $cid = intval($_POST['cid']);
} else {
    abort_script('You cannot access this script directly...', '');
}

// Is there an action requested, or just display the record?
if( isset($_POST['op']) && is_numeric($_POST['op']) ) {
    $op = intval($_POST['op']);
} elseif ( isset($_GET['op']) && is_numeric($_GET['op']) ){
    $op = intval($_GET['op']);
} else {
    unset($op);
}
if ( $op ) {	
    switch( $op ) {
        case 1:
            modify_record($cid);
            break;
        case 2:
            new_record();
            break;
        case 3:
            confirm_delete($cid, $_GET['del']);
            die();
            break;
        case 4:
            sync_records();
            break;
        default:
            abort_script('Unkown error - this shouldn\'t happen...', '');
    }
}

// Test our database connection
db_connect();
if ( !$result = mysql_query("SELECT * FROM `customer` WHERE `cid` = '$cid'") ) {
    abort_script('Database error: ' . mysql_error(), '');
} else {
    $numrows = mysql_num_rows($result);
}

// We want exactly one record
if ( $numrows === 1 ) {
    send_header();
    $row = mysql_fetch_array($result);
    // We are posting raw data - not a good idea. Sanitizing needed here
    echo"
<p>
<form name=\"record_mod\" method=\"post\" action=\"dbadmin.php\">
<table border=\"1\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
  <tr>
    <td>
      <table border=\"0\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
        <tr>
          <td align=\"left\">Customer ID: {$row['cid']}</td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"0\" cellpadding=\"4\">
        <tr>
          <td width=\"75\" align=\"right\">Title:</td>
          <td colspan=\"3\"><input name=\"title\" type=\"text\" size=\"5\" maxlength=\"5\" value=\"{$row['title']}\" /></td>
        </tr>
        <tr>
          <td width=\"75\" align=\"right\">First:</td>
          <td><input name=\"first\" type=\"text\" size=\"20\" maxlength=\"20\" value=\"{$row['first_name']}\" /></td>
          <td>Surname:</td>
          <td><input name=\"surname\" type=\"text\" size=\"30\" maxlength=\"20\" value=\"{$row['surname']}\" /></td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"0\" cellpadding=\"4\">
        <tr>
          <td width=\"75\" align=\"right\">Address 1:</td>
          <td><input name=\"address1\" type=\"text\" size=\"40\" maxlength=\"25\" value=\"{$row['address1']}\" /></td>
        </tr>
        <tr>
          <td width=\"75\" align=\"right\">Address 2:</td>
          <td><input name=\"address2\" type=\"text\" size=\"40\" maxlength=\"25\" value=\"{$row['address2']}\" /></td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"0\" cellpadding=\"4\">
        <tr>
          <td width=\"75\" align=\"right\">Town:</td>
          <td><input name=\"town\" type=\"text\" size=\"20\" maxlength=\"25\" value=\"{$row['town']}\" /></td>
          <td>County:</td>
          <td><input name=\"county\" type=\"text\" size=\"20\" maxlength=\"25\" value=\"{$row['county']}\" /></td>
          <td>Postcode:</td>
          <td><input name=\"postcode\" type=\"text\" size=\"15\" maxlength=\"8\" value=\"{$row['postcode']}\" /></td>
        </tr>
      </table>
      <hr align=\"left\" width=\"650\">
      <table border=\"0\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
        <tr>
          <td align=\"left\">Contact INFO</td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"0\" cellpadding=\"4\">
        <tr>
          <td width=\"75\" align=\"right\">Telephone:</td>
          <td><input name=\"telephone\" type=\"text\" size=\"15\" maxlength=\"15\" value=\"{$row['telephone']}\" /></td>
          <td>Mobile:</td>
          <td><input name=\"mobile\" type=\"text\" size=\"15\" maxlength=\"15\" value=\"{$row['mobile']}\" /></td>
          <td>Email:</td>
          <td><input name=\"email\" type=\"text\" size=\"35\" maxlength=\"35\" value=\"{$row['email']}\" /></td>
        </tr>
      </table>
      <hr align=\"left\" width=\"650\">
      <table border=\"0\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
        <tr>
          <td align=\"left\">Additional INFO (500 chars max)</td>
        </tr>
      </table>
      <table border=\"0\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
        <tr>
          <td align=\"left\"><textarea name=\"additional\" rows=\"4\" cols=\"80\">" . stripslashes($row['additional_info']) . "</textarea></td>
        </tr>
      </table>
      <hr align=\"left\" width=\"650\">
      <table border=\"0\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
        <tr>
          <td align=\"left\">Process</td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"2\" cellpadding=\"4\">
        <tr>
          <td><input name=\"op\" type=\"radio\" value=\"1\" /> Update Record</td>
          <td><input name=\"op\" type=\"radio\" value=\"2\" /> New Record</td>
          <td><input name=\"op\" type=\"radio\" value=\"3\" /> DELETE Record</td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"2\" cellpadding=\"4\">
        <tr>
          <td colspan=\"3\"><input type=\"submit\" value=\" Do It ! \" /></td>
          <td>[ <a href=\"customers.php\">Return to Records List</a> ]</td>
          <td>[ <a href=\"dbadmin.php?cid=0&op=4\">Synchronize All Records</a> ]</td>
        </tr>
      </table>
    </td>
  </tr>
</table></p>
<input name=\"cid\" type=\"hidden\" value=\"$cid\" />
</form>";
    send_footer();
} else {
    // We didn't get one record - sorry!
    $msg2 = "<p>
--> <a href=\"customers.php\">Click here to return to viewing records</a> <--</p>";
    abort_script('No such record found in database!', $msg2);
}

// FUNCTION LIBRARY //

function db_connect() {
    global $db;
    if ( !empty($db['port']) ) {
        $db['server'] .= ':' . $db['port'];
    }
    @$con = mysql_connect($db['server'], $db['user'], $db['pass']);
    if (!$con) {
        abort_script('Could not connect: ' . mysql_error(), '');
    }
    mysql_select_db($db['name'], $con);

}

function modify_record($cid) {
    db_connect();
    $additional = addslashes( substr($_POST['additional'], 0, 500) );
    $sql = "UPDATE `customer`
            SET `title` = '".$_POST['title']."', `first_name` = '".$_POST['first']."', `surname` = '".$_POST['surname']."',
                `address1` = '".$_POST['address1']."', `address2` = '".$_POST['address2']."', `town` = '".$_POST['town']."',
                `county` = '".$_POST['county']."', `postcode` = '".$_POST['postcode']."', `telephone` = '".$_POST['telephone']."',
                `mobile` = '".$_POST['mobile']."', `email` = '".$_POST['email']."', `additional_info` = '$additional'
            WHERE `cid` = $cid
           ";
    if ( !$result = mysql_query($sql) ) {
        abort_script('Database error: ' . mysql_error(), '');
    }
    header("location: dbadmin.php?cid=$cid");

}

function new_record() {
    db_connect();
    $sql = "INSERT INTO `customer`
            (`cid`, `title`, `first_name`, `surname`, `address1`, `address2`,
             `town`, `county`, `postcode`, `telephone`, `mobile`, `email`, `additional_info`)
            VALUES
            (NULL, '".$_POST['title']."', '".$_POST['first']."', '".$_POST['surname']."', '".$_POST['address1']."',
             '".$_POST['address2']."', '".$_POST['town']."', '".$_POST['county']."', '".$_POST['postcode']."',
             '".$_POST['telephone']."', '".$_POST['mobile']."', '".$_POST['email']."', '".addslashes($_POST['additional'])."')
           ";
    if ( !$result = mysql_query($sql) ) {
        abort_script('Database error: ' . mysql_error(), '');
    }
    $sql = "SELECT LAST_INSERT_ID()";
    if ( !$result = mysql_query($sql) ) {
        abort_script('Database error: ' . mysql_error(), '');
    }
    $last_id = mysql_fetch_array($result);
    header("location: dbadmin.php?cid=$last_id[0]");

}

function confirm_delete($cid, $del) {
    if ( isset($del) && is_numeric($del) ) {
        $del = intval($del);
        if ( $del === 1 ) {
            db_connect();
            $sql = "DELETE FROM `customer` WHERE `cid` = $cid LIMIT 1";
            if ( !$result = mysql_query($sql) ) {
                abort_script('Database error: ' . mysql_error(), '');
            } else {
                $sql = "OPTIMIZE TABLE `customer`";
                if ( !$result = mysql_query($sql) ) {
                    abort_script('Database error: ' . mysql_error(), '');
                }
                header("location: customers.php");
            }
        } else {
            header("location: dbadmin.php?cid=$cid");
            die();
        }
    }
    send_header();
    echo "
<p>
<h3>Please confirm you wish to remove this record? [
 <a href=\"dbadmin.php?cid=$cid&op=3&del=1\">yes</a> |
 <a href=\"dbadmin.php?cid=$cid\">no</a> ]</h3>";
    send_footer();

}

function sync_records() {
    db_connect();
    // Load all records into an array for temp storage
    $sql = "SELECT * FROM `customer` ORDER BY `cid`";
    if ( !$result = mysql_query($sql) ) {
        abort_script('Database error: ' . mysql_error(), '');
    } else {
        $numrows = mysql_num_rows($result);
    }
    if ( $numrows > 0 ) {
        WHILE ($row = mysql_fetch_array($result)) {
            $t_row[] = $row;
        }
        $sql = "TRUNCATE TABLE `customer`";
        if ( !$result = mysql_query($sql) ) {
            abort_script('Database error: ' . mysql_error(), '');
        }
        for ($x=0 ; $x < count($t_row) ; $x++) {
            $sql = "INSERT INTO `customer`
                    (`cid`, `title`, `first_name`, `surname`, `address1`, `address2`,
                     `town`, `county`, `postcode`, `telephone`, `mobile`, `email`, `additional_info`)
                    VALUES
                    (NULL, '".$t_row[$x]['title']."', '".$t_row[$x]['first_name']."', '".$t_row[$x]['surname']."', '".$t_row[$x]['address1']."',
                     '".$t_row[$x]['address2']."', '".$t_row[$x]['town']."', '".$t_row[$x]['county']."', '".$t_row[$x]['postcode']."',
                     '".$t_row[$x]['telephone']."', '".$t_row[$x]['mobile']."', '".$t_row[$x]['email']."', '".addslashes($t_row[$x]['additional_info'])."')
                   ";
            if ( !$result = mysql_query($sql) ) {
                abort_script('Database error: ' . mysql_error(), '');
            }
        }
        header("location: customers.php");
    } else {
        header("location: customers.php");
    }

}

function send_header() {
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Individual Record Editor</title>
<style type="text/css">
body {
background-color: #888888;
margin: 20px;
text-align: left;
font-family: Trebuchet MS;
}
a:link {
    color:#333333;
    text-decoration: none;
}
a:vlink {
    color:#333333;
    text-decoration: none;
}
a:visited {
    color:#333333;
    text-decoration: none;
}
a:alink {
    color:#333333;
    text-decoration: none;
}
a:hover {
    color:#DDDDDD;
    text-decoration: underline;
}
input {
    background-color: #BBBBBB;
    border: 0px;
}
textarea {
    background-color: #BBBBBB;
    border: 0px;
}
</style>
</head>

<body>
<?php

}

function send_footer() {
    echo "
</body>
</html>";

}

function abort_script($msg, $msg2) {
    send_header();
    echo "
<p>
<h2>$msg</h2></p>$msg2";
    send_footer();
    die();

}

?>

Save as customers.php - Run once after your database and table is setup (see dbadmin.php). NOT BEFORE... after! ::)

<?php
// WARNING - This script is NOT SAFE for a public server! It has not been
// thoroughly secured from SQL injection or other vulnerabilities. YOU HAVE
// BEEN WARNED, and YOU USE AT YOUR OWN RISK!!

#############################################################################
## If you have NOT setup your database, refer to dbadmin.php for everything
## you need to do first. Once you have followed the instructions in
## dbadmin, set the variables below to the SAME things you put into
## dbadmin.php
#############################################################################
$sql_server = 'localhost'; // You can usually leave this alone
$sql_port = '';
$sql_dbname = 'example'; // Put whatever your database name is (use lower case)
$sql_user   = 'root'; // MySQL User
$sql_pass   = 'secret'; // MySQL User password

#############################################################################
## No further user modifications are needed below here. Changes to code
## from here down will only alter actual script behavior and output.
#############################################################################

/* SCRIPT PROPER */
// Pack database variables
$db = array("name"   => $sql_dbname,
            "user"   => $sql_user,
            "pass"   => $sql_pass,
            "server" => $sql_server,
            "port"   => $sql_port
           );

if ( isset($_GET['sort']) ) {
    switch( $_GET['sort'] ) {
        case 'cid':
            $sort = 'cid';
            break;
        case 'title':
            $sort = 'title';
            break;
        case 'first':
            $sort = 'first_name';
            break;
        case 'add1':
            $sort = 'address1';
            break;
        case 'add2':
            $sort = 'address2';
            break;
        case 'town':
            $sort = 'town';
            break;
        case 'county':
            $sort = 'county';
            break;
        case 'postcode':
            $sort = 'postcode';
            break;
        case 'tele':
            $sort = 'telephone';
            break;
        case 'mobile':
            $sort = 'mobile';
            break;
        case 'email':
            $sort = 'email';
            break;
        default:
            $sort = 'surname';
    }
} else {
    $sort = 'surname';
}
db_connect();
if ( !$result = mysql_query("SELECT * FROM `customer` ORDER BY `$sort`") ) {
    die('Database error: ' . mysql_error());
}
// We want at least one record
if ( mysql_num_rows($result) > 0 ) {
    $x = 1;
    send_header();
    WHILE ($row = mysql_fetch_array($result)) {
        // We are posting raw data - not a good idea. Sanitizing needed here
        echo "  <tr>
    <td align=\"center\">$x</td>
    <td align=\"center\"><a href=\"dbadmin.php?cid={$row['cid']}\">{$row['cid']}</a></td>
";
        if ( empty($row['surname']) ) {
            echo "    <td> </td>\n";
        } else {
            echo "    <td>{$row['surname']}</td>\n";
        }
        if ( empty($row['title']) ) {
            echo "    <td> </td>\n";
        } else {
            echo "    <td>{$row['title']}</td>\n";
        }
        if ( empty($row['first_name']) ) {
            echo "    <td> </td>\n";
        } else {
            echo "    <td>{$row['first_name']}</td>\n";
        }
        if ( empty($row['address1']) ) {
            echo "    <td> </td>\n";
        } else {
            echo "    <td>{$row['address1']}</td>\n";
        }
        if ( empty($row['address2']) ) {
            echo "    <td> </td>\n";
        } else {
            echo "    <td>{$row['address2']}</td>\n";
        }
        if ( empty($row['town']) ) {
            echo "    <td> </td>\n";
        } else {
            echo "    <td>{$row['town']}</td>\n";
        }
        if ( empty($row['county']) ) {
            echo "    <td> </td>\n";
        } else {
            echo "    <td>{$row['county']}</td>\n";
        }
        if ( empty($row['postcode']) ) {
            echo "    <td> </td>\n";
        } else {
            echo "    <td>{$row['postcode']}</td>\n";
        }
        if ( empty($row['telephone']) ) {
            echo "    <td> </td>\n";
        } else {
            echo "    <td>{$row['telephone']}</td>\n";
        }
        if ( empty($row['mobile']) ) {
            echo "    <td> </td>\n";
        } else {
            echo "    <td>{$row['mobile']}</td>\n";
        }
        if ( empty($row['email']) ) {
            echo "    <td> </td>\n";
        } else {
            echo "    <td>{$row['email']}</td>\n";
        }
        echo"  </tr>
";
        $x++;
    }
    echo"</table>";
    send_footer();
} else {
    first_record();
    die();
}

// FUNCTION LIBRARY //

function db_connect() {
    global $db;
    if ( !empty($db['port']) ) {
        $db['server'] .= ':' . $db['port'];
    }
    @$con = mysql_connect($db['server'], $db['user'], $db['pass']);
    if (!$con) {
        abort_script('Could not connect: ' . mysql_error(), '');
    }
    mysql_select_db($db['name'], $con);

}

function first_record() {
    db_connect();
    $sql = "INSERT INTO `customer`
            (`cid`, `title`, `first_name`, `surname`, `address1`, `address2`,
             `town`, `county`, `postcode`, `telephone`, `mobile`, `email`, `additional_info`)
            VALUES
            (NULL, NULL, 'Blank Record', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
           ";
    if ( !$result = mysql_query($sql) ) {
        abort_script('Database error: ' . mysql_error(), '');
    }
    $sql = "SELECT LAST_INSERT_ID()";
    if ( !$result = mysql_query($sql) ) {
        abort_script('Database error: ' . mysql_error(), '');
    }
    $last_id = mysql_fetch_array($result);
    header("location: dbadmin.php?cid=$last_id[0]");

}

function send_header() {
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>List Customer Records</title>
<style type="text/css">
body {
    background-color: #888888;
    margin: 20px;
    text-align: left;
    font-family: Trebuchet MS;
}
a:link {
    color:#333333;
    text-decoration: none;
}
a:vlink {
    color:#333333;
    text-decoration: none;
}
a:visited {
    color:#333333;
    text-decoration: none;
}
a:alink {
    color:#333333;
    text-decoration: none;
}
a:hover {
    color:#DDDDDD;
    text-decoration: underline;
}
input {
    background-color: #BBBBBB;
    border: 0px;
}
textarea {
    background-color: #BBBBBB;
    border: 0px;
}
</style>
</head>

<body>
<p>
<span><h5>Click on the CID for any record you wish to Modify, Delete, or view Additional Info.</h5></span></p>
<p>
<span><h5>Click on the Table Headers to sort by that field (currently only ascending).</h5></span></p>
<p>
<table border="1" cellspacing="0" cellpadding="4">
  <tr>
    <th>Record</th>
    <th><a href="customers.php?sort=cid">CID</a></th>
    <th><a href="customers.php">Surname</a></th>
    <th><a href="customers.php?sort=title">Title</a></th>
    <th><a href="customers.php?sort=first">FirstName</a></th>
    <th><a href="customers.php?sort=add1">Address1</a></th>
    <th><a href="customers.php?sort=add2">Address2</a></th>
    <th><a href="customers.php?sort=town">Town</a></th>
    <th><a href="customers.php?sort=county">County</a></th>
    <th><a href="customers.php?sort=postcode">Postcode</a></th>
    <th><a href="customers.php?sort=tele">Telephone</a></th>
    <th><a href="customers.php?sort=mobile">Mobile</a></th>
    <th><a href="customers.php?sort=email">Email</a></th>
  </tr>
<?php

}

function send_footer() {
    echo "
</body>
</html>";

}

function abort_script($msg, $msg2) {
    send_header();
    echo "
<p>
<h2>$msg</h2></p>$msg2";
    send_footer();
    die();

}

?>

PhREEEk

nealios · November 10, 2007

This looks great! Thanks for all your help, its helped me no end! you are a legend

jimlawrnc · November 10, 2007

Very nice

Can you point out some sections were the code can be protected ?

PHP_PhREEEk · November 11, 2007

Very nice

Can you point out some sections were the code can be protected ?

Well, first off... in the interest of keeping it simple (which quickly went out the door as I wanted to provide more features! hehe), I did not want to have any included files. If this was done a little more 'pro', all the functions would go into a functions.php include, and all of the database variables would be in a config.php include.

As far as security, you want to lock all POSTed content down to absolute formats where possible. For instance, most country postal codes (or zipcodes in the US) have a specific format. You would analyze the incoming POST variable for the postal code, and reject anything that doesn't fit the exact format. That's usually done with a ereg or eregi.

So let's consult Frank's Compulsive Guide to Postal Addresses, which is an incredibly thorough resource for helping formulate REGEX's for address formats worldwide. Frank's says all UK Postal Codes have the following format:

AN NAA
AAN NAA
ANA NAA
ANN NAA
AANN NAA
AANA NAA

Where A = Alpha character (always upper-case)
and N is a number

So let's see... There are all A's as the first character of each set, so our REGEX would start out matching ONE alpha character at the BEGINNING of the string. At the end of string (thank goodness!), we have a definite pattern of sNAA (s = space). So if we created a REGEX which verifies the existence of one alpha at the start, then 2 or 3 alpha-numerics, and finally sNAA at the END of the string, we would be very close to locking that variable down (or rather, rejecting anything that didn't 'look' like a UK postal code to our REGEX). If our variable passes the regex, last thing we would do is run it through strtoupper (just formatting, no security concern there). So you want it 110% accurate? Consider the following from the UK Government Data Standards Catalogue, which in addition to the above posted format, adds this:

Please note the following:-

The letters Q, V and X are not used in the first position. 
The letters I, J and Z are not used in the second position. 
The only letters to appear in the third position are A, B, C, D, E, F, G, H, J, K, S, T, U and W. 
The only letters to appear in the fourth position are A, B, E, H, M, N, P, R, V, W, X and Y. 
The second half of the Postcode is always consistent numeric, alpha, alpha format and the letters C, I, K, M, O and V are never used.

Well.... we could really go crazy here. As you can see, if we used our formula above (any alpha char in pos 1), Q V and X would pass our test. To be more accurate, we would need to disclude those. There is a posted ereg formula at php.net that attempts to be 110% accurate. You could then add this function to dbadmin.php:

function IsPostcode($postcode) {
    $postcode = strtoupper(str_replace(chr(32),'',$postcode));
    if(ereg("^(GIR0AA)|(TDCU1ZZ)|((([A-PR-UWYZ][0-9][0-9]?)|"
    ."(([A-PR-UWYZ][A-HK-Y][0-9][0-9]?)|"
    ."(([A-PR-UWYZ][0-9][A-HJKSTUW])|"
    ."([A-PR-UWYZ][A-HK-Y][0-9][ABEHMNPRVWXY]))))"
    ."[0-9][ABD-HJLNP-UW-Z]{2})$", $postcode))
        return $postcode;
    } else {
        return FALSE;
    }

}

and pass $_POST['postcode'] through it. You would either get back a valid and cased UK postalcode, or FALSE.

Ok, enough already... now you see why this adds so much overhead to programming a public script. That was only one field. In the modify record section of this script, there are 11 such fields which can be locked down in this fashion, and 1 large textarea to deal with (all I have done is to addslashes to this field so that punctuation marks do not crash the database insert). So there you have it...

PhREEEk

nealios · November 13, 2007

Hello again,

I have tried editing the phpadmin so that i can navigate through a single record at a time. I have added an additional radio button in the process section to enable me to scroll from one record to the next. I have added an extra case in the switch statement and created a function.

Though i dont think i have created the function properly. Is there a way of doing this or do you have to go back to the customer.php list each time.

The function code is as follows


function next_record($cid) {//enables you navigate
    db_connect();
$nextcid = $cid+1; 
$sql = "SELECT * FROM customer WHERE cid='$nextcid'"; 

    if ( !$result = mysql_query($sql) ) {
        abort_script('Database error: ' . mysql_error(), '');
    }
    header("location: dbadmin.php?cid=$cid");

}

Thanks

Nealios

PHP_PhREEEk · November 13, 2007

That'd prolly be a nice feature Nealios. I'll add it in and post it up when done.

PhREEEk

PHP_PhREEEk · November 13, 2007

Here ya go... discussion about what we did is after the script:

(this is a new dbadmin.php - overwrite the existing one)

<?php
// WARNING - This script is NOT SAFE for a public server! It has not been
// thoroughly secured from SQL injection or other vulnerabilities. YOU HAVE
// BEEN WARNED, and YOU USE AT YOUR OWN RISK!!

#############################################################################
## First, create a database of any name. Then, if necessary, create a User
## (with a password!) and give that User permissions on the new database.
## Add the server, port, database name, User, and password below.
## NOTES: port can usually be left blank. Only define a port if you have
## MySQL configured to run on a non-default port (other than 3306). Define
## these same values for the customers.php script, then run the customers.php
## script one time to have it generate a blank record to start with.
#############################################################################
$sql_server = 'localhost'; // You can usually leave this alone
$sql_port = '';
$sql_dbname = 'example'; // Put whatever your database name is (use lower case)
$sql_user   = 'root'; // MySQL User
$sql_pass   = 'secret'; // MySQL User password

#############################################################################
## Next, using phpMyAdmin or similar, copy the following text to the SQL
## input box and execute to build the table.
## ( Copy/Paste everything between /* and */ )
## Define the same variables above over to customers.php. Run customers.php
## once, and a blank 'first record' will be created, which you can then
## edit your first contact's information to, and select MODIFY to save it.
## To create a new record, load any record by clicking the CID field, and it
## will be loaded into the record editor. Edit the fields with the new
## information, and then select NEW. After adding and deleting many records,
## there will exist gaps in the CustomerID (CID) numerical order. Selecting
## SYNCHRONIZE from the record editor will re-number all records, fixing
## any gaps. The Database is auto-OPTIMIZED every time a record is deleted.
#############################################################################
/*
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
  `cid` int(20) NOT NULL auto_increment,
  `title` varchar(5) default NULL,
  `first_name` varchar(20) default NULL,
  `surname` varchar(20) default NULL,
  `address1` varchar(25) default NULL,
  `address2` varchar(25) default NULL,
  `town` varchar(25) default NULL,
  `county` varchar(25) default NULL,
  `postcode` varchar( default NULL,
  `telephone` varchar(15) default NULL,
  `mobile` varchar(15) default NULL,
  `email` varchar(35) default NULL,
  `additional_info` varchar(500) default NULL,
  PRIMARY KEY  (`cid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
*/

#############################################################################
## No further user modifications are needed below here. Changes to code
## from here down will only alter actual script behavior and output.
#############################################################################

/* SCRIPT PROPER */
// Pack database variables
$db = array("name"   => $sql_dbname,
            "user"   => $sql_user,
            "pass"   => $sql_pass,
            "server" => $sql_server,
            "port"   => $sql_port
           );

// We need a record number, or this script won't run
if( isset($_GET['cid']) && is_numeric($_GET['cid']) ) {
$cid = intval($_GET['cid']);
} elseif ( isset($_POST['cid']) && is_numeric($_POST['cid']) ) {
$cid = intval($_POST['cid']);
} else {
abort_script('You cannot access this script directly...', '');
}

// Is there an action requested, or just display the record?
if( isset($_POST['op']) && is_numeric($_POST['op']) ) {
$op = intval($_POST['op']);
} elseif ( isset($_GET['op']) && is_numeric($_GET['op']) ){
$op = intval($_GET['op']);
} else {
unset($op);
}
if ( $op ) {	
switch( $op ) {
	case 1:
		modify_record($cid);
		break;
	case 2:
		new_record();
		break;
	case 3:
		confirm_delete($cid, $_GET['del']);
		die();
		break;
	case 4:
		sync_records();
		break;
	case 5:
		next_record($cid, $_GET['move']);
		break;
	default:
		abort_script('Unkown error - this shouldn\'t happen...', '');
}
}

// Test our database connection
db_connect();
if ( !$result = mysql_query("SELECT * FROM `customer` WHERE `cid` = '$cid'") ) {
abort_script('Database error: ' . mysql_error(), '');
} else {
$numrows = mysql_num_rows($result);
}

// We want exactly one record
if ( $numrows === 1 ) {
send_header();
$row = mysql_fetch_array($result);
// We are posting raw data - not a good idea. Sanitizing needed here
echo"
<p>
<form name=\"record_mod\" method=\"post\" action=\"dbadmin.php\">
<table border=\"1\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
  <tr>
    <td>
      <table border=\"0\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
        <tr>
          <td align=\"left\">Customer ID: {$row['cid']}</td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"0\" cellpadding=\"4\">
        <tr>
          <td width=\"75\" align=\"right\">Title:</td>
          <td colspan=\"3\"><input name=\"title\" type=\"text\" size=\"5\" maxlength=\"5\" value=\"{$row['title']}\" /></td>
        </tr>
        <tr>
          <td width=\"75\" align=\"right\">First:</td>
          <td><input name=\"first\" type=\"text\" size=\"20\" maxlength=\"20\" value=\"{$row['first_name']}\" /></td>
          <td>Surname:</td>
          <td><input name=\"surname\" type=\"text\" size=\"30\" maxlength=\"20\" value=\"{$row['surname']}\" /></td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"0\" cellpadding=\"4\">
        <tr>
          <td width=\"75\" align=\"right\">Address 1:</td>
          <td><input name=\"address1\" type=\"text\" size=\"40\" maxlength=\"25\" value=\"{$row['address1']}\" /></td>
        </tr>
        <tr>
          <td width=\"75\" align=\"right\">Address 2:</td>
          <td><input name=\"address2\" type=\"text\" size=\"40\" maxlength=\"25\" value=\"{$row['address2']}\" /></td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"0\" cellpadding=\"4\">
        <tr>
          <td width=\"75\" align=\"right\">Town:</td>
          <td><input name=\"town\" type=\"text\" size=\"20\" maxlength=\"25\" value=\"{$row['town']}\" /></td>
          <td>County:</td>
          <td><input name=\"county\" type=\"text\" size=\"20\" maxlength=\"25\" value=\"{$row['county']}\" /></td>
          <td>Postcode:</td>
          <td><input name=\"postcode\" type=\"text\" size=\"15\" maxlength=\"8\" value=\"{$row['postcode']}\" /></td>
        </tr>
      </table>
      <hr align=\"left\" width=\"650\">
      <table border=\"0\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
        <tr>
          <td align=\"left\">Contact INFO</td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"0\" cellpadding=\"4\">
        <tr>
          <td width=\"75\" align=\"right\">Telephone:</td>
          <td><input name=\"telephone\" type=\"text\" size=\"15\" maxlength=\"15\" value=\"{$row['telephone']}\" /></td>
          <td>Mobile:</td>
          <td><input name=\"mobile\" type=\"text\" size=\"15\" maxlength=\"15\" value=\"{$row['mobile']}\" /></td>
          <td>Email:</td>
          <td><input name=\"email\" type=\"text\" size=\"35\" maxlength=\"35\" value=\"{$row['email']}\" /></td>
        </tr>
      </table>
      <hr align=\"left\" width=\"650\">
      <table border=\"0\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
        <tr>
          <td align=\"left\">Additional INFO (500 chars max)</td>
        </tr>
      </table>
      <table border=\"0\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
        <tr>
          <td align=\"left\"><textarea name=\"additional\" rows=\"4\" cols=\"80\">" . stripslashes($row['additional_info']) . "</textarea></td>
        </tr>
      </table>
      <hr align=\"left\" width=\"650\">
      <table border=\"0\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
        <tr>
          <td align=\"left\">Process</td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"2\" cellpadding=\"4\">
        <tr>
          <td><input name=\"op\" type=\"radio\" value=\"1\" /> Update Record</td>
          <td><input name=\"op\" type=\"radio\" value=\"2\" /> New Record</td>
          <td><input name=\"op\" type=\"radio\" value=\"3\" /> DELETE Record</td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"2\" cellpadding=\"4\">
        <tr>
          <td colspan=\"3\"><input type=\"submit\" value=\" Do It ! \" /></td>
          <td>[ <a href=\"customers.php\">Return to Records List</a> ]</td>
          <td>[ <a href=\"dbadmin.php?cid=0&op=4\">Synchronize All Records</a> ]</td>
          <td style=\"padding-left: 50px;\">
            [ <a href=\"dbadmin.php?cid=$cid&op=5&move=2\">< Prev</a>
              Next Record   <a href=\"dbadmin.php?cid=$cid&op=5&move=1\">Next ></a> ]
          </td>
        </tr>
      </table>
    </td>
  </tr>
</table></p>
<input name=\"cid\" type=\"hidden\" value=\"$cid\" />
</form>";
send_footer();
} else {
// We didn't get one record - sorry!
$msg2 = "<p>
--> <a href=\"customers.php\">Click here to return to viewing records</a> <--</p>";
abort_script('No such record found in database!', $msg2);
}

// FUNCTION LIBRARY //

function db_connect() {
global $db;
if ( !empty($db['port']) ) {
	$db['server'] .= ':' . $db['port'];
}
@$con = mysql_connect($db['server'], $db['user'], $db['pass']);
if (!$con) {
	abort_script('Could not connect: ' . mysql_error(), '');
}
mysql_select_db($db['name'], $con);

}

function modify_record($cid) {
db_connect();
$additional = addslashes( substr($_POST['additional'], 0, 500) );
$sql = "UPDATE `customer`
            SET `title` = '".$_POST['title']."', `first_name` = '".$_POST['first']."', `surname` = '".$_POST['surname']."',
                `address1` = '".$_POST['address1']."', `address2` = '".$_POST['address2']."', `town` = '".$_POST['town']."',
                `county` = '".$_POST['county']."', `postcode` = '".$_POST['postcode']."', `telephone` = '".$_POST['telephone']."',
                `mobile` = '".$_POST['mobile']."', `email` = '".$_POST['email']."', `additional_info` = '$additional'
            WHERE `cid` = $cid
           ";
if ( !$result = mysql_query($sql) ) {
	abort_script('Database error: ' . mysql_error(), '');
}
header("location: dbadmin.php?cid=$cid");

}

function new_record() {
db_connect();
$sql = "INSERT INTO `customer`
            (`cid`, `title`, `first_name`, `surname`, `address1`, `address2`,
             `town`, `county`, `postcode`, `telephone`, `mobile`, `email`, `additional_info`)
            VALUES
            (NULL, '".$_POST['title']."', '".$_POST['first']."', '".$_POST['surname']."', '".$_POST['address1']."',
             '".$_POST['address2']."', '".$_POST['town']."', '".$_POST['county']."', '".$_POST['postcode']."',
             '".$_POST['telephone']."', '".$_POST['mobile']."', '".$_POST['email']."', '".addslashes($_POST['additional'])."')
           ";
if ( !$result = mysql_query($sql) ) {
	abort_script('Database error: ' . mysql_error(), '');
}
$sql = "SELECT LAST_INSERT_ID()";
if ( !$result = mysql_query($sql) ) {
	abort_script('Database error: ' . mysql_error(), '');
}
$last_id = mysql_fetch_array($result);
header("location: dbadmin.php?cid=$last_id[0]");

}

function confirm_delete($cid, $del) {
if ( isset($del) && is_numeric($del) ) {
	$del = intval($del);
	if ( $del === 1 ) {
		db_connect();
		$sql = "DELETE FROM `customer` WHERE `cid` = $cid LIMIT 1";
		if ( !$result = mysql_query($sql) ) {
			abort_script('Database error: ' . mysql_error(), '');
		} else {
			$sql = "OPTIMIZE TABLE `customer`";
			if ( !$result = mysql_query($sql) ) {
				abort_script('Database error: ' . mysql_error(), '');
			}
			header("location: customers.php");
		}
	} else {
		header("location: dbadmin.php?cid=$cid");
		die();
	}
}
send_header();
echo "
<p>
<h3>Please confirm you wish to remove this record? [
 <a href=\"dbadmin.php?cid=$cid&op=3&del=1\">yes</a> |
 <a href=\"dbadmin.php?cid=$cid\">no</a> ]</h3>";
send_footer();

}

function sync_records() {
db_connect();
// Load all records into an array for temp storage
$sql = "SELECT * FROM `customer` ORDER BY `cid`";
if ( !$result = mysql_query($sql) ) {
	abort_script('Database error: ' . mysql_error(), '');
} else {
	$numrows = mysql_num_rows($result);
}
if ( $numrows > 0 ) {
	WHILE ($row = mysql_fetch_array($result)) {
		$t_row[] = $row;
	}
	$sql = "TRUNCATE TABLE `customer`";
	if ( !$result = mysql_query($sql) ) {
		abort_script('Database error: ' . mysql_error(), '');
	}
	for ($x=0 ; $x < count($t_row) ; $x++) {
		$sql = "INSERT INTO `customer`
                    (`cid`, `title`, `first_name`, `surname`, `address1`, `address2`,
                     `town`, `county`, `postcode`, `telephone`, `mobile`, `email`, `additional_info`)
                    VALUES
                    (NULL, '".$t_row[$x]['title']."', '".$t_row[$x]['first_name']."', '".$t_row[$x]['surname']."', '".$t_row[$x]['address1']."',
                     '".$t_row[$x]['address2']."', '".$t_row[$x]['town']."', '".$t_row[$x]['county']."', '".$t_row[$x]['postcode']."',
                     '".$t_row[$x]['telephone']."', '".$t_row[$x]['mobile']."', '".$t_row[$x]['email']."', '".addslashes($t_row[$x]['additional_info'])."')
                   ";
		if ( !$result = mysql_query($sql) ) {
			abort_script('Database error: ' . mysql_error(), '');
		}
	}
	header("location: customers.php");
} else {
	header("location: customers.php");
}

}

function next_record($cid, $move) {
// Figure out move up or down
if ( isset($move) && is_numeric($move) ) {
	$move = intval($move);
	if ( $move === 1 ) {
		$move = '>';
		$direction = 'ASC';
	} elseif ( $move === 2 ) {
		$move = '<';
		$direction = 'DESC';
	} else {
		header("location: dbadmin.php?cid=$cid");
		die();
	}
	// We have a confirmed direction, let's get the next record in that direction
	db_connect();
	$sql = "SELECT `cid` FROM `customer`
                        WHERE `cid` $move '$cid'
                        ORDER BY `cid` $direction
                        LIMIT 1";
	if ( !$result = mysql_query($sql) ) {
		abort_script('Database error: ' . mysql_error(), '');
	}
	if ( mysql_num_rows($result) === 1 ) {
		list($cid) = mysql_fetch_row($result);
		header("location: dbadmin.php?cid=$cid");
		die();
	} else {
		header("location: dbadmin.php?cid=$cid");
		die();
	}
}

}

function send_header() {
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Individual Record Editor</title>
<style type="text/css">
body {
background-color: #888888;
margin: 20px;
text-align: left;
font-family: Trebuchet MS;
}
a:link {
color:#333333;
text-decoration: none;
}
a:vlink {
color:#333333;
text-decoration: none;
}
a:visited {
color:#333333;
text-decoration: none;
}
a:alink {
color:#333333;
text-decoration: none;
}
a:hover {
color:#DDDDDD;
text-decoration: underline;
}
input {
background-color: #BBBBBB;
border: 0px;
}
textarea {
background-color: #BBBBBB;
border: 0px;
}
</style>
</head>

<body>
<?php

}

function send_footer() {
echo "
</body>
</html>";

}

function abort_script($msg, $msg2) {
send_header();
echo "
<p>
<h2>$msg</h2></p>$msg2";
send_footer();
die();

}

?>

Alrighty then.... first things first, you were on the right track that all we need to do is get a new cid and send them on their way. However, just doing $cid+1 would be flawed. Why? Well, what if we have an out of sync database? Let's say we are on record 12 and the next record (do to deletes) is 15? We would be sending them to record 13, which doesn't exist. And what about if they want to move backwards? So right away, we need one additional operation (op) added to the switch/case, then a direction they want to go (I assigned $move). So if op=5, we send them to the function, and provide the function with the cid they are currently viewing, and the movement variable (1 will equal forward, 2 will equal backwards). We then assign variables the things we want changed in the SQL query depending on conditions. That way, we only have one query to work with. If we get exactly one row returned, then we have a new valid cid (either upwards or down) and then we can send them packing. If any garbage is encountered along the way, we send them packing back to the original cid they were viewing, which would include if they are at record 1 and request a previous record, or they are at the last record and request the next one.

PhREEEk

nealios · November 14, 2007

Yet again thanks very much.

however . Did you get the code working, ive copied in the code and made the neccessary changes such as the name of my db and port number. However when i click on an ID in the customers.php and try to open up dbadmin it brings up a blank screen. This only happens with the new code.

nealios · November 14, 2007

Dont worry about it Phreek i fixed it! Think i had an extra { in there somewhere!

Cheers again

nealios

nealios · November 22, 2007

Hello Phreek,

If you have the time i have another question.

I have copied the code and changed all the variables et al to create a jobadmin page. Works essentially the same as the customer admin you gave me. That went well and it now enables me to see which jobs there are and how they link to a customer (using sql statement that selects the job information from the job table and customer info from the customer table using cid as a foreign key.)

I was thinking it would be good to have a dynamic drop down box that i can select a customer by name rather than having to enter their ID each time i want to create a new record.

The code below the main echo statement is a dynamic drop down box that populates correctly. But i want it to be able to update the database. At the moment i can get it to display outside the looped echo statement. But I cant put in additional php to work within the echo? Is this possible?

Can you help out at all? I hope i explained it well enough.

Thanks

<?php

$sql_server = 'localhost'; // You can usually leave this alone
$sql_port = '8889';
$sql_dbname = 'freek'; // Put whatever your database name is (use lower case)
$sql_user   = 'root'; // MySQL User
$sql_pass   = 'root'; // MySQL User password

// To create a new record, load any record by clicking the CID field, and it
// will be loaded into the record editor. Edit the fields with the new
// information, and then select NEW. After adding and deleting many records,
// there will exist gaps in the CustomerID (CID) numerical order. Selecting
// SYNCHRONIZE from the record editor will re-number all records, fixing
// any gaps. The Database is auto-OPTIMIZED every time a record is deleted.


// Pack database variables
$db = array("name"   => $sql_dbname,
            "user"   => $sql_user,
            "pass"   => $sql_pass,
            "server" => $sql_server,
            "port"   => $sql_port
           );
	   
// need a record number, or this script won't run
if( isset($_GET['JobID']) && is_numeric($_GET['JobID']) ) {
    $JobID = intval($_GET['JobID']);
} elseif ( isset($_POST['JobID']) && is_numeric($_POST['JobID']) ) {
    $JobID = intval($_POST['JobID']);
} else {
    abort_script('You cannot access this script directly...', '');
}

// Is there an action requested, or just display the record?
if( isset($_POST['op']) && is_numeric($_POST['op']) ) {
    $op = intval($_POST['op']);
} elseif ( isset($_GET['op']) && is_numeric($_GET['op']) ){
    $op = intval($_GET['op']);////////eh
} else {
    unset($op);
}
if ( $op ) {	
switch( $op ) {
	case 1:
		modify_record($JobID);
		break;
	case 2:
		new_record();
		break;
	case 3:
		confirm_delete($JobID, $_GET['del']);
		die();
		break;
	case 4:
		sync_records();
		break;
	case 5:
		next_record($JobID, $_GET['move']);
		break;
	default:
		abort_script('Unkown error - this shouldn\'t happen...', '');
}
}



// Test our database connection
db_connect();
if ( !$result = mysql_query("SELECT * FROM customer, Job WHERE JobID = $JobID AND customer.cid = Job.cid") ) {
    abort_script('Database error: ' . mysql_error(), '');
} else {
    $numrows = mysql_num_rows($result);
}




// We want exactly one record
if ( $numrows === 1 ) {
    send_header();
    $row = mysql_fetch_array($result);
    // We are posting raw data - not a good idea. Sanitizing needed here
    echo"
<p>
<form name=\"record_mod\" method=\"post\" action=\"jobadmin.php\">
<table border=\"1\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
  <tr>
    <td>
      <table border=\"0\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
        <tr>
          <td align=\"left\">JobID: {$row['JobID']}</td>
        </tr>
      </table>
    
  <table border=\"0\" cellspacing=\"0\" cellpadding=\"4\">
        <tr>
          <td width=\"75\" align=\"right\">Start Date:</td>
          
	  <td colspan=\"3\">
	  
	  <input id=\"demo3\" type=\"text\" name=\"StartDate\" size=\"25\" value=\"{$row['StartDate']}\">  
	  <a  href=\"javascript:NewCal('demo3','ddmmyyyy')\"><img  src=\"cal.gif\" width=\"16\"  height=\"16\" border=\"0\"></a>
	  


	  
        </tr>
        <tr>
          <td width=\"75\" align=\"right\">End Date:</td>
          <td><input id=\"demo4\" type=\"text\" name=\"EndDate\" size=\"25\" value=\"{$row['EndDate']}\">  
	  <a  href=\"javascript:NewCal('demo4','ddmmyyyy')\"><img  src=\"cal.gif\" width=\"16\"  height=\"16\" border=\"0\"></a></td></tr>
          <td>Job Address 1:</td>
	  
          <td><input name=\"JobAddress1\" type=\"text\" size=\"30\" maxlength=\"20\" value=\"{$row['JobAddress1']}\" /></td>
	   <td>Job Address 2:</td>
          <td><input name=\"JobAddress2\" type=\"text\" size=\"30\" maxlength=\"20\" value=\"{$row['JobAddress2']}\" /></td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"0\" cellpadding=\"4\">
        <tr>
          <td width=\"75\" align=\"right\">Postcode:</td>
          <td><input name=\"JobPostcode\" type=\"text\" size=\"40\" maxlength=\"25\" value=\"{$row['JobPostcode']}\" /></td>
        </tr>
        <tr>
          <td width=\"75\" align=\"right\">Price :</td>
          <td><input name=\"Price\" type=\"text\" size=\"40\" maxlength=\"25\" value=\"{$row['Price']}\" /></td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"0\" cellpadding=\"4\">
        <tr>
          <td width=\"75\" align=\"right\">Description:</td>
          <td><input name=\"Description\" type=\"text\" size=\"20\" maxlength=\"25\" value=\"{$row['Description']}\" /></td>
          <td>Materials:</td>
          <td><input name=\"Materials\" type=\"text\" size=\"20\" maxlength=\"25\" value=\"{$row['Materials']}\" /></td>
       <td>Customer Id:</td> 
   
   <td><input name=\"cid\" type=\"text\" size=\"20\" maxlength=\"25\" value=\"{$row['cid']}\" /></td>
    <tr> <td>First Name:</td> 
	<td><input name=\"first_name\" type=\"text\" size=\"20\" maxlength=\"25\" value=\"{$row['first_name']}\" /></td></tr><td>surname:</td> 
	   <td><input name=\"surname\" type=\"text\" size=\"20\" maxlength=\"25\" value=\"{$row['surname']}\" /></td>
 <td>Paid<input name =\"paid\" type=\"checkbox\" value=\"{$row['paid']}\" /></td>



   
      </table>
     
      <hr align=\"left\" width=\"650\">
      <table border=\"0\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
        <tr>
          <td align=\"left\">Process</td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"2\" cellpadding=\"4\">
        <tr>
          <td><input name=\"op\" type=\"radio\" value=\"1\" /> Update Record</td>
          <td><input name=\"op\" type=\"radio\" value=\"2\" /> New Record</td>
          <td><input name=\"op\" type=\"radio\" value=\"3\" /> DELETE Record</td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"2\" cellpadding=\"4\">
        <tr>
          <td colspan=\"3\"><a href=\"#\" onclick=\"record_mod.submit()\" class=\"button\"><span class=\"icon\"> Submit </span><a></td>
	  
	  
   
          <td><a href=\"jobs.php\" class=\"button\" span class=\"icon\"> Records List </span></a></td>      
          <td><a href=\"jobadmin.php?JobID=0&op=4\" class=\"button\" span class=\"icon\"> Synchronize</span></a></td>
	   <td style=\"padding-left: 50px;\">
           <td> <a href=\"jobadmin.php?JobID=$JobID&op=5&move=2\" class=\"button\" span class=\"icon\">Previous</span></a></td><td>Navigate</td>
  		   <td> <a href=\"jobadmin.php?JobID=$JobID&op=5&move=1\" class=\"button\" span class=\"icon\">Next</span></a></td>
          </td>
	  <td><a href=\"invoice.php?JobID={$row['JobID']}\" class=\"button\" span class=\"icon\"> Create Invoice </span></a></td> 
        </tr>
      </table>
    </td>
  </tr>
</table></p>
<input name=\"JobID\" type=\"hidden\" value=\"$JobID\" />
</form>";   

$query = mysql_query("SELECT 
cid,first_name,surname FROM customer 
ORDER BY surname ASC") or                                     
        die (mysql_error());
   
    echo "<select name='customer'>\n";
   
    while ($data = mysql_fetch_array($query, MYSQL_ASSOC))
    {
        echo "<option 
value='{$data['cid']}'6>{$data['first_name']},
        {$data['surname']}</option>\n";
    }
   
    echo "</select>\n";
  


send_footer();
} else {
    // We didn't get one record - sorry!
    $msg2 = "<p>
--> <a href=\"jobs.php\">Click here to return to viewing records</a> <--</p>";
    abort_script('No such record found in database!', $msg2);
}













// FUNCTION LIBRARY //
                                
function db_connect() {
    global $db;
    if ( !empty($db['port']) ) {
        $db['server'] .= ':' . $db['port'];
    }
    @$con = mysql_connect($db['server'], $db['user'], $db['pass']);
    if (!$con) {
        abort_script('Could not connect: ' . mysql_error(), '');
    }
    mysql_select_db($db['name'], $con);

}

function modify_record($JobID) {//enables you to edit and save the current record
    db_connect();
    $sql = "UPDATE Job
            SET StartDate = '".$_POST['StartDate']."', EndDate = '".$_POST['EndDate']."', Price = '".$_POST['Price']."', JobAddress1 = '".$_POST['JobAddress1']."',
                JobAddress2 = '".$_POST['JobAddress2']."', JobPostcode = '".$_POST['JobPostcode']."',
                Description = '".$_POST['Description']."', Materials = '".$_POST['Materials']."', paid = '".$_POST['paid']."', cid = '".$_POST['cid']."' WHERE JobID = $JobID ";

    if ( !$result = mysql_query($sql) ) {
        abort_script('Database error: ' . mysql_error(), '');
    }
    header("location: jobadmin.php?JobID=$JobID");


}



function new_record() {//saves as new record
    db_connect();
    $sql = "INSERT INTO Job
            (JobID, StartDate, EndDate, Price, JobAddress1, JobAddress2, JobPostcode,
             Description, Materials, paid, cid)
            VALUES
            (NULL, '".$_POST['StartDate']."', '".$_POST['EndDate']."',  '".$_POST['Price']."',  '".$_POST['JobAddress1']."', '".$_POST['JobAddress2']."',
             '".$_POST['JobPostcode']."','".$_POST['Description']."','".$_POST['Materials']."', '".$_POST['paid']."','".$_POST['cid']."')";

 if ( !$result = mysql_query($sql) ) {
        abort_script('Database error: ' . mysql_error(), '');
    }
    $sql = "SELECT LAST_INSERT_ID()";
    if ( !$result = mysql_query($sql) ) {
        abort_script('Database error: ' . mysql_error(), '');
    }
    $last_id = mysql_fetch_array($result);
    header("location: jobadmin.php?JobID=$last_id[0]");

}

function confirm_delete($JobID, $del) {//delete record
    if ( isset($del) && is_numeric($del) ) {
        $del = intval($del);
        if ( $del === 1 ) {
            db_connect();
            $sql = "DELETE FROM Job WHERE JobID = $JobID LIMIT 1";
            if ( !$result = mysql_query($sql) ) {
                abort_script('Database error: ' . mysql_error(), '');
            } else {
                $sql = "OPTIMIZE TABLE `Job`";
                if ( !$result = mysql_query($sql) ) {
                    abort_script('Database error: ' . mysql_error(), '');
                }
                header("location: jobs.php");
            }
        } else {
            header("location: jobadmin.php?JobID=$JobID");
            die();
        }
    }
    send_header();
    echo "
<p>
<h3>Please confirm you wish to remove this record? [
 <a href=\"jobadmin.php?JobID=$JobID&op=3&del=1\">yes</a> |
 <a href=\"jobadmin.php?JobID=$JobID\">no</a> ]</h3>";
    send_footer();

}



function sync_records() {//see above for sync definition
    db_connect();
    // Load all records into an array for temp storage
    $sql = "SELECT * FROM Job ORDER BY JobID";
    if ( !$result = mysql_query($sql) ) {
        abort_script('Database error: ' . mysql_error(), '');
    } else {
        $numrows = mysql_num_rows($result);
    }
    if ( $numrows > 0 ) {
        WHILE ($row = mysql_fetch_array($result)) {
            $t_row[] = $row;
        }
        $sql = "TRUNCATE TABLE Job";
        if ( !$result = mysql_query($sql) ) {
            abort_script('Database error: ' . mysql_error(), '');
        }
        for ($x=0 ; $x < count($t_row) ; $x++) {
            $sql = "INSERT INTO Job
                    (JobID, StartDate, EndDate, JobAddress1, JobAddress2, JobPostcode,
                     Price, Description, Materials, paid, cid)
                    VALUES
                    (NULL, '".$t_row[$x]['StartDate']."', '".$t_row[$x]['EndDate']."', '".$t_row[$x]['JobAddress1']."', '".$t_row[$x]['JobAddress2']."',
                     '".$t_row[$x]['JobPostcode']."', '".$t_row[$x]['Price']."', '".$t_row[$x]['Descripiton']."', '".$t_row[$x]['Materials']."', '".$t_row[$x]['paid']."', '".$t_row[$x]['cid']."')
                   ";
            if ( !$result = mysql_query($sql) ) {
                abort_script('Database error: ' . mysql_error(), '');
            }
        }
        header("location: jobs.php");
    } else {
        header("location: jobs.php");
    }

}

function next_record($JobID, $move) {
// Figure out move up or down
if ( isset($move) && is_numeric($move) ) {
	$move = intval($move);
	if ( $move === 1 ) {
		$move = '>';
		$direction = 'ASC';
	} elseif ( $move === 2 ) {
		$move = '<';
		$direction = 'DESC';
	} else {
		header("location: jobadmin.php?JobID=$JobID");
		die();
	}
	// We have a confirmed direction, let's get the next record in that direction
	db_connect();
	$sql = "SELECT JobID FROM Job
                        WHERE JobID $move '$JobID'
                        ORDER BY JobID $direction
                        LIMIT 1";
	if ( !$result = mysql_query($sql) ) {
		abort_script('Database error: ' . mysql_error(), '');
	}
	if ( mysql_num_rows($result) === 1 ) {
		list($JobID) = mysql_fetch_row($result);
		header("location: jobadmin.php?JobID=$JobID");
		die();
	} else {
		header("location: jobadmin.php?JobID=$JobID");
		die();
	}
}
}


function send_header() {
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>

<script language="javascript"  type="text/javascript"  src="datetimepicker.js">

</script>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Individual Record Editor</title>


<style type="text/css">

a.button, a.buttonDis {
display: block;
background-color: transparent;
background-image: url(buttonBackground.gif);
background-repeat: no-repeat;
width: 132px;
height: 28px;
margin: 5px auto;
padding: 5px 0 0 0;
text-align: center;
font-family: Arial, Helvetica, sans-serif;
font-size: 100%;
font-weight: bold;
text-decoration: none;
}
a.button:link, a.button:visited {
color: #002577;
}
a.button:hover, a.button:active {
background-position: 0 -36px;
color: #FF7200;
}
a.buttonDis:link, a.buttonDis:visited, a.buttonDis:hover, a.buttonDis:active {
background-position: 0 -72px;
color: #5F5F5F;
cursor: default;
}
.icon {
display: inline-block;
background-repeat: no-repeat;
padding: 0 0 5px 18px;
}
a.button:hover .icon, a.button:active .icon {
background-position: 0 -28px;
}
a.buttonDis:link .icon, a.buttonDis:visited .icon, a.buttonDis:hover .icon, a.buttonDis:active .icon {
background-position: 0 -56px;
}
/* list of button icons */
#buttonOK .icon {
background-image: url(ok.gif);
}
#buttonCancel .icon {
background-image: url(cancel.gif);
}
#buttonImport .icon {
background-image: url(import.gif);
}
</style>

</head>

<body>



<?php

}

function send_footer() {
    echo "
</body>
</html>";

}

function abort_script($msg, $msg2) {
    send_header();
    echo "
<p>
<h2>$msg</h2></p>$msg2";
    send_footer();
    die();

}

?>

PHP_PhREEEk · November 22, 2007

Ok, well first off, you are creating this drop-down OUTSIDE of the form.

<?php
</form>";   

$query = mysql_query("SELECT 
cid,first_name,surname FROM customer 
ORDER BY surname ASC") or                                     
        die (mysql_error());
   
    echo "<select name='customer'>\n";
   
    while ($data = mysql_fetch_array($query, MYSQL_ASSOC))
    {
        echo "<option 
value='{$data['cid']}'6>{$data['first_name']},
        {$data['surname']}</option>\n";
    }
   
    echo "</select>\n";
?>

so, you need to get this drop-down into the table, and within the form. You can create a new row, or a new table. Since we are using $row already, we will will make a new query BEFORE building the form, build a string with the drop down HTML in it, then echo it out when needed.

<?php
// We want exactly one record
if ( $numrows === 1 ) {
    send_header();
    $row = mysql_fetch_array($result);
    $sql = "SELECT `cid`, `first_name`, `surname` FROM `customer`
            ORDER BY `surname`
            ASC
           ";
    if ( !$result = mysql_query($sql) ) {
        abort_script('Database error: ' . mysql_error(), '');
    } else {
        $customer_select = "            <select name=\"customer\">\n";
        while( $data = mysql_fetch_array($result) ) {
             $customer_select .= "                <option value=\"{$data['cid']}\">{$data['first_name']}, {$data['surname']}</option>\n";
        }
        $customer_select .= "            </select>\n";
    }
    // $row contains our Job record, and now $customer_select has our drop-down with cid's as values. We can now echo the HTML.
    echo"
<p>
<form name=\"record_mod\" method=\"post\" action=\"jobadmin.php\">
<table border=\"1\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
  <tr>
    <td>
      <table border=\"0\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
        <tr>
          <td align=\"left\">JobID: {$row['JobID']}</td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"0\" cellpadding=\"4\">
        <tr>
          <td width=\"75\" align=\"right\">Start Date:</td>
          <td colspan=\"3\">
          <input id=\"demo3\" type=\"text\" name=\"StartDate\" size=\"25\" value=\"{$row['StartDate']}\">
          <a href=\"javascript:NewCal('demo3','ddmmyyyy')\"><img src=\"cal.gif\" width=\"16\" height=\"16\" border=\"0\"></a>
        </tr>
        <tr>
          <td width=\"75\" align=\"right\">End Date:</td>
          <td>
            <input id=\"demo4\" type=\"text\" name=\"EndDate\" size=\"25\" value=\"{$row['EndDate']}\">
            <a  href=\"javascript:NewCal('demo4','ddmmyyyy')\"><img  src=\"cal.gif\" width=\"16\"  height=\"16\" border=\"0\"></a>
          </td>
        </tr>
        <tr>
          <td>Job Address 1:</td>
          <td><input name=\"JobAddress1\" type=\"text\" size=\"30\" maxlength=\"20\" value=\"{$row['JobAddress1']}\" /></td>
          <td>Job Address 2:</td>
          <td><input name=\"JobAddress2\" type=\"text\" size=\"30\" maxlength=\"20\" value=\"{$row['JobAddress2']}\" /></td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"0\" cellpadding=\"4\">
        <tr>
          <td width=\"75\" align=\"right\">Postcode:</td>
          <td><input name=\"JobPostcode\" type=\"text\" size=\"40\" maxlength=\"25\" value=\"{$row['JobPostcode']}\" /></td>
        </tr>
        <tr>
          <td width=\"75\" align=\"right\">Price :</td>
          <td><input name=\"Price\" type=\"text\" size=\"40\" maxlength=\"25\" value=\"{$row['Price']}\" /></td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"0\" cellpadding=\"4\">
        <tr>
          <td width=\"75\" align=\"right\">Description:</td>
          <td><input name=\"Description\" type=\"text\" size=\"20\" maxlength=\"25\" value=\"{$row['Description']}\" /></td>
          <td>Materials:</td>
          <td><input name=\"Materials\" type=\"text\" size=\"20\" maxlength=\"25\" value=\"{$row['Materials']}\" /></td>
          <td>Customer Id:</td> 
          <td><input name=\"cid\" type=\"text\" size=\"20\" maxlength=\"25\" value=\"{$row['cid']}\" /></td>
        </tr>
        <tr>
          <td>First Name:</td> 
          <td><input name=\"first_name\" type=\"text\" size=\"20\" maxlength=\"25\" value=\"{$row['first_name']}\" /></td></tr><td>surname:</td> 
          <td><input name=\"surname\" type=\"text\" size=\"20\" maxlength=\"25\" value=\"{$row['surname']}\" /></td>
          <td>Paid<input name =\"paid\" type=\"checkbox\" value=\"{$row['paid']}\" /></td>
        </tr>
      </table>
      <hr align=\"left\" width=\"650\">
      <table border=\"0\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
        <tr>
          <td align=\"left\">Process</td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"2\" cellpadding=\"4\">
        <tr>
          <td><input name=\"op\" type=\"radio\" value=\"1\" /> Update Record</td>
          <td><input name=\"op\" type=\"radio\" value=\"2\" /> New Record</td>
          <td><input name=\"op\" type=\"radio\" value=\"3\" /> DELETE Record</td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"2\" cellpadding=\"4\">
        <tr>
          <td colspan=\"3\"><a href=\"#\" onclick=\"record_mod.submit()\" class=\"button\"><span class=\"icon\"> Submit </span><a></td>
          <td><a href=\"jobs.php\" class=\"button\" span class=\"icon\"> Records List </span></a></td>      
          <td><a href=\"jobadmin.php?JobID=0&op=4\" class=\"button\" span class=\"icon\"> Synchronize</span></a></td>
          <td style=\"padding-left: 50px;\"></td>
          <td><a href=\"jobadmin.php?JobID=$JobID&op=5&move=2\" class=\"button\" span class=\"icon\">Previous</span></a></td><td>Navigate</td>
          <td><a href=\"jobadmin.php?JobID=$JobID&op=5&move=1\" class=\"button\" span class=\"icon\">Next</span></a></td>
          <td><a href=\"invoice.php?JobID={$row['JobID']}\" class=\"button\" span class=\"icon\"> Create Invoice </span></a></td> 
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"2\" cellpadding=\"4\">
        <tr>
          <td>
            $customer_select</td>
        </tr>
      </table>
    </td>
  </tr>
</table></p>
<input name=\"JobID\" type=\"hidden\" value=\"$JobID\" />
</form>";
?>

Ok, so now you should be able to select a customer from the drop-down, then the variable $_POST['customer'] should contain the cid you want to work with. I'm unsure what you want it to do with that variable, so you'll have to let me know what you expect to happen (if you can't figure it out on your own).

Couple of notes:

First, the table layout was missing a lot of tags. I tried to repair it as best as possible, but in the end, I'm only guessing as to how you wanted it to 'look'. You may have to adjust it a bit...

Secondly, you have added a bunch of javascript, images, and an entirely different database table, so I obviously cannot 'run' the script. Therefore, as mentioned, I can only 'guess' what it looks like, and there may be a parse error, or two, or three embedded in the re-write. Did the best I could with what's there...

PhREEEk

nealios · November 27, 2007

Hi Phreeek,

Thanks for your help the other day, ive had a play around with it trying to do what i want it to do. But i was unable to get it going as i get the following error.

Cannot add or update a child row: a foreign key constraint fails

I understand what the error is saying but not entirely sure why its happening. I have created a jobadmin page based on the customer one you created for me. The idea of the page is to manage jobs within in the database and it will also link to my other tables such as each job will have a customer a plumbing engineer etc.

Using primary and foreign keys i was able to link a customer to a job by using cid the primary key in the customer table and making a cid foreign key in the Job table that references the customer table.

At the moment it works and displays the customers first name and surname where the cid in the job table corresponds to the cid in the customer table. It updates fine if i change the number and remains there next time i go into the record.

But to achieve this I have to know and enter the cid when creating a new job record. I wanted to implement the drop down box to choose the customer by name each time i wanted to add a new job rather than finding there cid in the table then going back and entering it.

I cant get the drop down box to interact with the db as expected as i get the error. Can you help?

<?php

$sql_server = 'localhost'; // You can usually leave this alone
$sql_port = '8889';
$sql_dbname = 'freek'; // Put whatever your database name is (use lower case)
$sql_user   = 'root'; // MySQL User
$sql_pass   = 'root'; // MySQL User password

// To create a new record, load any record by clicking the CID field, and it
// will be loaded into the record editor. Edit the fields with the new
// information, and then select NEW. After adding and deleting many records,
// there will exist gaps in the CustomerID (CID) numerical order. Selecting
// SYNCHRONIZE from the record editor will re-number all records, fixing
// any gaps. The Database is auto-OPTIMIZED every time a record is deleted.


// Pack database variables
$db = array("name"   => $sql_dbname,
            "user"   => $sql_user,
            "pass"   => $sql_pass,
            "server" => $sql_server,
            "port"   => $sql_port
           );
	   
// need a record number, or this script won't run
if( isset($_GET['JobID']) && is_numeric($_GET['JobID']) ) {
    $JobID = intval($_GET['JobID']);
} elseif ( isset($_POST['JobID']) && is_numeric($_POST['JobID']) ) {
    $JobID = intval($_POST['JobID']);
} else {
    abort_script('You cannot access this script directly...', '');
}

// Is there an action requested, or just display the record?
if( isset($_POST['op']) && is_numeric($_POST['op']) ) {
    $op = intval($_POST['op']);
} elseif ( isset($_GET['op']) && is_numeric($_GET['op']) ){
    $op = intval($_GET['op']);////////eh
} else {
    unset($op);
}
if ( $op ) {	
switch( $op ) {
	case 1:
		modify_record($JobID);
		break;
	case 2:
		new_record();
		break;
	case 3:
		confirm_delete($JobID, $_GET['del']);
		die();
		break;
	case 4:
		sync_records();
		break;
	case 5:
		next_record($JobID, $_GET['move']);
		break;
	default:
		abort_script('Unkown error - this shouldn\'t happen...', '');
}
}



// Test our database connection
db_connect();
if ( !$result = mysql_query("SELECT * FROM customer, Job WHERE JobID = $JobID AND customer.cid = Job.cid") ) {
    abort_script('Database error: ' . mysql_error(), '');
} else {
    $numrows = mysql_num_rows($result);
}



// We want exactly one record
if ( $numrows === 1 ) {
    send_header();
    $row = mysql_fetch_array($result);
    $sql = "SELECT `cid`, `first_name`, `surname` FROM `customer`
            ORDER BY `surname`
            ASC
           ";
    if ( !$result = mysql_query($sql) ) {
        abort_script('Database error: ' . mysql_error(), '');
    } else {
        $customer_select = "            <select name=\"customer\">\n";
        while( $data = mysql_fetch_array($result) ) {
             $customer_select .= "                <option value=\"{$data['cid']}\">{$data['cid']} {$data['first_name']} {$data['surname']}</option>\n";
        }
        $customer_select .= "            </select>\n";
    }
    // $row contains our Job record, and now $customer_select has our drop-down with cid's as values. We can now echo the HTML.
    echo"
<p>
<form name=\"record_mod\" method=\"post\" action=\"jobadmin.php\">
<table border=\"1\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
  <tr>
    <td>
      <table border=\"0\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
        <tr>
          <td align=\"left\">JobID: {$row['JobID']}</td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"0\" cellpadding=\"4\">
        <tr>
          <td width=\"75\" align=\"right\">Start Date:</td>
          <td colspan=\"3\">
          <input id=\"demo3\" type=\"text\" name=\"StartDate\" size=\"25\" value=\"{$row['StartDate']}\">
          <a href=\"javascript:NewCal('demo3','ddmmyyyy')\"><img src=\"cal.gif\" width=\"16\" height=\"16\" border=\"0\"></a>
        </tr>
        <tr>
          <td width=\"75\" align=\"right\">End Date:</td>
          <td>
            <input id=\"demo4\" type=\"text\" name=\"EndDate\" size=\"25\" value=\"{$row['EndDate']}\">
            <a  href=\"javascript:NewCal('demo4','ddmmyyyy')\"><img  src=\"cal.gif\" width=\"16\"  height=\"16\" border=\"0\"></a>
          </td>
        </tr>
        <tr>
          <td>Job Address 1:</td>
          <td><input name=\"JobAddress1\" type=\"text\" size=\"30\" maxlength=\"20\" value=\"{$row['JobAddress1']}\" /></td>
          <td>Job Address 2:</td>
          <td><input name=\"JobAddress2\" type=\"text\" size=\"30\" maxlength=\"20\" value=\"{$row['JobAddress2']}\" /></td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"0\" cellpadding=\"4\">
        <tr>
          <td width=\"75\" align=\"right\">Postcode:</td>
          <td><input name=\"JobPostcode\" type=\"text\" size=\"40\" maxlength=\"25\" value=\"{$row['JobPostcode']}\" /></td>
        </tr>
        <tr>
          <td width=\"75\" align=\"right\">Price :</td>
          <td><input name=\"Price\" type=\"text\" size=\"40\" maxlength=\"25\" value=\"{$row['Price']}\" /></td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"0\" cellpadding=\"4\">
        <tr>
          <td width=\"75\" align=\"right\">Description:</td>
          <td><input name=\"Description\" type=\"text\" size=\"20\" maxlength=\"25\" value=\"{$row['Description']}\" /></td>
          <td>Materials:</td>
          <td><input name=\"Materials\" type=\"text\" size=\"20\" maxlength=\"25\" value=\"{$row['Materials']}\" /></td>
          <td>Customer Id:</td> 
          <td><input name=\"cid\" type=\"text\" size=\"20\" maxlength=\"25\" value=\"{$row['cid']}\" /></td>
        </tr>
        <tr>
          <td>First Name:</td> 
          <td><input name=\"first_name\" type=\"text\" size=\"20\" maxlength=\"25\" value=\"{$row['first_name']}\" /></td></tr><td>Surname:</td> 
          <td><input name=\"surname\" type=\"text\" size=\"20\" maxlength=\"25\" value=\"{$row['surname']}\" /></td>

          <td>Paid<input name =\"paid\" type=\"checkbox\" value=\"{$row['paid']}\" /></td>
	  <td>View ID:$customer_select</td>
        </tr>
      </table>
      <hr align=\"left\" width=\"650\">
      <table border=\"0\" width=\"750\" cellspacing=\"0\" cellpadding=\"0\">
        <tr>
          <td align=\"left\">Process</td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"2\" cellpadding=\"4\">
        <tr>
          <td><input name=\"op\" type=\"radio\" value=\"1\" /> Update Record</td>
          <td><input name=\"op\" type=\"radio\" value=\"2\" /> New Record</td>
          <td><input name=\"op\" type=\"radio\" value=\"3\" /> Delete Record</td>
        </tr>
      </table>
      <table border=\"0\" cellspacing=\"2\" cellpadding=\"4\">
        <tr>
          <td colspan=\"3\"><a href=\"#\" onclick=\"record_mod.submit()\" class=\"button\"><span class=\"icon\">Submit </span><a></td>
          <td><a href=\"jobs.php\" class=\"button\" span class=\"icon\"> Records List </span></a></td>      
          <td><a href=\"jobadmin.php?JobID=0&op=4\" class=\"button\" span class=\"icon\"> Synchronize</span></a></td>
          <td style=\"padding-left: 50px;\"></td>
          <td><a href=\"jobadmin.php?JobID=$JobID&op=5&move=2\" class=\"button\" span class=\"icon\">Previous</span></a></td><td>Navigate</td>
          <td><a href=\"jobadmin.php?JobID=$JobID&op=5&move=1\" class=\"button\" span class=\"icon\">Next</span></a></td>
          <td><a href=\"invoice.php?JobID={$row['JobID']}\" class=\"button\" span class=\"icon\"> Create Invoice </span></a></td> 
        </tr>
      </table>
    </td>
  </tr>
</table></p>
<input name=\"JobID\" type=\"hidden\" value=\"$JobID\" />
</form>";




//if (isset($_POST['paid']))
//{
//$querypaid = "UPDATE `Job` SET `paid` = 'y' WHERE `JobID` = " . $JobID;
//$querypaid = mysql_query($querypaid);
//}

  


send_footer();
} else {
    // We didn't get one record - sorry!
    $msg2 = "<p>
--> <a href=\"jobs.php\">Click here to return to viewing records</a> <--</p>";
    abort_script('No such record found in database!', $msg2);
}













// FUNCTION LIBRARY //
                                
function db_connect() {
    global $db;
    if ( !empty($db['port']) ) {
        $db['server'] .= ':' . $db['port'];
    }
    @$con = mysql_connect($db['server'], $db['user'], $db['pass']);
    if (!$con) {
        abort_script('Could not connect: ' . mysql_error(), '');
    }
    mysql_select_db($db['name'], $con);

}

function modify_record($JobID) {//enables you to edit and save the current record
    db_connect();
    $sql = "UPDATE Job
            SET StartDate = '".$_POST['StartDate']."', EndDate = '".$_POST['EndDate']."', Price = '".$_POST['Price']."', JobAddress1 = '".$_POST['JobAddress1']."',
                JobAddress2 = '".$_POST['JobAddress2']."', JobPostcode = '".$_POST['JobPostcode']."',
                Description = '".$_POST['Description']."', Materials = '".$_POST['Materials']."', paid = '".$_POST['paid']."', cid = '".$_POST['cid']."' WHERE JobID = $JobID ";

    if ( !$result = mysql_query($sql) ) {
        abort_script('Database error: ' . mysql_error(), '');
    }
    header("location: jobadmin.php?JobID=$JobID");


}



function new_record() {//saves as new record
    db_connect();
    $sql = "INSERT INTO Job
            (JobID, StartDate, EndDate, Price, JobAddress1, JobAddress2, JobPostcode,
             Description, Materials, paid, cid)
            VALUES
            (NULL, '".$_POST['StartDate']."', '".$_POST['EndDate']."',  '".$_POST['Price']."',  '".$_POST['JobAddress1']."', '".$_POST['JobAddress2']."',
             '".$_POST['JobPostcode']."','".$_POST['Description']."','".$_POST['Materials']."', '".$_POST['paid']."','".$_POST['cid']."')";

 if ( !$result = mysql_query($sql) ) {
        abort_script('Database error: ' . mysql_error(), '');
    }
    $sql = "SELECT LAST_INSERT_ID()";
    if ( !$result = mysql_query($sql) ) {
        abort_script('Database error: ' . mysql_error(), '');
    }
    $last_id = mysql_fetch_array($result);
    header("location: jobadmin.php?JobID=$last_id[0]");

}

function confirm_delete($JobID, $del) {//delete record
    if ( isset($del) && is_numeric($del) ) {
        $del = intval($del);
        if ( $del === 1 ) {
            db_connect();
            $sql = "DELETE FROM Job WHERE JobID = $JobID LIMIT 1";
            if ( !$result = mysql_query($sql) ) {
                abort_script('Database error: ' . mysql_error(), '');
            } else {
                $sql = "OPTIMIZE TABLE `Job`";
                if ( !$result = mysql_query($sql) ) {
                    abort_script('Database error: ' . mysql_error(), '');
                }
                header("location: jobs.php");
            }
        } else {
            header("location: jobadmin.php?JobID=$JobID");
            die();
        }
    }
    send_header();
    echo "
<p>
<h3>Please confirm you wish to remove this record? [
 <a href=\"jobadmin.php?JobID=$JobID&op=3&del=1\">yes</a> |
 <a href=\"jobadmin.php?JobID=$JobID\">no</a> ]</h3>";
    send_footer();

}



function sync_records() {//see above for sync definition
    db_connect();
    // Load all records into an array for temp storage
    $sql = "SELECT * FROM Job ORDER BY JobID";
    if ( !$result = mysql_query($sql) ) {
        abort_script('Database error: ' . mysql_error(), '');
    } else {
        $numrows = mysql_num_rows($result);
    }
    if ( $numrows > 0 ) {
        WHILE ($row = mysql_fetch_array($result)) {
            $t_row[] = $row;
        }
        $sql = "TRUNCATE TABLE Job";
        if ( !$result = mysql_query($sql) ) {
            abort_script('Database error: ' . mysql_error(), '');
        }
        for ($x=0 ; $x < count($t_row) ; $x++) {
            $sql = "INSERT INTO Job
                    (JobID, StartDate, EndDate, JobAddress1, JobAddress2, JobPostcode,
                     Price, Description, Materials, paid, cid)
                    VALUES
                    (NULL, '".$t_row[$x]['StartDate']."', '".$t_row[$x]['EndDate']."', '".$t_row[$x]['JobAddress1']."', '".$t_row[$x]['JobAddress2']."',
                     '".$t_row[$x]['JobPostcode']."', '".$t_row[$x]['Price']."', '".$t_row[$x]['Descripiton']."', '".$t_row[$x]['Materials']."', '".$t_row[$x]['paid']."', '".$t_row[$x]['cid']."')
                   ";
            if ( !$result = mysql_query($sql) ) {
                abort_script('Database error: ' . mysql_error(), '');
            }
        }
        header("location: jobs.php");
    } else {
        header("location: jobs.php");
    }

}

function next_record($JobID, $move) {
// Figure out move up or down
if ( isset($move) && is_numeric($move) ) {
	$move = intval($move);
	if ( $move === 1 ) {
		$move = '>';
		$direction = 'ASC';
	} elseif ( $move === 2 ) {
		$move = '<';
		$direction = 'DESC';
	} else {
		header("location: jobadmin.php?JobID=$JobID");
		die();
	}
	// We have a confirmed direction, let's get the next record in that direction
	db_connect();
	$sql = "SELECT JobID FROM Job
                        WHERE JobID $move '$JobID'
                        ORDER BY JobID $direction
                        LIMIT 1";
	if ( !$result = mysql_query($sql) ) {
		abort_script('Database error: ' . mysql_error(), '');
	}
	if ( mysql_num_rows($result) === 1 ) {
		list($JobID) = mysql_fetch_row($result);
		header("location: jobadmin.php?JobID=$JobID");
		die();
	} else {
		header("location: jobadmin.php?JobID=$JobID");
		die();
	}
}
}


function send_header() {
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>

<script language="javascript"  type="text/javascript"  src="datetimepicker.js">

</script>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Individual Record Editor</title>


<style type="text/css">

a.button, a.buttonDis {
display: block;
background-color: transparent;
background-image: url(buttonBackground.gif);
background-repeat: no-repeat;
width: 132px;
height: 28px;
margin: 5px auto;
padding: 5px 0 0 0;
text-align: center;
font-family: Arial, Helvetica, sans-serif;
font-size: 100%;
font-weight: bold;
text-decoration: none;
}
a.button:link, a.button:visited {
color: #002577;
}
a.button:hover, a.button:active {
background-position: 0 -36px;
color: #FF7200;
}
a.buttonDis:link, a.buttonDis:visited, a.buttonDis:hover, a.buttonDis:active {
background-position: 0 -72px;
color: #5F5F5F;
cursor: default;
}
.icon {
display: inline-block;
background-repeat: no-repeat;
padding: 0 0 5px 18px;
}
a.button:hover .icon, a.button:active .icon {
background-position: 0 -28px;
}
a.buttonDis:link .icon, a.buttonDis:visited .icon, a.buttonDis:hover .icon, a.buttonDis:active .icon {
background-position: 0 -56px;
}
/* list of button icons */
#buttonOK .icon {
background-image: url(ok.gif);
}
#buttonCancel .icon {
background-image: url(cancel.gif);
}
#buttonImport .icon {
background-image: url(import.gif);
}
</style>

</head>

<body>



<?php

}

function send_footer() {
    echo "
</body>
</html>";

}

function abort_script($msg, $msg2) {
    send_header();
    echo "
<p>
<h2>$msg</h2></p>$msg2";
    send_footer();
    die();

}

?>

Here is the current set up of the customer and job tables.

-- phpMyAdmin SQL Dump
-- version 2.7.0-pl2
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Nov 27, 2007 at 04:38 PM
-- Server version: 5.0.19
-- PHP Version: 5.1.6
-- 
-- Database: `freek`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `Job`
-- 

CREATE TABLE `Job` (
  `JobID` int(20) NOT NULL auto_increment,
  `StartDate` varchar(10) default NULL,
  `EndDate` varchar(10) default NULL,
  `Price` decimal(7,2) default NULL,
  `JobAddress1` varchar(25) default NULL,
  `JobAddress2` varchar(25) default NULL,
  `JobPostcode` varchar( default NULL,
  `Description` varchar(50) default NULL,
  `Materials` varchar(50) default NULL,
  `cid` int(20) NOT NULL,
  `paid` tinyint(1) default NULL,
  PRIMARY KEY  (`JobID`),
  KEY `cid` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;

-- 
-- Dumping data for table `Job`
-- 

INSERT INTO `Job` VALUES (1, '1-11-2007', '6-11-2007', 50.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', 'hello', 'jikednwn', 4, 0);
INSERT INTO `Job` VALUES (2, '22-11-2007', '30-11-2007', 500.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', '', 'jikednwn', 3, 0);
INSERT INTO `Job` VALUES (3, '1-11-2007', '6-11-2007', 50.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', '', 'jikednwn', 1, 0);
INSERT INTO `Job` VALUES (4, '1-11-2007', '6-11-2007', 50.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', '', 'jikednwn', 1, 0);
INSERT INTO `Job` VALUES (5, '1-11-2007', '6-11-2007', 50.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', '', 'jikednwn', 1, 0);
INSERT INTO `Job` VALUES (6, '1-11-2007', '6-11-2007', 50.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', '', 'jikednwn', 1, 0);
INSERT INTO `Job` VALUES (7, '22-11-2007', '30-11-2007', 500.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', '', 'jikednwn', 1, 0);
INSERT INTO `Job` VALUES (8, '22-11-2007', '30-11-2007', 500.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', '', 'jikednwn', 1, 0);
INSERT INTO `Job` VALUES (9, '22-11-2007', '30-11-2007', 500.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', '', 'jikednwn', 1, 0);
INSERT INTO `Job` VALUES (10, '1-11-2007', '6-11-2007', 50.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', '', 'jikednwn', 2, 0);
INSERT INTO `Job` VALUES (11, '1-11-2007', '6-11-2007', 50.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', '', 'jilost', 2, 0);
INSERT INTO `Job` VALUES (12, '1-11-2007', '6-11-2007', 50.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', '', 'jilost', 2, 0);
INSERT INTO `Job` VALUES (13, '1-11-2007', '6-11-2007', 50.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', '', 'jilost', 2, 0);
INSERT INTO `Job` VALUES (14, '1-11-2007', '6-11-2007', 50.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', '', 'jilost', 2, 0);
INSERT INTO `Job` VALUES (15, '1-11-2007', '6-11-2007', 50.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', '', 'jilost', 2, 0);
INSERT INTO `Job` VALUES (16, '1-11-2007', '6-11-2007', 50.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', '', 'jilost', 2, 0);
INSERT INTO `Job` VALUES (17, '1-11-2007', '6-11-2007', 50.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', '', 'jilost', 2, 0);
INSERT INTO `Job` VALUES (18, '1-11-2007', '6-11-2007', 50.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', '', 'jikednwn', 1, 0);
INSERT INTO `Job` VALUES (19, '1-11-2007', '6-11-2007', 50.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', '', 'jikednwn', 1, 0);
INSERT INTO `Job` VALUES (20, '1-11-2007', '6-11-2007', 50.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', '', 'jikednwn', 1, 0);
INSERT INTO `Job` VALUES (21, '1-11-2007', '6-11-2007', 50.00, '97 Hurst Road', 'jiewdkjs', 'BN21 2PN', '', 'jikednwn', 3, 0);

-- --------------------------------------------------------

-- 
-- Table structure for table `customer`
-- 

CREATE TABLE `customer` (
  `cid` int(20) NOT NULL auto_increment,
  `title` varchar(5) default NULL,
  `first_name` varchar(20) default NULL,
  `surname` varchar(20) default NULL,
  `address1` varchar(25) default NULL,
  `address2` varchar(25) default NULL,
  `town` varchar(25) default NULL,
  `county` varchar(25) default NULL,
  `postcode` varchar( default NULL,
  `telephone` varchar(15) default NULL,
  `mobile` varchar(15) default NULL,
  `email` varchar(35) default NULL,
  `additional_info` varchar(500) default NULL,
  PRIMARY KEY  (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `customer`
-- 

INSERT INTO `customer` VALUES (1, 'Mr', 'Tom', 'Neal', '42 street', 'Townville', 'Eastbourne', 'East Sussex', 'BN22 7HE', '01323657848', '07764646378', 'nealios@hotmail.com', 'Visited Website');
INSERT INTO `customer` VALUES (2, 'Miss', 'Harriet', 'Coombs', 'The house', 'Huntington', 'Eastbourne', 'East Sussex', 'BN21 7ER', '01323949955', '07743929989', 'hcoombs@yahoo.com', 'Phonebook');
INSERT INTO `customer` VALUES (3, 'Mr', 'Adam', 'Black', 'Flat 4', 'Heron Court', 'Bournemouth', 'Dorset', 'BH8 8QA', '01202636628', '07764789778', 'black@jpmorgan.co.uk', 'Visited Website');
INSERT INTO `customer` VALUES (4, 'Mr', 'Tony', 'Wong', '74 Goof Road', 'Old Town', 'Eastbourne', 'East Sussex', 'BN22 7HE', '01323989843', '0778367632', 'tong@hotmail.com', 'Visited Website');

-- 
-- Constraints for dumped tables
-- 

-- 
-- Constraints for table `Job`
-- 
ALTER TABLE `Job`
  ADD CONSTRAINT `Job_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `customer` (`cid`);

nealios · November 30, 2007

Still no luck, been playing around with trying to get it into my db but i cant seem to get it to work!

Sign In

PHP/HTML Form scrolling through records/adding removing data

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information