A baffling problem

[riddhi]

I am running the script having the following querry:-

 

The code takes keyword as input it recognises words beginning with -n as the product name and querries the database accordingly.

 

          if (ereg("-n",$keyword)){ //The Item Search by product name 
      $keyword=trim($keyword,"-n"); // Strips of -n option
			  	  
	  $res=dbQuery("SELECT B.PD_THUMBNAIL,B.PD_NAME, 
                      B.PD_DESCRIPTION,B.PD_ID,B.PD_PRICE 
    	             FROM  TBL_CATEGORY A, TBL_PRODUCT B
                             WHERE A.cat_Parent_ID=$category 
		     AND A.CAT_ID=B.CAT_ID AND 
		      B.PD_NAME LIKE '$keyword'");
	}

	if(mysql_num_rows($res) == 0 ){ //feature not found
	   echo "<br><br><marquee bgcolor='blue'>
	        <b>No Product in The Shop has the Specified Feature</b></marquee>"; 
	    exit;
     }   			 

     

    The database is querried giving the following output:-

 

mysql> SELECT B.PD_THUMBNAIL,B.PD_NAME,

    ->        B.PD_DESCRIPTION,B.PD_ID,B.PD_PRICE

    ->        FROM  TBL_CATEGORY A, TBL_PRODUCT B

    ->        WHERE A.cat_Parent_ID=21

    ->              AND A.CAT_ID=B.CAT_ID AND

    ->        B.PD_NAME LIKE '%nothing%'

    -> ;

+--------------------------------------+---------+----------------+-------+-----

-----+

| PD_THUMBNAIL                        | PD_NAME | PD_DESCRIPTION | PD_ID | PD_P

RICE |

+--------------------------------------+---------+----------------+-------+-----

-----+

| f3738c5176628516539b1c09140d792f.jpg | nothing | jiofffffff    |    23 |  20

0.00 |

+--------------------------------------+---------+----------------+-------+-----

-----+

1 row in set (0.03 sec)

 

 

even if the input is adjusted so that $category=21 and $keyword="-n nothing" yet when the script is run if gives the output:-

 

No Product in The Shop has the Specified Feature

 

why is it?

 

[Psycho]

What "feature"? Do you mean name? It's kind of confusing when you mix your terminology.

 

I think I know what you are saying, let me paraphrase. Your query, when using '-n nothing' for the $keyword is not returning any results, but you do have a record which you believe does match the result. If that is the case here is your problem.

 

When trimming your keyword you are trimming '-n'. That leaves a leading space on the front of 'nothing', so the query ends up with this:

 

B.PD_NAME LIKE ' nothing'

 

Your value in the database doesn't ahve a space at the beginning so there is no match.

 

Plus, your LIKE statement in the query in your code is not using %value%

[riddhi]

Thank you for taking time to go though code. Actually the confusion arises as it is smaler part of larger code.

 

  However, even after making the following changes the same problem presists;-

 

  1> $keyword=trim($keyword); //to remove spaces

 

  2> like '%$keyword'";

 

  please guide me further .

[riddhi]

come on no reply till now??

[Psycho]

Oh, hell. I thought I had posted a response. Must have closed the window before submitting.

 

Anyway, I think you need to verify the query that is getting created. I always save my queries to a variable so I can echo them to the page for debugging. One problem here is that you are using a custom function to run your query. So, it is possible that the problem in in that function as well. But, first try this and verify that the query printed to the page is valid. Copy and paste the query into PHPMyAdmin and see if it gives you the expected results.

 

A few other notes:

1. changed the table aliases to 'cat' & 'prod' instead of 'A' & 'B'. It's jsut good practice to give descriptive names

 

2. I just noticed something out of place in the logic above. The first IF statement (ereg search for '-n') contains the trim and the running of the query. Then you follow that with a test of the number of rows in $res. But, if $keyword does not contain '-n' then $res will never get defined. Youshould only test $res if you have run the query. So, the running of the query and the test of the query shoud both be inside or outside that first IF statement.

 

3. I removed the first IF statement entirely on the assumption that this code only gets run if $keyword has a value. There is no harm in trimming a string that doesn't need to be.

 

<?php

$keyword=trim($keyword,"-n"); // Strips off -n option
$keyword=trim($keyword); // Strips white space

  // Create the query
  $query = "
  SELECT prod.PD_THUMBNAIL, prod.PD_NAME,
         prod.PD_DESCRIPTION, prod.PD_ID, prod.PD_PRICE
  FROM  TBL_CATEGORY cat, TBL_PRODUCT prod
  WHERE cat.cat_Parent_ID=$category
    AND cat.CAT_ID=prod.CAT_ID
    AND prod.PD_NAME LIKE '%$keyword%'";

  // Print the query to the page
  echo "<pre>$query</pre>";

  // Run the query	  
  $res=dbQuery($query);

  // Test the results
  if(mysql_num_rows($res) == 0 ){ //feature not found
    echo "<br><br><marquee bgcolor='blue'>
    <b>No Product in The Shop has the Specified Feature</b></marquee>"; 
    exit;
  }

?>