problem with PHP DB webinterface

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
GreggK
Forum Newbie
Posts: 2
Joined: Tue Dec 21, 2004 10:40 am

problem with PHP DB webinterface

Post by GreggK »

Sorry about the previous mistake. Let me know if I missed something else. :(

I am new to PHP. I set up a MySQL database and want to use PHP in conjunction with it. I found a PHP admin tool (code listed below) to allow me to administer the DB from a web based interface.

Everything works in the tool except one thing. When I select a table from the drop down list, it returns to the first table in the list. Any help would be appreciated. Thanks in advance.

If you need the include file code, let me know.

Code: Select all

<? 
session_start("DBDataManagement"); 

require("inc/global.php"); //Defines global parameters 
require("inc/connect.php"); //Connect to MySQL 
require("inc/library.php"); //Upper section 

session_register("sesTheTableName"); 
session_register("sesTheDBName"); 

settype ($action, "string"); 
settype ($sql, "string"); 
settype ($startingAt, "string"); 
settype ($startingAt, "string"); 
settype ($orderBy, "string"); 
settype ($numberOfRecord, "string"); 
settype ($sesTheTableName, "string"); 

if (isset($_POST["tableName"])) 
$thetableName = $_POST["tableName"]; 
else 
$thetableName = ""; 


if (trim($thetableName)!="") { 
$sesTheTableName=$tableName; 
$TheTableName=$sesTheTableName; 
} else { 
if (trim($sesTheTableName)=="") { 
mysql_select_db($DB); 
$tables=mysql_list_tables($DB); 
if (!empty($tables)) { 
list($TheTableName)=mysql_fetch_array($tables); 
} else { 
$TheTableName=""; 
} 
$sesTheTableName=$TheTableName; 
} else { 
$TheTableName=$sesTheTableName; 
} 
} 

putHeader(); 

if (!empty($TheTableName)) { 
$fields = mysql_list_fields($DB, $TheTableName); 
$columns = mysql_num_fields($fields); 
$uniqueFieldName = mysql_field_name($fields,0); 
$uniqueFieldType= mysql_field_type($fields,0); 
} else { 
echo "<h4>No table found</h4>"; 
exit; 
} 

if ($action=="edit") { 
if (preg_match ("/int/i", $uniqueFieldType)) { 
$sql = "SELECT * ". 
"FROM $TheTableName ". 
"WHERE $uniqueFieldName = $uniqueId"; 
} else { 
$sql = "SELECT * ". 
"FROM $TheTableName ". 
"WHERE $uniqueFieldName = "$uniqueId""; 
} 
$res=mysql_query($sql); 
echo "<FORM name=frmUpdate method=POST>"; 
echo "<input type=hidden name=action value=update>"; 
echo "<input type=hidden name=oldUniqueId value=$uniqueId>"; 
echo "<TABLE BORDER=1 width=100%>"; 
echo "<tr><td COLSPAN=2 align=center><b>$TheTableName</b></td></tr>"; 

while($row=mysql_fetch_array($res)) 
{ 
for ( $i = 0; $i < $columns; $i++ ) 
{ 
$arr = get_defined_vars(); 
$fieldName= mysql_field_name($fields,$i); 
$fieldType= mysql_field_type($fields,$i); 
$fieldNameValue= $arr[$fieldName."Val"]; 

echo "<TR>"; 
echo "<TD>".mysql_field_name($fields,$i)."</TD>"; 
if (preg_match ("/blob/i", $fieldType)) { 
echo "<TD><textarea cols="30" rows="5" name=".mysql_field_name($fields,$i)."Val >".$row[$i]."</textarea></TD>"; 
} else { 
echo "<TD><input name=".mysql_field_name($fields,$i)."Val value="".$row[$i].""></input></TD>"; 
} 
echo "</TR>"; 
} 
echo "<TR>"; 
echo "<TD COLSPAN=2 align=center><input type=submit value=Update name=submit></TD>"; 
echo "</TR>"; 
} 
echo "</TABLE>"; 
echo "</FORM>"; 

} else if ($action=="add") { 
$sql="INSERT INTO $TheTableName VALUES("; 
for ( $i = 0; $i < $columns; $i++ ) 
{ 
$arr = get_defined_vars(); 
$fieldName= mysql_field_name($fields,$i); 
$fieldType= mysql_field_type($fields,$i); 
$fieldNameValue= $arr[$fieldName."Val"]; 

if ($i == 0) { //For first column 
$uniqueFieldName=$fieldName; 
$uniqueId=$arr[$fieldName."Val"]; 
$uniqueFieldNameValue=$arr[$fieldName."Val"]; 
if (trim($uniqueFieldNameValue)=="") { //If it is empty treat it as a autoincrement field and put value 0 
$uniqueFieldNameValue=0; 
} 
if (preg_match ("/int/i", $fieldType)) { 
$sql = $sql . $uniqueFieldNameValue . ", "; 
} else { 
$sql = $sql . " "" . $uniqueFieldNameValue . "", "; 
} 
} else if ($i == $columns-1) { //For Last Column 
if (preg_match ("/int/i", $fieldType)) { 
$sql = $sql .$fieldNameValue.") "; 
} else { 
$sql = $sql . " "".$fieldNameValue."") "; 
} 
} else { 
if (preg_match ("/int/i", $fieldType)) { 
$sql = $sql . $arr[$fieldName."Val"].", "; 
} else { 
$sql = $sql . " "" .$fieldNameValue."", "; 
} 
} 
} 
$res=mysql_query($sql); 
if (!$res) echo mysql_error(); 
else echo "<br>Record inserted: $res<br>"; 

} else if ($action=="addForm") { 
echo "<FORM name=frmAddForm method=POST>"; 
echo "<input type=hidden name=action value=add>"; 
echo "<TABLE BORDER=1 width=100%>"; 
echo "<tr><td COLSPAN=2 align=center><b>$TheTableName</b></td></tr>"; 

for ( $i = 0; $i < $columns; $i++ ) 
{ 
$arr = get_defined_vars(); 
$fieldName= mysql_field_name($fields,$i); 
$fieldType= mysql_field_type($fields,$i); 
$fieldNameValue= $arr[$fieldName."Val"]; 

echo "<TR>"; 
echo "<TD>".mysql_field_name($fields,$i)."</TD>"; 
if (preg_match ("/blob/i", $fieldType)) { 
echo "<TD><textarea cols="30" rows="5" name=".mysql_field_name($fields,$i)."Val></textarea></TD>"; 
} else { 
echo "<TD><input name=".mysql_field_name($fields,$i)."Val value=""></input></TD>"; 
} 
echo "</TR>"; 
} 


$test=$test+1; 
echo "<TR>"; 
echo "<TD COLSPAN=2 align=center><input type=submit value=Add name=submit></TD>"; 
echo "</TR>"; 

echo "</TABLE>"; 
echo "</FORM>"; 

} else if ($action=="delete") { 
// uniqueFieldName should be column 0 
$uniqueFieldName= mysql_field_name($fields,0); 
foreach ($IdsForDelete as $IdForDelete) 
{ 
//Delete selected rows 
$sql="DELETE FROM $TheTableName". 
" WHERE $uniqueFieldName = $IdForDelete"; 
$res=mysql_query($sql); 
if (!$res) giveError(mysql_error()); 
else echo "$IdForDelete deleted<br>"; 
} 

} else if ($action=="search") { 
$sql="SELECT * FROM $TheTableName"; 
$j = 0; 
for ( $i = 0; $i < $columns; $i++ ) 
{ 
$arr = get_defined_vars(); 
$fieldName= mysql_field_name($fields,$i); 
$fieldType= mysql_field_type($fields,$i); 
$fieldNameValue= $arr[$fieldName."Val"]; 
if (trim($fieldNameValue)!="") { // If search criteria selected 
if ($j == 0) { 
$uniqueFieldName=$fieldName; 
$uniqueId=$arr[$fieldName."Val"]; 
if (preg_match ("/int/i", $fieldType)) { 
$sql = $sql . " WHERE $fieldName=".$fieldNameValue." "; 
} else { 
$sql = $sql . " WHERE $fieldName LIKE '".$fieldNameValue."%' "; 
} 
} else if ($j == $columns-1) { 
if (preg_match ("/int/i", $fieldType)) { 
$sql = $sql . " AND $fieldName=".$fieldNameValue." "; 
} else { 
$sql = $sql . " AND $fieldName LIKE "".$fieldNameValue."%" "; 
} 
} else { 
if (preg_match ("/int/i", $fieldType)) { 
$sql = $sql . " AND $fieldName=".$fieldNameValue.", "; 
} else { 
$sql = $sql . " AND $fieldName LIKE "".$fieldNameValue."%", "; 
} 
} 
$j = j + 1; 
} 
} 
$pos = strrpos($sql, ","); 
if (trim($pos)!="") { 
$sql = substr_replace($sql, "", $pos); // Delete ',' at the end 
} 
listData($sql,$columns,$fields,$TheTableName,$startingAt,$numberOfRecord,$orderBy); 
} else if ($action=="update") { 
$sql="UPDATE $TheTableName"; 
for ( $i = 0; $i < $columns; $i++ ) 
{ 
$arr = get_defined_vars(); 
$fieldName= mysql_field_name($fields,$i); 
$fieldType= mysql_field_type($fields,$i); 
$fieldNameValue= $arr[$fieldName."Val"]; 

if ($i == 0) { 
$uniqueFieldName=$fieldName; 
$uniqueId=$arr[$fieldName."Val"]; 
if (preg_match ("/int/i", $fieldType)) { 
$sql = $sql . " SET $fieldName=".$fieldNameValue.", "; 
} else { 
$sql = $sql . " SET $fieldName="".$fieldNameValue."", "; 
} 
} else if ($i == $columns-1) { 
if (preg_match ("/int/i", $fieldType)) { 
$sql = $sql . " $fieldName=".$fieldNameValue." "; 
} else { 
$sql = $sql . " $fieldName="".$fieldNameValue."" "; 
} 
} else { 
if (preg_match ("/int/i", $fieldType)) { 
$sql = $sql . " $fieldName=".$fieldNameValue.", "; 
} else { 
$sql = $sql . " $fieldName="".$fieldNameValue."", "; 
} 
} 
} 
$sql = $sql . " WHERE $uniqueFieldName=$oldUniqueId"; 

$res=mysql_query($sql); 
if (!$res) echo mysql_error(); 
else echo "<br>Record updated: $res<br>"; 

} else { //List 
if (trim($sql)=="") { 
$sql = "SELECT * ". 
"FROM $TheTableName "; 
} else { 
} 
listData($sql,$columns,$fields,$TheTableName,$startingAt,$numberOfRecord,$orderBy); 
} 




//////////////////////////////////////////////////////////////////////////////////////////////////// 
// Functions 
function listData($sql,$columns,$fields,$TheTableName,$startingAt,$numberOfRecord,$orderBy) 
{ 
global $gNUMBEROFRECORDS; 
//numberOfRecord and startingAt controls 
if(trim($numberOfRecord)==""){ 
$numberOfRecord = $gNUMBEROFRECORDS; 
} 
if(trim($startingAt)==""){ 
$startingAt = 0; 
} 

//echo "<br><br>sql=$sql,columns=$columns,fields=$fields,TheTableName=$TheTableName,startingAt=$startingAt,numberOfRecord=$numberOfRecord<br>"; 

if (!empty($orderBy)) { 
$sql=$sql." ORDER BY $orderBy"; 
} 

global $action,$SqlForPrevNext; 
if (trim($action)=="next" OR trim($action)=="prev") { 
session_register("SqlForPrevNext"); 
$sql=$SqlForPrevNext; 
} else { 
session_register("SqlForPrevNext"); 
$SqlForPrevNext=$sql; 
} 

echo "<FORM name=frmAction method=POST>"; 
echo "<input type=hidden name=action value="">"; 

$res=mysql_query($sql); 
$showNumberOfRecord=0; 

echo "Show <input type="text" name="numberOfRecord"> number of record starting at <input type="text" name="startingAt">&nbsp;<input type="button" value="Search" name="" onclick="frmAction.submit();"><br>"; 
$numberOfRows = mysql_num_rows($res); 
//echo "<br>Number Of Rows=$numberOfRows<br>"; 

// Prev 
if ($startingAt-$numberOfRecord<0) { 
echo "Prev "; 
} else { 
$prevStartingAt=$startingAt-$numberOfRecord; 
echo "<a href=?action=prev&startingAt=$prevStartingAt&numberOfRecord=$numberOfRecord>Prev</a> "; 
} 

// Next 
$nextStartingAt=$startingAt+$numberOfRecord; 
if ($numberOfRows<$nextStartingAt) { 
echo "- Next"; 
} else { 
echo "- <a href=?action=next&startingAt=$nextStartingAt&numberOfRecord=$numberOfRecord>Next</a>"; 
} 

// Show record numbers on the screen 
if ($numberOfRows<$nextStartingAt) { 
echo "&nbsp;&nbsp;&nbsp;$startingAt - $numberOfRows / $numberOfRows<br>"; 
} else { 
echo "&nbsp;&nbsp;&nbsp;$startingAt - ".($startingAt+$numberOfRecord)." / $numberOfRows<br>"; 
} 
//Lower table - heading = account 
echo "<TABLE BORDER=1 width=100%>"; 
$tableColumns=$columns+2; //For Edit column 
echo "<tr><td COLSPAN=$tableColumns align=center><b>$TheTableName</b></td></tr>"; 

// Delete, field names as header, add button 
echo "<TR>"; 
echo "<TD width="20" align="center"><input type="button" value="Delete" name="" onclick="submitDelete()"></TD>"; //For checkbox column 
echo "<TD width="20" align="center"><input type="button" value="Add" name="" onclick="submitAdd()"></TD>"; //For checkbox column 
for ( $i = 0; $i < $columns; $i++ ) 
{ 
echo "<TD align="center"><a href="?orderBy=".mysql_field_name($fields,$i)."">".mysql_field_name($fields,$i)."</a></TD>"; 
} 
echo "<TD></TD>"; //For Edit column 
echo "</TR>"; 

// Search the fields 
echo "<TR>"; 
echo "<TD width="20" align="center"></TD>"; //For checkbox column 
echo "<TD width="20" align="center"><input type="submit" value="Search" name="" onclick="submitSearch()"></TD>"; //For checkbox column 
for ( $i = 0; $i < $columns; $i++ ) 
{ 
echo "<TD align=center><input name=".mysql_field_name($fields,$i)."Val value=""></input></TD>"; 
} 
echo "</TR>"; 



if ($startingAt>0){ 
if ($startingAt<=$numberOfRows){ 
$startingAt=$startingAt-1; 
if (!mysql_data_seek ($res, $startingAt)) { 
echo "Cannot seek to row $startingAt\n"; 
exit; 
} 
} else { 
echo "Row starting point($startingAt) can not be greater than number of rows($numberOfRows)\n"; 
exit; 
} 
} 

while($row=mysql_fetch_array($res) AND $showNumberOfRecord<$numberOfRecord) 
{ 
echo "<TR>"; 
echo "<TD align="center"><input type="checkbox" name="IdsForDelete[]" value="".$row[0].""></TD>"; 
echo "<TD><A HREF='?uniqueId=$row[0]&action=edit'>Edit</A></TD>"; 
for ( $i = 0; $i < $columns; $i++ ) 
{ 
if (trim($row[$i])!="") { // if colomn is empty put a space for td border 
echo "<TD align="center">".$row[$i]."</TD>"; 
} else { 
echo "<TD align="center"> &nbsp;</TD>"; 
} 
} 
echo "</TR>"; 
$showNumberOfRecord=$showNumberOfRecord+1; 
} 
echo "</TABLE>"; 
echo "</FORM>"; 

?>
<SCRIPT>
function submitDelete(){
frmAction.action.value="delete";
frmAction.submit();
}
function submitAdd(){
frmAction.action.value="addForm";
frmAction.submit();
}
function submitSearch(){
frmAction.action.value="search";
frmAction.submit();
}
function submitQuery(){
frmAction.sql.value=frmAction.sqlQueryString.value;
frmAction.action.value="list";
frmAction.submit();
}
</SCRIPT>
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

I've split your post from the sticky "Posting code in the forums" - which is not where anyone is supposed to post code (including you). ;)
Post Reply