Creating Dynamic html table from mysql query

'creating Dynamic html tables' from the mysql database

[Gruessle]

Below is how I am crating my html tables right now but I have like 10 of those tables and a change takes for ever.

This is what I would like to do:
query the mysql database

And create the columns with the column name for each column from the mysql database.

Then fill the rows like I am doing it right now.

Now to make it perfect I would like to not use column one and two since those are just id & cid.

<?php
print "<div align='center'><font color='red'>$message</font></div><br>";

// read data from database
$p_edu = mysql_query("SELECT * FROM profile_edu WHERE (cid = $uid) ORDER BY cYear DESC")
or die ("Could not read data because ".mysql_error());

// print the data in a table
if (mysql_num_rows($p_edu)) {
print "<table cellpadding=2 cellspacing=0 border=0 >\n";
print "<tr>
<th>Cert. Year</th>
<th>Education</th>
<th>School</th>
<th>Program</th>
</tr>\n";
while ($qry = mysql_fetch_array($p_edu))
{
$tx = ($tx == 'odd')? ('even') : ('odd');
print "
<tr>
<td class='$tx'>$qry[cYear]</td>
<td class='$tx'>$qry[Education]</td>
<td class='$tx'>$qry[School]</td>
<td class='$tx'>$qry[Program]</td>
<td class='$tx'>
<form name='form1' method='post' action=$PHP_SELF>
<input name='delid' type='hidden' value=$qry[id]>
<input type='submit' name='delete' value=Delete>
</form>
</td>
</tr>\n
";
}
print "</table>\n";
}

?>

[coopster]

How about creating a foreach() loop, skipping over those column indexes which match the id values you don't want to print?

[Gruessle]

Can Someone tell me how following works?

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

"dumping" function but with the optional possibility to choose wich field_name to be dumped. "Have Fun and please email me if you do optimization of this code"

<?php
function mysql_format($strTemp){
//Format sql sentence for insert
$bad_chars= array("\\", "'", "\"");
$good_chars = array("\\\\", "''", "\"\"");
return str_replace($bad_chars, $good_chars, $strTemp);
}

function mysql_dump_table(){
/*******************\
* MYSQL_DUMP_TABLE *********************************\
* Param&#65533;tres : *
* 1- Table Name *
* 2- Field(s) (in string format) Name to be dumped *
* If empty, all field will be dumped *
\******************************************************/

if (!(func_num_args())) die ("<b>mysql_dump_table</b>: Need At Least A Table Name");

$arg_list = func_get_args();
$arrReturn = "";
$strTblName = mysql_format("`{$arg_list[0]}`");
$strFields = "*";

if (func_num_args() > 1){
$strFields = "";
for ($noArg=1; $noArg<func_num_args(); $noArg++){
if ($strFields) $strFields .= ", ";
$strFields .= "`$arg_list[$noArg]`";
}
}

$result = mysql_query("SELECT $strFields FROM $strTblName") or die ("Incapable d'ex&#65533;cuter la requ&#65533;te");

$nbRecord = 0;
while ($row = mysql_fetch_assoc($result)){
$strFieldsNames = "";
$strFieldsValues = "";

foreach ($row as $field_name => $field_value){
if ($strFieldsNames) $strFieldsNames .= ", ";
$strFieldsNames .= "`$field_name`";

if($strFieldsValues) $strFieldsValues .= ", ";
$strFieldsValues .= "'" . mysql_format($field_value) . "'";
}

$arrReturn[$nbRecord++] = "INSERT INTO $strTblName($strFieldsNames) values($strFieldsValues);";
}
mysql_free_result($result);
return $arrReturn;
}

require_once("config_connexion_db_test.php");

/****************
* AUTRE EXEMPLE **********************************************
* Vous pouvez d&#65533;cider d'afficher quelques champs seulements *
* en sp&#65533;cifiant les champs d&#65533;sir&#65533; *
**************************************************************/
$db = mysql_connect(DBTEST_HOST, DBTEST_USER, DBTEST_PASSWORD) or die("Impossible de se connecter : ");
mysql_select_db(DBTEST_NAME, $db);

$result = mysql_dump_table("tbl_produits", "code_produit", "description");

foreach ($result as $sentence){
//Afficher chaque &#65533;l&#65533;ment du tableau
print "$sentence<br>";
}
mysql_close($db);

//Retourne
/********************************************************
INSERT INTO `tbl_produits`(`code_produit`,`description`) VALUES('produit1', 'don\'t have description');
INSERT INTO `tbl_produits`(`code_produit`,`description`) VALUES('produit2', 'without \"description\" too');
INSERT INTO `tbl_produits`(`code_produit`,`description`) VALUES('produit3', '1\\3 more than before');
...
...
*********************************************************/

?>

[ironik]

function mysql_format($strTemp){
//Format sql sentence for insert
$bad_chars= array("\\", "'", "\"");
$good_chars = array("\\\\", "''", "\"\"");
return str_replace($bad_chars, $good_chars, $strTemp);
}

Just a formatting function to generate a safer SQL statement for inserting data.

function mysql_dump_table(){
[...]
}

A function that uses func_num_args to accept a variable number of arguments, the first of which is the table name, the second..third..fourth are field names to output. The function returns an array of generated insert statements.

$db = mysql_connect(DBTEST_HOST, DBTEST_USER, DBTEST_PASSWORD) or die("Impossible de se connecter : ");
mysql_select_db(DBTEST_NAME, $db);

$result = mysql_dump_table("tbl_produits", "code_produit", "description");

Pretty self explanatory, establishes a connection to the database and dumps the table based on arguments that are passed.

Is that what you were after? Code's a bit lengthy to give you a step by step run down.
?>