PHP/MySQL search - query builder not working

system · January 1, 2012, 10:54am

Yes exactly the same error.

AnthonySterling · January 1, 2012, 11:35am

I cant see how that’s possible, unless the error is coming from another query? Can you post the entire, unaltered, script as it stands now in BBCODE tags please?

system · January 1, 2012, 11:52am



<?php require_once('Connections/conwydeals.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($_GET['search'])){
mysql_select_db($database_conwydeals, $conwydeals);
$query_rs_deals = "SELECT * FROM deals";
$rs_deals = mysql_query($query_rs_deals, $conwydeals) or die(mysql_error());
$row_rs_deals = mysql_fetch_assoc($rs_deals);
$totalRows_rs_deals = mysql_num_rows($rs_deals);
}
// search query builder starts
function execute_query($query){
	if( ! ($result = mysql_query($query))){
		throw new Exception(
			sprintf('Error: %s SQL: %s', mysql_error(), $query),
			mysql_errno()
		);
	}
	return $result;
}
if (isset($_GET['search'])){
//set search variable	
$search=$_GET['search'];
// reset search query variables
$where_clauses= "";
$location="";
$category="";
$dealtype="";
// set search query variables with GET data
if (isset($_GET['location'])){$location=$_GET['location'];}
if (isset($_GET['category'])){$category=$_GET['category'];}
if (isset($_GET['dealtype'])){$dealtype=$_GET['dealtype'];}

if (strlen($location)>1){
$where_clauses .= " AND location = '$location'";
}
if (strlen($category)>1){
$where_clauses .= " AND category = '$category'";
}
if (strlen($dealtype)>1){
$where_clauses .= " AND dealtype = '$dealtype'";
}
$where_clauses = rtrim($where_clauses);
//construct query
mysql_select_db($database_conwydeals, $conwydeals);
$query_rs_deals = "SELECT * FROM deals WHERE headline LIKE '$search' OR description LIKE '$search'".$where_clauses."";
$rs_deals = execute_query($query_rs_deals, $conwydeals) or die(mysql_error());
$row_rs_deals = mysql_fetch_assoc($rs_deals);
$totalRows_rs_deals = mysql_num_rows($rs_deals);
}
// search query builder ends

mysql_select_db($database_conwydeals, $conwydeals);
$query_rs_categories = "SELECT categoryname FROM categories ORDER BY categoryname ASC";
$rs_categories = mysql_query($query_rs_categories, $conwydeals) or die(mysql_error());
$row_rs_categories = mysql_fetch_assoc($rs_categories);
$totalRows_rs_categories = mysql_num_rows($rs_categories);

mysql_select_db($database_conwydeals, $conwydeals);
$query_rs_locations = "SELECT * FROM location ORDER BY town ASC";
$rs_locations = mysql_query($query_rs_locations, $conwydeals) or die(mysql_error());
$row_rs_locations = mysql_fetch_assoc($rs_locations);
$totalRows_rs_locations = mysql_num_rows($rs_locations);

mysql_select_db($database_conwydeals, $conwydeals);
$query_rs_dealtype = "SELECT * FROM dealtype ORDER BY type ASC";
$rs_dealtype = mysql_query($query_rs_dealtype, $conwydeals) or die(mysql_error());
$row_rs_dealtype = mysql_fetch_assoc($rs_dealtype);
$totalRows_rs_dealtype = mysql_num_rows($rs_dealtype);
?>

<!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>Latest Deals, Offers and Discounts in Conwy</title>
<?php
//echo $query_rs_deals;
//
//die();
?>
<link href="css/screen.css" rel="stylesheet" type="text/css" />
<!-- DMXXzone scripts -->
<script type="text/javascript" src="ScriptLibrary/jquery-latest.pack.js"></script>
<!-- DMXzone scripts Ends -->
<!-- Typekit Fonts Set -->
<script type="text/javascript" src="http://use.typekit.com/ecq3igb.js"></script>
<script type="text/javascript">try{Typekit.load();}catch(e){}</script>
<!-- Typekit Fonts Ends -->
<!-- menu scripts -->
<link type="text/css" href="menu.css" rel="stylesheet" />
<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript" src="menu.js"></script>
<script type="text/javascript" src="js/uniform.js"></script>
<link rel="stylesheet" href="css/uniform.default.css" type="text/css" media="screen" charset="utf-8" />
<!-- menu ends -->
<!--[if IE 5]>
<style type="text/css">
/* place css box model fixes for IE 5* in this conditional comment */
.twoColFixRt #sidebar1 { width: 220px; }
</style>
<![endif]--><!--[if IE]>
<style type="text/css">
/* place css fixes for all versions of IE in this conditional comment */
.twoColFixRt #sidebar1 { padding-top: 30px; }
.twoColFixRt #mainContent { zoom: 1; }
/* the above proprietary zoom property gives IE the hasLayout it needs to avoid several bugs */
</style>
<![endif]-->

