Page 1 of 2
subresult
Posted: Thu Jan 24, 2008 6:10 pm
by dramiditis
Hi, I have one problem:
For example I make one database table with 7 fields an on HTML form so my visitors can search trought my db with LIKE, so it comes 15 results with displayed 2 fields(title and description e.t.c.) and my visitor click on one. NOW here is the problem, how can I write "subresult query" and to display all 7 fields from clicked result?
Anyone knows?
Re: subresult
Posted: Thu Jan 24, 2008 8:17 pm
by Chris Corbyn

Please elaborate (with code), I'm scared to read your post a fourth time.
Re: subresult
Posted: Fri Jan 25, 2008 8:45 am
by dramiditis
It's a search engine with LIKE:
$field_to_search = "title";
$query = "SELECT * FROM sport WHERE $field_to_search LIKE \"%$trimmed%\" order by id DESC";
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
//RESULTS DISPLAY
Print "<table border cellpadding=0>";
while ($row= mysql_fetch_array($result))
{
Print "<tr>";
Print "<td>".$row['image'] . "</td> ";
Print "<td>".$row['description'] . " </td> ";
Print "<td>".$row['title'] . " </td></tr>";
echo "$count.<br><br>$title" ;
}
Print "</table>";
$currPage = (($s/$limit) + 1)- And now the results are displayed to my visitor and he click on one result to see the content. My problem is that I don't know how to link clicked result to the content.( I don't want to make files, and to link files to search results, I would like my content to be in DataBase )
Re: subresult
Posted: Fri Jan 25, 2008 1:30 pm
by califdon
You were confusing us by using the term "subresults", which nobody understands. There are at least 2 different approaches that you could take. The easiest one would be to just store the "content" field of the result set in an array, if the content isn't huge and you don't expect to return more than a few result rows. A better way, I would think, would be make a second query against the table when one of your results rows is selected.
In any case, you need to format at least one of the display <td>'s as a hyperlink ( <a>...</a> ) so the user can select the desired item. The exact syntax depends, of course, on what your primary key is named, etc. The href= part of the hyperlink will need to call another (or the same, depending on how you decide to structure it) script and pass the primary key to it, so it can query the table.
Re: subresult
Posted: Fri Jan 25, 2008 2:47 pm
by dramiditis

