Select query with 4 fields

[bobby44]

Are you any good with mysql queries? I have a prob I need help with.

Think it will involve inner join but I can't get my head around it.

I have two tables:
-- 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 venue_type Hall 22 9
55 description Nice place 34 8
59 venue_type Hotel 2 55
etc..

-- listingDB --
ID user_ID Title active
1 8 Eastbury Manor House yes
2 9 Avenue House yes
3 16 The red House no

etc..

I have a search form on the site that people can select or enter information they want to search for on a venue. The fields are as follows:

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'.
But also if someone searches for $venue_types = Halls and also select $county = Yorkshire i dont want all the venues in Yorkshire and all the Halls in other counties.
Just the Halls in Yorkshire. Same goes for if they search for a town, county, venue type and venue name in one search.

Basically its re-writing the directory search form on the home page of weddingvenues.com which passes a url string like:
http://www.weddingvenues.com/listingsearchfast.php?venue_name=weston&city=Bude&county=Cornwall&venue_type=Barn

So far I have this sql working for just one variable but not sure how to expand to include all 4:

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 and I'd be really grateful. I am using MySQL client version: 4.1.20 and PHP 4.

Many thanks

Bob

[bpat1434]

You'd have to use PHP to construct the query based upon the user input. Your PHP would dynamically generate the "WHERE" clause of your SQL. Something like this:

$query = "SELECT DISTINCT e.listing_id, e.user_id
          FROM `listingsDBElements` AS e
          INNER JOIN `listingsDB` AS l
            ON e.`user_id` = l.`user_ID`
          WHERE l.`active` = 'yes'";

if(isset($_GET['venue_name']))
    $query .= ",
            AND e.`venue_name` LIKE '%" . mysql_real_escape_string($_GET['venue_name']) . "%'";

if(isset($_GET['city']))
    $query .= ",
             AND e.`city` LIKE '%" . mysql_real_escape_string($_GET['city']) . "%'";

if(isset($_GET['county']))
    $query .= ",
             AND e.`county` LIKE '%" . mysql_real_escape_string($_GET['county']) . "%'";

if(isset($_GET['type']))
    $query .= ",
             AND e.`type` LIKE '%" . mysql_real_escape_string($_GET['type']) . "%'";

$query .= "
          ORDER BY e.`user_id` DESC";

Hope that helps.

[bobby44]

Hi,

Great, thanks for the help. Yes I did have php generating the extra bits of the select based on if the user input but didn't add it in case I confused things. I'll certainly try it out and let you know. Thankyou so much for the help.

Just to understand things, is the first part of the select creating a kind of temp table called 'e' with e.listing_id and e.user_id containing all the listings?

Then the part that says:

AS e INNER JOIN `listingsDB` AS l ON e.`user_id` = l.`user_ID`WHERE l.`active` = 'yes'"; 

Does this check that the listings in the temp table are active against the listingDB table?

Then the final select sections containing the where statements only keeps certain values in if they meet the criteria?

I'm going to try it now anyway and will report back.

Again, many thanks.

Bob

[tomhath]

I don't see why you have user_id in both tables, it only needs to be in listingsDB. Anyway, I'd start with something like this. Add two more LEFT JOINs to get the other field_name values. And make sure php substitutes the values in $venue_type, $county, etc.

I'm not a big fan of building queries with string concatenations in php, but lot's of people do it that way and what bobby44 posted looks like it will work. I find it easier to write and test a single query in an admin tool.

I tested this so if it doesn't return anything make sure it isn't a problem in your data.

SELECT ld.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

[bpat1434]

The "e" and "l" items are what's known as "aliases" for the table. Instead of typing "listinsDB" and "listingsDBEntry" I can simply refer to them using shorter names 😉 Just a habit I have when i use multiple tables. Makes things easier for me. So "e" is just a references to listingsDBEntry and "l" is just a reference to "listingsDB", there are no temp tables.