<!-- Typekit Fonts Set -->
<script type="text/javascript" src="http://use.typekit.com/ecq3igb.js"></script>
<script type="text/javascript">try{Typekit.load();}catch(e){}</script>
<!-- Typekit Fonts Ends -->

</head>

<body class="twoColFixRt">

<p>&nbsp;</p>
<div id="container">
<div id="header">
</div><!-- Header div ends -->
<!-- Searchbox div ends -->
  <div id="sidebar1">
  <?php // include('includes/searchbox.php');?>
  <?php include('includes/sidebar.php');?>
    <!-- end #sidebar1 -->
<div>
<?php include('includes/topmenu.php'); ?>
</div><!-- Topmenu div ends -->
<div id="mainContent">
<br />
<h1>Latest Deals, Offers and Discounts in Conwy.</h1>
<p>&nbsp;</p>
<div id ="dealsearchwrapper">
<p>
  <?php // include('includes/dealsearch.php');?>

  <script language="JavaScript">
<!--
$(function(){ $("select").uniform(); });;
$("select, input:checkbox, input:file, input:radio, input:file input:select input:textarea").uniform();
// -->
</script>
</p>
<form action="deals7.php" method="get" enctype="multipart/form-data" name="frm_dealsearch" id="frm_dealsearch">
<select name="location" id="location">
  <option value="">Anywhere in conwy</option>
  <?php
do {
?>
  <option value="<?php echo $row_rs_locations['town']?>"><?php echo $row_rs_locations['town']?></option>
  <?php
} while ($row_rs_locations = mysql_fetch_assoc($rs_locations));
  $rows = mysql_num_rows($rs_locations);
  if($rows > 0) {
      mysql_data_seek($rs_locations, 0);
	  $row_rs_locations = mysql_fetch_assoc($rs_locations);
  }
?>
  </select>
  <select name="dealtype" id="dealtype">
    <option value="">Any deals</option>
    <?php
do {
?>
    <option value="<?php echo $row_rs_dealtype['type']?>"><?php echo $row_rs_dealtype['type']?></option>
    <?php
} while ($row_rs_dealtype = mysql_fetch_assoc($rs_dealtype));
  $rows = mysql_num_rows($rs_dealtype);
  if($rows > 0) {
      mysql_data_seek($rs_dealtype, 0);
	  $row_rs_dealtype = mysql_fetch_assoc($rs_dealtype);
  }
?>
  </select>
  <select name="category" id="category">
    <option value="">All categories</option>
    <?php
do {
?>
    <option value="<?php echo $row_rs_categories['categoryname']?>"><?php echo $row_rs_categories['categoryname']?></option>
    <?php
} while ($row_rs_categories = mysql_fetch_assoc($rs_categories));
  $rows = mysql_num_rows($rs_categories);
  if($rows > 0) {
      mysql_data_seek($rs_categories, 0);
	  $row_rs_categories = mysql_fetch_assoc($rs_categories);
  }
?>
  </select>
</p>
<p>
  <input type="text" name="search" id="search" style="margin-left:5px; width:495px;" / >
  <input type="submit" name="submit" id="submit" value="Search" style="display:inline; margin-left:20px;"  />
