mysql select query search one word in multiple columns

Question

Help is required. i am preparing a search form. need help in select query is it possible that i can search "building" word in all columns of my table following is the query i've tried.

$search = $_POST['search'];
$sql = mysql_query("SELECT * FROM property WHERE posthead,requestto,ptype,requestby,owner,bed,bath,price,sqft,descp = '".$search."'");
$result = (mysql_fetch_array($sql));

echo "Post Head : ". $result['posthead']."<br />";
echo "Request To : ". $result['requestto']."<br />";
echo "Type : ". $result['ptype']."<br />";
echo "Request By : ". $result['requestby']."<br />";
echo "Owner : ". $result['owner']."<br />";
echo "Bed : ". $result['bed']."<br />";
echo "Bath : ". $result['bath']."<br />";
echo "Price : ". $result['price']."<br />";
echo "Sq ft. : ". $result['sqft']."<br />";
echo "Description : ". $result['descp']."<br />";

Yes, read: dev.mysql.com/doc/refman/5.0/en/where-optimizations.html — Jon
– Jon, Commented Feb 13, 2013 at 4:48

Prasanth Bendra · Accepted Answer · 2013-02-13 04:48:50Z

2

$sql = mysql_query("SELECT * FROM property WHERE posthead = '".$search."' OR requestto= '".$search."' OR ptype= '".$search."' OR requestby = '".$search."' OR owner= '".$search."' OR bed = '".$search."' OR bath= '".$search."' OR price= '".$search."' OR sqft,descp = '".$search."'");

and change $result = (mysql_fetch_array($sql)); to $result = (mysql_fetch_assoc($sql));

NOTE :

your query is vulnerable to sql injection
mysql_* functions are deprecated use mysqli_* or PDO

answered Feb 13, 2013 at 4:48

Prasanth Bendra

33.1k9 gold badges56 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DWX · Accepted Answer · 2014-12-27 20:19:50Z

1

TRY THIS:

Better is to use "CONCAT" for the columns:

$sql = mysql_query("SELECT * FROM property WHERE CONCAT(posthead,requestto,ptype,requestby,owner,bed,bath,price,sqft,descp) LIKE "%.$search."%'");

OR will work but CONCAT can reduce query size.

answered Dec 27, 2014 at 20:19

community wiki

DWX

Comments

afarazit · Accepted Answer · 2013-02-13 04:49:47Z

0

Try SELECT * FROM property WHERE posthead = $search OR requestto = $search ... etc

answered Feb 13, 2013 at 4:49

afarazit

4,9692 gold badges31 silver badges53 bronze badges

Comments

peterm · Accepted Answer · 2013-02-13 05:20:33Z

0

Since you are creating a searching form you might want to be able to find records by partial match and not exact word. In order to do that you can use LIKE

$q = "SELECT * FROM property 
WHERE posthead  LIKE '%$search%' OR
      requestto LIKE '%$search%' OR
      ptype     LIKE '%$search%' OR
      requestby LIKE '%$search%' OR
      owner     LIKE '%$search%' OR
      bed       LIKE '%$search%' OR
      bath      LIKE '%$search%' OR
      price     LIKE '%$search%' OR
      sqft      LIKE '%$search%' OR
      descp     LIKE '%$search%'";

$sql = mysql_query($q);

edited Feb 13, 2013 at 5:20

answered Feb 13, 2013 at 5:08

peterm

93k16 gold badges157 silver badges162 bronze badges

Collectives™ on Stack Overflow

mysql select query search one word in multiple columns

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related