Webocreation

Wednesday, February 3, 2010

Simple retrieve, amend and update MySQL database

PHP Scripts
Simple retrieve, amend and update MySQL database
1. 1st page to aquire reference ( display all to make it easy)
2. 2nd page display results and allow viewer to change
3. Update the database
amend.php
<html><TD WIDTH="29%" HEIGHT="60"><DIV ALIGN="LEFT"><BR>Input
the Reference, to make sure we have the right one:<BR><BR><FONT SIZE="1">(quick
reference listed below for your convenience)</FONT></DIV>
<form method=POST action="amend1.php">
<DIV ALIGN="LEFT"><INPUT TYPE="text" NAME="record" SIZE="50" MAXLENGTH="50"><BR><BR><IMG SRC="search.jpg" WIDTH="51" HEIGHT="46" ALIGN="ABSMIDDLE" BORDER="1"><INPUT TYPE="submit" NAME="go" VALUE="Search on that Input"></DIV></form></TD></TR><TR><TD WIDTH="29%"><DIV ALIGN="LEFT">
<?php
// Show simple format of the records so person can choose the reference name/number
// this is then passed to the next page, for all details

$db = mysql_connect("db_host", "db_name", "db_password");
mysql_select_db("database",$db) or die ('Unable to connect to database');
$q="SELECT * FROM table ORDER BY reference ASC";
$result = mysql_query( $q, $db )
or die(" - Failed More Information:<br><pre>$q</pre><br>Error: " . mysql_error());
$num_rows = mysql_num_rows($result);
if ($myrow = mysql_fetch_array($result)) {
echo "<br>A Quick View<BR><br>";
echo "<table border=1>\n";
echo "<tr><td><b>Ref:</b></td><td>Description:</td><td>Consultant:</td><td>Thumb:</td></tr>\n";
do {
printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>\n", $myrow["reference"], $myrow["shortdescription"], $myrow["consultant"], $myrow["thumb"]);
} while ($myrow = mysql_fetch_array($result));
echo "</table>\n";
} else {
echo "$ref: That record appears to be unavailable";
}
mysql_free_result($result);
mysql_close($db);
?></DIV></TD></html>
________________________________________
amend1.php
<?PHP
session_start();
?>
<HTML>
<?php
$record = $_POST['record'];
echo "Reference: $record<br><BR>";
$host = "db_host";
$login_name = "db_name";
$password = "db_password";
//Connecting to MYSQL
MySQL_connect("$host","$login_name","$password");
//Select the database we want to use
mysql_select_db("database") or die("Could not find database");
$result=mysql_query(" SELECT * FROM table WHERE reference='$record'");
$num=mysql_num_rows($result);
$i=0;
while ($i < $num) {
// collect all details for our one reference
$description=mysql_result($result,$i,"description");
$text2=mysql_result($result,$i,"text2");
$reference=mysql_result($result,$i,"reference");
$thumb=mysql_result($result,$i,"thumb");
$img1=mysql_result($result,$i,"image1");
$img2=mysql_result($result,$i,"image2");
$img3=mysql_result($result,$i,"image3");

$f='<font face=Verdana,Arial,Helvetica size=2 Color=Blue';
$view='<img src=http://your_website/thumbs/';
echo "<br>$view$thumb.jpg><br><br>";
//next we display only the details we want to allow to be changed in a form object
// the other details that we won't allow to be changed can be echoed to the screen
//note the hidden input line 3 below. We don't need to echo it to the screen
?>
<TABLE WIDTH="100%" CELLPADDING="10" CELLSPACING="0" BORDER="2"> <TR ALIGN="center" VALIGN="top">
<TD ALIGN="center" COLSPAN="1" ROWSPAN="1" BGCOLOR="#F2F2F2">
<FORM ACTION="amend2.php" METHOD="post">
<P ALIGN="LEFT">
<INPUT TYPE="hidden" NAME="ud_id" VALUE="<? echo "$record" ?>">
<BR>Description1:<BR><TEXTAREA NAME="ud_description" COLS="50" ROWS="4">
<? echo "$description"?></TEXTAREA></P><P ALIGN="LEFT">Description:<BR>
<TEXTAREA NAME="ud_text2" COLS="50" ROWS="4"><? echo "$text2"?></TEXTAREA></P><HR><B>
</B><P ALIGN="LEFT">Thumbnail:<BR> <INPUT TYPE="text" NAME="ud_thumb" VALUE="<? echo "$thumb"?>" SIZE="30" MAXLENGTH="50"></P><P ALIGN="LEFT">Image
1 :<BR> <INPUT TYPE="text" NAME="ud_img1" VALUE="<? echo "$img1"?>" SIZE="30" MAXLENGTH="50"></P><P ALIGN="LEFT">Image
2 :<BR> <INPUT TYPE="text" NAME="ud_img2" VALUE="<? echo "$img2"?>" SIZE="30" MAXLENGTH="50"></P><P ALIGN="LEFT">Image
3 :<BR> <INPUT TYPE="text" NAME="ud_img3" VALUE="<? echo "$img3"?>" SIZE="30" MAXLENGTH="50"><BR><BR>
</P><P><INPUT TYPE="Submit" VALUE="Update the Record" NAME="Submit"> </P></FORM></TD></TR></TABLE>
<?
++$i;
}
?>
________________________________________
amend2.php
<?PHP
session_start();
?>
<?php
$ud_id=$_POST['ud_id'];
$ud_description=$_POST['ud_description'];
$ud_text2=$_POST['ud_text2'];
$ud_thumb=$_POST['ud_thumb'];
$ud_img1=$_POST['ud_img1'];
$ud_img2=$_POST['ud_img2'];
$ud_img3=$_POST['ud_img3'];
if ($ud_id == "") echo "! No identifier retrieved";
else
echo "Amending record $ud_id";
//clean up any carriage returns etc
$ud_description = preg_replace("/[\n\r]*/","",$ud_description);
$ud_text2 = preg_replace("/[\n\r]*/","",$ud_text2);
$host = "db_host";
$login_name = "db_name";
$password = "db_password";
//Connecting to MYSQL
MySQL_connect("$host","$login_name","$password");
//Select the database we want to use
mysql_select_db("db_database") or die("Could not select database");
mysql_query(" UPDATE db_table
SET description='$ud_description', text2='$ud_text2', thumb='$ud_thumb', image1='$ud_img1', image2='$ud_img2', image3='$ud_img3', WHERE reference='$ud_id'");
echo "<BR>Record $ud_id <-- Updated<BR><BR>";
?>
<?php
//if you want to check it's ok, display new data
echo "Search on $ud_id<BR>";
$db = mysql_connect("host", "name", "password");
mysql_select_db("db_database",$db) or die ('Unable to connect to database');
$q="SELECT * FROM db_table WHERE reference ='$ud_id'";
$result = mysql_query( $q, $db )
or die(" - Failed More Information:<br><pre>$q</pre><br>Error: " . mysql_error());
$num_rows = mysql_num_rows($result);
if ($myrow = mysql_fetch_array($result)) {
$thumb='<img src=http://your_website/thumbs/';

//if the record leads to a specific page
$web='http://www.your_website/detail/index.php?name=';
echo "<table border=0>\n";
echo "<tr><td></td><td></td><td></td><td></td></tr>\n";

do {
printf("<tr><td>$thumb%s.jpg></td><td>%s</td><td>%s</td><td>%s</td>
</tr>\n", $myrow["thumb"], $myrow["reference"], $myrow["description"], $myrow["text2"]);
} while ($myrow = mysql_fetch_array($result));
echo "</table>\n";
} else {
echo "Sorry, no records were found";
}
mysql_free_result($result);
mysql_close($db);
session_destroy();
?>

No comments:

Post a Comment