Page 1 of 1

searching all fields in mysql database

Posted: Mon Nov 16, 2009 11:50 am
by getseen
Hi there, hoping someone can help me.

Ive got my database and I want to be able to search all fields in a certain table.

Ive got code that can retrieve data from one specific table field, but I need it to be able to search through ALL of the fields in my table and then use an array to list the data.

Im not going to include all the code as Im hoping its just these snippets.

this gets the variable from the previous form

$var = @$_GET['q'] ;
$trimmed = trim($var);

and this bit is the mysql query

$query = "select * from listed_properties where county like \"%$trimmed%\"
order by county"

Re: searching all fields in mysql database

Posted: Mon Nov 16, 2009 12:50 pm
by akuji36
Hello

This should be fine. It connects mysql with php

then selects and returns all the data.

Code: Select all

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("my_db", $con);
 
$result = mysql_query("SELECT * FROM Persons");
 
while($row = mysql_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "<br />";
  }
 
mysql_close($con);
?>

Note: the table name is Persons/

the names of the columns:

FirstName And LastName

and database names is my_db

thanks

Rod

Re: searching all fields in mysql database

Posted: Mon Nov 16, 2009 1:17 pm
by Apollo
Here's what you need:

Code: Select all

$fields = array();
$result = mysql_query("SHOW COLUMNS FROM dinertable");
if (mysql_num_rows($result) > 0) 
{ 
    while ($row = mysql_fetch_assoc($result)) $fields[] = $row['Field'];
}
$like = " LIKE '%".mysql_real_escape_string($var)."%' ";
$where = implode($like.' OR ',$fields).$like;
$result = mysql_query('SELECT * FROM dinertable WHERE '.$where);
// have fun with $result