dynamic table display function problems

[clavain]

Hello all,

 

I am trying to create a catalogue of all the DVD's and music I have collected over the years, it might be re-inventing the wheel a bit but its also helping to teach me php.

 

I want to create a function where I enter a few details; the table name, which fields I want to view, what I want to the column headers to be etc. and the function creates a stylised table in html and css. I just wanted to have one function and have it pull the data out dynamically so I wouldnt have to copy the same function and just change a few things for different tables.

 

I have posted my code below. Example 1 is the code which I am currently using to display one table, its loads the data relativly fast and works fine but would mean copying the function for each new kind of media i.e. DVD, CD, BlueRay etc.

 

Example 2 is my attempt at making it dynamic, the problem was it resulted in it being extremely slow!

 

I could well be going about it completley wrong so if theres a better way of doing it or a solution to my problem it would be much appreciated.

 

Thanks.

 

 

Example 1:

 

function getMoviesList($order) {
$thisPage = $_SERVER["PHP_SELF"];

//create table column headers
$data = "<table width=\"920\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\" class=\"resultsTable\">\n"
."<tr>\n"
."  <td class=\"resultsHeader\" width=\"85\" align=\"center\" valign=\"middle\"><a href=\"$thisPage?type=retail_games&sort=disc_num$letterlink\">Disc #</a></td>\n"
."  <td class=\"resultsHeader\" width=\"575\" align=\"center\" valign=\"middle\"><a href=\"$thisPage?type=retail_games&sort=title$letterlink\">Title</td>\n"
."  <td class=\"resultsHeader\" width=\"50\" align=\"center\" valign=\"middle\"><a href=\"$thisPage?type=retail_games&sort=num_discs$letterlink\"># Discs</td>\n"
."  <td class=\"resultsHeader\" width=\"86\" align=\"center\" valign=\"middle\"><a href=\"$thisPage?type=retail_games&sort=disc_type$letterlink\">Media</td>\n"
."  <td class=\"resultsHeader\" width=\"25\" align=\"center\" valign=\"middle\"> </td>\n"
."</tr>\n";

openDB();

	$query="SELECT id, disc_num, num_discs, title, disc_type, format FROM movies ORDER BY $order;";

	$result = mysql_query($query) or die (mysql_error());
	for ($i = 0; $i < mysql_numrows($result); $i++) {

		//change row colour depending on odd or even
		if($i/2 == round($i/2)){
			$c = "class=\"resultsOdd\"";
		}else{
			$c = "class=\"resultsEven\"";
		}

		$id = mysql_result($result,$i,"id");
		$disc_num = mysql_result($result,$i,"disc_num");
		$num_discs = mysql_result($result,$i,"num_discs");
		$title = mysql_result($result,$i,"title");
		$disc_type = mysql_result($result,$i,"disc_type");
		$data = $data."<tr id=\"row-$id\">\n"
		." <td $c align=\"center\" valign=\"middle\" height=\"25\"><div id=\"disc_num-$id\">$disc_num</div></td>\n"
		." <td $c align=\"left\" valign=\"middle\" height=\"25\"><div id=\"title-$id\">$title</div></td>\n"
		." <td $c align=\"center\" valign=\"middle\" height=\"25\"><div id=\"num_discs-$id\">$num_discs</div></td>\n"
		." <td $c align=\"center\" valign=\"middle\" height=\"25\"><div id=\"disc_type-$id\">$disc_type</div></td>\n"
		." <td $c align=\"center\" valign=\"middle\" width=\"25\" height=\"25\"><div id=\"button-$id\"><a href=\"Javascript: updateMovie('$id');\"><img src=\"images/edit_small.gif\" border=\"0\"></a></div></td>\n"
		."</tr>\n";
	}
$data = $data."</table>\n";
return $data;
}

 

 

Example 2:

 

$table_fields = array(0 => "id", 1 => "disc_num", 2 => "title", 3 => "num_discs", 4 => "format", 5 => "disc_type");
$field_titles = array(1 => "Disc #", 2 => "Title", 3 => "# Discs", 4 => "Format", 5 => "Media");
$field_width = array(1 => "85", 2 => "575", 3 => "55", 4 => "85", 5 => "85");

 

function getAnyList($table_name, $pretty_name, $table_fields, $field_width, $field_titles, $result_order, $search_keyword) {
$thisPage = $_SERVER["PHP_SELF"];


openDB();

//create table header
$results_header = "<table width=\"920\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\" class=\"resultsTable\">\n"
." <tr>\n";

foreach ($field_titles as $i => $value) {
	$results_header = $results_header."  <td class=\"resultsHeader\" width=\"".$field_width[$i]."\" align=\"center\" valign=\"middle\"><a href=\"$thisPage?type=".$table_name."&sort=".$table_fields[$i]."\">".$value."</a></td>\n";
}
$results_header = $results_header."</tr>\n";

//get list of needed fields
foreach ($table_fields as $i => $value) {
	$table_field_list = $table_field_list.$value.", ";
}
$table_field_list = rtrim($table_field_list, ", ");

//search or get all results
if ($search_keyword) {
	$query="SELECT ".$table_field_list." FROM movies WHERE title LIKE '%".$search_keyword."%' ORDER BY $result_order;";
} else {
	$query="SELECT ".$table_field_list." FROM movies ORDER BY $result_order;";
}
$result = mysql_query($query) or die ($query);
$r_total = mysql_numrows($result);


for ($i = 0; $i < $r_total; $i++) {

	//check for odd or even
	if($i/2 == round($i/2)){
		$c = "class=\"resultsOdd\"";
	}else{
		$c = "class=\"resultsEven\"";
	}
//get id of row
$id = mysql_result($result,$i,"id");

$row_data = $row_data." <tr id=\"row-$id\">\n";

foreach ($table_fields as $x => $value) {
	if ($value != "id") {
		//$field_value = mysql_result($result,$i,$value);
		$row_data = $row_data."  <td $c align=\"center\" valign=\"middle\" height=\"25\"><div id=\"".$value."-".$id."\">".$field_value."</div></td>\n";
	}
}

$row_data = $row_data."  <td $c align=\"center\" valign=\"middle\" width=\"25\" height=\"25\"><div id=\"button-$id\"><a href=\"Javascript: update".$pretty_name."('$id');\"><img src=\"images/edit_small.gif\" border=\"0\"></a></div></td>\n"
." </tr>\n";


}

$results_footer = "</table>\n";

$data = $results_header.$row_data.$results_footer;
return $data;
}