</form>
<div class="clearleft">&nbsp;</div>
</div><!-- dealsearchwrapper div ends -->
<div id="alldealswrapper">
<?php do { ?>
<?php $business = $row_rs_deals['userref'];
mysql_select_db($database_conwydeals, $conwydeals);
$query_rs_business = "SELECT name FROM users WHERE id = $business";
$rs_business = mysql_query($query_rs_business, $conwydeals) or die(mysql_error());
$row_rs_business = mysql_fetch_assoc($rs_business);
$totalRows_rs_business = mysql_num_rows($rs_business);
?>
<!-- deal wrapper starts -->
<div class="dealwrapper">
    <h2><?php echo $row_rs_deals['headline']; ?></h2>
    <h3><?php echo $row_rs_business['name']; ?></h3>
    <p><img src="images/deals/thumb_<?php echo $row_rs_deals['image']; ?>" alt="Great Deals and Discounts in Conwy" class="shadow" />
      <?php echo $row_rs_deals['description']; ?>
    </p>
    <div class="datestart"><span class="label">Deal Starts:</span><?php print strftime("%a %e %b",strtotime($row_rs_deals['datestart']));?></div><br />
    <div class="datefinish"><span class="label">Deal Ends:</span><?php print strftime("%a %e %b",strtotime($row_rs_deals['datefinish']));?></div>

    <div>
    <a href="deal.php?id=<?php echo $row_rs_deals['id']; ?>" class="more" >
    More Details Here
    </a>
    </div>
    <p>&nbsp;</p>
    <p>&nbsp;</p>
    <p class="clearer">&nbsp;</p>
</div><!-- deal wrapper ends -->
  <?php } while ($row_rs_deals = mysql_fetch_assoc($rs_deals)); ?>
</div><!-- all deals wrapper ends -->

</div><!-- Main Content div ends -->
<!-- This clearing element should immediately follow the #mainContent div in order to force the #container div to contain all child floats --><br class="clearfloat" />
<!-- end #container --></div>
</body>
</html>
<?php
mysql_free_result($rs_deals);

mysql_free_result($rs_categories);

mysql_free_result($rs_locations);

mysql_free_result($rs_dealtype);
?>

AnthonySterling · January 1, 2012, 12:23pm

Try running this…


<?php require_once('Connections/conwydeals.php');
function execute_query($query, $connection = null){
	if( ! ($result = mysql_query($query, $connection))){
		throw new Exception(
			sprintf('Error: %s SQL: %s', mysql_error(), $query),
			mysql_errno()
		);
	}
	return $result;
}
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($_GET['search'])){
mysql_select_db($database_conwydeals, $conwydeals);
$query_rs_deals = "SELECT * FROM deals";
$rs_deals = execute_query($query_rs_deals, $conwydeals) or die(mysql_error());
$row_rs_deals = mysql_fetch_assoc($rs_deals);
$totalRows_rs_deals = mysql_num_rows($rs_deals);
}
// search query builder starts
if (isset($_GET['search'])){
//set search variable	
$search=$_GET['search'];
// reset search query variables
$where_clauses= "";
$location="";
$category="";
$dealtype="";
// set search query variables with GET data
if (isset($_GET['location'])){$location=$_GET['location'];}
if (isset($_GET['category'])){$category=$_GET['category'];}
if (isset($_GET['dealtype'])){$dealtype=$_GET['dealtype'];}


if (strlen($location)>1){
$where_clauses .= " AND location = '$location'";
}
if (strlen($category)>1){
$where_clauses .= " AND category = '$category'";
}
if (strlen($dealtype)>1){
$where_clauses .= " AND dealtype = '$dealtype'";
}
$where_clauses = rtrim($where_clauses);
//construct query
mysql_select_db($database_conwydeals, $conwydeals);
$query_rs_deals = "SELECT * FROM deals WHERE headline LIKE '$search' OR description LIKE '$search'".$where_clauses."";
$rs_deals = execute_query($query_rs_deals, $conwydeals) or die(mysql_error());
$row_rs_deals = mysql_fetch_assoc($rs_deals);
$totalRows_rs_deals = mysql_num_rows($rs_deals);
}
// search query builder ends


mysql_select_db($database_conwydeals, $conwydeals);
$query_rs_categories = "SELECT categoryname FROM categories ORDER BY categoryname ASC";
$rs_categories = execute_query($query_rs_categories, $conwydeals) or die(mysql_error());
$row_rs_categories = mysql_fetch_assoc($rs_categories);
$totalRows_rs_categories = mysql_num_rows($rs_categories);


mysql_select_db($database_conwydeals, $conwydeals);
$query_rs_locations = "SELECT * FROM location ORDER BY town ASC";
$rs_locations = execute_query($query_rs_locations, $conwydeals) or die(mysql_error());
$row_rs_locations = mysql_fetch_assoc($rs_locations);
$totalRows_rs_locations = mysql_num_rows($rs_locations);


