When searching the table using LIKE operator, I can easily find the category if I type a number. What I need is to actually type in the category’s value, and not its key and then find it against the entries in the table. I’ve been trying to include the array to the search using completely weird stuff, but to no vail.
Found here, in these forums, a lead: implode INSIDE the query but I was not able to pull it.
If you guys could help me out here, I’d be eternally grateful
My SQL query:
SELECT DISTINCT
`swm_id`,
`name`,
`desc1`,
`category`,
`file`
FROM
`swims`
WHERE
(
`name` LIKE '%".$q."%'
OR `desc1` LIKE '%".$q."%'
OR `file` LIKE '%".$q."%'
OR `swm_id` LIKE '%".$q."%'
>> OR `category` LIKE '%".$q."%' <<
)
AND `active` = '1'
Can you post your table structure for both tables? Are you using 2 tables or is this all one table? Either way, the full table structure will be useful.
Hey, thanks for you input.
Here’s the structure of the table:
CREATE TABLE IF NOT EXISTS `swims` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`swm_id` int(4) NOT NULL,
`name` varchar(150) NOT NULL,
`desc1` text NOT NULL,
`category` int(3) NOT NULL,
`file` varchar(150) NOT NULL,
`submitted_on` datetime NOT NULL,
`active` enum('0','1') NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
Basically what need to achieve is when I type something in the search box, eg. “Plu”, or “Plumb”, or “Plumbing” I need that to be understood by script as if I was looking for Category 3. At the same time “Elec”, or “Electric” to be translated into Category 1 cus there are only numeric values in that column.
My brain must be looking now like a potato soup, I am completely out of ideas.
Hi, I know the ideal solution would be to set up another db (table) and I agree that’d be the best solution. However, for few reasons that cannot be done for this project so I guess I will have to drop the idea of searching for the Category.
Thank you all for your input!
Not sure what other checks you have for $q but you might need to define $cat=0; before running foreach check just in case $q is only one character in length and thus would be undefined in query.
<?php
$swm_array = array(
'1' => 'Electrical',
'2' => 'HVAC',
'3' => 'Plumbing',
);
$cat = 0;
if(isset($q)):
if (strlen($q)>1):
$search = strtolower($q);
foreach($swm_array as $k => $v):
$match = stristr($v,$search);
if(!empty($match)){
$cat = array_search($match,$swm_array);
break;
}else{
$cat = 0;
}
endforeach;
endif;
endif;
$sql = "SELECT DISTINCT
`swm_id`,
`name`,
`desc1`,
`category`,
`file`
FROM
`swims`
WHERE
(
`name` LIKE '%".$q."%'
OR `desc1` LIKE '%".$q."%'
OR `file` LIKE '%".$q."%'
OR `swm_id` LIKE '%".$q."%'
OR `category` = '".$cat."'
)
AND `active` = '1'";
echo "$sql<br />";
?>
