Column name troubles

Jodely

Okay, here's what im trying to make:

A script that can log onto any database table, grabs everything from the table and spills it out in a nice HTML table with add/remove/edit buttons.

My first problem is to make a query to only grab the column names such as id, naam, adres etc

My seccond problem is how to create functional delete/edit button for the reccord in question.

And finaly my third problem (least significant) making it possible to add a new record.

Here's the script so far:

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

=========HTML Part==========

<html>

<head>

</head>

<body bgcolor="#ffffff">
<form method="post" action="connect.php">
		<table width="291" border="1" cellspacing="2" cellpadding="0">
			<tr>
				<td colspan="2">Tablemanager Login<br>
					<br>
				</td>
			</tr>
			<tr>
				<td>Host</td>
				<td><input type="text" name="host" value="Localhost"></td>
			</tr>
			<tr>
				<td>Database name</td>
				<td><input type="text" name="database"></td>
			</tr>
			<tr>
				<td>Table name</td>
				<td><input type="text" name="table"></td>
			</tr>
			<tr>
				<td>Login name</td>
				<td><input type="text" name="login" value="root"></td>
			</tr>
			<tr>
				<td>Password</td>
				<td><input type="text" name="password"></td>
			</tr>
			<tr>
				<td></td>
				<td><input type="submit" name="send" value="Login"></td>
			</tr>
		</table>
	</form>

</body>

</html>

==========================
=========PHP Part==========

========connect.php==========

<form>

<?php

// Input van Form naar variabelen
@extract($_POST);
$host = stripslashes($host);
$login = stripslashes($login);
$password = stripslashes($password);
$database = stripslashes($database);
$table = stripslashes($table);

// Delete en Wijzig variabelen
$deleterecord =
$editrecord =

// Connectie maken met server en database
$link = mysql_connect("$host", "$login", "$password")
or die("Kan geen verbinding maken");
print "Verbinding succesvol gemaakt";
mysql_select_db("$database")
or die("Kan geen database selecteren");

// QRY voor kolomnamen
$qry = "SELECT * FROM $table";
$result = mysql_query($qry)
or die("Fout bij uitvoeren query");;

// Start tabel
print "\n<table border=1>\n";

// Tabel column titels
print "\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)){
print "\t<tr>\n";
foreach ($line as $colname) {
print "\t\t<td><b>$colname</b></td>\n";
}
print "\t</tr>\n";
}

// SQL query uitvoeren
$query = "SELECT * FROM $table";
$result = mysql_query($query)
or die("Fout bij uitvoeren query");

// Printen resultaten in html tabel
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
print "\t<tr>\n";
foreach ($line as $col_value) {
print "\t\t<td>$col_value</td>\n";
}
// Hieronder het stukje om knoppen naast de lijst toe te voegen per rij
print "\t\t<td><input type=button name=delete value=Delete action=$deleterecord ></td>\n\t\t<td><input type=button name=edit value=Alter action=$editrecord ></td>\n\t</tr>\n";
}

print "\n\n";

// QRY voor kolomnamen
$qry = "SELECT * FROM $table";
$result = mysql_query($qry)
or die("Fout bij uitvoeren query");;

// Record toevoegen

print "\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)){
print "\t<tr>\n";
foreach ($line as $colname) {
print "\t\t<td><input type=text name=$colname></td>\n";
}
print "\t\t<td><input type=submit name=addrec value=Add Record></td>\n";
}

// Einde tabel
print "</table>\n\n";

// Resultaatset vrij maken
mysql_free_result($result);

// Verbinding afsluiten
mysql_close($link);

</form>
</font>
</body>
</html>

LarsLai

hi!
only have a hint for your first problem. when you have submitted the query "SELECT * FROM $table" you can get the names of the columns with mysql_field_name().
something like this:

$NumberOfFields = mysql_num_fields($result);
for ($i = 0; $i <= $NumberOfFields; $i++)
{
$NameOfField[$i] = mysql_field_name($result, $i);
}

PallaviDalvi

try mysql_list_fields to grab the column names.
as far as the other probs are concerned.. i think u can easily manage with the Edit, Insert and Delete SQL's.

Jodely

Lars, thanks for the info, i tried it but somehow it gives me the eroor the script took longer then 30 secconds to execute and terminated.

That link helped me out Palla, added into my script and it seems to work now.

Still need to figure out more how to add/modify/delete etc, i saw you mention about Edit, Insert and Delete SQL's, got any more information about these?

Anyways here's my updated connect.php:


<html><body>

<font face=Verdana>

<form>

<?php

// Input van Form naar variabelen
@extract($_POST);
$host = stripslashes($host);
$login = stripslashes($login);
$password = stripslashes($password);
$database = stripslashes($database);
$table = stripslashes($table);


// Delete en Wijzig variabelen
$deleterecord = 
$editrecord = 


// Connectie maken met server en database
 $link = mysql_connect("$host", "$login", "$password")
     or die("Kan geen verbinding maken");
 print "Verbinding succesvol gemaakt";
 mysql_select_db("$database")
     or die("Kan geen database selecteren");


// QRY voor kolomnamen
$qry = "SELECT * FROM $table"; 
$result = mysql_query($qry)
	or die("Fout bij uitvoeren query");;



 // Start tabel
 print "\n<table border=1 bordercolor=cccccc>\n";


// column names
 $fields = mysql_list_fields($database, $table);
$columns = mysql_num_fields($fields);
	for ($i = 0; $i < $columns; $i++) {
    	echo "\t\t<td><b>";
		echo mysql_field_name($fields, $i) . "</b></td>\n";
}

  // SQL query uitvoeren
 $query = "SELECT * FROM $table";
 $result = mysql_query($query)
     or die("Fout bij uitvoeren query");


 // Printen resultaten in html tabel
 while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
     print "\t<tr>\n";
     foreach ($line as $col_value) {
         print "\t\t<td>$col_value</td>\n";
     }
     // Hieronder het stukje om knoppen naast de lijst toe te voegen per rij
     print "\t\t<td><input type=button name=delete value=Delete action=$deleterecord ></td>\n\t\t<td><input type=button name=edit value=Alter action=$editrecord ></td>\n\t</tr>\n";
 }

 print "\n\n";


// QRY voor kolomnamen
$qry = "SELECT * FROM $table"; 
$result = mysql_query($qry)
	or die("Fout bij uitvoeren query");;



// Record toevoegen

$fields = mysql_list_fields($database, $table);
$columns = mysql_num_fields($fields);
	for ($i = 0; $i < $columns; $i++) {
    	echo "\t\t<td>";
		echo mysql_field_name($fields, $i) . "</td>\n";
}


/*
 print "\n";
 	while ($line = mysql_fetch_array($fields, MYSQL_ASSOC)){
 	print "\t<tr>\n";
 		foreach ($line as $colname) {
 		print "\t\t<td><input type=text name=$colname></td>\n";
 									 }
 	print "\t\t<td><input type=submit name=addrec value=Add Record></td>\n";
 															 }
*/

 // Einde tabel
 print "</table>\n\n";


 // Resultaatset vrij maken
 mysql_free_result($result);

 // Verbinding afsluiten
 mysql_close($link);


?>


</form>
</font>
</body>
</html>

PallaviDalvi

u can read about Insert/delete sql's here hope this helps u.