...

View Full Version : Hyperlinking Results



alex98uk
03-12-2010, 04:21 PM
Hey, I made a thread like this a week or two ago, but I have the same issue but in a different scenario and I can't work out how to relate the two. This is a Uni project and thus doesn't need to be brilliant, simply work :p

They input a tag and it looks in the database table for every time the tag occurs. It then prints the name of the article that has that tag assigned to it. What I want is the names of the articles it prints to be hyperlinked to their respective page.

I have the name of the article (article_name) and it's URL (address) in a table called "articles". This is my code at the moment to search the database for articles with the tag assigned. 'q' is the user input:


<?php

// Get the search variable from URL

$var = @$_GET['q'] ;
$trimmed = trim($var); //trim whitespace from the stored variable

// rows to return
$limit=10;

// check for an empty string and display a message.
if ($trimmed == "")
{
echo "<p>Please enter a search...</p>";
exit;
}

// check for a search parameter
if (!isset($var))
{
echo "<p>We dont seem to have a search parameter!</p>";
exit;
}

//connect to your database
mysql_connect("xxxxxx", "xxxxxx", "xxxxx"); //(host, username, password)

//specify database
mysql_select_db("xxxxxx") or die("Unable to select database"); //select which database we're using

// Build SQL Query
$query = "select * from article_tag WHERE tag like \"%$trimmed%\"
order by tag";

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

// If we have no results, offer a google search as an alternative

if ($numrows == 0)
{
echo "<h4>Results</h4>";
echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; returned zero results</p>";

// google
echo "<p><a href=\"http://www.google.com/search?q="
. $trimmed . "\" target=\"_blank\" title=\"Look up
" . $trimmed . " on Google\">Click here</a> to try the
search on google</p>";
}

// next determine if s has been passed to script, if not use 0
if (empty($s)) {
$s=0;
}

// get results
$query .= " limit $s,$limit";
$result = mysql_query($query) or die("Couldn't execute query");

// display what the person searched for
echo "<p>You searched for: &quot;" . $var . "&quot;</p>";

// begin to show results set
echo "<b>";
echo "Results:";
echo "</b>";
echo "<p/>";
$count = 1 + $s ;

// now you can display the results returned
while ($row= mysql_fetch_array($result)) {
$title = $row["articlename"];

echo "$count: $title" ;
echo "<br/>";
$count++ ;
}

$currPage = (($s/$limit) + 1);

//break before paging
echo "<br />";

// next we need to do the links to other results
if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
print "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt;
Prev 10</a>&nbsp&nbsp;";
}

// calculate number of pages needing links
$pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division

if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}

// check to see if last page
if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

// not last page so give NEXT link
$news=$s+$limit;

echo "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 &gt;&gt;</a>";
}

$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<p>Showing results $b to $a of $numrows</p>";

?>

How might I hyperlink the results to lead to their respective page?

Fou-Lu
03-12-2010, 04:30 PM
This is what you want to change here:


echo "$count: $title" ;


Wrap the $title in an <a> tag, the url is $row['address'].

This code is a little on the antiquated side though; you're instructor is either unaware of the security issues that have existed with the use of register_globals for almost 10 years now, or is unaware that they have been officially deprecated as of 5.3.0. In particular, I'm looking at this $PHP_SELF variable; this should be $_SERVER['SCRIPT_NAME'] instead; $PHP_SELF itself is a registered variable which is $_SERVER['PHP_SELF'], but unless its been changed within the last few versions, PHP_SELF is an XSS exploitable variable.

alex98uk
03-12-2010, 04:56 PM
This is what you want to change here:


echo "$count: $title" ;


Wrap the $title in an <a> tag, the url is $row['address'].

This code is a little on the antiquated side though; you're instructor is either unaware of the security issues that have existed with the use of register_globals for almost 10 years now, or is unaware that they have been officially deprecated as of 5.3.0. In particular, I'm looking at this $PHP_SELF variable; this should be $_SERVER['SCRIPT_NAME'] instead; $PHP_SELF itself is a registered variable which is $_SERVER['PHP_SELF'], but unless its been changed within the last few versions, PHP_SELF is an XSS exploitable variable.

Thank you for the response, I see the line you mean and understand what you're saying, but i'm not quite sure how I need to implement the code you talk of. Would it be possible to show me the code you'd use?

Also, I was unaware of the register_global issue. We have to teach ourselves PHP and SQL at University and therefore i'm still learning:) I shall bear it in mind for next time, thanks :D

