Adding Hyperlinks to scraped data

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Adding Hyperlinks to scraped data

Post by phpflixnewbie »

Hi guys,

I have a table of results pulled from a mysql database which is printed to html using the code below. I now want to add hyperlinks to each record for taking the visitor to the details page of each record. I guess its a case of adding another table to the database containing the hyperlinks with matching ids, but i have no idea how to go about writing the php code for it. Please advise.

Code: Select all

$Host = "hostname"; //you can use IP address instead of localhost
$User = "username";
$Password = "password";
$Database = "databasename";

$Link_ID=mysql_pconnect($Host, $User, $Password);
     if (!$Link_ID)
     {
        echo "Failed to connect to Server=".$Host;
          return 0;
     }
     else
     {
#         echo "<B>Successfully to connected to Server  </B>" .$Host;
     }


     if (!@mysql_select_db($Database,$Link_ID))
     {
#         echo "<br>Cannot use database=  " .$Database;
      }
      else
     {
#         echo "<br> Successfully connected to database= ";
      }

// Performing SQL query
$query = "select dvd_title, round(avg(rating),1) AS rounded_rating , date_format(dvd_rlsdate,'%d %b %y') from dvd_ratings, dvd_titles where dvd_titles.dvd_id=dvd_ratings.dvd_id group by dvd_ratings.dvd_id order by rounded_rating desc limit 10";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Printing results in HTML

$odd = true;
echo "<table>\n";
echo "<tr><th>Title</th><th>Rating</th><th>DVD Release Date</th></tr>";  // Setting Column Names
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   if ($odd) echo "\t<tr bgcolor=\"#CCF1FF\">\n";
   else echo "\t<tr bgcolor=\"#FBF6D5\">\n";
   foreach ($line as $col_value) {
       echo "\t\t<td>$col_value</td>\n";
   }
   echo "\t</tr>\n";
   $odd = !$odd;
}
echo "</table>\n";


// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($Link_ID);
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

What's the complete structure of the table dvd_titles?
Where are the details stored? What's the structure of that table?
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Below is the structure of dvd_titles. I do not yet have a table for the details pages, they are still being created, but i want to start testing links to them. I suspect the table will have dvd_id and url columns.


Code: Select all

Field 	Type 	Null 	Default
dvd_id 	int(11) 	No  	0  	 
dvd_title 	varchar(200) 	No  	  	 
prodn_year 	year(4) 	Yes  	NULL  	 
dvd_rlsdate 	date 	Yes  	NULL  	 


Indexes:
Keyname 	Type 	Cardinality 	Field
PRIMARY 	PRIMARY 	33  	dvd_id
dvd_title


Space usage:
Type 	Usage
Data 	16,384 	B
Index 	0 	B
Total 	16,384 	B
	  	Row Statistics:
Statements 	Value
Format 	dynamic
Rows 	33
Row length ø 	496
Row size  ø 	496 B
Creation 	Dec 17, 2006 at 12:52 AM
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

and url columns.
I don't understand what the url columns would be, but you only need the dvd_id.
something like

Code: Select all

echo '<a href="detail.php?dvd_id=', $row['dvd_id'], '">show details</a>';
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

How would i apply that every row in the result?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

phpflixnewbie wrote:How would i apply that every row in the result?
by putting that in your loop?
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

I dont want to echo 'show details'. I want the table of results as it is now, but each title in the 'Title' result column i want as a link to its relevant details page.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

then change 'show details' to $row['title'] or whatever.. :wink:
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Im really struggling to get this to work.

The table produced from the sql query within the php looks like this:

Title Rating DVD Release Date
"title1" 5.0 20 Nov 06
"title2 5.0 27 Nov 06
"title3" 4.3 27 Nov 06
"title4" 4.0 30 Oct 06
"title5" 4.0 06 Nov 06
"title6" 4.0 13 Nov 06
"title7" 3.7 30 Oct 06
"title8" 3.6 04 Dec 06
"title9" 3.6 11 Dec 06
"title10" 3.4 16 Oct 06

Baring in mind this table of results will change every week as the database is updated. Please advise what the exact href php code needs to look like and where i should insert it into my current code.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

May I ask how much you know about php and how much of the code you posted here you actually understand?
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

volka wrote:May I ask how much you know about php and how much of the code you posted here you actually understand?
I am a php beginner, i understand most of the code im already using. However, as a complete noobie i'd appreciate any guidance i can get.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

You are already looping through your data, you are already outputting the database fields. Study your code carefully and you should be able to apply what we've mentioned easily.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

I have tried adding the line of code into my while loop below, but nothing happens, please advise:

Code: Select all

// Printing results in HTML

$odd = true;
echo "<table>\n";
echo "<tr><th>Title</th><th>Rating</th><th>DVD Release Date</th></tr>";  // Setting Column Names
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   echo '<a href="detail.php?dvd_id=', $row['dvd_id'], '"</a>';
   if ($odd) echo "\t<tr bgcolor=\"#CCF1FF\">\n";
   else echo "\t<tr bgcolor=\"#FBF6D5\">\n";
   foreach ($line as $col_value) {
       echo "\t\t<td>$col_value</td>\n";
   }
   echo "\t</tr>\n";
   $odd = !$odd;
}
echo "</table>\n";


// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($Link_ID);
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

try

Code: Select all

$query = "SELECT
		dvd_id, dvd_title, round(avg(rating),1) AS rounded_rating , date_format(dvd_rlsdate,'%d %b %y') as rlsdate
	FROM
		dvd_ratings, dvd_titles
	WHERE
		dvd_titles.dvd_id=dvd_ratings.dvd_id
	GROUP BY
		dvd_ratings.dvd_id
	ORDER BY
		rounded_rating desc
	LIMIT
		10";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
echo '<table border="1">';
while( $row=mysql_fetch_array($result, MYSQL_ASSOC) ) {
	echo '<tr>',
			'<td><a href="detail.php?id=', $row['dvd_id'], '">', htmlentitites($row['dvd_title']), '</a></td>',
			'<td>', htmlentitites($row['rounded_rating']), '</td>',
			'<td>', htmlentitites($row['rlsdate']), '</td>',
		'</tr>';
}
echo '</table>';
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Thanks for the feedback. I tried your code, but i get the error: 'query dvd_id is ambiguous', so i added the tablename to the query on the first line making 'dvd_titles.dvd_id'. Then i get another error: 'Fatal error: Call to undefined function: htmlentities()'
The table border is printed, but no table content.
Post Reply