Making Hyperlinks from SQL Queries

jwoo

Ok I need to make a hyperlink from a current date mySQL query.

Here is the php code.

$result = mysql_query("SELECT * FROM modems WHERE instdate = curdate() ORDER BY jobnum;",$db);

if ($myrow = mysql_fetch_array($result)) {

  echo "<table border=1>\n";

  echo "<tr><td>Job Number</td><td>Customer</td><td>Address</td></tr>\n";

  do {



printf("<tr><td>%s</td><td>%s</td><td>%s</tr>\n", $myrow["jobnum"], $myrow["custname"], $myrow["custadd"]);

  } while ($myrow = mysql_fetch_array($result));

    echo "</table>\n";

} else {

    echo "Sorry, no records were found!";

What I want to do is make the Job Number a hyperlink to jump to a page that will query the database again to edit that job. So in a sense the hyperlink will take the user to a new page and query the database again using that $jobnum variable.

Thanks.

LordShryku

Bear with me, I'm going to change this around a bit. This is the way I would do it:

$result = mysql_query("SELECT * FROM modems WHERE instdate = curdate() ORDER BY jobnum;",$db);
if(mysql_num_rows($result) > 0) {
  echo "<table border=\"1\">
          <tr>
            <td>Job Number</td>
            <td>Customer</td>
            <td>Address</td>
          </tr>";
   while($myrow = mysql_fetch_array($result)) {
      echo "<tr>
              <td><a href=\"page.php?jobnum=".$myrow['jobnum']."\">".$myrow['jobnum']."</a></td>
              <td>".$myrow['custname']."</td>
              <td>".$myrow['custadd']."</td>
            </tr>";
   }
   echo "</table>\n";
} 
else {
   echo "Sorry, no records were found!";
}

Actually, I would close php to do the HTML parts, since I really don't like the way echoing HTML can screw up the layout of the page, but you get the idea

shpayer

LordShryku,

Could you post an example of how you would close out the PHP part to preserve the HTML layout? I was just working on something like this tonight and felt like I wasn't quiet doing things the best way. I used echo to create my tables so if there's a way to not have to do that, I'd love to see an example.

I'm just transitioning from ColdFusion to PHP so any help would be greatly appreciated.

Thx.

LordShryku

Good transition 😃
I've converted a few Colfusioners to PHP too. They all like it much better. Here's the example of a full page with the table. I've become quite a stickler with the layout of my html, so I keep it as neat as possible.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Blah</title>
</head>
<body>
<?php
$connect = mysql_connect("","","");
           mysql_select_db("blah",$connect);

$sql = "SELECT * 
          FROM modems 
         WHERE instdate = curdate() 
      ORDER BY jobnum";

$result = mysql_query($sql);

if(mysql_num_rows($result) > 0) {
?>
<table border="1">
  <tr>
    <td>Job Number</td>
    <td>Customer</td>
    <td>Address</td>
  </tr>
<?php
   while($myrow = mysql_fetch_array($result)) {
?>
  <tr>
    <td><a href="page.php?jobnum=<?php echo $myrow['jobnum'] ?>"><?php echo $myrow['jobnum']?></a></td>
    <td><?php echo $myrow['custname']?></td>
    <td><?php echo $myrow['custadd']?></td>
  </tr>
<?php
   }
?>
</table>
<?php
} 
else {
?>
<p>Sorry, no records were found!</p>
<?php
}
mysql_free_result($result);
mysql_close($connect);
?>
</body>
</html>

Since it's procedural, it'll only show what fits. So if there's rows, it's show the table with all o the results, otherwise, it will just show the no records found message.

HTH

shpayer

Sweet!!! 😃

I knew I was busting my head for nothing. I kept looking at my code and how I had echo'd the whole table and thought, "I never had to do this in CF! This is oogly! This stinks!!"

That's much cleaner. I'm glad I asked.

In terms of transitioning, PHP will be another offerring for my clients but it may turn out to be the main programming language I use going forward. I love CF but PHP is as, if not more, effective a solution than CF and more cost effective overall. Hosting is substantially cheaper and as of late, more clients are asking if I can do my work in PHP. Finally, the PHP community is unbelievable. Your help was evidence of that.

LordShryku, thanks for helping out a newbie bud. It is much appreciated. Hope to see you on the forum again.

SHP....