homepage Welcome to WebmasterWorld Guest from 50.19.169.37
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
SQL Dreamweaver query
cdphp




msg:4093380
 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




msg:4093429
 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




msg:4093449
 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




msg:4093503
 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">
<!--
DeSign2 by Free CSS Templates
[freecsstemplates.org...]
Released for free under a Creative Commons Attribution 2.5 License
-->
<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">
<!--
.style1 {color: #FFFFFF}
.style2 {color: #CCFFFF}
-->
</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">&nbsp;</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>&nbsp;
<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']; ?>&nbsp; </td>
<td><a href="Detail2.php?recordID=<?php echo $row_rsArea['UniqueID']; ?>"> <?php echo $row_rsArea['Name']; ?>&nbsp; </a> </td>
<td><?php echo $row_rsArea['Town']; ?>&nbsp; </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;">&nbsp;</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);
?>

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved