Skip to main content
A
ant01
Participating Frequently
July 25, 2012
Question

Problems using multiple joins for search

  • July 25, 2012
  • 1 reply
  • 861 views

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

This topic has been closed for replies.

1 reply

B
bregent
Participating Frequently
July 25, 2012

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?

A
ant01Author
Participating Frequently
July 26, 2012

Hi bregent

I read up on runtime variables in David Powers essential guide to dreamweaver and this is what he says;

The runtime variables are not PHP variables, so they shouldn’t begin with a dollar sign. You can use any alphanumeric characters to create the variables, as long as they don’t clash with the names of columns or any other part of the SQL query. I normally call the runtime variables var1, var2, and so on, but another common convention is to use col1, col2, and so on.

Dreamweaver uses runtime variables to prevent a type of malicious attack known as SQL injection, which exploits poorly written scripts to inject spurious code into SQL queries.SQL injection can be used to gain unauthorized access to a database and even wipe out all the stored data. Dreamweaver changed its approach to SQL injection with the 8.0.2 updater for Dreamweaver 8, so if you’re upgrading from an earlier version of

Dreamweaver, the way you insert these runtime variables has changed slightly. You will probably also find that recordsets built with versions of Dreamweaver prior to 8.0.2 need to be rebuilt.

Dreamweaver replaces the runtime variables with PHP format specifiers (normally %s or %d), and uses the GetSQLValueString() function (see “Inspecting the server behavior code” in Chapter 14) to handle quotes and other characters that might cause problems with the SQL query. It also automatically adds quotes around text values. This is an important change. Prior to Dreamweaver 8.0.2, you needed to add the quotes around the

runtime variables yourself. Now you insert the runtime variables without quotes.

B
bregent
Participating Frequently
July 26, 2012

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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices