Solved

query for two fields from one table in mysql

Posted on 2011-03-14
9
235 Views
Last Modified: 2012-06-27
hi,
iam trying to search for a word from two fields in one table . for example :

topic_title

topic_txt

i want search for "camel" in both fields .How can i do that?.

i tryed this one but it didn't work...
SELECT topic_title & ' ' & topic_txt as fulltxt FROM topic where fulltxt LIKE '$keyword'

Open in new window

0
Comment
Question by:eCamel
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35130105
in mysql, to concat, use concat:

SELECT concat(topic_title , ' ' , topic_txt ) as fulltxt FROM topic where fulltxt LIKE '$keyword'



0
 
LVL 2

Expert Comment

by:sihar86
ID: 35130141
use wildcard character

SELECT topic_title, topic_txt FROM topic where topic_title LIKE  '%$keyword%'  or  topic_txt  LIKE  '%$keyword%'

Open in new window

0
 

Author Comment

by:eCamel
ID: 35130152
thanx for your quick reply

i tryed your code but it gave me this in php

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in C:\AppServ\www\eharaj\inc\search.php on line 26

Warning: Division by zero in C:\AppServ\www\eharaj\inc\class.pager.php on line 28

Open in new window

0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35130164
indeed, you cannot reuse the alias in the WHERE clause
SELECT concat(topic_title , ' ' , topic_txt ) as fulltxt 
  FROM topic 
 where concat(topic_title , ' ' , topic_txt ) LIKE '$keyword'

Open in new window

0
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 500 total points
ID: 35130184
You need to post the PHP code just before line 26 of C:\AppServ\www\eharaj\inc\search.php

It SHOULD look something like this

$rs = mysql_query("SELECT topic_title, topic_txt FROM topic where topic_title LIKE  '%$keyword%'  or  topic_txt  LIKE  '%$keyword%' ");

....

if ( mysql_num_rows( $rs ) ...... etc )

The $rs should be what came back from the query running successfully. YOu can add an extra check like so

if ( ! $rs )
     echo "Query failed - " . mysql_error();
else {
     if ( mysql_num_rows( $rs ) ...... etc )
      ..... more code
}
0
 

Author Comment

by:eCamel
ID: 35130213
i tryed the second answer and also gave me the same error
0
 

Author Comment

by:eCamel
ID: 35130257
ok i think i need to post my whole code here it is >> can you figure out what is wrong??

$num1 = mysql_query("SELECT topic_title, topic_txt FROM topic where topic_title LIKE  '%$keyword%'  or  topic_txt  LIKE  '%$keyword%'");
$max=mysql_numrows($num1);
$pager = new pager( 
    $max ,                     /*see above*/
    $number ,                         /*how many records to display at one time*/
    @$_GET['_p']  /*this is the current page no carried via _GET*/
    ) ;
$pager->set_range(10);
//end pager//
$result = mysql_query("SELECT topic_title, topic_txt FROM topic where topic_title LIKE  '%$keyword%'  or  topic_txt  LIKE  '%$keyword%' ORDER BY mthbt,lastupdate DESC LIMIT ".$pager->get_limit());
$num=mysql_numrows($result);
while($r=mysql_fetch_array($result))
{	
$tid=$r["id"];
$themsg .=get_topic($tid);
}

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
ID: 35131705
try this.
$num1 = mysql_query("SELECT * FROM (SELECT concat(topic_title, ' ',topic_txt) as fulltext FROM topic) as t1 where fulltext LIKE  '%$keyword%'");

Open in new window

0
 

Author Closing Comment

by:eCamel
ID: 35131761
that was the right answer thanks ....

thank you all.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question