Jump to content


Photo

Searching more than 1 column


  • Please log in to reply
8 replies to this topic

#1 OLM3CA

OLM3CA
  • Members
  • PipPipPip
  • Advanced Member
  • 52 posts
  • LocationCyprus

Posted 19 September 2006 - 11:06 PM

I want to search a word more than one column in table but result doesnt change while I add the other columns.Is there something wrong with the usage of the below code ?

<?php 
                $query="SELECT * FROM link WHERE words LIKE '%".$sword."%' OR url LIKE '%".$sword."%'
		AND status ='1' LIMIT $from, $max_results";
?>

AMD rather than Intel , PHP rahter than ASP

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 20 September 2006 - 12:43 AM

When mixing OR and AND use (..) to specify the logic

Is it
$query="SELECT * FROM link WHERE 
    (words LIKE '%".$sword."%') 
    OR 
    ((url LIKE '%".$sword."%') AND (status ='1')) 
    LIMIT $from, $max_results";    

or is it
       
$query="SELECT * FROM link WHERE 
        ((words LIKE '%".$sword."%') OR (url LIKE '%".$sword."%'))
		AND (status ='1') 
        LIMIT $from, $max_results";    

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 OLM3CA

OLM3CA
  • Members
  • PipPipPip
  • Advanced Member
  • 52 posts
  • LocationCyprus

Posted 20 September 2006 - 10:26 PM

Thank you I have done that query but I got error,Something wrong with the query but what ?

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in ...

<?php	
               
                // This query works fine I think.

		$query="SELECT * FROM links
			 WHERE ((keywords LIKE '%".$sword."%') 
			        OR (url LIKE '%".$sword."%') OR (aciklama LIKE '%".$sword."%')
				OR (baslik LIKE '%".$sword."%'))
			      AND (status ='1') LIMIT $from, $max_results";
		
                $result=mysql_query($query);


                // The problem is in this query I guess

                $total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM links
		       WHERE ((keywords LIKE '%".$sword."%') 
			      OR (url LIKE '%".$sword."%') OR (aciklama LIKE '%".$sword."%')
			      OR (baslik LIKE '%".$sword."%'))
			    AND (status ='1')"),0); 
?>

AMD rather than Intel , PHP rahter than ASP

#4 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 20 September 2006 - 11:14 PM

When using mysql_result(), don't use inline code, assign the result to a variable first...

change this:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM links WHERE ((keywords LIKE '%".$sword."%')
OR (url LIKE '%".$sword."%') OR (aciklama LIKE '%".$sword."%') OR (baslik LIKE '%".$sword."%')) AND (status ='1')"),0);


to this:
$totalsql = "SELECT COUNT(*) as Num FROM links WHERE ((keywords LIKE '%".$sword."%') OR (url LIKE '%".$sword."%')
OR (aciklama LIKE '%".$sword."%') OR (baslik LIKE '%".$sword."%')) AND (status ='1')";
$sqlresult = mysql_query($totalsql);
$total_results = mysql_result($sqlresult, 0);


Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#5 OLM3CA

OLM3CA
  • Members
  • PipPipPip
  • Advanced Member
  • 52 posts
  • LocationCyprus

Posted 20 September 2006 - 11:35 PM

Thank you for your reply but the same problem still continious...
AMD rather than Intel , PHP rahter than ASP

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 20 September 2006 - 11:53 PM

Try to find out why query gives error (I agree Huggie, don't use mysql_query inline).
Try
<?php
$result = mysql_query("SELECT COUNT(*) as Num FROM links
		       WHERE ((keywords LIKE '%".$sword."%') 
			      OR (url LIKE '%".$sword."%') OR (aciklama LIKE '%".$sword."%')
			      OR (baslik LIKE '%".$sword."%'))
			    AND (status ='1')") or die (mysql_error());

$total_results = mysql_result($result, 0); 
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 OLM3CA

OLM3CA
  • Members
  • PipPipPip
  • Advanced Member
  • 52 posts
  • LocationCyprus

Posted 21 September 2006 - 12:07 AM

Thank you very much I am so ashamed that I have done a vaiable name mistake I am sorry thank you for your help but I have learned not to use mysql_result in line code thank you.
AMD rather than Intel , PHP rahter than ASP

#8 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 21 September 2006 - 12:20 AM

Thank you very much I am so ashamed that I have done a vaiable name mistake


Haven't we all.

Get into the habit of using ... or die(mysql_error()), or better still, something like this, so you can also print the query giving the problem
$sql = "SELECT bla etc"
$result = mysql_query ($sql) or die (mysql_error() . " in <p>$sql</p>");

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#9 OLM3CA

OLM3CA
  • Members
  • PipPipPip
  • Advanced Member
  • 52 posts
  • LocationCyprus

Posted 21 September 2006 - 12:25 AM

ok I take your advises and will apply them.thanks
AMD rather than Intel , PHP rahter than ASP




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users