Sedang Disiapkan

218979 PHP MySQL search form query

Hi,

We have a form on our site which people can enter up to 4 different fields. Visitors can enter all or just some of the fields and search our site for venues. This results in a query string in the form of:

[url removed, login to view];city=Bideford&county=Devon&venue_type=Hall

So the variables are as follows:

county

city

venue_name

venue_type

I'd like to get help writing a php query to extract distinct venue information from the database on each search. Only records that have all the search terms entered should be returned. This way people can narrow the search down by adding information to the form rather than widening it.

The two tables that hold the information are structured as follows:

TABLE `listingsDB` (

`ID` int(11) NOT NULL auto_increment,

`user_ID` int(11) NOT NULL default '0',

`Title` varchar(80) NOT NULL default '',

`expiration` date NOT NULL default '0000-00-00',

`notes` text NOT NULL,

`creation_date` date NOT NULL default '0000-00-00',

`last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

`hitcount` int(11) NOT NULL default '0',

`featured` char(3) NOT NULL default 'no',

`active` char(3) NOT NULL default 'yes',

PRIMARY KEY (`ID`),

KEY `active` (`active`),

KEY `featured` (`featured`),

KEY `creation_date` (`creation_date`),

KEY `last_modified` (`last_modified`),

TABLE `listingsDBElements` (

`ID` int(11) NOT NULL auto_increment,

`field_name` varchar(20) NOT NULL default '',

`field_value` text NOT NULL,

`listing_id` int(11) NOT NULL default '0',

`user_id` int(11) NOT NULL default '0',

PRIMARY KEY (`ID`),

KEY `user_id` (`user_id`),

KEY `listing_id` (`listing_id`),

KEY `fieldname_valueindex` (`field_name`,`field_value`(100))

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=595501 ;

Here is some sample data for each of these tables:

listingsDBElements :example data

ID field_name field_value listing_id user_id

545411 county Avon 432 362

545410 city Wimbledon 1222 332

545409 street The Broadway 3811 557

545408 venue_name Eastbury Manor 1 103

500737 parking Yes 3812 775

500738 venue_type Hall 3 125

545411 county Essex 354 557

545410 city Bristol 432 362

545409 street The Broadway 3811 881

545408 venue_name Antoinette Hotel 3811 902

500758 venue_type Hotel 1 103

listingsDBElements :example data

ID user_ID Title active

1 103 Eastbury Manor yes

3811 557 The Broadway yes

3 125 Old Hall yes

I have a php query that works fine when just searching for matches against one field but can't seem to get multiple field selections working. Here is the working php I have to search for a single search field eg. county

$sql3="SELECT DISTINCT listingsDBElements.listing_id, listingsDBElements.user_id FROM listingsDBElements LEFT JOIN listingsDB on (listingsDBElements.listing_id = [url removed, login to view] ) WHERE ";

$sql3 .= "[url removed, login to view] = 'yes' ";

if (trim($county) != "") {

$sql3 .= " and ( listingsDBElements.field_name = 'county' AND listingsDBElements.field_value like '%$county%') ";

}

$sql3 .= "order by listingsDBElements.user_id DESC";

However when I try getting it working for 2 or more fields searched it does not work. eg.

$sql3="SELECT DISTINCT listingsDBElements.listing_id, listingsDBElements.user_id FROM listingsDBElements LEFT JOIN listingsDB on (listingsDBElements.listing_id = [url removed, login to view] ) WHERE ";

$sql3 .= "[url removed, login to view] = 'yes' ";

if (trim($county) != "") {

$sql3 .= " and ( listingsDBElements.field_name = 'county' AND listingsDBElements.field_value like '%$county%') ";

}

if (trim($city) != "") {

$sql3 .= " and (listingsDBElements.field_name = 'city' AND listingsDBElements.field_value like '%$city%') ";

}

$sql3 .= "order by listingsDBElements.user_id DESC";

If more information is needed I can supply.

Bob

Kemahiran: Semua Boleh, MySQL, PHP

Lihat lebih lanjut: writing com search, text string search, string searching in c, string searching, string search in c, some search string, search text string, search string in c, search string c, search string, searching a string in c, searching a string, search here, search for people, search engine people search, search engine people, search c string, php and mysql search engine, people search work, c string search, c search string, broadway.com, Trim, title searching, timestamp

Tentang Majikan:
( 5 ulasan ) Instow, United Kingdom

ID Projek: #1965212

Dianugerahkan kepada:

ecartsolutions

Can get it done right away.. please check message board for solution approach.. Hari

$30 USD dalam 0 hari
(310 Ulasan)
6.5