mysql_select_db($database_conwydeals, $conwydeals);
$query_rs_dealtype = "SELECT * FROM dealtype ORDER BY type ASC";
$rs_dealtype = execute_query($query_rs_dealtype, $conwydeals) or die(mysql_error());
$row_rs_dealtype = mysql_fetch_assoc($rs_dealtype);
$totalRows_rs_dealtype = mysql_num_rows($rs_dealtype);
?>


<!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>Latest Deals, Offers and Discounts in Conwy</title>
<?php 
//echo $query_rs_deals;
//
//die();
?>
<link href="css/screen.css" rel="stylesheet" type="text/css" />
<!-- DMXXzone scripts -->
<script type="text/javascript" src="ScriptLibrary/jquery-latest.pack.js"></script>
<!-- DMXzone scripts Ends -->
<!-- Typekit Fonts Set -->
<script type="text/javascript" src="http://use.typekit.com/ecq3igb.js"></script>
<script type="text/javascript">try{Typekit.load();}catch(e){}</script>
<!-- Typekit Fonts Ends -->
<!-- menu scripts -->
<link type="text/css" href="menu.css" rel="stylesheet" />
<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript" src="menu.js"></script>
<script type="text/javascript" src="js/uniform.js"></script>
<link rel="stylesheet" href="css/uniform.default.css" type="text/css" media="screen" charset="utf-8" />
<!-- menu ends -->
<!--[if IE 5]>
<style type="text/css"> 
/* place css box model fixes for IE 5* in this conditional comment */
.twoColFixRt #sidebar1 { width: 220px; }
</style>
<![endif]--><!--[if IE]>
<style type="text/css"> 
/* place css fixes for all versions of IE in this conditional comment */
.twoColFixRt #sidebar1 { padding-top: 30px; }
.twoColFixRt #mainContent { zoom: 1; }
/* the above proprietary zoom property gives IE the hasLayout it needs to avoid several bugs */
</style>
<![endif]-->


<!-- Typekit Fonts Set -->
<script type="text/javascript" src="http://use.typekit.com/ecq3igb.js"></script>
<script type="text/javascript">try{Typekit.load();}catch(e){}</script>
<!-- Typekit Fonts Ends -->


</head>


<body class="twoColFixRt">


<p>&nbsp;</p>
<div id="container">
<div id="header">
</div><!-- Header div ends -->
<!-- Searchbox div ends -->
  <div id="sidebar1">
  <?php // include('includes/searchbox.php');?>
  <?php include('includes/sidebar.php');?>
    <!-- end #sidebar1 -->
<div>
<?php include('includes/topmenu.php'); ?>
</div><!-- Topmenu div ends -->
<div id="mainContent">
<br />
<h1>Latest Deals, Offers and Discounts in Conwy.</h1>
<p>&nbsp;</p>
<div id ="dealsearchwrapper">
<p>
  <?php // include('includes/dealsearch.php');?>
  
  <script language="JavaScript">
<!--
$(function(){ $("select").uniform(); });;
$("select, input:checkbox, input:file, input:radio, input:file input:select input:textarea").uniform();
// -->
</script>
</p>
<form action="deals7.php" method="get" enctype="multipart/form-data" name="frm_dealsearch" id="frm_dealsearch">
<select name="location" id="location">
  <option value="">Anywhere in conwy</option>
  <?php
do {  
?>
  <option value="<?php echo $row_rs_locations['town']?>"><?php echo $row_rs_locations['town']?></option>
  <?php
} while ($row_rs_locations = mysql_fetch_assoc($rs_locations));
  $rows = mysql_num_rows($rs_locations);
  if($rows > 0) {
      mysql_data_seek($rs_locations, 0);
	  $row_rs_locations = mysql_fetch_assoc($rs_locations);
  }
?>
  </select>
  <select name="dealtype" id="dealtype">
    <option value="">Any deals</option>
    <?php
