Database search query help!

[kaigth]

Hello, I am having an issue with my PHP code, my code is for a database search. for some reason when I post a search it list the entire database in list order and doesnt do a search at all, nor does it organize them in the search for a specific word... also I set it up so it list them by 10, when I click on the "next page" to see the next list of searchs in the database it does nothing

 

what am I doing wrong?  heres the code...

 

^<?

^{$query = "SELECT * FROM widgetlist WHERE cname LIKE '%$trimmed%'";}

 

^{$numresults=mysql_query($query);}

^{$numrows=mysql_num_rows($numresults);}

 

  ^{$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>";}

  ^}

^else{

 

^{if ($numrows == 0)}

  ^{

  ^{echo "<h4>Results</h4>";}

  ^{echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</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><strong>The organization or college you are looking for: "" . $var . ""</strong</p><br>";}

 

^{// begin to show results set}

^{$count = 1 + $s ;}

 

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

  ^{$title = $row["cname"];}

 

  ^{echo "<br />";}

  ^{echo "$count.) $title" ;}

    ^{echo "<br>";}

  ^{echo "Description: $title" ;}

  ^{$count++ ;}

  ^}

 

  ^{echo "<br />";}

 

^{$currPage = (($s/$limit) + 1);}

 

  ^{echo "<br />";}

 

^{//checking links to other pages}

  ^{if ($s>=1) { // bypass PREV link if s is 0}

  ^{$prevs=($s-$limit);}

  ^{print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><<}

  ^{Prev 10</a>&nbsp ";}

  ^}

 

^{// calculate number of pages needing links}

  ^{$pages=intval($numrows/$limit);}

 

  ^{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 10 >></a>";}

  ^}

 

^{$a = $s + ($limit) ;}

  ^{if ($a > $numrows) { $a = $numrows ; }}

  ^{$b = $s + 1 ;}

  ^{echo "<p>Showing results $b to $a of $numrows</p>";}

^}

^?>

[kaigth]

forgot the form code below:

 

<form name="form" action="<?=$PHP_SELF?>" method="get">

  Search our database of widgets:<br />

  <input type="text" class="search_input" name="q" />

  <input type="image" name="Submit" src="images/search.gif" value="Search" />

</form>

[-Karl-]

If you want people to help, use PHP tags.

[kaigth]

wouldnt let me edit it so I had to repost the data

 

<?
$query = "SELECT * FROM widgetlist WHERE cname LIKE '%$trimmed%'";
  
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

  $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>";
  }
else{
  
if ($numrows == 0)
  {
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</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><strong>The organization or college you are looking for: "" . $var . ""</strong</p><br>";

// begin to show results set
$count = 1 + $s ;

  while ($row= mysql_fetch_array($result)) {
  $title = $row["cname"];
  
  echo "<br />";
  echo "$count.) $title" ;
    echo "<br>";
  echo "Description: $title" ;
  $count++ ;
  }
  
  echo "<br />";

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

  echo "<br />";

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

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

  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 10 >></a>";
  }

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

[-Karl-]

That's still only in code tags, on PHPfreaks there's [ PHP ] tags (syntax highlighting). Makes it easier to read. I have looking at black and white code, I've only just started using PhpED, I could never go back to notepad only

[kaigth]

lol, I should prolly just make a new post   thanks for your help karl

 

but im not seeing a syntax highlight  lol sigh 

[kaigth]

ok syntax highlighted... So once again my problem is it pulls the whole database and not the search terms.. what am I doing wrong here? it list them in order placed in the database, so not matter what my search is even if the word isnt in the database it pulls the entire database in the same order every time

 

[code]<?
$query = "SELECT * FROM widgetlist WHERE cname LIKE '%$trimmed%'";
  
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

// 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>";
  }
else{
  
if ($numrows == 0)
  {
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</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><strong>The organization or college you are looking for: "" . $var . ""</strong</p><br>";

// begin to show results set
$count = 1 + $s ;

// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {
  $title = $row["cname"];
  
  echo "<br />";
  echo "$count.) $title" ;
    echo "<br>";
  echo "Description: $title" ;
  $count++ ;
  }
  
  echo "<br />";

$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 " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><< 
  Prev 10</a>&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 " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
  }

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

[/code]

[DavidAM]

The code tags recognize full PHP tags ( <?php ) but not short ones, which you should not be using anyway.

 

The problem with the code is at the top:

/* Let's build the query string from a variable that is not defined yet (so it has no value) - 
Move this line down below then one that sets $trimmed  */
$query = "SELECT * FROM widgetlist WHERE cname LIKE '%$trimmed%'";
  
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

// Get the search variable from URL
  $var = @$_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable
// PUT THE $query line here

[kaigth]

Hey thanks a lot david your awesome