Thank you for your info,
The content is huge and I need to make a second query against the table. My primary key is "id" and I would like to call the same script.
So, can you help me with the syntax, it does't need to be exact one, I will figure it!
One more time thank you!!!
Re: subresult
Posted: Fri Jan 25, 2008 3:11 pm
by califdon
dramiditis wrote:
Thank you for your info,
The content is huge and I need to make a second query against the table. My primary key is "id" and I would like to call the same script.
So, can you help me with the syntax, it does't need to be exact one, I will figure it!
One more time thank you!!!
Here's at least one way to begin. This would replace the code you showed in your previous post:
Code: Select all
$field_to_search = "title";
$query = "SELECT id, image, description, title FROM sport WHERE $field_to_search LIKE \"%$trimmed%\" order by id DESC";
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
//RESULTS DISPLAY
echo "<table border cellpadding=0>";
while ($row= mysql_fetch_array($result))
{
echo "<tr>";
echo "<td><a href='script2.php?id=".$row['id']."'/>".$row['image'] . "</a></td> ";
echo "<td>".$row['description'] . " </td> ";
echo "<td>".$row['title'] . " </td></tr>";
echo "$count.<br><br>$title" ;
}
echo "</table>";
You will see that I made the image of each row into a hyperlink with the anchor tag, calling another script ("script2.php") with a GET parameter of id=xxx, the id of the record selected. That second script could get the parameter with $_GET['id'] and use the value in the second query to retrieve the content and display it. There are numerous variations possible, including calling the same script, but having the script test at the beginning whether it received the $_GET[pid'] parameter, and using that to branch to the appropriate part of the PHP code.
I would also suggest that you name the specific fields you need, rather than SELECT *, especially since you say that your results set may be very large.
Re: subresult
Posted: Fri Jan 25, 2008 4:01 pm
by dramiditis
Thank you califdon!
I get it now. I have study PHP/MySQL for just 20 days from Paul Hudson's online book
http://www.hudzilla.org/php/ and now I will try to make one project(video sharing site) it will be very simple ( as my experience) but at least I will try. So, in couple of weeks when I finish it, I will msg it's website.
Best Regards
Dragan
Re: subresult
Posted: Fri Jan 25, 2008 4:39 pm
by califdon
dramiditis wrote:Thank you califdon!
I get it now. I have study PHP/MySQL for just 20 days from Paul Hudson's online book
http://www.hudzilla.org/php/ and now I will try to make one project(video sharing site) it will be very simple ( as my experience) but at least I will try. So, in couple of weeks when I finish it, I will msg it's website.
Best Regards
Dragan
Glad I could help get you started. Yes, come back and show us your work!

Re: subresult
Posted: Sat Jan 26, 2008 10:58 am
by dramiditis
Ok, with this script is ok, but now I have problem to pass the primary key to ("script2.php"). Actualy hyperlinking work ok, but I don't know how to set query in (script2.php). Can you help me with syntax?
This is my last problem, everything alse is set.
Regards
Re: subresult
Posted: Sat Jan 26, 2008 1:47 pm
by califdon
dramiditis wrote:Ok, with this script is ok, but now I have problem to pass the primary key to ("script2.php"). Actualy hyperlinking work ok, but I don't know how to set query in (script2.php). Can you help me with syntax?
This is my last problem, everything alse is set.
Regards
Ok, so the first script will generate HTML code that will look something like this:
Code: Select all
...
<table border cellpadding=0>
<tr>
<td><a href='script2.php?id=123'><img src='xyz.jpg'></a></td>
<td>Acropolis</td>
<td>This is a photo of the Acropolis in Athens</td></tr>
<tr>
<td><a href='script2.php?id=456'><img src='foo.jpg'></a></td>
<td>Eiffel Tower</td>
<td>This is a photo of the Eiffel Tower in Paris</td></tr>
...
right?
So if the user clicks on the first image, it will send a request back to the server address:
script2.php?id=123
Then script2.php will start off something like this:
Code: Select all
<?php
$id = $_GET['id'];
mysql_connect("myhost","myuser","mypassword") or die("Could not connect");
mysql_select_db("mydatabase") or die("Could not select DB");
$sql = "SELECT content FROM mytable WHERE id = $id";
$result = mysql_query($sql);
if ($row = mysql_fetch_array($result)) {
$content = $row['content'];
?>
<HTML>
<HEAD>
<title>Here is the content you requested</title>
<!-- maybe CSS styles, etc. -->
</HEAD>
<BODY>
<h2 Here is the content you requested</h2>
...
<?php
echo $content
echo </BODY></HTML>
}
?>
That is over simplified, of course. You would surely want to format your output and you may very well have more than one field of content. You should also test the value in the $id variable to make sure that it is within the range of your database and doesn't contain any malicious code. But I hope this will give you an idea of how it basically works.
Re: subresult
Posted: Sat Jan 26, 2008 2:10 pm
by Christopher
For security I would recommend:
Re: subresult
Posted: Sat Jan 26, 2008 2:28 pm
by califdon
arborint wrote:For security I would recommend:
At the very least. Thanks.
Re: subresult
Posted: Fri Feb 01, 2008 12:05 pm
by dramiditis
Hi, how are you?
Last week I was working on the script and I will finish it very soon. I have problem wuth pagination. I have limited row results to 5 per page and with "Next 5" need to go next 5 results, but it does't. It display same page with first 5 results.
Ok, here is code, but I think is somethink about syntax, I don't know.
<?php
include("wudar5.htm");
// Get the search variable from URL
$var = @$_GET['q'] ;
$trimmed = trim($var); //trim whitespace from the stored variable
// rows to return
$limit=5;
// check for an empty string and display a message.
if ($trimmed == "")
{
echo "<p>Please enter a search...</p><br>";
exit;
}
// check for a search parameter
if (!isset($var))
{
echo "<p>We dont seem to have a search parameter!</p><br>";
exit;
}
include("db.php");
// Build SQL Query
$field_to_search = "keywords";
$query = "SELECT * FROM video WHERE $field_to_search LIKE \"%$trimmed%\" order by v DESC";
$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><br>"
echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p><br>";
// 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><br>";
}
// 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: "<strong>" . $var . "</strong>"</p>";
// begin to show results set
// now you can display the results returned
Print "<table height=600 cellSpacing=0 cellPadding=3 width=606 align=center border=0>";
while ($row= mysql_fetch_array($result))
{
Print "<tr>";
Print "<td><a href='script3.php?v=".$row['v']."'/>".$row['image'] ."<br> ".$row['title'] . "<br></a><br>Category : ".$row['category'] . "<br> <hr></td>";
}
Print "</table>";
$currPage = (($s/$limit) + 1);
//break before paging
echo "<br>";
if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
print "a href=\"$PHP_SELF??s=$prevs&q=$var\"><<
Prev 5</a>  ";
}
// 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 "a href=\"$PHP_SELF?s=$news&q=$var\">Next 5 >></a>";
}
$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<p>Showing results $b to $a of $numrows</p>";
include("wudar6.htm");
include("wudar4.htm");
include("wudar7.htm");
include("wudar8.htm");
include("wudar9.htm");
?>
If you can figure it and have some advise, thank you.
Regards
Re: subresult
Posted: Fri Feb 01, 2008 7:53 pm
by califdon
First of all, please enclose your code with CODE and /CODE (or better yet, PHP) tags, to make it easier to read.
Your SQL isn't doing any pagination at all:
Code: Select all
$query = "SELECT * FROM video WHERE $field_to_search LIKE \"%$trimmed%\" order by v DESC";
If you want to display the first 5 records, it will have to be:
Code: Select all
$query = "SELECT * FROM video WHERE $field_to_search LIKE \"%$trimmed%\" order by v DESC" LIMIT 5;
Then, to display records 6 through 10, it will have to be:
Code: Select all
$query = "SELECT * FROM video WHERE $field_to_search LIKE \"%$trimmed%\" order by v DESC" LIMIT 6, 5;
See how it works?
Re: subresult
Posted: Sat Feb 02, 2008 7:14 am
by dramiditis
Hi,
I try with "LIMIT" in the SQL query, but then the script is dead. I have alredy set limit to 5 at the beggining of the script, but when the results is displayed ( with more than 5 ) and I pres "NEXT " it does't goes to the rest of results, but only refresh the same page.
I'm testing this script on one free hosting site, so you can see it:
http://www.videoworld.fancyfreehosting. ... mit=Search
Regards