do {  
?>
    <option value="<?php echo $row_rs_dealtype['type']?>"><?php echo $row_rs_dealtype['type']?></option>
    <?php
} while ($row_rs_dealtype = mysql_fetch_assoc($rs_dealtype));
  $rows = mysql_num_rows($rs_dealtype);
  if($rows > 0) {
      mysql_data_seek($rs_dealtype, 0);
	  $row_rs_dealtype = mysql_fetch_assoc($rs_dealtype);
  }
?>
  </select>
  <select name="category" id="category">
    <option value="">All categories</option>
    <?php
do {  
?>
    <option value="<?php echo $row_rs_categories['categoryname']?>"><?php echo $row_rs_categories['categoryname']?></option>
    <?php
} while ($row_rs_categories = mysql_fetch_assoc($rs_categories));
  $rows = mysql_num_rows($rs_categories);
  if($rows > 0) {
      mysql_data_seek($rs_categories, 0);
	  $row_rs_categories = mysql_fetch_assoc($rs_categories);
  }
?>
  </select>
</p>
<p>
  <input type="text" name="search" id="search" style="margin-left:5px; width:495px;" / >
  <input type="submit" name="submit" id="submit" value="Search" style="display:inline; margin-left:20px;"  />
</form>
<div class="clearleft">&nbsp;</div>
</div><!-- dealsearchwrapper div ends -->
<div id="alldealswrapper">
<?php do { ?>
<?php $business = $row_rs_deals['userref'];
mysql_select_db($database_conwydeals, $conwydeals);
$query_rs_business = "SELECT name FROM users WHERE id = $business";
$rs_business = execute_query($query_rs_business, $conwydeals) or die(mysql_error());
$row_rs_business = mysql_fetch_assoc($rs_business);
$totalRows_rs_business = mysql_num_rows($rs_business);
?>
<!-- deal wrapper starts -->
<div class="dealwrapper">
    <h2><?php echo $row_rs_deals['headline']; ?></h2>
    <h3><?php echo $row_rs_business['name']; ?></h3>
    <p><img src="images/deals/thumb_<?php echo $row_rs_deals['image']; ?>" alt="Great Deals and Discounts in Conwy" class="shadow" />
      <?php echo $row_rs_deals['description']; ?>
    </p>
    <div class="datestart"><span class="label">Deal Starts:</span><?php print strftime("%a %e %b",strtotime($row_rs_deals['datestart']));?></div><br />
    <div class="datefinish"><span class="label">Deal Ends:</span><?php print strftime("%a %e %b",strtotime($row_rs_deals['datefinish']));?></div>
    
    <div>
    <a href="deal.php?id=<?php echo $row_rs_deals['id']; ?>" class="more" >
    More Details Here
    </a>
    </div>
    <p>&nbsp;</p>
    <p>&nbsp;</p>
    <p class="clearer">&nbsp;</p>
</div><!-- deal wrapper ends -->
  <?php } while ($row_rs_deals = mysql_fetch_assoc($rs_deals)); ?>
</div><!-- all deals wrapper ends -->
  
</div><!-- Main Content div ends -->
<!-- This clearing element should immediately follow the #mainContent div in order to force the #container div to contain all child floats --><br class="clearfloat" />
<!-- end #container --></div>
</body>
</html>
<?php
mysql_free_result($rs_deals);


mysql_free_result($rs_categories);


mysql_free_result($rs_locations);


mysql_free_result($rs_dealtype);
?>

system · January 1, 2012, 12:41pm

Something New :)!!!
A new error

Fatal error: Uncaught exception ‘Exception’ with message 'Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ at line 1 SQL: SELECT name FROM users WHERE id = ’ in /Applications/MAMP/htdocs/Conwydeals/deals7B.php:4 Stack trace: #0 /Applications/MAMP/htdocs/Conwydeals/deals7.php(243): execute_query(‘SELECT name FRO…’, Resource id #3) #1 {main} thrown in /Applications/MAMP/htdocs/Conwydeals/deals7B.php on line 4

thanks, I reckon this will give me something to go on.

Dave

system · January 1, 2012, 12:46pm

Think I’ve got it. Seems I was missing a couple of apostrophes in a query further down the page. That must have been messing that lot up. Wierd.
Thanks very, very much for your help.

system · January 1, 2012, 1:01pm

All fixed now. Working smoothly - thanks to all you guys.
As usual I learned loads from this.

Thanks again.

Dave

AnthonySterling · January 1, 2012, 1:02pm

\o/ Brilliant.