Page 1 of 1

Mysql php displaying a table

Posted: Wed Apr 28, 2010 4:08 pm
by Peuplarchie
Good day to you all,
I finally made the switch between txt based database to real database , i'm using mysql.


I'm working on a table display and I cannot find a solution to 2 of my questions:


1. how can I make the first td of each row to have no border on the left as well as the last td of the row to have a right border ?

2. How can I auto generate the first field to so I can add my delete option ?

Here is my code so far:

Code: Select all

<?php
$db_host = 'localhost';
$db_user = 'peuplarc_sports';
$db_pwd = 'sports!123';

$database = 'peuplarc_sports';
$table = 'NHL_GBG_PLAYERS';

if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");

// sending query
$result = mysql_query("SELECT * FROM {$table}");
if (!$result) {
    die("Query to show fields from table failed");
}

$fields_num = mysql_num_fields($result);

echo "<h1>Table: {$table}</h1>";
echo "<table border='0' id=\"table\" cellspacing=\"0\" cellpadding=\"0\" align=\"center\"><tr>";
echo "<td id=\"title\">options</td>";
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    
    $field = mysql_fetch_field($result);
    echo "<td id=\"title\">{$field->name}</td>";
}
echo "</tr>\n";
// printing table rows
$s = 0;
while(($row = mysql_fetch_row($result)) !== false){
	$s++;
	
    echo "<tr>";
    echo "<td id=\"fields".($s & 1)."\"><a href=\"delete_table_row_db.php?id=" . $row . "\" title=\"DELETE : Row ID #:" . $row . "\"><img src=\"Template/Images/delete.png\" border=\"0\"/></a><INPUT TYPE=\"checkbox\" name=\"" . $row['id'] . "\"></td>";

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $cell)
        echo "<td id=\"fields".($s & 1)."\">$cell</td>";

    echo "</tr>\n";
}
mysql_free_result($result);
?>

Thank and have a great day !

Re: Mysql php displaying a table

Posted: Wed Apr 28, 2010 4:53 pm
by social_experiment
1. how can I make the first td of each row to have no border on the left as well as the last td of the row to have a right border ? It should be possible with css. You give each of the first <td> cells a different id (or class) and then change the border settings (in the stylesheet). I think you might also have to do the table's border with css as well because the setting you have currently (border='0') might overwrite whatever you have in the stylesheet.

2. How can I auto generate the first field to so I can add my delete option ?

Code: Select all

"><?php <a href=\"delete_table_row_db.php?id=" . $row . "\" title=\"DELETE : Row ID #:" . $row . "\"><img src=\"Template/Images/delete.png\" border=\"0\"/></a><INPUT TYPE=\"checkbox\" name=\"" . $row['id'] . "\"> ?>
Are you using the link or a form to do proceed to the delete action? If you are using the link (delete_table_row_db.php?id=value) you can remove the checkbox option as it serves no purpose apart from taking up a <td> cell.

Re: Mysql php displaying a table

Posted: Wed Apr 28, 2010 5:15 pm
by mecha_godzilla
If you want to apply different border settings to individual <td> tags, I suggest you use something like:

Code: Select all

<td id="id_of_this_table" style="border-right: 1px solid #F7C997; border-top: 1px solid #F7C997; border-bottom: 1px solid #F7C997;">
For easy maintenance, it's probably better if you define a custom class and put these kind of settings in a style sheet. As you've already done in your script, you'll need to escape some of the special characters used in that code. To avoid having to escape all the quote marks, I tend to use a single quote to enclose the string of HTML that I want to generate, like this:

Code: Select all

<?php
	$html_example = '<p class="introductory_text">Text goes here<br /><img src="image.jpg" width="40" height="40 /></p>';
?>
If you don't know what the difference is between using single and double quotes, it's that if you use single quotes then the values aren't interpreted inside it. For example, if you wanted to echo a value called $result in the HTML you'd either need to use

Code: Select all

echo '<p class="result_style">' . $result .  '</p>';
or

Code: Select all

echo "<p class=\"result_style\">$result</p>
The only 'gotcha' with using single quotes is that it can't handle special characters like \r \n \t (return, new line and tab) because it isn't interpreting them, so if you want to output a line break for any reason (such as if you're generating mail headers) you need to use something like:

Code: Select all

echo 'From: ' . $email_address . "\r\n";
With regard to your second question, I didn't quite understand what you meant - do you want to only generate the delete option once, or only make it appear under certain conditions? If you only want the option to appear once then the script needs to remember when it's generated it once and then not do it again - the way to do this would be to have a value that is set to zero (0) before that part of the script runs, then when you get into that part of the script you do a test to see if it is set to zero and if so, add 1 to the counter and carry on executing the rest of the script. The code looks something like this:

Code: Select all

$counter = 0;
	
for ($i = 1; $i <= 10; $i++) {

	if ($counter == 0) {
		echo '<p>This message should only appear once</p>';
		$counter++;
	}
		
	echo '<p>This message should appear 10 times</p>';
	
}
HTH,

Mecha Godzilla