Problems using multiple joins for search

Report · Jul 25, 2012

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

Report · Jul 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?

Report · Jul 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.

Report · Jul 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.

Report · Jul 26, 2012

Hi bregent

Thank you for all the help, below is the code. I have clened it up as best I could as dreamweaver seems to add recordset everytime I edit it. I then have to delete the old code. It also seems adds a totalRows variable and moves one of the runtime variables to the totalRows variable. Its all very confusing but its working.

Results Page

<?php require_once('Connections/tova.php'); ?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

if (PHP_VERSION < 6) {

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

if (isset($_POST['delegates'])) {

$varDel_results = $_POST['delegates'];

}

$varProv_results = "-1";

if (isset($_POST['province'])) {

$varProv_results = $_POST['province'];

}

$varCat_results = "-1";

if (isset($_POST['category'])) {

$varCat_results = $_POST['category'];

}

mysql_select_db($database_tova, $tova);

$query_results = sprintf("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 wp_dbt_venues.category = %s AND wp_dbt_venues.province = %s AND wp_dbt_conferencefacilties.maxcapacity < %s", GetSQLValueString($varCat_results, "text"),GetSQLValueString($varProv_results, "int"),GetSQLValueString($varDel_results, "int"));

$results = mysql_query($query_results, $tova) or die(mysql_error());

$row_results = mysql_fetch_assoc($results);

$totalRows_results = mysql_num_rows($results);

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Untitled Document</title>

</head>

<body>

<p>Search Results</p>

<table width="200" border="1">

<tr>

<td> </td>

<td>Name</td>

<td>Category</td>

<td>City</td>

<td>Province</td>

<td>Delegates</td>

</tr>

<?php do { ?>

<tr>

<td><?php echo $row_results['venuesID']; ?></td>

<td><?php echo $row_results['name']; ?></td>

<td><?php echo $row_results['category']; ?></td>

<td><?php echo $row_results['city']; ?></td>

<td><?php echo $row_results['province']; ?></td>

<td><?php echo $row_results['maxcapacity']; ?></td>

</tr>

<?php } while ($row_results = mysql_fetch_assoc($results)); ?>

</table>

<p> </p>

</body>

</html>

<?php mysql_free_result($results);

?>

Search Page

<?php require_once('Connections/tova.php'); ?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

if (PHP_VERSION < 6) {

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

mysql_select_db($database_tova, $tova);

$query_category = "SELECT category FROM wp_dbt_categories ORDER BY category ASC";

$category = mysql_query($query_category, $tova) or die(mysql_error());

$row_category = mysql_fetch_assoc($category);

$totalRows_category = mysql_num_rows($category);

mysql_select_db($database_tova, $tova);

$query_province = "SELECT * FROM wp_dbt_province ORDER BY province ASC";

$province = mysql_query($query_province, $tova) or die(mysql_error());

$row_province = mysql_fetch_assoc($province);

$totalRows_province = mysql_num_rows($province);

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Search</title>

</head>

<body>

<p><strong>Advanced Search</strong></p>

<form action="results.php" method="post" name="form1" target="_blank" id="form1">

<p>

<label>Category

<select name="category" id="category">

<?php

do {

?>

<?php

} while ($row_category = mysql_fetch_assoc($category));

$rows = mysql_num_rows($category);

if($rows > 0) {

mysql_data_seek($category, 0);

$row_category = mysql_fetch_assoc($category);

}

?>

</select>

</label>

</p>

<p>

<label>Province

<select name="province" id="province">

<?php

do {

?>

<?php

} while ($row_province = mysql_fetch_assoc($province));

$rows = mysql_num_rows($province);

if($rows > 0) {

mysql_data_seek($province, 0);

$row_province = mysql_fetch_assoc($province);

}

?>

</select>

</label>

</p>

<p>

<label>Delegates

<input name="delegates" type="text" id="delegates" value="" />

</label>

</p>

<p>

<label>

<input type="checkbox" name="Facilities" value="golf" id="Facilities_0" />

Golf</label>

<br />

<label>

<input type="checkbox" name="Facilities" value="game" id="Facilities_1" />

Game</label>

<br />

</p>

<p>

<label>Search

<input type="submit" name="submit" id="submit" value="Submit" />

</label>

</p>

</form>

<p> </p>

</body>

</html>

<?php

mysql_free_result($category);

mysql_free_result($province);

?>