[SOLVED] Select distinct results from form with 4 fields

[bobby4]

Hi,

Hopefully this is easy for someone suggest a fix. I've spent all day on it but not getting closer.

 

I have two tables which contains venue information. Here is the table format

 

-- listingDBElements --

ID field_name field_value listing_id user_id

23 county         Yorkshire       34     56

43 county         Yorkshire       26     45

45 city           London         22       1

67 venue_name    The Mill             2           22

55 venue_type Hall       34     8

55 description Nice place       34     8

59 venue_type Hotel       2     55

etc..

 

-- listingDB --

ID user_ID Title                           active

1 103           Eastbury Manor House   yes

2  1              Avenue House        no

etc..

 

I also have a search form on the site that people can select or enter information they want to search for on a venue.:

 

Venue name (variable = $venue_name)

City or town (variable = $city)

County (variable = $county, a drop down list)

Venue type (variable = $venue_type, a drop down list)

 

I'd like to return distinct listing ID references from the first table and check with the second table that the listings are marked as 'active=yes'.

 

I can only seem to get all venues returned related to any part of a search field entered, rather than just say venues that are a 'Hall' in 'Yorkshire'. My select statement seems to get all the 'Yorkshire' venues AND all the 'Hall' venues.

 

I tried using Distinct but think I am out of my depth here. Any suggestions welcomed.

 

MySQL client version: 4.1.20

 

Oh and here is the php I have so far:

 

if ($venue_name !=""){ // if we have a name search for it
$sql3 ="SELECT DISTINCT listingsDBElements.listing_id, listingsDBElements.user_id FROM listingsDBElements, listingsDB WHERE ";
$sql3 .= "listingsDB.active = 'yes'";
$sql3 .= " AND listingsDBElements.field_name = 'venue_name' AND listingsDBElements.field_value like '%$venue_name%' ";
}

if ($city !=""){ // if we have a name search for it
$sql3 .= " UNION ";
$sql3 ="SELECT DISTINCT listingsDBElements.listing_id, listingsDBElements.user_id FROM listingsDBElements, listingsDB WHERE ";
$sql3 .= "listingsDB.active = 'yes'";
$sql3 .= "AND listingsDBElements.field_name = 'city' AND listingsDBElements.field_value like '%$city%'";
}

if ($county !=""){ // if we have a name search for it
$sql3 .= " UNION ";
$sql3 ="SELECT DISTINCT listingsDBElements.listing_id, listingsDBElements.user_id FROM listingsDBElements, listingsDB WHERE ";
$sql3 .= "listingsDB.active = 'yes'";
$sql3 .= "AND listingsDBElements.field_name = 'county' AND listingsDBElements.field_value like '%$county%'";
}

if ($venue_type !=""){ // if we have a name search for it
$sql3 .= " UNION ";
$sql3 ="SELECT DISTINCT listingsDBElements.listing_id, listingsDBElements.user_id FROM listingsDBElements, listingsDB WHERE ";
$sql3 .= "listingsDB.active = 'yes'";
$sql3 .= "AND listingsDBElements.field_name = 'venue_type' AND listingsDBElements.field_value like '%$venue_type%'";
}


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

$resultvens = mysql_query($sql3);

 

Many thanks,

 

Bob

[fenway]

What's the final sql query?

[bobby4]

Ah, Sorry,

 

The next part of the sql is this:

 

$resultvensRecordSet = $conn->SelectLimit($sql3, 10, $limit_str );
if ($resultvensRecordSet === false) log_error($sql3);

while (!$resultvensRecordSet->EOF)  
{
$current_ID = $resultvensRecordSet->fields[listing_id];
?>
Then output html with functions using current_ID to get images and venue details
<? 

$resultvensRecordSet->MoveNext();
} // end while

 

Hope that is what you needed. The search relates to the directory search on Weddingvenues dot com if thats also a help. But at the moment it collects all the listings data we have a deletes what we don't need which is slow going.

 

Many thanks

 

Bob

[fenway]

No, it's not what I needed... I need plaintext SQL queries.

[bobby4]

Hi Fenway,

 

Sorry I misunderstood. I think this is what you are after. This is the sql query when I search for 'Barn' venues in a county called 'Cornwall'. With the hope that it will show all the Barns in Cornwall but it shows just returns all the Barns in the whole country. Seems I am not using 'Union' properly for a start.

 

SELECT DISTINCT listingsDBElements.listing_id, listingsDBElements.user_id FROM listingsDBElements, listingsDB WHERE listingsDB.active = 'yes' AND listingsDBElements.field_name = 'venue_type' AND listingsDBElements.field_value like '%barn%' order by listingsDBElements.user_id DESC 

 

Any ideas anyone?

 

Many thanks,

Bob

[bobby4]

Just to be completely clear what I'm after.

 

When i search for venues that are Barns in Cornwall I'm asking the database to search listingsDBElements table column field_name and match against 'venue_type' and  then check to see if the field_value column matches my venues type eg. Barn. Then if we get a hit I'd like to get the listings ID and check that this listing also has a field_name that matches against 'county' and the field_value columns value for it is the county I have chosen eg. Cornwall.

 

Is this maybe something to do with an inner join? Not entirely sure how to use this.

 

Hope this helps,

 

Bob

[fenway]

Where's the join condition relating the two tables?

[bobby4]

Hi Fenway,

 

Good point. This is the bit I have a problem with. Clutching at straws really. I'm just guessing it's an Inner Join I  need to use. Can you suggest a solution or can I give you more info to help work this out in any way?

 

Many thanks,

 

Bob

[bobby4]

Oh, all sorted.

Someone posted this on another site which works a treat. I hope it helps anyone who is in a similar stop

SELECT vn.listing_id, ld.user_ID, ld.Title
FROM listingsDB ld
LEFT JOIN listingsDBElements vn
    ON vn.listing_id = ld.ID
LEFT JOIN listingsDBElements vc
    ON vc.listing_id = ld.ID
WHERE ld.active = 'yes'
  AND ( (vn.field_name = 'venue_type' AND vn.field_value like '%$venue_type%')
     OR '$venue_type' = ''
    )
  AND ( (vc.field_name = 'county' AND vc.field_value like '%$county%')
     OR '$county' = ''
    )
order by ld.user_ID DESC

 

Hope this is a help for others in the same sot

 

bob