Solved

Creating A Dynamic Link

Posted on 2013-01-31
17
378 Views
Last Modified: 2013-02-01
I have a MySQL table that is being generated via PHP.  The table is dynamic so I have no idea what restaurant will be in it.  The columns are always the same, but the individual restayrants that will be in the table will always be different.  How can I make a link, so that when athe user clicks on the restaurant name it goes to that restaurants page?  Here iis the php that generates the table.

<?php
			include("config.php");
				if(!$rs = mysql_query("SELECT tblRestaurants.RestName, tblLocations.CityID,
				tblLocations.AreaID, tblLocations.CuisineID, 		
				CONCAT(tblLocations.StreetNumber,' ', tblLocations.Street) Address,
				tblLocations.Phone, tblDetails.Price, tblDetails.Rating
				FROM tblRestaurants INNER JOIN (tblLocations LEFT JOIN tblDetails
				ON tblLocations.LocationID = tblDetails.LocationID) 				
				ON tblRestaurants.RestID = tblLocations.RestID
				WHERE tblLocations.CityID='16'
				AND tblLocations.AreaID='131'
				AND tblLocations.CuisineID='3'
				ORDER BY tblRestaurants.RestName ASC")) {
					echo "Cannot parse query";
				}
				elseif(mysql_num_rows($rs) == 0) {
					echo "No records found";
				}
				else {
					echo "<table id=\"myTable\" table width=\"720\" class=\"tablesorter\" align=\"Left\" cellspacing=\"0\">\n";
					echo "<thead>\n<tr>";
					echo "<th>PLACE</th>";
					echo "<th>ADDRESS</th>";
					echo "<th>PHONE</th>";
					echo "<th>PRICE</th>";
					echo "<th>RATING</th>";
					echo "</tr>\n</thead>\n";
					while($row = mysql_fetch_array($rs)) {
					echo"<tr><td>$row[RestName]</td><td>$row[Address]</td><td>$row[Phone]</td><td>$row[Price]</td><td>$row[Rating]</td></tr>\n";
					}
					echo "</table><br />\n";
				}
        ?>

Open in new window


Thank you.
0
Comment
Question by:DS928
  • 8
  • 3
  • 3
  • +1
17 Comments
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Is the MySQL table being created dynamically or is a table being created dynamically from the database?

It is not clear what you are asking?

If you are generating a table from the database and you want the user to be able to click a restuarant and have it go to that page you need to wrap the text you want to be the link in a <a> tags with the url pointing to a page that receives the primary key for the table.

You have not given us your MySQL table definition but it should have a unique key (otherwise you are going to be unable to solve this problem)

When you construct the html table you create the link like so
<a href="resturaunt.php?id=<?php echo $row['primarykey'];?>"><?php echo $row['RestName'];?></a>

Open in new window

In the above code you would change the $row values to those that match the database.

Without knowing more detail about what you want to do and your database format cannot provide more assistence than that.
0
 

Author Comment

by:DS928
Comment Utility
Thank you for your response.  The table is generated from the database.  Here is the table setup.

tblRestaurants
RestID  (PrimaryKey)
RestName
RestPage (The page name that we will be going to.  myrest.html)

The query is run, then the table, (for lack of better words) appears on the webpage.  Being new I'm not sure if this is what you are asking for,

If you go to this link
http://www.menuhead.net/Steelers/Head.php
Then select City, Boston,Back Bay, American then search, you can see what I am doing.  You have the code above and the table setup.  I hope this will help.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
I think you would want to add RestID to the SELECT query that builds the HTML table.  Then you could create a URL that said something like this:

$url = <<<EOD
<a href="myrest.html?id=$RestID">$RestName</a>
EOD;

Some considerations:

To use a ".html" script here, your server would need to parse .html scripts through PHP.  You might think of using "myrest.php" for this URL.

The variables $RestID and $RestName would be extracted from the query results set and would be processed through htmlentities() before being sent to the browser.

The "myrest.php" script will find the $RestID value in the $_GET['id'] variable.  This key will be used to look up the corresponding row in the data base.

This is a very common design (a menu of summary data, with clickable links to detail data) so it will be easy to find examples and programmers who know how to do this.  If you're not already a member of a local PHP user group, look around meetup.com or look in the right sidebar of php.net to see if there is a group near you.

Best of luck with it, ~Ray
0
 

Author Comment

by:DS928
Comment Utility
Thank you.  Are there any examples out there that I can see?  What exactly would I search for? I appreciate your help.
0
 
LVL 33

Accepted Solution

by:
Slick812 earned 500 total points
Comment Utility
not sure if you can see how a SQL SELECT is built to get back the parts of a Table that you need - you say that this Table has a web address -

tblRestaurants
RestID  (PrimaryKey)
RestName
RestPage    // - web page URL you need

I looked at your rather long SQL SELECT statement in your question, and I did NOT see any  "RestPage" column anywhere, but may have missed it?

I can guess that this SELECT query works for you? if so, you might try and add - tblRestaurants.RestPage, to it as -

mysql_query("SELECT tblRestaurants.RestName, tblLocations.CityID, tblRestaurants.RestPage, // RestPage added here
tblLocations.AreaID, tblLocations.CuisineID,
// rest of the long statement here

= = = = =

now in the output you might try -
echo"<tr><td><a href='$row[RestPage]' title='Click to web page'>$row[RestName]</a></td> // rest of long code here

The above is only a guess, this is NOT tested and may have typing errors in it, but it may give you enough info to do your own setup of how you want it,
if you have little knowledge of SQL SELECT statements and ROW output, you may need to get someone to do this for you.
0
 

Author Comment

by:DS928
Comment Utility
Ok this is pretty good!  I'm getting the links on the restaurant names, and its leaving the current page and going to the new one....ooops!  Can't find the page!  The pages that I am trying to open are on the current website, They are not websites, just pages of each restaurant with their info on it.  They are in the same folder as the page that is calling them.
0
 

Author Comment

by:DS928
Comment Utility
So I did this......
<td><a href='$row[RestPage]' title='Webclick'>$row[RestName]</a></td>

Open in new window



And this
<?php
function Webclick()
{
require("config.php");
$test= mysql_query("SELECT * FROM tblRestaurants WHERE RestID=$_GET[RestID]");
$test1=mysql_fetch_array($test);
echo $test[RestID];
}
?>

Open in new window


Now I am getting an access denied error.  Is there a format in which I hae to store the web pages in the database?
0
 
LVL 33

Expert Comment

by:Slick812
Comment Utility
I read what you said, but I can not see where you are going with this? You say something about - "its leaving the current page and going to the new one", , which is exactly what it's suppose to do the way you wrote the HTML. You have a web address in the RestPage, and that is something I know Nothing about, sorry.



I am not sure about your " an access denied error", but I can tell you you have a typing error -
$test1=mysql_fetch_array($test);
echo $test[RestID]; // HERE

it seems to me it should be -
echo $test1[RestID];

you might should use more easy to separate variable names like -
$result = mysql_query("SELECT
$row1=mysql_fetch_array($result);

but you already Know what the RestID is because you have - WHERE RestID=$_GET[RestID]

I looked at your web page at  http://www.menuhead.net/Steelers/Head.php , and I would think that your Restaurant search and sort will take extensive know how for PHP- SQL data base set up and SELECT SQL to get good page results with many hundreds of cities, and many thousands of Restaurants, if you add in your "Filters" like "great Views", you will need to have a tried and tested approach to your Data search and sort code. You do not look like you can set up the very basic SQL SELECT you have in your comment ID: 38842231 , you maybe can learn the SQL for such a site as this first?
I went up to the "Home" page at  http://www.menuhead.net/  , just to see what kinda site you got so far and I got this -

Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'MyHead'@'97.74.144.209' (using password: YES) in /home/content/d/s/t/dstr3/html/MENUHEAD/index.php on line 2
Error connecting to MySQL server.


with no web page at all ?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:DS928
Comment Utility
Thank you.  I am using this for now.

http://www.menuhead.net/Steelers/Head.php

The website is still being worked on.  The page at www.menuhead.net doesn't even have the right connection pass or users name.

Its obvious I don't know how to do this.  If you do, could you please show me.  I just want to be able to click on the restaurants name and go to it's page.  The problem for me is that it's dealing with variables because the table contents will depend on the search.  Any help is appreciated.  Thank you.
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
What is the link to the resturaunt detail page?

In other words - creating the link on the results table is easy - but to do that we need to know where it must go to pull up  the right page - will this be a link to the resturaunt's web page or are you constructing a template on your site to display the information?
0
 

Author Comment

by:DS928
Comment Utility
I will be cinstructing a page for each restaurant or maybe a template,  one page is 28_Degrees.php for instance.
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
If you have not construted the template yet then you can't build a link to it.

Lets though assume the page (template) you will use to display the results is called result.php

In this script we assume that a unique ID is required to load the data for the location being viewed.

Then as per my earlier post all you do is wrap your $row['RestName'] with an <a> tag putting the RestID in the URL like so

<a href="result.php?id=<?php echo $row['RestID'];?>"><?php echo $row['RestName'];?><a/>

In place of the part where you currently just outputting $row['RestName'];
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
... any examples out there that I can see?
From reading this thread again this morning, it's apparent to me that you would benefit from some structured learning about how PHP and MySQL play together.  To that end, you might want to get this book.  It uses the PDO extension (not the deprecated MySQL extension).
http://www.sitepoint.com/books/phpmysql5/

If you give yourself a month or so to study the principles, you will be way ahead when you start trying to write the code, probably a year or more when compared to trying to "pick it up" on your own.

Best of luck with your project, ~Ray
0
 
LVL 33

Expert Comment

by:Slick812
Comment Utility
OK, I will try and explain some to you, but there is many, much, whole lots, of things to know in learning about MySQL functions , and the returns for SELECT, , , and even more you can learn about the using the SQL language to do SELECT (and other) database operations to have the database give you the things (data) you need to display for your restaurants.

If you Do NOT want a link <a> to leave the current page, but open another tab (window) you could try and add  target='_blank'  -
<td><a href='$row[RestPage]' title='Webclick'  target='_blank'>$row[RestName]</a></td>

Next, if you want to have the above code to go to the restaurant's  Web Page, you will need to
1. get the Table Column with the column NAME for the string with that web page address, if  RestPage does not have that web address, use the column name that does (and the table name if in another Table);

OR - -
2. UPDATE the tblRestaurants Table column of name "RestPage" with the correct web address for the restaurant in the RestName column.

you can see some code and explanation for MySQL UPDATE at -
http://www.w3schools.com/php/php_mysql_update.asp

you looked like you wanted to SEE what was in the tblRestaurants Table in your SELECT * , , so you might look at or try the following code, I did not test it, but it should be correctly written for the most part.

$con = mysql_connect("localhost","name","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("restdb", $con); // use YOUR correct Database here

$result = mysql_query("SELECT * FROM tblRestaurants");

while($row = mysql_fetch_array($result))
  {
  foreach ($row as $key => $value)
      {
      echo  " -k- " .$key . " -v- " . $value." | ";
      }
  echo "<br />";
   }

mysql_close($con); // close when you are finished

Open in new window


There are thousands and thousands of PHP MySQL tutorials and how-to web sites that you can learn from, but it takes time.

You have this =
"SELECT tblRestaurants.RestName, tblLocations.CityID,
tblLocations.AreaID, tblLocations.CuisineID,             
CONCAT(tblLocations.StreetNumber,' ', tblLocations.Street) Address,
tblLocations.Phone, tblDetails.Price, tblDetails.Rating
FROM tblRestaurants INNER JOIN (tblLocations LEFT JOIN tblDetails
ON tblLocations.LocationID = tblDetails.LocationID)                         
ON tblRestaurants.RestID = tblLocations.RestID
WHERE tblLocations.CityID='16'
AND tblLocations.AreaID='131'
AND tblLocations.CuisineID='3'
ORDER BY tblRestaurants.RestName ASC"

which has a multilevel JOIN -
" INNER JOIN (tblLocations LEFT JOIN tblDetails
ON tblLocations.LocationID = tblDetails.LocationID) "

this is NOT simple beginners SQL, and to get correct SELECT string returns from 3 or more Tables, with the SEARCH choices you have (by state, city, food-type, and more), you will need to have Not Simple SQL SELECT with JOIN and other conditionals in the WHERE, you have this Hard Coded, but if you actually use it I would think you would have something like -
WHERE tblLocations.CityID='"$cityID"'
AND tblLocations.AreaID='"$areaID"'
AND tblLocations.CuisineID='"$cusID"'

learning how to use the JOIN options in SELECT will take some time, and even more for multilevel JOIN. . . .

This is about all of the instruction I can do for you, I hope it helps you, but considering your complex SELECT statement above, you seen a long way from changing that to use 3 tables to have different search results for different conditions in your search choices.
0
 

Author Comment

by:DS928
Comment Utility
Thank you Ray.  I am buying the book.  Unfortunatly at this point I don't have a month.  I have two things left to finish this project.  One is to make the link, the other i to get the filter combo working.  Then I am done.  I've come this far, I can't stop at he end here, so in the meantime, I just need more guidance.  Thank you, once again.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Is there a PHP user group in your area?  You might find someone there who could get "hands on" with your code and data base.  You may feel that you are near the end of the project, but I've seen a lot of projects over the years, and I can tell you with 100% certainty that you're not really near the end.  You're near the end of the first prototype, and there are still many decision paths with far-reaching consequences that you will have to face between now and deployment.  Some of these may require you to rewrite large sections of the code.

I don't say that to discourage you; it's an interesting project with lots of potential.  But it is a dynamic, data base driven web site.  Those things are somewhat complicated by their very nature.

A few things I would check right now...

Print out your CREATE TABLE scripts and find a SQL data base administrator.  Ask for a review and suggestions.  If you get the table structure correct, the queries will be easier to write.  Once you know what all of your queries need to do, in a generalized way, you can begin adding in the "dynamic" part that uses variable substitution and that gets you away from potentially rigid and brittle designs.  This is an example of what I would call a brittle design... "a page for each restaurant or maybe a template,  one page is 28_Degrees.php for instance."  Instead of a page for each restaurant you want one page for all restaurants.  The page will receive a restaurant token (maybe a data base Id number) and will use the token to look up all the information about the restaurant and present it to the client.

Convert any of your information-only requests to GET method.  You use POST for changes to the data model.  Consider getting away from the AJAX stuff until you get the back-end PHP scripts working.  The AJAX requests hide information you may need for debugging.

You might want to go into your config.php script and make sure that error_reporting(E_ALL) is set.  That will save you from accidentally relying on undefined variables.  

You might want to check and see that register globals and magic quotes are both set to off.  

You would probably want to add some error checking and visualization to your queries.  A message that says "cannot parse query" is not very helpful.  It would be better to print the query that failed, along with the information in mysql_error().

You should check to be sure that the HTML document generated by your PHP scripts is a valid document.  The W3C has a validator.
http://validator.w3.org/check?uri=http%3A%2F%2Fwww.menuhead.net%2FSteelers%2FHead.php&charset=%28detect+automatically%29&doctype=Inline&group=0

You might want to switch to the HTML5 doctype declaration.  It's more forgiving.

In any case, best of luck with it, ~Ray
0
 

Author Closing Comment

by:DS928
Comment Utility
Its funny, this was such a simple answer, but it wasn't.......!

<td><a href='$row[RestPage]' title='More Info'>$row[RestName]</a></td>

This works most perfectly!

Thank you!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now