mysql-demo.php

Hi,

Below is a bunch of programs to demo the basic sql commands.
Suggestions on possible improvements will be appreciated.

<?php // mysql-demo.php
include ("connectdb.php");

$setSQL="SET FOREIGN_KEY_CHECKS=0";
$dropSQL="DROP TABLE IF EXISTS temp";
Print "<br>MYSQL DEMO <br> $setSQL <br> $dropSQL<br>";
$createSQL="CREATE TABLE temp (id int(10) NOT NULL AUTO_INCREMENT PRIMARY
KEY,name VARCHAR(20) NULL,address VARCHAR(20) NULL,phone VARCHAR(10)
NULL,pay int(10) DEFAULT '0') ";
$selectSQL="SELECT * FROM temp";
$insert1SQL="INSERT INTO temp(name,address,phone,pay) VALUES('Baskar','75
Cuff Road','90080088','2000') ";
$insert2SQL="INSERT INTO temp(name,address,phone,pay) VALUES('John','123
Temple Street','8765678','1500') ";
$insert3SQL="INSERT INTO temp(name,address,pay) VALUES('Johnny','2 Templar
Street','1000') ";
$update1SQL="UPDATE `temp` SET `name`='Siva',`phone` = NULL , `pay`='3100'
WHERE `id`='2'";
$update2SQL="UPDATE `temp` SET name='Siva', pay='2500' WHERE id='2'";
$deleteSQL=("DELETE FROM temp WHERE id='3'");

mysql_query($setSQL);
$query=mysql_query($dropSQL);
$query=mysql_query($createSQL); showtable($createSQL);
$query=mysql_query($insert1SQL); showtable($insert1SQL);
mysql_query($insert2SQL); showtable($insert2SQL);
mysql_query($insert3SQL); showtable($insert3SQL);
$query=mysql_query($deleteSQL); showtable($deleteSQL);
$query=mysql_query($update1SQL); showtable($update1SQL);
mysql_query($update2SQL); showtable($update2SQL);

function showtable($task)
{ static $n=1;


Print "<br><b>$n. Query= $task </b> <br>"; $selectSQL="SELECT * FROM temp";
$n++;
$result = mysql_query($selectSQL) or die('Select Query failed: ' .
mysql_error());
echo "<table border='2'>";
echo "<tr>\n";
Print "<b> <td> ID </td> <td> NAME </td> <td> ADDRESS </td> <td> PHONE NO.
</td> <td> PAY </td> </b>";
echo "</tr>\n";
while ($line = mysql_fetch_array($result))
{
while (list($a, $b) = each($line)) { $$a = $b; }
{
echo "<tr>\n";
Print "<td> $id </td> <td> $name </td> <td> $address </td> <td>
$phone </td> <td> $pay </td>";
echo "</tr>\n";
}
}
echo "</table> <p>"; }

$query = "SELECT * FROM temp" ;
$result = mysql_query($query) or die(mysql_error());


