Page 1 of 2

Create link in one field of table...

Posted: Sat Jun 23, 2007 8:58 pm
by audibel
I have two tables in mysql that I am accessing. I have one that contains a list of providers, with a list of links, and I have another table with a list of providers with information for the provider (fees, monthly cost, startup cost, etc.) I am trying to use the link with the first field of the html table below, but I am not quite sure how to go about this, without having to create the entire table row manually.

Code: Select all

for ($i=0;$i < mysql_num_rows($result);$i++)
     {
        echo "<tr>";
        $row = mysql_fetch_row($result);
        foreach($row as $value)
        {
    	    $provider_list="SELECT provider, outside_link FROM provider_list WHERE provider='".$value."'";		
  		 	$plist_query=mysql_query($provider_list);
		    $plist_row=mysql_fetch_row($plist_query);
			echo "<td class='style2' align='center'>";
	    	echo "<a href=$plist_row[1]>".$value."</a></td>";

        }
        echo "</tr>";
    }
Am I going to need to create the rows manually in order to make only one field in the row a link?

Posted: Sat Jun 23, 2007 9:03 pm
by feyd
I'm having a tough time understanding your problem, please explain more.

Posted: Sat Jun 23, 2007 9:14 pm
by audibel
I am sorry if I'm not making myself clear. I am new to mysql and php so I am learning as I go.

I have a table1

Code: Select all

Provider       Outside Link
Provider A    http://www.providera.com
Provider B    http://www.providerb.com
and a table2

Code: Select all

Provider       Fees   Monthly Cost   Startup Cost
Provider A    3.50    44.95              33.50
Provider B    2.50    59.95              31.50
I want to output table2 and use the Outside Link from table1 for the Provider field on table2.

I hope this makes my problem more clear.

Thank you

Posted: Sat Jun 23, 2007 9:19 pm
by superdezign
Then you'll want to select from both tables by joining them together.

Code: Select all

SELECT `table1`.`someColumn`, `table2`.`someOtherColumn` FROM `table1` LEFT JOIN (`table2`) ON (`table1`.`id` = `table2`.`id`) WHERE `table1`.`id` = 1;
You tell it the columns (and specify which table the column comes from), then the main table, then the table to join with the main one, then the condition to merge the two tables, and then the rest of your query.

Posted: Sat Jun 23, 2007 9:35 pm
by audibel
I don't actually want the link to print, I want to use the link in php to create a link from the first field in table2. My problem is with the php side. Your mySQL solution looks better than mine most definately, but the php is the part that I want to correct, because I can't see how I can make just the first field in the result html table a link without dropping the foreach statement, and entering each field from the mysql_fetch_row. If that's what I have to do, then that's okay, I just thought there may be a better way.

The code, as it stands, makes the entire html table row links (only the first row is a valid link).


Thank you for your help, and your patience.

Posted: Sat Jun 23, 2007 9:44 pm
by superdezign
I've re-read your posts three times and still don't know what you're doing. Maybe if you explained why instead of what, it'd be clearer because so far, it's not making sense.

Posted: Sat Jun 23, 2007 9:51 pm
by Benjamin
Are you wanting to create a link to another page on your site which displays the details for that record?

If so, create a link with the record ID

Code: Select all

<a href="show_record.php?record_no=<?php echo $blah['record_no']; ?>" >View Record</a>

Posted: Sat Jun 23, 2007 9:51 pm
by audibel
Allright, the reason I want the first field of the html table (Provider A, Provider B, Provider C) to be links, is to have Provider A link to Provider A's website, but still be part of the html table which displays information about that provider.

The table with the provider information (setup cost, etc) has several entries for each provider, the table that contains the outside links has only one entry for each provider. I'm looking for something similar to this in pseudo code:

Code: Select all

foreach($row as $field)
if $field = $provider
   echo "<a href=$outside_link>$provider</a>";
else
   echo $provider;
Could this be done with my current foreach($row as $value) statement

Posted: Sat Jun 23, 2007 9:56 pm
by superdezign
That's what I thought. You need to join the tables. And you have way too many database queries. Ideally, you want to get everything that you need in one query, and then display it.

Posted: Sat Jun 23, 2007 9:59 pm
by audibel
So I would need to handle each field seperately as opposed to fetching the row and displaying with foreach in order to create the link?

And the less you have to do server side the better right? I knew this was extremely inefficient code, but am not yet well versed enough in either php or mysql to think of a better way. Thank you.

Posted: Sat Jun 23, 2007 10:11 pm
by superdezign

