Nightshift was nice enought to help me out with this project but I just can't get it to work so I thought I'd post it because doing it through a PM is just to difficult.
Anyway, the deal is this. I have 5 tables in a Postgresql DB that I am accessing with a simple select * from command.
Well now I would like to add a section that allows the user to do simple querys and display the info, update the information, add new rows, add new tables, and delete tables. I'm not sure if all of this is possible through a simple web interface.
Even a text input window would be ok, I think. Anyway, this has expanded a little from what the code below allows for but in either case I can't get anything to work.
Any help would be appriciated.
PHP Code:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Alexamara Marina</title>
if (isset($dTable)) {
echo ("<p><font face='Arial' color='#ffffff'>You have selected the <i>$dTable</i>table</font></p>");
require("psqlCommon.php");
if( strcmp($dTable,"marina") != 0) {
displayTable($dTable);
} else {
displaymarina($dTable);
}
} else {
echo("<p><font face='Arial' color='#ffffff'>Welcome to the Alexamara Marina database web portal.</br>
Please select from the table options above to view there content.</font></p>");
}
?>
Write a simple script to display a form with the information.
Then comes a script to insert information.
Next add a script to modify the information.
And on to a script to delete information.
etc.
Start off with each such script as a separate file. Once you have your directoin firmly set, you can combine them into a single script and joining the whole thing together.
Here it is. I would basically place a link on the index page to an identical page that allowed for the input of an SQL query. I'm hoping that the query can be exicuted from there and either the proper data displayed or a message returned that the updates have been made. Is that possible?
HTML Code:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><title>Alexamara Marina - Run Query</title><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"></head><body background="sky.jpg" text="white" link="white" vlink="white" alink="white"><?PHP
unset($dTable);
if (isset( $_GET['tablename']) )
{
$dTable = $_GET['tablename'];
}
?><div align="center"><p><h1><font face="Arial">Alexamara Marina</font></h1></p></div><table align="center" width="765" height="110" cellpadding="0" border="2" bordercolor="#ffffff"><tr><td height="80" width="740" cellpadding="0" border="4"><img src="header.jpg"/></td></tr></table></br><?php
$arrTableNames = array();
$arrTableNames[] = "Marina";
$arrTableNames[] = "Owner";
$arrTableNames[] = "MarinaSlip";
$arrTableNames[] = "ServiceCategory";
$arrTableNames[] = "ServiceRequest";
?><div align="center"><table><tbody><tr><?php
FOREACH ($arrTableNames as $key => $thisTABLE) :
print "<td><a href='index.php?tablename=$thisTABLE' style='font-family:Arial;color: ffffff;'>$thisTABLE</a> </td>\n";
ENDFOREACH;
?>
</td><td><font face="Arial"><a href="http://students.pointpark.edu/kebreeg/query">Run a Query</a></font></td></tr><tr><td> </td></tr></tbody></table></div><div align="center"><form action="query" method="post" target="_self"><font face="Arial">Enter a Query:</font><br/></br><TEXTAREA NAME="query" COLS=100 ROWS=6></textarea></br></br><INPUT TYPE=SUBMIT VALUE="Run Query"></FORM><div align="center"><?PHP
if(isset($dTable))
{
echo("<p><font face='Arial' color='#ffffff'>You have selected the <i>Alexamara.".$dTable." </i> table</font></p>");
require("psqlCommon.php");
if( strcmp($dTable,"marina") != 0)
displayTable($dTable);
else
displaymarina($dTable);
}
else
echo("<p><font face='Arial' color='#ffffff'>Welcome to the Alexamara Marina database web portal.</br>
Please select from the table options above to view there content.</font></p>");
?></br></br></br></br></br></div><div align="center" vertical-align="bottom"><font face="Arial">Copyright 2007 Alexamara Marina | <a href="mailto:kebreeg@pointpark.edu">Contact Us</a> | <a href="http://students.pointpark.edu/kebreeg/">Home</a></font></div></br></body></html>
I'm asking myself if you really want to give visitors the opportunity to type in a query and then blindly executing it. There's a few very destructive commands in the SQL language.
I think the ultimate plan would be to have password security that allowed for certian things. But in this case this is just a class project. Our database instructor knows we don't know PHP and allows us to get help anyway we can. He's been trying to teach it to us but there is olnly so much he can teach in a couple weeks.
So bottom line is this. I don't know the best way to code it. I agree with you that certain regulations need to be etablished but I wouldn't know how to go about doing it.
I'm asking for help with the PHP part to allow the DB administrator to do everything they could possibley need to do. And it could be password protected.
Ok, I've found that we can simplify what we are doing. I'm only going to need to allow for the ability to update data and insert new data to only one table.
I know how to pull up the data for the individual rows in the table but I don't know how to allow for an update or insert.
The table name is: owner
the column names are: ownernum, lastname, firstname, address, city, state, and zip.
Reply With Quote
Bookmarks