MySQL/PHP CRUD
CRUD Code Jotter
Connect to the database
<?php
/* set the connections to the database */
$host = "localhost";
$username = "your username here";
$passwd = "your password here";
$thedatabase = "your username here";
$connection = mysql_connect($host, $username, $passwd) or die("could not connect");
mysql_select_db($thedatabase) or die ("could not find the database, sorry.");
?>
<?php
require ("connect.php");
if (mysql_query("create table rooms(
RoomId integer unsigned not null auto_increment primary key,
RoomName varchar(80) not null,
RoomDessie text not null,
RoomMedia text)"))
{ echo "ROOMS table created successfully"; }
else
{ echo "had a problem - ".mysql_error(); };
if (mysql_query("create table exits(
ExitId integer unsigned not null auto_increment primary key,
Origin integer unsigned not null,
Destination integer unsigned not null,
DoorName varchar(80) not null)"))
{ echo "<br>EXITS table created successfully"; }
else
{ echo "<br>had a problem - ".mysql_error(); }
?>
Notes: I decided to make both tables in the same file, and also append the error message to the error statement to let you know what goes wrong if anything. I added a droptables file merely for testing purposes, naturally you would not leave this exposed in a rolled out application else some bastard somewhere would kill your database on you.
<?PHP
require ("connect.php");
if (mysql_query("insert into rooms(RoomName, RoomDessie, RoomMedia)
values ('The Vermillion Room',
'It looks vermillion, yada yada',
'http://www.wonko.info/ipt/iis/php/crud/vermillion.jpg'),
('The Green Room',
'It looks green, blah blah',
'http://www.wonko.info/wonko.jpg')"))
{ echo "<br>Rooms Data added successfully"; }
else
{ echo "<br>had a problem - ".mysql_error(); }
?>
<?php
require ("connect.php");
$stuff = "select * from rooms";
/* attempt to retreive the data */
$answertable = mysql_query($stuff, $connection);
if (!$answertable)
{
echo "no data was returned";
}
else
{
$numcolumns = mysql_num_fields($answertable);
echo "<table cellpadding=5 border=1 cellspacing=0><tr>";
for ($counter = 0; $counter < $numcolumns; $counter++)
{
$name = mysql_field_name($answertable,$counter);
echo "<th>$name</th>";
};
echo "</tr>";
while (list($RoomId, $RoomName, $RoomDessie, $RoomMedia) = mysql_fetch_row($answertable))
{
echo "<tr>";
echo "<td>$RoomId</td>";
echo "<td>$RoomName</td>";
echo "<td>$RoomDessie</td>";
echo "<td><img width=50 alt='$RoomName' src=$RoomMedia><br>$RoomMedia</td>";
echo "</tr>";
}
echo "</table>";
}
?>
Notes: in the above coed, I retreive all rows in the Rooms table and then present them in tabular form with column headings and all. Not really practical for use but merely because I want to confirm what was there. You should also notice that the Image is presented as a url as well as rendered inline (although I have reduced it's size to 50 px wide).
<?php
/* INCOMING Parameter = $room */
require ("connect.php");
$stuff = "select * from rooms where RoomId=$room";
/* attempt to retrieve the data */
$theroom = mysql_query($stuff, $connection);
if (!$theroom)
{
echo "no data was returned";
}
else
{
while (list($RoomId, $RoomName, $RoomDessie, $RoomMedia) = mysql_fetch_row($theroom))
{
echo "<table align=center width=50% border=0 cellspacing=0 cellpadding=5>";
echo "<tr><td><h2>$RoomId: $RoomName</h2></td></tr>";
if ($RoomMedia)
{ echo "<tr><td><center><img width=300 alt='$RoomName' src=$RoomMedia></center></td></tr>"; }
else
{ echo "<tr><td><center>[no visual supplied]</center></td></tr>"; }
echo "</center></td></tr>";
echo "<tr><td><p>$RoomDessie</p></td></tr>";
echo "<tr><td><p>Available Exits: <br>";
echo "Available Actions: <a href=edit.php?room=0>build new room</a> | ";
echo "<a href=edit.php?room=".$RoomId.">edit this room</a> | ";
echo "add a door";
echo "</p></td></tr></table>";
}
}
?>
Note: This code segment uses a PARAMETER that is supplied as part of the URL (http://whatever?room=number). This parameter is an incoming variable used as a filter clause in the data retrieval prior to display. You should also notice I have begin to form the single room in a one-column table much like the design brief suggested.
<?php
require ("connect.php");
if ($room==0) /* add an empty room to edit later on */
{
/* claim a row first = primitive record locking allowing multiuser addition */
$claimroom = "insert into rooms (RoomName, Roomdessie, RoomMedia)
values ('empty room','you see a room needing a description',null);";
mysql_query($claimroom,$connection) or die ("Stopping here: had trouble getting a record lock");
/* retreive the record number of that claimed row */
$findlargestroomid = "select max(RoomId) from rooms";
$result = mysql_query($findlargestroomid,$connection);
$largestroomid = mysql_fetch_row($result);
$room = $largestroomid[0];
}
/* no retrieve the row data for that room number */
$getroomdata = "select * from rooms where roomid =".$room;
if ($roomdata = mysql_query($getroomdata,$connection))
{
list($RoomId, $RoomName, $RoomDessie, $RoomMedia) = mysql_fetch_row($roomdata);
/* place room data in the form */
echo "<form action=doupdate.php?room=".$room." method=post name=roomdataentry id=roomdataentry>";
echo " <table border=1 align=center cellpadding=5 cellspacing=0>";
echo " <tr>";
echo " <td width=100>Room Name<br>id= ".$RoomId."</td>";
echo " <td><input name=rname type=text id=rname tabindex=1 value='".$RoomName."' size=80></td>";
echo " </tr>";
echo " <tr>";
echo " <td>Room Description</td>";
echo " <td><textarea name=rdessie cols=70 rows=4 tabindex=2 id=rdessie>".$RoomDessie."</textarea></td>";
echo " </tr>";
echo " <tr>";
echo " <td>Room Media(URL):</td>";
echo " <td><input name=rurl type=text id=rurl tabindex=3 value='".$RoomMedia."' size=80></td>";
echo " </tr>";
echo " <tr><td colspan=2>";
echo " <div align=center><input type=submit name=Submit value='save my room'></div>";
echo " </td></tr>";
echo " </table>";
echo "</form>";
}
else
{
echo " Had a problem with the whole thing, sorry ".mysql_error();
}
?>
Notes:
- There is lots in this file, much of it fairly clever. The idea was to use edit to both create a new room and edit an existing one.
- Because the web is intrinsically multi-user, you could be adding a room at the same time as someone else, without knowing which room was yours - this then does a form of primitive record locking (or more correctly, record claiming).
- When the add new is selected, a row is added to the rooms table with dummy data to make the new roomid (I used autoincrementing ids, right?) Then the maximum roomid is requested (which will correspond to the room you just created) and then a request is sent to bring that data into the edit form - neat huh?
It then passes the edited data to a simple forms processor file which updates that row. The forms processor form is below:
<?PHP
require("connect.php");
/* define the update request */
$updateroom = "update rooms
set RoomName = '".$_POST['rname']."',
Roomdessie = '".$_POST['rdessie']."',
RoomMedia = '".$_POST['rurl']."'
where RoomId= ".$_POST['room'] ;
/* Jam the data into the table for that record */
mysql_query($updateroom,$connection) or die ("Stopping here: had trouble updating the room");
/* Redirect to the room view for this newly edited room */
echo "<script language=\"javascript\">
window.location.href='seeonenominatedroom.php?room=".$_POST['room']."' ;
</script>" ;
?>
Notes: I initially tried to include this stuff in the file that had the form but later found that the data was being blanked out - this is because I needed to refer to the data that was POSTED (ie. the updated data) and that requires a POST action to happen first. So when I moved this update command to a separate but linked file, the POST had to happen before this file was opened and all was sweet. There seems not to be a delay in the re-direct and I think this method is safe for all browsers. Thanks "K" for the advice regarding the re-director (love your work mate).
Other Stuff
|
