SQL Dreamweaver query

Forum Moderators: open

Message Too Old, No Replies

SQL Dreamweaver query

cdphp

4:45 pm on Mar 8, 2010 (gmt 0)

HI im building a search page on dreamweaver based on bars clubs and pubs. At the moment i have 3 checkboxes labelled Bar, Club and pub with list/menu. I want the checkboxes so that when 1 is clicked eg Club it will come up with all the clubs in the database. However at the moment when i click that it comes up with the right amount of records but with clubs, bars and pubs.

Also i want it so that if i select Clubs and Pubs in the checkboxes, it will come up with all the Clubs and Pubs in the database.

This is the SQL query in the recordset i created to get the values from the search page. I think this is wherte its going wrong

[code]
SELECT *
FROM `places`
WHERE `Town` LIKE %area% AND `MusicGenre` LIKE %music% AND (`DisabledAcess` = 'Yes' OR da1 ='0' ) AND (`Club/Bar/Pub` = 'Club' OR club1 = '0') AND (`Club/Bar/Pub` = 'Pub' OR pub1 = '0') AND (`Club/Bar/Pub` = 'Bar' OR bar1 = '0')
ORDER BY `Name` ASC
[/code]

Help Please!

Demaestro

6:09 pm on Mar 8, 2010 (gmt 0)

You should have 3 queries, one for each checkbox.

The SQL you have checks for all 3 types and pays no attention to which checkbox was clicked.

[code for bar]
SELECT *
FROM `places`
WHERE `Town` LIKE %area% AND `MusicGenre` LIKE %music% AND (`DisabledAcess` = 'Yes' OR da1 ='0' )AND
(`Club/Bar/Pub` = 'Bar' OR bar1 = '0')
ORDER BY `Name` ASC
[/code for bar]

[code for pub]
SELECT *
FROM `places`
WHERE `Town` LIKE %area% AND `MusicGenre` LIKE %music% AND (`DisabledAcess` = 'Yes' OR da1 ='0' ) AND
(`Club/Bar/Pub` = 'Pub' OR pub1 = '0')
ORDER BY `Name` ASC
[/code for pub]

I will leave the code for club up to you. It will help you figure out what is happening. If you can't figure it out or run into a new problem post back.

rocknbil

6:31 pm on Mar 8, 2010 (gmt 0)

At the moment i have 3 checkboxes labelled Bar, Club and pub with list/menu. I want the checkboxes so that when 1 is clicked eg Club it will come up with all the clubs in the database.

Note that a checkbox will only exist in get/post if it's checked.


$where=NULL; 
// MAP the checkboxes to the FIELD 
$checkboxes = Array( 
 'Bar' => "'Bar' OR bar1 = '0'", 
 'Club' => "'Club' OR club1 = '0'", 
 'Pub' => "'Pub' OR pub1 = '0'" 
); 
// 
foreach ($checkboxes as $key=>$value) { 
 if (isset($_POST[$key])) { 
 // Need an AND only if where is populated 
 if ($where) { $where .= ' and'; } 
 $where .= " (`Club/Bar/Pub`= $value)"; 
 } 
} 
// Not locations of spaces, this is important 
// or it will generate a mysql error 
$select = "select * from places where Town like '%area%' and `MusicGenre` LIKE '%music%'; 
$select .= " and (`DisabledAcess`='Yes' OR da1 ='0')"; 
if ($where) { $select .= $where; } 
$select .= " order by Name asc";

cdphp

7:50 pm on Mar 8, 2010 (gmt 0)

to Demaestro

where do i insert the code? im doing it through dreamweaver by using a recordset

and to rocknbil

where do i put the php my php script from the recordset from dreamweaver is this so far

<?php require_once('Connections/nightlifeinlondon.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$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;
}
}

$currentPage = $_SERVER["PHP_SELF"];