print("<table align=center border=0 cellpadding=3 cellspacing=3>");
print("<tr>
<th>Edit<hr></th>
<th>NAME<hr></th>
<th>ADDRESS<hr></th>
<th>PHONE NO.<hr></th>
<th>PAY<hr></th>
<th>Delete<hr></th>
</tr>");

while($row = mysql_fetch_array($result))
{
print("<tr>");
printf("<td div=box><a href=edit-disp.php?id=%s>%s</a>" ,$row["id"]
,$row["id"]);
printf("<td>%s</td>", $row["name"]);
printf("<td>%s</td>", $row["address"]);
printf("<td>%s</td>",$row["phone"] );
printf("<td>%s</td>", $row["pay"]);
printf("<td div=box><a href=edit-delete.php?id=%s>%s</a>" ,$row["id"]
,$row["id"]);
print("</tr>");
}
print("<tr>");
printf("<td> </td> <td div=box><a href=edit-add.php>Add a new record</a>");
print("</tr> </table>");
?>

================================================== ========

<?php //connectdb.php
$hostname = "localhost";
$username = "t9_misc";
$dbname= "t9_misc";
$password = "misc";

MySQL_connect("$hostname","$username","$password") ;
mysql_select_db("$dbname") or die("Could not find database");
?>

================================================== ========

<? // edit-add.php
include ("connectdb.php");

$query = "SELECT * FROM temp where id='$id'" ;
$result = mysql_query($query) or die(mysql_error());
while ($line = mysql_fetch_array($result))
{
while (list($a, $b) = each($line)) { $$a = $b; }
{
Print "<p>Details: $id - $name - $address - $phone - $pay ";
}
}
?>
<form action="edit-add1.php" method="post">
<p align=center><b>Add a New Record</b><br>Please key in details.</p>
<table align=center>
<col span="1" align="right">
<tr>

<td><input type="hidden" name="id"
value="<? echo $id; ?>"></td></tr>
<tr>

<td><font color="red">Name:</font></td>
<td><input type="text" name="name"
value="<? echo $name; ?>" size=50></td></tr>
<tr>

<td><font color="red">Address:</font></td>
<td><input type="text" name="address"
value="<? echo $address; ?>" size=50></td></tr>

<tr>

<td><font color="red">Phone:</font></td>
<td><input type="text" name="phone"
value="<? echo $phone; ?>" size=50></td></tr>

<tr>

<td><font color="red">Pay:</font></td>
<td><input type="text" name="pay"
value="<? echo $pay; ?>" size=20></td></tr>

<tr>

<td></td><td><input type="submit" value="Add a new record"></td></tr>
</table>
</form>

================================================== ========

<? // edit-add1.php
include ("connectdb.php");

$insertSQL="INSERT INTO temp SET name='$name', address='$address', phone
='$phone' , pay='$pay'";
mysql_query($insertSQL);


Print "<br><b>AFTER ADDING A NEW RECORD</b> <br>";
$selectSQL="SELECT * FROM temp";
$result = mysql_query($selectSQL) or die('Select Query failed: ' .
mysql_error());
echo "<table border='2'>";
echo "<tr>\n";
Print "<b> <td> ID </td> <td> NAME </td> <td> ADDRESS </td> <td> PHONE NO.
</td> <td> PAY </td> </b>";
echo "</tr>\n";
while ($line = mysql_fetch_array($result))
{
while (list($a, $b) = each($line)) { $$a = $b; }
{
echo "<tr>\n";
Print "<td> $id </td> <td> $name </td> <td> $address </td> <td>
$phone </td> <td> $pay </td>";
echo "</tr>\n";
}
}
echo "</table> <p>";
?>

================================================== ========

<? // edit-delete.php
include ("connectdb.php");
$deleteSQL=("DELETE FROM temp WHERE id='$id'");
mysql_query($deleteSQL);

Print "<br><b>AFTER DELETE</b> <br>";
$selectSQL="SELECT * FROM temp";
$result = mysql_query($selectSQL) or die('Select Query failed: ' .
mysql_error());
echo "<table border='2'>";
echo "<tr>\n";
Print "<b> <td> ID </td> <td> NAME </td> <td> ADDRESS </td> <td> PHONE NO.
</td> <td> PAY </td> </b>";
echo "</tr>\n";
while ($line = mysql_fetch_array($result))
{
while (list($a, $b) = each($line)) { $$a = $b; }
{
echo "<tr>\n";
Print "<td> $id </td> <td> $name </td> <td> $address </td> <td>
$phone </td> <td> $pay </td>";
echo "</tr>\n";
}
}
echo "</table> <p>"; ?>

================================================== ========

<? // edit-disp.php
include ("connectdb.php");

$query = "SELECT * FROM temp where id='$id'" ;
$result = mysql_query($query) or die(mysql_error());
while ($line = mysql_fetch_array($result))
{
while (list($a, $b) = each($line)) { $$a = $b; }
{
Print "<p>Details: $id - $name - $address - $phone - $pay ";
}
}
?>
<form action="edit-save.php" method="post">
<p align=center><b>Edit Account</b><br>Please amend details.</p>
<table align=center>
<col span="1" align="right">
<tr>

<td><input type="hidden" name="id"
value="<? echo $id; ?>"></td></tr>
<tr>

<td><font color="red">Name:</font></td>
<td><input type="text" name="name"
value="<? echo $name; ?>" size=50></td></tr>
<tr>

<td><font color="red">Address:</font></td>
<td><input type="text" name="address"
value="<? echo $address; ?>" size=50></td></tr>

<tr>

<td><font color="red">Phone:</font></td>
<td><input type="text" name="phone"
value="<? echo $phone; ?>" size=50></td></tr>

<tr>

<td><font color="red">Pay:</font></td>
<td><input type="text" name="pay"
value="<? echo $pay; ?>" size=20></td></tr>

<tr>

<td></td><td><input type="submit" value="Submit"></td></tr>
</table>
</form>

================================================== ========

<? // edit-save.php
include ("connectdb.php");

$updateSQL="UPDATE temp SET name='$name', address='$address', phone
='$phone' , pay='$pay' WHERE id='$id'";
mysql_query($updateSQL);


Print "<br><b>AFTER UPDATE</b> <br>";
$selectSQL="SELECT * FROM temp";
$result = mysql_query($selectSQL) or die('Select Query failed: ' .
mysql_error());
echo "<table border='2'>";
echo "<tr>\n";
Print "<b> <td> ID </td> <td> NAME </td> <td> ADDRESS </td> <td> PHONE NO.
</td> <td> PAY </td> </b>";
echo "</tr>\n";
while ($line = mysql_fetch_array($result))
{
while (list($a, $b) = each($line)) { $$a = $b; }
{
echo "<tr>\n";
Print "<td> $id </td> <td> $name </td> <td> $address </td> <td>
$phone </td> <td> $pay </td>";
echo "</tr>\n";
}
}
echo "</table> <p>";
?>

cheers

Its a little much. consider using echo statements instead of prints. in
the querys you dont need to use ` unless it is a variable so you use '
instead. its a little easier to read if you are a novice to mysql or
are used to it being in a simler form.eg:

Less segregation is better too. maybe integrate the SQL and run
commands.

$result = mysql_query("select * from temp where temp.one =
'$examplevariable'",$dbconnection);

then easy enough its $rows=(mysql_fetch_row($result));

hope it helps

Sean Barton