Code: Select all

$result = mysql_query("SELECT `table1`.`provider` AS `provider`, `table1`.`outside_link`, `table2`.`fees`, `table2`.`monthly`, `table2`.`startup` FROM `table1` LEFT JOIN(`table2`) ON(`table1`.`provider` = `table2`.`provider`);");

echo '<table><th>Provider</th><th>Fees</th><th>Monthly</th><th>Statup</th>';
while($data = mysql_fetch_object($result))
{
    echo '<tr>';
    echo '<td><a href="' . $data->outside_link . '">' . $data->provider . '</a></td>';
    echo '<td>' . $data->fees . '</td>';
    echo '<td>' . $data->monthly . '</td>';
    echo '<td>' . $data->startup . '</td>';
    echo '</tr>';
}
echo '</table>';
Think about it like that. You don't need to only get one at a time. Try it and you'll see what I mean.

Posted: Sat Jun 23, 2007 10:18 pm
by audibel
That's exactly what I am wanting to do, thank you for the patience in understanding the incoherent babbling. My table has quite a few columns in it, so I wasn't sure if I could do it without making one <td></td> entry for each column, but I don't guess that it's possible, considering I want to take two values in one pass and one each on the rest. Or, could I use two mysql queries, one for provider and outside link, and one for the rest of the fields, nesting while statements... My mind reels. I'll just do it this way and be done.

Posted: Sat Jun 23, 2007 10:23 pm
by Benjamin
Forgot some <tr> tags there bucko.

Code: Select all

<?php
$result = mysql_query("SELECT t1.outside_link, t2.fees, t2.monthly, t2.startup FROM table1 t1, table2 t2 WHERE t1.provider = t2.provider")
?>
<table>
  <tr>
    <th>Provider</th>
    <th>Fees</th>
    <th>Monthly</th>
    <th>Statup</th>
  </tr>
<?php
while($data = mysql_fetch_assoc($result))
{
    ?>
    <tr>
      <td><a href="<?php echo $data['outside_link']; ?>"><?php echo $data['provider']; ?></a></td>
      <td><?php echo $data['fees']; ?></td>
      <td><?php echo $data['monthly']; ?></td>
      <td><?php echo $data['startup']; ?></td>
    </tr>
    <?php
}
?>
</table>

Posted: Sat Jun 23, 2007 11:35 pm
by superdezign
You don't typically add <tr> tags to table headers. Maybe a <thead> tag, but the <tr> can easily be omitted because the head of a table should only be one row. That's why they get their own element, <th>.

Posted: Sun Jun 24, 2007 12:01 am
by Benjamin
superdezign wrote:You don't typically add <tr> tags to table headers. Maybe a <thead> tag, but the <tr> can easily be omitted because the head of a table should only be one row. That's why they get their own element, <th>.
Strange because:

Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
  <title>Validate Me</title>
</head>
<body>
  <table>
    <th>Head One</th>
    <th>Head Two</th>
    <tr>
      <td>Cell One</td>
      <td>Cell Two</td>
    </tr>
  </table>
</body>
</html>
Does not validate:
http://validator.w3.org/check wrote:
This page is not Valid XHTML 1.0 Transitional!

Below are the results of checking this document for XML well-formedness and validity.

1. Error Line 8 column 7: document type does not allow element "th" here; assuming missing "tr" start-tag.

<th>Head One</th>


2. Error Line 10 column 7: document type does not allow element "tr" here.

<tr>

The element named above was found in a context where it is not allowed. This could mean that you have incorrectly nested elements -- such as a "style" element in the "body" section instead of inside "head" -- or two elements that overlap (which is not allowed).

One common cause for this error is the use of XHTML syntax in HTML documents. Due to HTML's rules of implicitly closed elements, this error can create cascading effects. For instance, using XHTML's "self-closing" tags for "meta" and "link" in the "head" section of a HTML document may cause the parser to infer the end of the "head" section and the beginning of the "body" section (where "link" and "meta" are not allowed; hence the reported error).


3. Error Line 14 column 9: end tag for "tr" omitted, but OMITTAG NO was specified.

</table>

You may have neglected to close an element, or perhaps you meant to "self-close" an element, that is, ending it with "/>" instead of ">".


4. Info Line 8 column 4: start tag was here.

<th>Head One</th>

And when I look on http://www.w3schools.com/tags/tag_th.asp it clearly indicates that the table header tags get placed inside of a table row.

Maybe I'm looking in the wrong places?