Page 1 of 2

Adding Hyperlinks to scraped data

Posted: Sun Dec 17, 2006 12:26 pm
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);

Posted: Sun Dec 17, 2006 12:42 pm
by volka
What's the complete structure of the table dvd_titles?
Where are the details stored? What's the structure of that table?

Posted: Sun Dec 17, 2006 1:03 pm
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

Posted: Sun Dec 17, 2006 1:14 pm
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>';

Posted: Sun Dec 17, 2006 5:04 pm
by phpflixnewbie
How would i apply that every row in the result?

Posted: Sun Dec 17, 2006 5:09 pm
by John Cartwright
phpflixnewbie wrote:How would i apply that every row in the result?
by putting that in your loop?

Posted: Sun Dec 17, 2006 5:14 pm
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.

Posted: Sun Dec 17, 2006 5:17 pm
by John Cartwright
then change 'show details' to $row['title'] or whatever.. :wink:

Posted: Sun Dec 17, 2006 5:38 pm
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.

Posted: Sun Dec 17, 2006 6:13 pm
by volka
May I ask how much you know about php and how much of the code you posted here you actually understand?

Posted: Sun Dec 17, 2006 6:31 pm
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.

Posted: Sun Dec 17, 2006 6:38 pm
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.

Posted: Mon Dec 18, 2006 11:59 am
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);

Posted: Mon Dec 18, 2006 12:12 pm
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>';

Posted: Mon Dec 18, 2006 1:36 pm
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.