subresult
Moderator: General Moderators
-
dramiditis
- Forum Newbie
- Posts: 13
- Joined: Tue Jan 22, 2008 7:23 am
subresult
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?
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?
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
Re: subresult
-
dramiditis
- Forum Newbie
- Posts: 13
- Joined: Tue Jan 22, 2008 7:23 am
Re: subresult
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 )
$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
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.
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.
-
dramiditis
- Forum Newbie
- Posts: 13
- Joined: Tue Jan 22, 2008 7:23 am
Re: subresult
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
Here's at least one way to begin. This would replace the code you showed in your previous post: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!!!
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>";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.
-
dramiditis
- Forum Newbie
- Posts: 13
- Joined: Tue Jan 22, 2008 7:23 am
Re: subresult
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
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
Glad I could help get you started. Yes, come back and show us your work!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
-
dramiditis
- Forum Newbie
- Posts: 13
- Joined: Tue Jan 22, 2008 7:23 am
Re: subresult
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
This is my last problem, everything alse is set.
Regards
Re: subresult
Ok, so the first script will generate HTML code that will look something like this: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
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>
...
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>
}
?>- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: subresult
At the very least. Thanks.arborint wrote:For security I would recommend:Code: Select all
$id = intval($_GET['id']);
-
dramiditis
- Forum Newbie
- Posts: 13
- Joined: Tue Jan 22, 2008 7:23 am
Re: subresult
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
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>";
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
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:
If you want to display the first 5 records, it will have to be:Then, to display records 6 through 10, it will have to be:See how it works?
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";
Code: Select all
$query = "SELECT * FROM video WHERE $field_to_search LIKE \"%$trimmed%\" order by v DESC" LIMIT 5;Code: Select all
$query = "SELECT * FROM video WHERE $field_to_search LIKE \"%$trimmed%\" order by v DESC" LIMIT 6, 5;-
dramiditis
- Forum Newbie
- Posts: 13
- Joined: Tue Jan 22, 2008 7:23 am
Re: subresult
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
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