Skip navigation
ant01
Currently Being Moderated

Problems using multiple joins for search

Jul 25, 2012 8:15 AM

Tags: #variables #recordsets #search_form #joins #outer_joins

I am new to dreamweaver and coding and I am battling to get my head around joining tables and using multiple joins to create a search result recordset.

 

I have a the following tables setup;

Venues table

  • venueID
  • name
  • category (text)
  • city
  • provinceID (numeric)
  • country
  • maxcapacity

 

Province table

  • provinceID
  • province (text)

 

Category Table

  • categoryID
  • category (text)

 

Max Conference Table

  • conferencefacilitiesID
  • venueID
  • maxcapacity

 

I am passing the search $_POST variables via a form and displaying it in a results page.

 

I have successfully done the search using only one table the problem results in using multiple joins. I cam not sure of the syntax to use but have successfully created the results page, using the outer join to link the province, category and maxcapacity to the venues table. Not all the venues have conferencing so I think need to use outer join for conferencing.

 

I can't seem to access the search and not sure if I can use the WHERE command to set varialbe 'category' = varCategory 

 

Below is my code which doesn't work;

SELECT wp_dbt_venues.venuesID, wp_dbt_venues.name, wp_dbt_venues.category, wp_dbt_venues.province, wp_dbt_venues.city, wp_dbt_province.provinceID, wp_dbt_province.province, wp_dbt_conferencefacilties.venueid, wp_dbt_conferencefacilties.maxcapacity

FROM ((wp_dbt_venues LEFT OUTER JOIN wp_dbt_province ON wp_dbt_venues.province = wp_dbt_province.provinceID)  LEFT OUTER JOIN wp_dbt_conferencefacilties ON wp_dbt_venues.venuesID = wp_dbt_conferencefacilties.venueid)

WHERE 'category'=varCategory

 

I would like to get on variable working and then expand onto the others like WHERE maxcapacity < varCapacity

 
Replies
  • Currently Being Moderated
    Jul 25, 2012 9:57 AM   in reply to ant01

    In PHP, you must use $ when representing variables. So try:

     

     

    WHERE 'category'=$varCategory

     

    or use sprintf() and placeholders.

     

    Also, do you need to use outer joins on all of the tables?

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 26, 2012 9:53 AM   in reply to ant01

    OK, it was not clear to me if the code you posted was actually from the generated code inserted by DW, or from inside the recordset wizard. Please post the entire page code so we can see if there are any errors.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points