While loops, tags and dysfunctional search scripts.

[FlashNinja]

So today I thought I'd try my hand at building a nice little search script for my site in progress. I haven't had any real success with this previously, but I'm trying to amend that.

 

I've produced this search script which should take the user's search request, call information from the 'ad' database - it contains all the advertisement information (name,images,tags etc.) - compare it to the 'name' and 'tag' fields of the database and display any matching results. I'm here of course because this is not working as planned. There are no errors popping up, the script just isn't displaying results when it should be. Searches that use keywords featured in tags do not produce any results. Anyone have any idea why this script doesn't produce any results?

 

SQL 'ad' Table:

 

CREATE TABLE IF NOT EXISTS `ad` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `text` varchar(600) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `apic` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `usr` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `tags` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `usr` (`usr`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

 

PHP script:

 

<?php include 'connect.php'; 

  session_start();
  $_SESSION['username']; 
  
  if(!(isset($_SESSION['login']) && $_SESSION['login']!= " ")){
       header("Location: login.php");
   }
   
  $username = $_SESSION['username'];
  $tablename = 'ad';
  
  $search = stripslashes($_POST['search']);
  $search = mysql_real_escape_string($search);
  $search = trim($search);
  
  if (isset($search))
     {

 $sql = " SELECT * FROM $tablename";
 $result = mysql_query($sql) or die (mysql_error());

 }

  else header("Location:search_err.php");
  
  if (mysql_num_rows($result) <0)
  {
   header("Location:search_err.php");
   }
   
  $ad_data = "Results for: " .$search."";
  
  $x=1;
  while($ads = mysql_fetch_array($result) && $x <= mysql_num_rows($result))
  {
    $ad_name = $ads['name'];
    $ad_text = $ads['text'];
    $ad_pic = $ads['apic'];
    $ad_usr = $ads['usr'];
    $ad_tags = $ads['tags'];
  
    if(stristr($ad_name, $search))
{
  $ad_data = "<a href = ad_page.php>". $ad_name ."</a>";
  $xi=1;
}
else
{
  $xi=0;
}
    
if(isset($xi) && $xi == 1)
{
  $ad_tags_exp = explode(',',$ad_tags);
  $fi = 0;
  
  while($fi = 0)
  {
    if($ad_tags_exp[$fi] == $search)
	{
	$fi = 1;
	$ad_data = $ad_data = "<a href = ad_page.php>". $ad_name ."</a>";
	}
  }
    }
$x++;
   }
   
   echo $ad_data;
?> 

 

 

[Psycho]

If my review of your code is correct, it looks like you are pulling all the records in your query and then processing them in PHP looking for matches. That is the wrong way to do a search script. You only need to add the appropriate search data to your query. And your script has numerous logic errors such as

  $search = stripslashes($_POST['search']);
  $search = mysql_real_escape_string($search);
  $search = trim($search);
  
  if (isset($search))
     {

 $sql = " SELECT * FROM $tablename";

 

You are explicitly setting $search so it will always be set and that if() condition will always be true.

 

Then there is this

if (mysql_num_rows($result) < 0)

How can the number of results be less than 0?

 

And this within a while() loop

    if(stristr($ad_name, $search))
{
  $ad_data = "<a href = ad_page.php>". $ad_name ."</a>";
  $xi=1;

You are redefining $ad_data on each loop instead of appending to it.

 

Give this a try as a starting point

<?php

include 'connect.php'; 

session_start();
$_SESSION['username']; //This does nothing
$username = $_SESSION['username']; //Username isn't used from what I see

if(!isset($_SESSION['login'] && $_SESSION['login']!= " "))
{
      header("Location: login.php");
}
   
//If no search term, redirect to error page
if(!isset($_POST['search']))
{
    header("Location:search_err.php");
}

$tablename = 'ad';
$searchStr = stripslashes(trim($_POST['search']));
$searchStrSQL = mysql_real_escape_string($searchStr);

$sql = "SELECT * FROM $tablename
         WHERE `name` LIKE '%{$searchStrSQL}%'
            OR `text` LIKE '%{$searchStrSQL}%'";
$result = mysql_query($sql) or die (mysql_error());

$ad_data = "Results for: {$searchStr}<br>\n";
if(!mysql_num_rows($result))
{
    $ad_data .= "There were no matches<br>\n";
}
else
{
    while($row = mysql_fetch_assoc($result))
    {
        $ad_data .= "<a href = ad_page.php>{$row['name']}</a><br>\n";
    }
}

echo $ad_data;

?> 

[FlashNinja]

Took a look at your amendements and fixed my code, but it's still not working as it should. No results are being produced where there shou;d be results. 

 

Updated code:

 

<?php include 'connect.php'; 

  session_start();
  $_SESSION['username']; 
  
  if(!(isset($_SESSION['login']) && $_SESSION['login']!= " ")){
       header("Location: login.php");
   }
   
  $username = $_SESSION['username'];
  $tablename = 'ad';
  
  $search = trim(stripslashes($_POST['search']));
  $searchSQL = mysql_real_escape_string($search);
  
  if (empty($search))
     {
 header("Location:search_err.php");
 }

  else 
  
  $sql = "SELECT * FROM $tablename
         WHERE `name` LIKE '%{$searchSQL}%'
            OR `tags` LIKE '%{$searchSQL}%'";
  $result = mysql_query($sql) or die (mysql_error());

  $ad_data = "Results for: {$search}<br>\n";

  
  if (mysql_num_rows($result) <1)
  {
   header("Location:search_err.php");
   }
   
  else
  
  $ad_data = "Results for: " .$search."";
  $x=1;
  $ads = mysql_fetch_array($result);
  
  while($ads = mysql_fetch_array($result) && $x <= mysql_num_rows($result))
  {
    $ad_name = $ads['name'];
    $ad_tags = $ads['tags'];
  
    if(stristr($ad_name, $search))
{
  $ad_data .= "<a href = ad_page.php>". $ad_name ."</a>";
  $xi=1;
}
else
{
  $xi=0;
}
    
if(isset($xi) && $xi == 1)
{
  $ad_tags_exp = explode(',',$ad_tags);
  $fi = 0;
  
  while($fi = 0)
  {
    if($ad_tags_exp[$fi] == $search)
	{
	$fi = 1;
	$ad_data .= "<a href = ad_page.php>". $ad_name ."</a>";
	}
  }
    }
$x++;
   }
   
   echo $ad_data;
?>


       
  

 

[FlashNinja]

I've narrowed down what seems to be the problem with my search script.

 

PHP code:

 

  $ad_data = "Results for: " .$search."<br>";
  $x = 0;
  $ads = mysql_fetch_array($result);
  $rows = mysql_num_rows($result);
  
  while($ads = mysql_fetch_array($result) && $x <= $rows)

 

Removing the "$x <= $rows" part of the requirements for the while loop to run, makes it actually run. Can anyone see why this is an issue?

[Psycho]

Did you not notice that I had removed that from the sample code I provided? I'm not going to even take the time to explain why it does not work because the fact of the matter is that you shouldn't even be using that. You are putting an additional condition on that loop that isn't even needed.

 

The condition of $ads = mysql_fetch_array($result) is all you need to have that loop continue until all records from the search result are processed. So, why would you add an additional condition to try and create a numeric process to do the same thing?

 

 

Anyway, the code you last posted is still rife with problems. Why did you still include the PHP code to "test" the records after the query to see if they contained the search phrase? The query was already updated to do that. You don't need to test the results for the same criteria again. I also see problems with basic formatting. I will be glad to continue helping you, but not if I have to continually fix the same mistakes over and over. Start with the code I provided.

[FlashNinja]

After trying your code with no additional tweaks by myself, it actually works fine. I thought I had already checked that, but I guess I was mistaken. Thank you for the help! 

[Psycho]

After trying your code with no additional tweaks by myself, it actually works fine. I thought I had already checked that, but I guess I was mistaken. Thank you for the help! 

The only thing I saw that needed to be updated in my code was you wanted the search to be done against the name and tags fields. I used name and text. Aside from that, if you are getting the right results - then figure out if additional processing is needed. Start simple THEN add complexity if needed.