i have two tables the following structures.
CREATE TABLE tblbranches (branchID tinyint(3) unsigned NOT NULL AUTO_INCREMENT,branchCode varchar(30) NOT NULL,branchName varchar(30) NOT NULL,regionID tinyint(3) unsigned NOT NULL,branchAddress varchar(400) NOT NULL,cityID smallint(5) unsigned NOT NULL,countryID smallint(3) unsigned NOT NULL,branchContact varchar(50) NOT NULL,branchFax varchar(20) DEFAULT NULL,branchEmail varchar(60) DEFAULT NULL,companyID tinyint(3) unsigned NOT NULL,dateAdded timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,userID smallint(4) unsigned NOT NULL,dateLastChanged date DEFAULT NULL,changedUserID smallint(4) unsigned DEFAULT NULL,
PRIMARY KEY (branchID),
KEY regionID (regionID),
KEY cityID (cityID),
KEY countryID (countryID),
KEY companyID (companyID),
KEY userID (userID),
KEY changedUserID (changedUserID),
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
CREATE TABLE tblphonenumbers (phoneNumberID smallint(3) unsigned NOT NULL AUTO_INCREMENT,phoneNumber varchar(20) NOT NULL,branchID tinyint(3) unsigned NOT NULL,dateAdded timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,userID smallint(4) unsigned NOT NULL,dateLastChanged date DEFAULT NULL,lastChangedUserID smallint(4) unsigned DEFAULT NULL,
PRIMARY KEY (phoneNumberID),
UNIQUE KEY phoneNumber (phoneNumber),
KEY userID (userID),
KEY lastChangedUserID (lastChangedUserID),
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
the two tables are linked via the branchID field found in tblphonenumbers. i'm using a html form with the following code to edit the phone numbers.
<?php
session_start();
/** addphonenumber.php
* this script adds a new city's details to the database
* @author FreeUser
* @copyright 2013
*/
require_once 'includes/mysql_connect.php';
require_once 'includes/functions.php';
if ( isset( $_POST['submitted'] ) ) {
$errors = array();
if ( is_digits( $_POST['txtPhoneNumber'] ) ) {
$phoneNumber = escape_value( $_POST['txtPhoneNumber'] );
} else {
$errors[] = 'Phone number must have numeric values only.';
}
if ( empty( $errors ) ) {
$sql = "SELECT * FROM tblphonenumbers WHERE phoneNumberID != {$_POST['txtPhoneNumberID']} AND phoneNumber ='{$phoneNumber}'";
if ( $result = mysql_query( $sql ) ) {
if ( mysql_num_rows( $result ) == 0 ) {
$sql = "UPDATE tblphonenumbers SET phoneNumber = '{$phoneNumber}', dateLastChanged = curdate(), lastchangedUserID = ";
$sql .= "{$_SESSION['loggedUserID']} WHERE phoneNumberID = {$_POST['txtPhoneNumberID']}";
if ( $result = mysql_query( $sql ) ) {
header( 'Location: phonenumbers.php' );
exit;
} else {
$errors[] = 'Could not update phone number: ' . mysql_error();
}
} else {
$errors[] = 'That phone number alreadys exists on a different record.';
}
}
}
} elseif ( $_REQUEST_METHOD = 'GET' ) {
if ( isset( $_GET['phonenumberid'] ) && is_numeric( $_GET['phonenumberid'] ) ) {
$phoneNumberID = (int)$_GET['phonenumberid'];
$sql = "SELECT phoneNumberID, phoneNumber FROM tblphonenumbers WHERE phoneNumberID = {$phoneNumberID}";
if ( $result = mysql_query( $sql ) ) {
if ( mysql_num_rows( $result ) == 0 ) {
echo '<p>Record not found. Click <a href="index.php">here<a> to go back.</p>';
die();
} elseif ( mysql_num_rows( $result ) == 1 ) {
$row = mysql_fetch_array( $result );
}
}
} elseif ( !isset( $GET['phonenumberid'] ) ) {
echo '<p>You have accessed this page in error. Click <a href="index.php">here</a> to go back.</p>';
die();
}
}
?>
<html>
<head>
<title>Edit Phone Number</title>
<style>
.error {
color: #FF0000;
font-weight: bold;
}
body {
background-color: #EBEBEB;
}
h2 {
font-family: Arial, Helvetica, sans-serif;;
font-size: x-large;
color: blue;
font-weight: bold;
}
h3 {
font-family: Arial, Helvetica, sans-serif;;
font-weight: normal;
font-size: 18px;
}
a {
text-decoration: none;
}
li {
list-style: disc;
}
</style>
</head>
<body>
<h2>Edit Phone Number Details</h2>
<?php
if ( isset( $errors ) && is_array( $errors ) ) {
// there are form submission errors, which must be displayed
echo '<ul>';
foreach ( $errors as $warning ) {
echo "<li class='error'>$warning</li>";
}
echo '</ul>';
}
?>
<form action="" method="post" >
<table cellpadding="3" cellspacing="3">
<tr>
<td><b>Branch:</b></td>
<td>
<select name="branchID">
<option value="">Select branch</option>
<option value="">---------------------</option>
<?php
$sql = "SELECT branchName, branchID FROM tblbranches ORDER BY branchName ASC";
if ( $result = mysql_query( $sql ) ) {
while( $row = mysql_fetch_array( $result ) ) {
if ( isset( $_POST['branchID']) && ( $_POST['branchID'] == $row['branchID'] ) ) {
$selectedBranch = " Selected";
} else {
$selectedBranch = "";
}
?>
<option value="<?php echo $row['branchID']; ?>" <?php echo $selectedBranch; ?> ><?php echo $row['branchName']; ?></option>
<?php
}
}
?>
</select>
</td>
</tr>
<tr>
<td><b>Phone Number:</b></td>
<td><input type="text" name="txtPhoneNumber" id="txtPhoneNumber"
value="<?php
if ( isset( $_POST['txtPhoneNumber'] ) ) {
echo $_POST['txtPhoneNumber'];
} elseif ( isset( $row['phoneNumber'] ) ) {
echo $row['phoneNumber'];
}
?>"/></td>
</tr>
<tr>
<td colspan="2" align="right">
<input type="hidden" name="submitted" />
<input type="hidden" name="txtPhoneNumberID" value="<?php if ( isset( $_GET['phonenumberid'] ) ) echo $_GET['phonenumberid']; ?>">
<input type="submit" value="Save Changes"/>
<input type="button" value="Cancel" onclick="window.location.href='phonenumbers.php'"/></td>
</tr>
</table>
</form>
</body>
</html>
my aim is to have the current branch and phone number selected and displayed when the page initially loads.
