Page 1 of 1

PHP Foreach MySQL Table

Posted: Sun May 02, 2010 9:40 am
by Plxply
Hello,

I'm currently doing a small personal project which I hope to make me strengthen my PHP skills, although I've encountered an issue. At the current time I'm running a MySQL select query and echoing back the results which is working perfectly fine although, I wish to be able to change the content that is displayed within each cell. Here is my code:

Code: Select all

$con = mysql_connect("localhost","supersecretusername","supersecretdatabasepassword");
mysql_select_db("url", $con);
$query  = "SELECT URL, Short, Time FROM `url` WHERE UID = '$UID'";
$result = mysql_query($query,$con);
$fields_num = mysql_num_fields($result);

echo "<table border='1'><tr>";
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    echo "<td>{$field->name}</td>";
}
echo "</tr>\n";

while($row = mysql_fetch_row($result))
{
    echo "<tr>";

    foreach($row as $cell)
        echo "<td>$cell</td>";

    echo "</tr>\n";
}
At the current time URL will be storing a full valid URL such as http://www.google.com and as such I would just like to place it within a hyperlink e.g. <a href="http://www.google.com">http://www.google.com</a>. The short cell contains a randomly generated 5 digit code which I would like to link to my own page for example <a href="http://www.example.com/12345">12345</a> whereas at the current time it will only echo the 12345. Also finally in the time field I have the result of time() when the record was added and as such I would like to convert that with date() to a human readable time.

I know it's a large amount of help I require, although anything would be greatly appreciated!

Re: PHP Foreach MySQL Table

Posted: Sun May 02, 2010 11:23 pm
by Jonah Bron
Since you need custom handling on each column, just replace the foreach loop with each explicit format.

Code: Select all

$con =  mysql_connect("localhost","supersecretusername","supersecretdatabasepassword");
mysql_select_db("url", $con);
$query  = "SELECT URL, Short, Time FROM `url` WHERE UID = '$UID'";
$result = mysql_query($query,$con);
$fields_num = mysql_num_fields($result);

echo "<table border='1'><tr>";
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    echo "<td>{$field->name}</td>";
}
echo "</tr>\n";

while($row = mysql_fetch_row($result))
{
    echo "<tr>";

    /*replace foreach($row as $cell)
        echo "<td>$cell</td>";
    with */
    echo '<td><a href="'. $row[0] .'">URL</a></td>';
    echo '<td><a href="http://example.com/'. $row[1] .'">'. $row[1] .'</a></td>';
    echo '<td>'. date(DATE_RFC822, $row[2]) .'</td>';

    echo "</tr>\n";
}