Solved

query for two fields from one table in mysql

Posted on 2011-03-14
9
236 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 143

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 143

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
XML extra information 8 28
How does PHP Storm display on Linux high resolution laptops? 1 36
Where on a calculated field 1 20
issue with DB import 1 17
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 …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

791 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