Fou-Lu
03-12-2010, 05:06 PM
Oh ok, you're kinda teaching yourself. Thats alright then.
To change the above, I would use this (forgive me I'm an old school print formatter :o)


printf('%d: <a href="%s">%s</a>' . PHP_EOL, $count, $row['address'], $title);


As for register_globals directive, the API has an article relating to the security of it located here: http://php.net/manual/en/security.globals.php

alex98uk
03-12-2010, 05:14 PM
Yes, it's weird. We get taught most things at University but one course we were just told to go away and learn PHP and MySQL. So I use the web a lot as a resource (along side books), so it means I sometimes pick up code that should no longer be used. I'm always learning though and the website is not public, so security issues are minor.

Anyway, the code you gave me is nice. It hyperlinks the words, however, they seem to be linked to "http://xxx/xxx/xxx/retrievetag.php?q=hockey&Submit=Search"

What I need it to do is be hyperlinked the URL it has stored in the articles table. I have done this before in a different page. This is the code I used:


<?php
$con = mysql_connect("xxxxxx", "xxxxxx", "xxxxxx");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db("xxxxxxx",$con);
$tag = mysql_real_escape_string($_GET['tag']);

$result = mysql_query("SELECT * FROM article_tag WHERE tag='$tag'");

while($row = mysql_fetch_array($result))
{


echo '<a href="articlesearchresults.php?tag='.$row['articlename'].'">'.$row['articlename'].'</a> ';


}

?>

Fou-Lu
03-12-2010, 09:26 PM
Sorry, I missed that this was from article_tag, and you said the article url is in the articles table? How are these related, with the articlename?


$query = "select t.*, a.address
from article_tag t
INNER JOIN article a ON (a.article_name = t.articlename)
WHERE tag like \"%$trimmed%\";

Based the above on the two sets of queries you provided. If they share the article_name as the foreign constraint, this will draw all information from the article_tag table and the associated url from the article table. It assumes that article_tag cannot exist without article record. This will add an 'address' offset to you're resultset, which is probably what you want to use as the hyperlink. Potentially, the query could be the other way around as well, but this depends completely on the intentions of the script in use.

alex98uk
03-15-2010, 01:47 PM
Hmm, seems I have done this a pretty bad way, but, it's too late now, I need to get the work done :eek:

This is the articles table:

http://i70.photobucket.com/albums/i120/alex98uk/Untitled.jpg

Basically, what I have done previously is a pretty basic means of searching. It takes the names of the articles that are returned, e.g.:

http://i70.photobucket.com/albums/i120/alex98uk/Untitled2.jpg

It then looks up the names returned in the articles table and if there is a match, it will display the URL assigned to it. Am I making sense, because I sometimes get lost in what I do? :p

This is the code I used previously:


<?php
$con = mysql_connect("xxxxxx", "xxxxxx", "xxxxxx");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db("xxxxxx",$con);
$tag = mysql_real_escape_string($_GET['tag']);

$result = mysql_query("SELECT * FROM articles WHERE article_name='$tag'");

while($row = mysql_fetch_array($result))
{


$URL=$row['address'];



header ("Location: $URL");


}

?>

I know it's ugly, but can we make it work :)?

Fou-Lu
03-15-2010, 04:34 PM
We can, but I may be misunderstanding what you're actual requirements are. I assumed you have a DB relationship like so:


+--------------+ +------------------+
| article | | article_tag |
+--------------+ +------------------+
| articleid PK |+o------------o<| articlename PKFK |
| article_name | | tag PK |
| authorid | +------------------+
| address |
+--------------+

Article_tag I assumed either has an articletagID unique to it, or that articlename and tag are a composite primary key.

Now, if I understand what you're trying to do correctly, you want to retrieve the address from the associated article table, where matches occur in the tag field of the article_tag database.

If this is correct, than executing this query:


$query = "select t.*, a.address
from article_tag t
INNER JOIN article a ON (a.article_name = t.articlename)
WHERE tag like \"%$trimmed%\"
ORDER BY tag;


instead of this one:


$query = "select * from article_tag WHERE tag like \"%$trimmed%\"
order by tag";


will allow you to access the associated address for the matching article. If my structure is incorrect, you'll need to play around a bit to make it work out. That will let you add the address into the print:


printf('%d: <a href="%s">%s</a>' . PHP_EOL, $count, $row['address'], $title);

instead of the


echo $count . ':' . $title;


That what you're trying to do?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum