Page 1 of 1

Printing Table via MYSQL with <a href> in first column.

Posted: Thu Dec 02, 2010 10:01 am
by dbreaka
Here's a snapshot of the MYSQL Database fields i'll be using:

Image

I'm using the following code to print a Table:

Code: Select all

<?php

//Connect to MySQL Host
mysql_connect("localhost","root","root") or die(mysql_error());
echo "Connected to MySQL Server<br /><hr />";

//Connect to the  Database
$db_selected = mysql_select_db("invoicedistribution");
if (!$db_selected) {
    die(mysql_error("DB Connection Error"));
}
echo "Connected to MySQL Database<br /><hr />";

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

$fields_num = mysql_num_fields($result);

echo "<h1>Table: {$table}</h1>";
echo "<table border='1'><tr>";
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    echo "<td>{$field->name}</td>";
}
echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
    echo "<tr>";

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $cell)
        echo "<td>$cell</td>";

    echo "</tr>\n";
}
mysql_free_result($result);
?>
In the first column 'MobileNumber', it'll display a bunch of numbers with it's respective information in the columns that follow.

2121111111
2122222222
2123333333
2124444444

In the same folder that the *.php file is located, the following files are in there as well:

2121111111.pdf
2122222222.pdf
2123333333.pdf
2124444444.pdf

I want to make links out of the first column to it's respective PDF file, but unsure how to modify my script to do this.

Re: Printing Table via MYSQL with <a href> in first column.

Posted: Thu Dec 02, 2010 10:34 am
by curlybracket
I'm not 100% sure but try this:
replace:

Code: Select all

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

Code: Select all

foreach($row as $key => $value)
{
  if($key == 'MobileNumber')
  {
    echo '<td>'.$value.'.pdf</td>';
  } else { echo "<td>$value</td>"; }
}

Re: Printing Table via MYSQL with <a href> in first column.

Posted: Thu Dec 02, 2010 10:46 am
by klevis miho
Try to replace this:

echo "<td>{$field->name}</td>";

with this:

echo '<td><a href="'.$field->name.'.pdf">'.$field->name.'</a></td>';

Re: Printing Table via MYSQL with <a href> in first column.

Posted: Thu Dec 02, 2010 10:52 am
by curlybracket
@klevis miho: this will change every column.

Re: Printing Table via MYSQL with <a href> in first column.

Posted: Thu Dec 02, 2010 11:00 am
by klevis miho
Ok this will make the first a link:

Code: Select all

$first = true;
for($i=0; $i<$fields_num; $i++)
{
    if($first = true) {
echo '<td><a href="'.$field->name.'.pdf">'.$field->name.'</a></td>';
} else {
    $field = mysql_fetch_field($result);
    echo "<td>{$field->name}</td>";
}

$first = false;
}

Re: Printing Table via MYSQL with <a href> in first column.

Posted: Thu Dec 02, 2010 11:16 am
by dbreaka
I'm getting the following now:

Image

Here's the code i'm using:

Code: Select all

<?php

//Connect to MySQL Host
mysql_connect("localhost","root","root") or die(mysql_error());
echo "Connected to MySQL Server<br /><hr />";

//Connect to the  Database
$db_selected = mysql_select_db("invoicedistribution");
if (!$db_selected) {
    die(mysql_error("DB Connection Error"));
}
echo "Connected to MySQL Database<br /><hr />";

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

$fields_num = mysql_num_fields($result);

echo "<h1>Table: {$table}</h1>";
echo "<table border='1'><tr>";
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    echo "<td>{$field->name}</td>";
}
echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
    echo "<tr>";

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $cell)
        echo "<td>$cell</td>";
		$first = true;
for($i=0; $i<$fields_num; $i++)
{
    if($first = true) {
echo '<td><a href="'.$field->name.'.pdf">'.$field->name.'</a></td>';
} else {
    $field = mysql_fetch_field($result);
    echo "<td>{$field->name}</td>";
}

$first = false;
}

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

Re: Printing Table via MYSQL with <a href> in first column.

Posted: Thu Dec 02, 2010 11:21 am
by dbreaka
Ok, fixed it up but looks like this now:

Image

Here's the code:

Code: Select all

<?php

//Connect to MySQL Host
mysql_connect("localhost","root","root") or die(mysql_error());
echo "Connected to MySQL Server<br /><hr />";

//Connect to the  Database
$db_selected = mysql_select_db("invoicedistribution");
if (!$db_selected) {
    die(mysql_error("DB Connection Error"));
}
echo "Connected to MySQL Database<br /><hr />";

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

$fields_num = mysql_num_fields($result);

echo "<h1>Table: {$table}</h1>";
echo "<table border='1'><tr>";
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    echo "<td>{$field->name}</td>";
}
echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
    echo "<tr>";

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
for($i=0; $i<$fields_num; $i++)
{
    if($first = true) {
echo '<td><a href="'.$field->name.'.pdf">'.$field->name.'</a></td>';
} else {
    $field = mysql_fetch_field($result);
    echo "<td>{$field->name}</td>";
}

$first = false;
}

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

Re: Printing Table via MYSQL with <a href> in first column.

Posted: Thu Dec 02, 2010 11:50 am
by curlybracket
You are displayin $field->name which is just a table column name. You should display data from $row instead.

Re: Printing Table via MYSQL with <a href> in first column.

Posted: Thu Dec 02, 2010 2:22 pm
by dbreaka
curlybracket wrote:You are displayin $field->name which is just a table column name. You should display data from $row instead.
Thanks. it works now. This is perfect!

Here's the new code:

Code: Select all

<?php

//Connect to MySQL Host
mysql_connect("localhost","root","root") or die(mysql_error());
echo "Connected to MySQL Server<br /><hr />";

//Connect to the  Database
$db_selected = mysql_select_db("invoicedistribution");
if (!$db_selected) {
    die(mysql_error("DB Connection Error"));
}
echo "Connected to MySQL Database<br /><hr />";

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

$fields_num = mysql_num_fields($result);

echo "<h1>Table: {$table}</h1>";
echo "<table border='1'><tr>";
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    echo "<td>{$field->name}</td>";
}
echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
    echo "<tr>";

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
foreach($row as $key => $value)
	{
	if($key == 'MobileNumber')
		{
		echo '<td><a href="/'.$value.'.pdf">'.$value.'</a></td>';
		} 
	else 
		{
		echo "<td>$value</td>";
		}
	}
}
    echo "</tr>\n";

mysql_free_result($result);
?>