Create link in one field of table...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

audibel
Forum Newbie
Posts: 7
Joined: Sat Jun 23, 2007 8:52 pm
Location: Evansville, IN

Create link in one field of table...

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I'm having a tough time understanding your problem, please explain more.
audibel
Forum Newbie
Posts: 7
Joined: Sat Jun 23, 2007 8:52 pm
Location: Evansville, IN

Post 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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
audibel
Forum Newbie
Posts: 7
Joined: Sat Jun 23, 2007 8:52 pm
Location: Evansville, IN

Post 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.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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>
audibel
Forum Newbie
Posts: 7
Joined: Sat Jun 23, 2007 8:52 pm
Location: Evansville, IN

Post 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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
audibel
Forum Newbie
Posts: 7
Joined: Sat Jun 23, 2007 8:52 pm
Location: Evansville, IN

Post 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.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
audibel
Forum Newbie
Posts: 7
Joined: Sat Jun 23, 2007 8:52 pm
Location: Evansville, IN

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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>
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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>.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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?
Post Reply