4 Replies Latest reply: Jul 26, 2012 11:24 AM by ant01 RSS

    Problems using multiple joins for search

    ant01

      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

        • 1. Re: Problems using multiple joins for search
          bregent MVP

          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?

          • 2. Re: Problems using multiple joins for search
            ant01 Community Member

            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.

            • 3. Re: Problems using multiple joins for search
              bregent MVP

              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.

              • 4. Re: Problems using multiple joins for search
                ant01 Community Member

                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 { 

                ?>

                        <option value="<?php echo $row_category['category']?>"<?php if (!(strcmp($row_category['category'], $row_category['category']))) {echo "selected=\"selected\"";} ?>><?php echo $row_category['category']?></option>

                        <?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 { 

                ?>

                        <option value="<?php echo $row_province['provinceID']?>"<?php if (!(strcmp($row_province['provinceID'], $row_province['provinceID']))) {echo "selected=\"selected\"";} ?>><?php echo $row_province['province']?></option>

                        <?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);

                ?>