Help with insert and viewing records php scripts
Posted: Tue Jul 11, 2006 7:03 pm
Hi people,
I'm trying to insert record and select/view record respectively, but to no avail. Can anyone help unravel the problems with these scripts below:
addentry.php - insert record script
---------------------------------
selentry.php - php script to select/view a record
----------------------------------------------
mysql database tables:
----------------------
I'm trying to insert record and select/view record respectively, but to no avail. Can anyone help unravel the problems with these scripts below:
addentry.php - insert record script
---------------------------------
Code: Select all
<?php
if ($_POST[op] != "add") {
//haven't seen the form, so show it
$display_block = "<h1>Add an Entry</h1>
<form method=\"post\" action=\"$_SERVER[PHP_SELF]\">
<P><strong>First/Last Names:</strong><br>
<input type=\"text\" name=\"f_name\" size=30 maxlength=75>
<input type=\"text\" name=\"l_name\" size=30 maxlength=75>
<P><strong>Address:</strong><br>
<input type=\"text\" name=\"address\" size=30>
<P><strong>City/State/Zip:</strong><br>
<input type=\"text\" name=\"city\" size=30 maxlength=50>
<input type=\"text\" name=\"state\" size=5 maxlength=2>
<input type=\"text\" name=\"zipcode\" size=10 maxlength=10>
<P><strong>Address Type:</strong><br>
<input type=\"radio\" name=\"add_type\" value=\"home\" checked> home
<input type=\"radio\" name=\"add_type\" value=\"work\"> work
<input type=\"radio\" name=\"add_type\" value=\"other\"> other
<P><strong>Telephone Number:</strong><br>
<input type=\"text\" name=\"tel_number\" size=30 maxlength=25>
<input type=\"radio\" name=\"tel_type\" value=\"home\" checked> home
<input type=\"radio\" name=\"tel_type\" value=\"work\"> work
<input type=\"radio\" name=\"tel_type\" value=\"other\"> other
<P><strong>Fax Number:</strong><br>
<input type=\"text\" name=\"fax_number\" size=30 maxlength=25>
<input type=\"radio\" name=\"fax_type\" value=\"home\" checked> home
<input type=\"radio\" name=\"fax_type\" value=\"work\"> work
<input type=\"radio\" name=\"fax_type\" value=\"other\"> other
<P><strong>Email Address:</strong><br>
<input type=\"text\" name=\"email\" size=30 maxlength=150>
<input type=\"radio\" name=\"email_type\" value=\"home\" checked> home
<input type=\"radio\" name=\"email_type\" value=\"work\"> work
<input type=\"radio\" name=\"email_type\" value=\"other\"> other
<P><strong>Personal Note:</strong><br>
<textarea name=\"note\" cols=35 rows=5 wrap=virtual></textarea>
<input type=\"hidden\" name=\"op\" value=\"add\">
<p><input type=\"submit\" name=\"submit\" value=\"Add Entry\"></p>
</FORM>";
} else if ($_POST[op] == "add") {
//time to add to tables, so check for required fields
if (($_POST[f_name] == "") || ($_POST[l_name] == "")) {
header("Location: addentry.php");
exit;
}
//connect to database
$conn = mysql_connect("localhost", "root", "olu1bal") or die(mysql_error());
mysql_select_db("testDB",$conn) or die(mysql_error());
//add to master_name table
$add_master = "insert into master_name values (null, now(), now(), '$_POST[f_name]', '$_POST[l_name]')";
mysql_query($add_master) or die(mysql_error());
//get master_id for use with other tables
$master_id = mysql_insert_id();
if (($_POST[address]) || ($_POST[city]) || ($_POST[state]) || ($_POST[zipcode])) {
//something relevant, so add to address table
$add_address = "insert into address values (null, $master_id, now(), now(), '$_POST[address]', '$_POST[city]', '$_POST[state]', '$_POST[zipcode]', '$_POST[add_type]')";
mysql_query($add_address) or die(mysql_error());
}
if ($_POST[tel_number]) {
//something relevant, so add to telephone table
$add_tel = "insert into telephone values (null, $master_id, now(), now(), '$_POST[tel_number]', '$_POST[tel_type]')";
mysql_query($add_tel) or die(mysql_error());
}
if ($_POST[fax_number]) {
//something relevant, so add to fax table
$add_fax = "insert into fax values (null, $master_id, now(), now(), '$_POST[fax_number]', '$_POST[fax_type]')";
mysql_query($add_fax) or die(mysql_error());
}
if ($_POST[email]) {
//something relevant, so add to email table
$add_email = "insert into email values (null, $master_id, now(), now(), '$_POST[email]', '$_POST[email_type]')";
mysql_query($add_email) or die(mysql_error());
}
if ($_POST[note]) {
//something relevant, so add to notes table
$add_note = "insert into personal_notes values (null, $master_id, now(), now(), '$_POST[note]')";
mysql_query($add_note) or die(mysql_error());
}
$display_block = "<h1>Entry Added</h1>
<P>Your entry has been added. Would you like to
<a href=\"addentry.php\">add another</a>?</p>";
}
?>
<HTML>
<HEAD>
<TITLE>Add an Entry</TITLE>
</HEAD>
<BODY>
<? print $display_block; ?>
</BODY>
</HTML>----------------------------------------------
Code: Select all
<?php
//connect to database
$conn = mysql_connect("localhost", "root", "olu1bal") or die(mysql_error());
mysql_select_db("testDB",$conn) or die(mysql_error());
if ($_POST[op] != "delete") {
//haven't seen the form, so show it
$display_block = "<h1>Select an Entry</h1>";
//get parts of records
$get_list = "select id, concat_ws(', ', l_name, f_name) as display_name from master_name order by l_name, f_name";
$get_list_res = mysql_query($get_list) or die(mysql_error());
if (mysql_num_rows($get_list_res) < 1) {
//no records
$display_block .= "<p><em>Sorry, no records to select!</em></p>";
} else {
//has records, so get results and print in a form
$display_block .= "
<form method=\"post\" action=\"$_SERVER[PHP_SELF]\">
<P><strong>Select a Record to Delete:</strong><br>
<select name=\"sel_id\">
<option value=\"\">-- Select One --</option>";
while ($recs = mysql_fetch_array($get_list_res)) {
$id = $recs['id'];
$display_name = stripslashes($recs['display_name']);
$display_block .= "<option value=\"$id\">
$display_name</option>";
}
$display_block .= "
</select>
<input type=\"hidden\" name=\"op\" value=\"view\">
<p><input type=\"submit\" name=\"submit\" value=\"View Selected Entry\"></p>
</FORM>";
}
} else if ($_POST[op] == "delete") {
//check for required fields
if ($_POST[sel_id] == "") {
header("Location: selentry.php");
exit;
}
//get master_info
$get_master = "select concat_ws(' ', f_name, l_name) as display_name from master_name where id = $_POST[sel_id]";
$get_master_res = mysql_query($get_master);
$display_name = stripslashes(mysql_result($get_master_res, 0,'display_name'));
$display_block = "<h1>Showing Record for $display_name</h1>";
//get all addresses
$get_addresses = "select address, city, state, zipcode, type from address where master_id = $_POST[sel_id]";
$get_addresses_res = mysql_query($get_addresses);
if (mysql_num_rows($get_addresses_res) > 0) {
$display_block .= "<P><strong>Addresses:</strong><br>
<ul>";
while ($add_info = mysql_fetch_array($get_addresses_res)) {
$address = $add_info[address];
$city = $add_info[city];
$state = $add_info[state];
$zipcode = $add_info[zipcode];
$address_type = $add_info[type];
$display_block .= "<li>$address $city $state $zipcode ($address_type)";
}
$display_block .= "</ul>";
}
//get all tel
$get_tel = "select tel_number, type from telephone where master_id = $_POST[sel_id]";
$get_tel_res = mysql_query($get_tel);
if (mysql_num_rows($get_tel_res) > 0) {
$display_block .= "<P><strong>Telephone:</strong><br>
<ul>";
while ($tel_info = mysql_fetch_array($get_tel_res)) {
$tel_number = $tel_info[tel_number];
$tel_type = $tel_info[type];
$display_block .= "<li>$tel_number ($tel_type)";
}
$display_block .= "</ul>";
}
//get all fax
$get_fax = "select fax_number, type from fax where master_id = $_POST[sel_id]";
$get_fax_res = mysql_query($get_fax);
if (mysql_num_rows($get_fax_res) > 0) {
$display_block .= "<P><strong>Fax:</strong><br>
<ul>";
while ($fax_info = mysql_fetch_array($get_fax_res)) {
$fax_number = $fax_info[fax_number];
$fax_type = $fax_info[type];
$display_block .= "<li>$fax_number ($fax_type)";
}
$display_block .= "</ul>";
}
//get all email
$get_email = "select email, type from email where master_id = $_POST[sel_id]";
$get_email_res = mysql_query($get_email);
if (mysql_num_rows($get_email_res) > 0) {
$display_block .= "<P><strong>Email:</strong><br>
<ul>";
while ($email_info = mysql_fetch_array($get_email_res)) {
$email = $email_info[email];
$email_type = $email_info[type];
$display_block .= "<li>$email ($email_type)";
}
$display_block .= "</ul>";
}
//get personal note
$get_notes = "select note from personal_notes where master_id = $_POST[sel_id]";
$get_notes_res = mysql_query($get_notes);
if (mysql_num_rows($get_notes_res) == 1) {
$note = nl2br(stripslashes(mysql_result($get_notes_res,0,'note')));
$display_block .= "<P><strong>Personal Notes:</strong><br>$note";
}
$display_block .= "<br><br><P align=center><a href=\"$_SERVER[PHP_SELF]\">select another</a></p>";
}
?>
<HTML>
<HEAD>
<TITLE>My Records</TITLE>
</HEAD>
<BODY>
<? print $display_block; ?>
</BODY>
</HTML>----------------------
Code: Select all
create table master_name
(
id int not null primary key auto_increment,
date_added datetime,
date_modified datetime,
f_name varchar(75),
l_name varchar(75)
);Code: Select all
create table address
(
id int not null primary key auto_increment,
master_id int not null,
date_added datetime,
date_modified datetime,
address varchar(255),
city varchar(30),
state char(2),
zipcode varchar(10),
type enum ('home', 'work', 'other')
);
create table telephone
(
id int not null primary key auto_increment,
master_id int not null,
date_added datetime,
date_modified datetime,
tel_number varchar(25),
type enum ('home', 'work', 'other')
);
create table fax
(
id int not null primary key auto_increment,
master_id int not null,
date_added datetime,
date_modified datetime,
fax_number varchar(25),
type enum ('home', 'work', 'other')
);
create table email
(
id int not null primary key auto_increment,
master_id int not null,
date_added datetime,
date_modified datetime,
email varchar(150),
type enum ('home', 'work', 'other')
);
create table personal_notes
(
id int not null primary key auto_increment,
master_id int not null,
date_added datetime,
date_modified datetime,
note text
);