Query from variable column name?

[RichardODreamweaver]

Running on LAMP

Table - CONTACTS (35 COLUMNS APPROX)

I want to be able to create a results page from a detailed query.

My first attempt was to include 35 text fields in a form and then build the query: like '%namesearch%' and like '%companysearch%' and like '%companytype%' etc. etc. etc.

But this is time consuming and prone to bugs.

I though a much easier way would be to have two fields: one criteria field and one field representing the column you want to search in....

Hmm...

This is the query that I thought might work.

SELECT * FROM CONTACTS
WHERE 'colsearch' LIKE '%critsearch%'
ORDER BY COMPANY, FULLNAME

Obviously 'colsearch' is a variable from a menu drop down field.

Strangely, if I set the default value for colsearch as COMPANY and default value for critsearch as '%', it does seem to return a result (all records) on the page load (I have the form and results on the same page) but if I hit submit, it returns no recordset!

Is this even possible?

CODE:

$colsearch_recordlookup = "COMPANY";
if (isset($HTTP_POST_VARS['scolumn'])) {
$colsearch_recordlookup = (get_magic_quotes_gpc()) ? $HTTP_POST_VARS['scolumn'] : addslashes($HTTP_POST_VARS['scolumn']);
}
$critsearch_recordlookup = "%";
if (isset($HTTP_POST_VARS['scriteria'])) {
$critsearch_recordlookup = (get_magic_quotes_gpc()) ? $HTTP_POST_VARS['scriteria'] : addslashes($HTTP_POST_VARS['scriteria']);
}
mysql_select_db($database_dbcrm1, $dbcrm1);
$query_recordlookup = sprintf("SELECT * FROM CONTACTS WHERE '%s' LIKE '%%%s%%' ORDER BY COMPANY, FULLNAME", $colsearch_recordlookup,$critsearch_recordlookup);

[Newsgroup_User · Accepted solution]

RichardODreamweaver wrote:
> This is the query that I thought might work.
>
> SELECT * FROM CONTACTS
> WHERE 'colsearch' LIKE '%critsearch%'
> ORDER BY COMPANY, FULLNAME
>
> Obviously 'colsearch' is a variable from a menu drop down field.

The name of the column should not be in quotes:

SELECT * FROM CONTACTS
WHERE colsearch LIKE '%critsearch%'
ORDER BY COMPANY, FULLNAME

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/