$area_rsArea = "%";
if (isset($_POST['area'])) {
$area_rsArea = $_POST['area'];
}
$club1_rsArea = "0";
if (isset($_POST['club1'])) {
$club1_rsArea = $_POST['club1'];
}
$pub1_rsArea = "0";
if (isset($_POST['pub1'])) {
$pub1_rsArea = $_POST['pub1'];
}
$bar1_rsArea = "0";
if (isset($_POST['bar1'])) {
$bar1_rsArea = $_POST['bar1'];
}
$da1_rsArea = "0";
if (isset($_POST['da1'])) {
$da1_rsArea = $_POST['da1'];
}
$music_rsArea = "%";
if (isset($_POST['music'])) {
$music_rsArea = $_POST['music'];
}
mysql_select_db($database_nightlifeinlondon, $nightlifeinlondon);
$query_rsArea = sprintf("SELECT * FROM `places` WHERE `Town` LIKE %s AND `MusicGenre` LIKE %s AND (`DisabledAcess` = 'Yes' OR %s ='0' ) AND (`Club/Bar/Pub` = 'Club' OR %s = '0') AND (`Club/Bar/Pub` = 'Bar' OR %s = '0') AND (`Club/Bar/Pub` = 'Pub' OR %s = '0') ORDER BY `Name` ASC", GetSQLValueString("%" . $area_rsArea . "%", "text"),GetSQLValueString("%" . $music_rsArea . "%", "text"),GetSQLValueString($da1_rsArea, "text"),GetSQLValueString($club1_rsArea, "text"),GetSQLValueString($bar1_rsArea, "text"),GetSQLValueString($pub1_rsArea, "text"));
$rsArea = mysql_query($query_rsArea, $nightlifeinlondon) or die(mysql_error());
$row_rsArea = mysql_fetch_assoc($rsArea);
$totalRows_rsArea = mysql_num_rows($rsArea);

$queryString_rsArea = "";
if (!empty($_SERVER['QUERY_STRING'])) {
$params = explode("&", $_SERVER['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_rsArea") == false &&
stristr($param, "totalRows_rsArea") == false) {
array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_rsArea = "&" . htmlentities(implode("&", $newParams));
}
}
$queryString_rsArea = sprintf("&totalRows_rsArea=%d%s", $totalRows_rsArea, $queryString_rsArea);
?>
<?php
require_once('auth.php');
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>Nightlife in London</title>
<meta name="keywords" content="" />
<meta name="description" content="" />
<link href="default.css" rel="stylesheet" type="text/css" />
<style type="text/css">

</style>
</head>
<body>
<span class="style1"><a href="Homepage.php">Logout</a></span>
<?php include "header2.php" ?>
<div id="wrapper">
<div id="content">
<div id="content">
<div id="welcome" class="post">
<h2 class="title"> </h2>
<h2 align="center" class="title">Results</h2>
<div class="meta"></div>
<div class="story">
<form id="form1" method="post" action="Detail.php">
<p>You can now choose a place to go by clicking on the Name. By clicking on name you can view the full details of your chosen place! If you would like to search again, please go back to the Advanced Search page</p>
<p> 
<div align="center" class="style2">
<table border="0" cellspacing="15">
<tr>
<td width="109"><div align="center"><strong>Club/Bar/Pub</strong></div></td>
<td width="123"><div align="center"><strong>Name</strong></div></td>
<td width="176"><div align="center"><strong>Town</strong></div></td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_rsArea['Club/Bar/Pub']; ?>  </td>
<td><a href="Detail2.php?recordID=<?php echo $row_rsArea['UniqueID']; ?>"> <?php echo $row_rsArea['Name']; ?>  </a> </td>
<td><?php echo $row_rsArea['Town']; ?>  </td>
</tr>
<?php } while ($row_rsArea = mysql_fetch_assoc($rsArea)); ?>
</table>
<br />
<?php echo $totalRows_rsArea ?> Records Total </div>
<div align="center"></div>
<div align="center"></div>
</form>
</div>
</div>

<div style="clear: both;"> </div>
</div>
<div id="footer">
<p id="legal">Copyright &copy Developed by Chanbir Dhesi. CSS templates from <a href="http://www.freecsstemplates.org/">Free CSS Templates</a></p>
</div>
</body>
</html>
<?php
mysql_free_result($rsArea);
?>