How to search fields?

898 Posts

chucktrukk Reply #1, 19 years, 9 months ago

I am modifying HyperFlexSearch to work with ppp or any outside table.

In this code I am trying to see if the search string matches data from any column in the table. I am trying to loop through to get a ’id LIKE ’%$searchString%’ OR webuser LIKE ’%$searchString%’’

Here is my code:

    $tbl = $modx->dbConfig['dbase'] . "." . $modx->dbConfig['table_prefix'] . "testing_pk";

		$searchFields = '';
		$tblColumns = $modx->db->getColumnNames("SELECT * FROM $tbl");
		foreach($tblColumns as $column) {
			$searchFields .= $column. " LIKE '%$searchString%' ";
		}

Thanks,
Chuck

Chuck the Trukk
ProWebscape.com :: Nashville-WebDesign.com
- - - - - - - -
What are TV's? Here's some info below.
http://modxcms.com/forums/index.php/topic,21081.msg159009.html#msg1590091
http://modxcms.com/forums/index.php/topic,14957.msg97008.html#msg97008

898 Posts

chucktrukk Reply #2, 19 years, 9 months ago

It looks like I have it working. Is there a better way than the following?

		$searchFields = array();
		$tblColumns = $modx->db->getColumnNames("SELECT * FROM $tbl");

		$result = count($tblColumns);
		$sqlWhere = '';

		while($i<=$result) {
			if(!isset($tblColumns[$i])) {
				unset ($tblColumns[$i]);
			}
			elseif($i==$result-1){
		  	$sqlWhere .= $tblColumns[$i] . " LIKE '%$searchString%' ";
			}
			else {
			  $sqlWhere .= $tblColumns[$i] . " LIKE '%$searchString%' OR ";
			}
		  $i++;
  	}
<h1>Testing</h1>
echo $sqlWhere;

Chuck the Trukk
ProWebscape.com :: Nashville-WebDesign.com
- - - - - - - -
What are TV's? Here's some info below.
http://modxcms.com/forums/index.php/topic,21081.msg159009.html#msg1590091
http://modxcms.com/forums/index.php/topic,14957.msg97008.html#msg97008

MODX Staff
10,725 Posts

opengeek Reply #3, 19 years, 9 months ago

First, change that SQL in the getColumnNames() call. You definitely don’t want to load every row in the database just to get the column names (what if it had a million rows?). You just need one row from the db to do that.

Otherwise, that’s not a bad way, though you can also do a lot cooler things using MySQL full-text indexes, including relevance ranking, full boolean search capabilities, and more, but you need control over the indexes in the tables to do that. Other benefits of this approach are improved search times, as often doing OR searches across every column in a table is going to take a lot more processor time than searching a dedicated full-text index. It is also less resource intensive in general to provide dedicated indexes for those kinds of searches. A minus is this is typically completely dependent on proprietary database features.

BTW, on a side note: one of the reasons I created xPDO was to be able access column meta data without having to even open a connection to the database. This comes in handy when you want to do full database result-set caching, and also eliminates the need to query a row from the table before being able to get that metadata. The same mechanisms in xPDO will also help developers (and eventually end-users) control advanced features, like custom full-text indexes to search specific important fields on the objects.

Anyway, enough of my rambling... rolleyes

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

898 Posts

chucktrukk Reply #4, 19 years, 9 months ago

Thanks Jason,

I will look into the MYSQL Search Indexes and see what can be done.

Also, I changed the {while} to a {for} as it seemed to get better times.

Chuck

Chuck the Trukk
ProWebscape.com :: Nashville-WebDesign.com
- - - - - - - -
What are TV's? Here's some info below.
http://modxcms.com/forums/index.php/topic,21081.msg159009.html#msg1590091
http://modxcms.com/forums/index.php/topic,14957.msg97008.html#msg97008

898 Posts

chucktrukk Reply #5, 19 years, 9 months ago

I am using a modified version of ppp to create the columns this table will search.

Would it be bad practice to automatically create/update an index automatically when I add a new field?

i was thinking of doing it this way:

		$tblColumns = $modx->db->getColumnNames("SELECT * FROM $tblToSearch LIMIT 1");
		$result = count($tblColumns);

		for ($i = 0; $i <= $result; $i++) {
			if(!isset($tblColumns[$i])) {
				unset ($tblColumns[$i]);
			}	elseif($i==$result-1){
				$columnsIndex .= $tblColumns[$i];
			}	else {
				$columnsIndex .= $tblColumns[$i] . ', ';
			}
		}
then...
if IDX_ALLFIELDS does not exist {
    CREATE INDEX IDX_ALLFIELDS
    on $tblToSearch ($columnsIndex)
} else {
   ALTER INDEX etc....
}

Chuck the Trukk
ProWebscape.com :: Nashville-WebDesign.com
- - - - - - - -
What are TV's? Here's some info below.
http://modxcms.com/forums/index.php/topic,21081.msg159009.html#msg1590091
http://modxcms.com/forums/index.php/topic,14957.msg97008.html#msg97008