am i doing this mysql_query incorrectly?

[jeger003]

hello everyone,

with the help of users here i created a small search feature for my site (classified site) but when i search for an item i need it to display only "live" listings. it doesnt....it searches and displays all matches

 

 

my query is here

$query_search = mysql_query("SELECT title,id,live,description,search_text,image,date,price,location_city 
				FROM   cls_classifieds 
				WHERE  live = 1
				AND    search_text 
				LIKE   '%$search_words%' 
				OR     title 
				LIKE   '%$search_words%'
				OR     description 
				LIKE   '%$search_words%'") or die(mysql_error());

 

what am i doing wrong? by the way it runs without any errors. so it does work.

 

 

thanks

[Ken2k7]

What's with all the  ?

[jeger003]

What's with all the  ?

 

lol idk my code doesnt have that........i think it showed up after i edited it....

 

 

here it is again

$query_search_exact_match = mysql_query("SELECT title,id,live,description,search_text,image,date,price,location_city 
							FROM   cls_classifieds 
							WHERE  live = 1
							AND    search_text 
							LIKE   '%$search_words%' 
							OR     title 
							LIKE   '%$search_words%'
							OR     description 
							LIKE   '%$search_words%'") or die(mysql_error());

[Ken2k7]

So is your issue that it matches all entries in cls_classified or that it matches all entries both live an non-live?

[jeger003]

i guess you can say it matches all entries live and non live...........so my issue is getting to display only live in the table cls_classifieds

[Ken2k7]

$query_search_exact_match = mysql_query("SELECT title,id,live,description,search_text,image,date,price,location_city 
							FROM   cls_classifieds 
							WHERE  live = '1'
							AND  ( search_text 
							LIKE   '%$search_words%' 
							OR     title 
							LIKE   '%$search_words%'
							OR     description 
							LIKE   '%$search_words%')") or die(mysql_error());

What about that? Is live an non-live triggered by 1 and 0?

[jeger003]

thats a good catch but it didnt actually work......and yes they are triggered by 1 and 0.........i cant figure out why it would be doing that or wat could possibly make it do that

[Ken2k7]

Is this doing the right thing so far:

 

$query_search_exact_match = mysql_query("SELECT `title`, `id`, `live`, `description`, `search_text`, `image`, `date`, `price`, `location_city` FROM `cls_classifieds` WHERE `live` = '1' LIMIT 20;");

 

That should return the first 20 elements where live is 1.

[jeger003]

Is this doing the right thing so far:

 

$query_search_exact_match = mysql_query("SELECT `title`, `id`, `live`, `description`, `search_text`, `image`, `date`, `price`, `location_city` FROM `cls_classifieds` WHERE `live` = '1' LIMIT 20;");

 

That should return the first 20 elements where live is 1.

 

no it didnt work........i dont think it makes a difference since when i run the query it definitly works....but i double checked everything even my db and still same result........i even tested it........like this

 

if($query['live'] == 1)
            {
               echo "it works!";
             }

 

so now we know that the live is being called from the query.....but not sure why its not selecting only 1's

[ignace]

Please post your code. If the query from Ken2k7 didn't work then that means that a) you have no records with live = 1 or b) there is something wrong with your code. I presume the latter.

[jeger003]

i double checked my code and i cant find any errors........and im 100% that there are listings with live = 1 so here is my code and hopefully you can catch what i cant

 

also i removed some unnecessary code

 

ini_set('error_reporting', E_ALL);


$search_words = mysql_real_escape_string($_POST['szs']);

$query_search_exact_match = mysql_query("SELECT title, 
								   id,
								   live,
								   description, 
								   search_text, 
								   image, 
								   date, 
								   price,
								   location_city 
							FROM   cls_classifieds 
							WHERE  live = '1'
							AND    search_text 
							LIKE   '%$search_words%' 
							OR     title 
							LIKE   '%$search_words%'
							OR     description 
							LIKE   '%$search_words%'") or die(mysql_error());

$num_exact = mysql_num_rows($query_search_exact_match);   

//----------------below is the word match that is exact--------
if($num_exact > 0)
{

$num_sub_one = ($num_exact - 1 );

while ($fetch_exact = mysql_fetch_array($query_search_exact_match) )
{

  while ($fetch_exact = mysql_fetch_array($query_search_exact_match) )
      {

      	 		if($fetch_exact['image'] > 0)
	      	 {
	      	 $selections = "thumb_filename,thumb_url,full_filename,classified_id,image_width,image_height,original_image_height,original_image_width,display_order";
	      	 $from = "cls_classifieds_images_urls";
	      	 $image = mysql_query("SELECT $selections
			   					   FROM $from 
								   WHERE classified_id = '".$fetch_exact['id']."' AND '".$fetch_exact['live']."' = 1") 
								   or die(mysql_error());
								   
	      	 $fetch_file = mysql_fetch_array($image);
	      	 	if($fetch_file['display_order'] == 1)
	      	 	{
			      	 	if($fetch_file['thumb_filename'] == 0)
			      	 	{
			      	 	$thumb_url = "user_images/".$fetch_file['full_filename']."'";
			      	 	$height = $fetch_file['original_image_height'];
			      	 	$width = $fetch_file['original_image_width'];
				      	 	if($width > $height)
				      	 	{
							$newwidth=49;
							$newheight=ceil(($height/$width)*$newwidth);	

				      	 	}
				      	 	else
							{
							$newheight=50;
							$newwidth=ceil(($width/$height)*$newheight);
							}
	     	 	

			      	 	}
			      	 	else
			      	 	{
			      	 	$thumb_url = "user_images/".$fetch_file['thumb_filename']."'";
			      	 	//$mouse_over_image = "/".$fetch_file['thumb_url']."'";
			      	 	$height = $fetch_file['original_image_height'];
			      	 	$width = $fetch_file['original_image_width'];
				      	 	if($width > $height)
				      	 	{
							$newwidth=49;
							$newheight=ceil(($height/$width)*$newwidth);	

				      	 	}
				      	 	else
							{
							$newheight=50;
							$newwidth=ceil(($width/$height)*$newheight);
							}

			      	 	}
	      	 	}  	 	
			      	 	

	      	 }
      	 else
      	 {
      	 $thumb_url = 'images/misc/nophoto.gif';
      	 $newwidth = '49' ;
      	 $newheight = '37';
      	 }
      	 echo "<tr>";
      	 
      	 $ad_num = $fetch_exact['id'];

	if($background_color == "#C9FFBB")
	{
		$background_color = "#E8FFE1";
	}
	else
	{
		$background_color = "#C9FFBB";
	}


	 echo "<td align='center' bgcolor='$background_color'><a href='/index.php?a=2&b=$ad_num' class='results'><img src='/$thumb_url' width='$newwidth' height='$newheight' border=0></a></td>";
	 echo "<td bgcolor='$background_color'><a href='/index.php?a=2&b=$ad_num' class='results'>".urldecode($fetch_exact['title'])."</a></td>";
	 echo "<td align='center' bgcolor='$background_color' class='results'>".ucwords(strtolower($fetch_exact['location_city']))."</td>";
	 echo "<td align='center' bgcolor='$background_color' class='results'>".$fetch_exact['price']."</td>";
	 echo "<td align='center' bgcolor='$background_color' class='results'>".date('m/d/y', $fetch_exact['date'])."</td>";
	 echo "</tr>";




      }
      echo "</table>";
}
}
//-------------------------below is the word match thats not exact
else
{

[jeger003]

anyone have any idea

[Mchl]

Probably:

WHERE 
  live = '1'
AND (
  search_text LIKE   '%$search_words%' 
    OR
  title LIKE   '%$search_words%'
    OR 
  description LIKE   '%$search_words%'
)