The "INNER JOIN" states that you want to join the "listingsDB" table onto the "listingsDBEntry" table. I use the "user_id" column of the listingsDBEntry to coincide with the user_ID field of the listingsDB table (since I'm presuming those match up).

The first part of the "WHERE" clause makes sure that only the listings with "active" being "yes" are selected.

The rest of the PHP that creates the ", AND column LIKE '%%'" do in fact keep only certain records in the result set.

[bobby44]

Hi Guys,
Ah, thanks again for the help. I have been trying to get it working but no joy. Will try by exchanging the e and l for the table names and see how that goes. Will also give the code from Tomhath a look.

Cheers all,

bob

[bobby44]

Hi Again,

Almost working. I'm afraid I haven't had a lot of luck with bpat1434's solution but maybe I am missing something obvious.

I was successful with Tomhath's but need the Listing_ID which is in the 1st table not the information from the second. Again, if someone could show me how to re-write Tomhath's query so it gets the listing_ID from table one I'd be super grateful. Here is the code for the query:

$sql3 = "SELECT ld.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 ";

Finally, how do I get the data out in a loop.

Many thanks.

Bob

[bpat1434]

Where you have "ld.ID" switch that with "vn.listing_id" 😉 (only in the "SELECT" part of the statement though)

To loop through the results, you can do it a number of ways. Easiest thing is something like:

$result = mysql_query($query);
while($row = mysql_fetch_array($result))
{
  // $row is now an array with whatever returned data...
  print_r($row);
}

Hope that helps.

My previous reply is only 1/2 finished... not sure why I didn't really look it over, but I was baby sitting and she had the hiccups and was screaming in my ear, so I missed a bit 😉

[bobby44]

Well would you look at that. Fantastic result. Works really well.

Many thanks to you both.

Hope the babysitting went well and I'd like to wish everyone on the site a jolly pleasant evening.

Bob

[bogu]

Don't forget to mark your thread as RESOLVED ...

[tomhath]

bobby44 wrote:

need the Listing_ID which is in the 1st table not the information from the second

The JOIN is on vn.listing_id = ld.ID, so if it matches a row in the Elements table they'll be the same. But note that it's doing a LEFT JOIN, so there might not be a match to that table (if the venue_type variable was blank the vn.listing_id might be null). So you should only be selecting fields from the listingsDB table.

Unless you are sure there will always be one and only one Element row of each field_name type ("county", etc) for each listing, in which case you can change the query to use INNER JOIN instead of LEFT. Then you will always have the columns from the four Elements rows.

[bobby44]

Hi Again,

Sorry but i still seem to have problems and wonder if you'd help.

Here is what I have in the sql:

SELECT vt.listing_id, ld.user_ID, ld.Title
 FROM listingsDB ld
left JOIN listingsDBElements vt
    ON vt.listing_id = ld.ID
left JOIN listingsDBElements vc
    ON vc.listing_id = ld.ID
left JOIN listingsDBElements vn
    ON vn.listing_id = ld.ID
left JOIN listingsDBElements vci
    ON vci.listing_id = ld.ID
WHERE ld.active = 'yes'
  AND ((vt.field_name = 'venue_type' AND vt.field_value like '%$venue_type%') OR '$venue_type' = '')
  AND ((vc.field_name = 'county' AND vc.field_value like '%$county%') OR '$county' = '')
  AND ((vn.field_name = 'venue_name' AND vn.field_value like '%$venue_name%') OR '$venue_name' = '') 
  AND ((vci.field_name ='city' AND vci.field_value like '%$city%') OR '$city' = '')

This works fine when I do a search such as this using all 4 fields:
php?venue_name=Weston%20Park&city=Shifnal&county=Shropshire&venue_type=Country%20House

But if I search just by county eg.
php?venue_name=&city=&county=Cornwall&venue_type=
The server freaks out and I have to restart.

If I use just three of the 4 fields then the venue results seem to appear multiple times.

This is the sql query that works when I include all 4 search fields:

SELECT vt.listing_id, ld.user_ID, ld.Title FROM listingsDB ld left JOIN listingsDBElements vt ON vt.listing_id = ld.ID left JOIN listingsDBElements vc ON vc.listing_id = ld.ID left JOIN listingsDBElements vn ON vn.listing_id = ld.ID left JOIN listingsDBElements vci ON vci.listing_id = ld.ID WHERE ld.active = 'yes' AND ((vt.field_name = 'venue_type' AND vt.field_value like '%Country House%') OR 'Country House' = '') AND ((vc.field_name = 'county' AND vc.field_value like '%Shropshire%') OR 'Shropshire' = '') AND ((vn.field_name = 'venue_name' AND vn.field_value like '%Weston Park%') OR 'Weston Park' = '') AND ((vci.field_name ='city' AND vci.field_value like '%Shifnal%') OR 'Shifnal' = '')

Can anyone please suggest how I can run the sql query but amend it so that it give a result even if users only fill in one, two or three fields in the form instead of all of them.

My only thought was to check which fields have been used and write multiple versions of the query and match one to the other. But this is rather clumsy and long winded.
eg.

SELECT vt.listing_id, ld.user_ID, ld.Title
 FROM listingsDB ld
left JOIN listingsDBElements vt
    ON vt.listing_id = ld.ID
left JOIN listingsDBElements vc
    ON vc.listing_id = ld.ID
left JOIN listingsDBElements vn
    ON vn.listing_id = ld.ID
left JOIN listingsDBElements vci
    ON vci.listing_id = ld.ID
WHERE ld.active = 'yes'";
if ($venue_type !=""){ // if we have a name search for it
 $sql3 .= "AND ((vt.field_name = 'venue_type' AND vt.field_value like '%$venue_type%') OR '$venue_type' = '') ";
 }
 if ($county !=""){ // if we have a name search for it
 $sql3 .= "AND ((vc.field_name = 'county' AND vc.field_value like '%$county%') OR '$county' = '') ";
 }
 if ($venue_name !=""){ // if we have a name search for it
 $sql3 .= "AND ((vn.field_name = 'venue_name' AND vn.field_value like '%$venue_name%') OR '$venue_name' = '') ";
 } 
 if ($city !=""){ // if we have a name search for it
$sql3 .= "AND ((vci.field_name ='city' AND vci.field_value like '%$city%') OR '$city' = '')  ";
}

 $sql3 .= " order by ld.user_ID DESC

But this still repeats the listings multiple times should I omit even just one of the fields searched. eg. omiting the venue name results in 56 of repeats of the same single venue listing information for instance.

Any suggestions would be welcomed.

Bob

[bpat1434]

You could "group by" whatever column you want (like the vt.listing_id) and then there will only be one result per unique listing_id 😉

[bobby44]

Hi,

Thanks for the suggestion. OK added the group by and seems to be working. eg.

$sql3 .= " group by vt.listing_id order by ld.user_ID DESC";

However, its only OK when I have 3 of the 4 fields filled in if I do 2 or just the one the server hangs again. maybe its just that I have a lot of records to sort through.

Do you have any ideas why this should be?

Again - cheers for the help and hope that kids stopped crying by now.

Bob

[bpat1434]

Oh, that kid went back to her parents 🙂 So no more screaming .... for now.

Are you using PHP to dynamically generate the query? If not, that's probably something you should be doing. As for why it's hanging when you do your query, it could be a multitude of things. If you have SSH (a.k.a. root) access to your server, try using a "top" command and running that page. See if MySQL really starts to bog down the server, of it it's something else.

My guess is, something in your coding is causing a "flub" in the query which is why the server hangs. But you only see this with one parameter, when you use multiple, it's okay.

[bobby44]

Hey thanks for the suggestions. I'm using this to generate the query:

$sql3 = "SELECT vt.listing_id, ld.user_ID, ld.Title
 FROM listingsDB ld
left JOIN listingsDBElements vt
    ON vt.listing_id = ld.ID
left JOIN listingsDBElements vc
    ON vc.listing_id = ld.ID
left JOIN listingsDBElements vn
    ON vn.listing_id = ld.ID
left JOIN listingsDBElements vci
    ON vci.listing_id = ld.ID
WHERE ld.active = 'yes'";
if ($venue_type !=""){ // if we have a name search for it
 $sql3 .= "AND ((vt.field_name = 'venue_type' AND vt.field_value like '%$venue_type%') OR '$venue_type' = '') ";
 }
 if ($county !=""){ // if we have a name search for it
 $sql3 .= "AND ((vc.field_name = 'county' AND vc.field_value like '%$county%') OR '$county' = '') ";
 }
 if ($venue_name !=""){ // if we have a name search for it
 $sql3 .= "AND ((vn.field_name = 'venue_name' AND vn.field_value like '%$venue_name%') OR '$venue_name' = '') ";
 } 
 if ($city !=""){ // if we have a name search for it
$sql3 .= "AND ((vci.field_name ='city' AND vci.field_value like '%$city%') OR '$city' = '')  ";
}

 $sql3 .= " group by vt.listing_id order by ld.user_ID DESC";

Seems to be working better now. I guess I just have a lot to look up. Will try your suggestions though.

Thanks again for the help. Now off to the beach for a well earned swim I reckon.

Cheers

Bob

[bpat1434]

Well, not to bring down the high hopes of you, it would seem that you may want to think about changing your structure. If you're only going to offer the 4 fields to search from, then why not just make those 4 fields columns? Make them "NULL" by default and then you can search in one small query:

SELECT `column` 
FROM `table`
WHERE `venue_name` LIKE '%$venue_name%'
  AND `venue_type` LIKE '%$venue_type%'
  AND `county` LIKE '%$county%'
  AND `city` LIKE '%$city%'

To which you could let PHP generate the query, or leave it blank and if any one of those items is blank, just replace the blank spot either with "*" (meaning any one character) or with a "%" (meaning any character any number of times). Would make things much easier. Something like:

$city = (isset($_POST['city']) && !empty($_POST['city'])) ? $_POST['city'] : '%';
$county = (isset($_POST['county']) && !empty($_POST['county'])) ? $_POST['county'] : '%';
$name = (isset($_POST['venue_name']) && !empty($_POST['venue_name'])) ? $_POST['venue_name'] : '%';
$type = (isset($_POST['venue_type']) && !empty($_POST['venue_type'])) ? $_POST['venue_type'] : '%';

$sql = "SELECT * FROM `table`
WHERE `venue_type` LIKE '%" . $type . "%'
  AND `venue_name` LIKE '%" . $name . "%'
  AND `county` LIKE '%" . $county . "%'
  AND `city` LIKE '%" . $city . "%'";

Just a suggestion in case you can't get this working properly. And just remember, this is only a suggestion if you know that you won't need a varying number of field_name values (i.e. it will always be one of 4 items: venue_name, venue_type, city or county).