Using a record for a link

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

Moderator: General Moderators

Post Reply
darren_plehn
Forum Newbie
Posts: 11
Joined: Tue Oct 06, 2009 8:48 pm

Using a record for a link

Post by darren_plehn »

Any help is appreciated! The user enters data into the database including a url and a name for that URL. For example: One might enter http://www.google.com for the URL and then "Visit Google" for the name. Once queried I want "Visit Google" to show up and when clicked it will take them to the link. I'm really close I know it. So far the code below produces what I need except rather than showing the Name it shows the link and then links the same. I know I need to define a new variable that is the Name, but I'm not sure how to do it. So the code below produces <a href="http://www.google.com">http://www.google.com</a>

Code: Select all

<html>
 
<head>
<title></title>
</head>
 
<body>
<?php
    
    
$con = mysql_connect('localhost','username','password');
if (!$con){
  die('Could not connect: ' . mysql_error());
}
 
 
$varlength = mysql_real_escape_string($_POST['varlength']);
$varwidth = mysql_real_escape_string($_POST['varwidth']);
$vardepth = mysql_real_escape_string($_POST['vardepth']);
 
 
 
mysql_select_db("database", $con); //Replace with your MySQL DB Name
 
if(isset($_POST["varmargin"]))
  $selection = $_POST["varmargin"];
else
  $selection = null;
switch ($selection)
{
case "0":
  $query = "SELECT Link, Length, Width, Depth, Product, Description FROM allcases WHERE ($varlength) <= Length AND Length <= ($varlength + 0) AND ($varwidth) <= Width AND Width <= ($varwidth + 0) AND ($vardepth) <= Depth AND Depth <= ($vardepth + 0) ORDER BY Length ASC";
  break;
  case "All":
  $query = "SELECT Link, Length, Width, Depth, Product, Description FROM allcases ORDER BY Length ASC";
  break;
default:
  echo 'Nothing to do here';
  
}
 
$q = mysql_query($query)
  or die("This is not a valid query: " . $query);
 
$fieldCount = mysql_num_fields($q);
 
echo "<b>Search Cases by Dimension</b>.<br />
All the cases below match your search in order of accuracy<br />
Length= Left to Right, Width= Front to Back, Depth= Top to Bottom<br /><br />
<table border='1'>
<tr>";
 
// print table headers
for($i=0; $i<$fieldCount; $i++)
{
    $field = mysql_fetch_field($q);
    echo "<th>{$field->name}</th>";
}
echo "</tr>\n";
 
// Print table rows
while($record = mysql_fetch_row($q))
{
    echo "<tr>";{
[b]        foreach ($record as $field => $cell) {
                if ($field == "Link") { 
                        echo "<td><a href=$cell>$cell</a></td>"; 
                }
                else { 
                        echo "<td>$cell</td>";
                }[/b]
        }
}       
    echo "</tr>";
}
echo "</table>";
 
mysql_close($con);
 
?>
 
 
</body>
 
</html>
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Using a record for a link

Post by califdon »

The reason it's doing that is that you are using the same field for the href= and the name to display, namely:

Code: Select all

echo "<td><a href=$cell>$cell</a></td>";
Is there a reason that you have to look for the fields "in the blind" like that? If you don't know which fields are which, you're probably not going to be getting the desired data anyway! In order to do what you want to do, you have to have a field for the URL and another field for the "name" and you need to know what those fieldnames are. Then you won't have to go through that foreach() loop, you just use the fieldname as the index for the row array. I'm not sure just what you are trying to do, with regard to the database.
darren_plehn
Forum Newbie
Posts: 11
Joined: Tue Oct 06, 2009 8:48 pm

Re: Using a record for a link

Post by darren_plehn »

Thanks for the response. I'm really new to PHP, so my apologizes. I attached a screen capture of the table being produced. This might give you more of an idea what I'm trying to do. I thought I would have to use the foreach statement because each row will have a different link. Ideally I want the column "Product" to be the name and of course the link to be its' link. (Once I get it working I wont actually show the "Product" column because it will be shown in the link column. Does that make sense.
Thanks again for your help.
Attachments
Query Example
Query Example
dimension-test.jpg (51.21 KiB) Viewed 1232 times
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Using a record for a link

Post by califdon »

No apologies necessary, there's nothing wrong with being new to PHP. Right offhand, I can't think of anyone I know who wasn't new to it at some point! :)

In database work, it helps if you always start with your data and how it is stored. It appears that your data is stored in a table with these fields:

Code: Select all

[b]allcases[/b]:
   Link         varchar
   Length       int (or double?)
   Width        int   "
   Depth        int   "
   Product      varchar
   Description  varchar
I'm a bit troubled that you don't have an obvious primary key field, but that won't affect this discussion.

Given that, your code only needs to access each row in the query and use the fields you want to display, like this:

Code: Select all

...
<?php
...
// Print table rows
while($record = mysql_fetch_assoc($q))
{
    echo "<tr><td><a href='".$record['Link']."'>".$record['Product']."</a></td>";
    echo "<td>".$record['Length']."</td><td>".$record['Width']."</td>";
    echo "<td>".$record['Depth']."</td><td>".$record['Description']."</td></tr>";
}
echo "</table>";
...
?>
Notice that I use a similar, but slightly different mysql_fetch function, mysql_fetch_assoc, which results in an associative array indexed by fieldname. The same result can be obtained with mysql_fetch_row, but I think this is clearer. It can even be further simplified by using the extract($record) function, which assigns all the row values to appropriately named variables, but I don't want to confuse the underlying issue here. It is that you should use the names of the fields in the tables, not cycle through all of them, looking to find the one you want.

You can read about mysql_fetch_assoc() at http://www.php.net/manual/en/function.m ... -assoc.php
darren_plehn
Forum Newbie
Posts: 11
Joined: Tue Oct 06, 2009 8:48 pm

Re: Using a record for a link

Post by darren_plehn »

Oh, I get it!! It keeps it simpler too... when you specify how each row should be displayed rather than that loop. Such a simple solution and it works perfectly. I really can't thank you enough for helping me. :D
On a side note me or anyone else could easily use your solution for displaying tables however they'd like using a number of variations on your solution. Very helpful post in general! Thanks again!!
-Darren
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Using a record for a link

Post by califdon »

You're welcome. That's the standard way of displaying data from a query. Now that you have that, I'll go ahead and show you the even simpler technique I referred to earlier, using the PHP function extract([array name]), which goes through an associative array and assigns values to PHP variables with the same names as the array index names.

Code: Select all

while($row=mysql_fetch_assoc($queryresult)) {
    extract($row);  // <-- assigns values to variables named the same as fields in the array $row
    echo "<tr><td>$Link</td><td>$Length</td><td>$Width</td><td>Depth</td><td>Description</td></tr>";
}
echo "</table>";
This way, you can immediately refer to variables, like $Link, which can then be inserted directly in double-quoted strings. PHP won't evaluate functions or array elements within double-quoted strings, requiring you to end the string and concatenate with periods. This is just simpler to write and read.
darren_plehn
Forum Newbie
Posts: 11
Joined: Tue Oct 06, 2009 8:48 pm

Re: Using a record for a link

Post by darren_plehn »

Your right. It's much easier to understand the variable solution after understanding the first solution though. Thanks.
Post Reply