Forums
This topic is locked
filtering not working
Posted 31 Mar 2007 14:51:48
1
has voted
31 Mar 2007 14:51:48 Miranda Parry posted:
HiI have a problem getting filters to work using PHP/MySQL. I can connect to the database. I can display unfiltered records. I get a correctly filtered result when I hit Test in the simple Recordset dialogue box, BUT when I try to get this result as live data (either by uploading to testing server, or by using live data display) I get no results at all.
Thinking it might be my database, I tested this using the Cafe Townsend db in the DW tutorial. Same result - will return an unfiltered result, but as soon as a filter is used, nothing.
It's a while since I did this and last time I was using DW Ultra Dev on PC. Am now using DW8 on Mac - is this a known issue? Any suggestions gratefullly received, thanks for reading...
Replies
Replied 31 Mar 2007 17:28:35
31 Mar 2007 17:28:35 Jim Paulino replied:
What is the code? If we can look at the coding, we might be of some use. Try to echo the string to see if you get any results.
Replied 31 Mar 2007 19:04:23
31 Mar 2007 19:04:23 Miranda Parry replied:
Thanks Jim
The code, generated by DW, is as follows:
<?php require_once('../Connections/dodo.php'); ?>
<?php
$maxRows_Recordset1 = 10;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
$pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;
$colname_Recordset1 = "-1";
if (isset($_GET['Postcode'])) {
$colname_Recordset1 = (get_magic_quotes_gpc()) ? $_GET['Postcode'] : addslashes($_GET['Postcode']);
}
mysql_select_db($database_dodo, $dodo);
$query_Recordset1 = sprintf("SELECT OrganizationName, PhoneNumber, Address2, Address3, Postcode FROM Retailer WHERE Postcode LIKE '%s%%'", $colname_Recordset1);
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $dodo) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
if (isset($_GET['totalRows_Recordset1'])) {
$totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
$all_Recordset1 = mysql_query($query_Recordset1);
$totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>Untitled Document</title>
</head>
<body>
<table width="650" border="2" cellpadding="10" cellspacing="0" bordercolor="#FF00FF">
<tr>
<td>name</td>
<td>phone</td>
<td>town</td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_Recordset1['OrganizationName']; ?></td>
<td><?php echo $row_Recordset1['PhoneNumber']; ?></td>
<td><?php echo $row_Recordset1['Address3']; ?></td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>
The following (basically the same, but without the WHERE bit), DOES work, but of course it just produces all the results, without filtering:
<?php require_once('../Connections/dodo.php'); ?>
<?php
$maxRows_Recordset1 = 10;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
$pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;
mysql_select_db($database_dodo, $dodo);
$query_Recordset1 = "SELECT OrganizationName, PhoneNumber, Address2, Address3, Postcode FROM Retailer";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $dodo) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
if (isset($_GET['totalRows_Recordset1'])) {
$totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
$all_Recordset1 = mysql_query($query_Recordset1);
$totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>Untitled Document</title>
</head>
<body>
<table width="650" border="2" cellpadding="10" cellspacing="0" bordercolor="#FF00FF">
<tr>
<td>name</td>
<td>phone</td>
<td>town</td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_Recordset1['OrganizationName']; ?></td>
<td><?php echo $row_Recordset1['PhoneNumber']; ?></td>
<td><?php echo $row_Recordset1['Address3']; ?></td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>
Thanks again
The code, generated by DW, is as follows:
<?php require_once('../Connections/dodo.php'); ?>
<?php
$maxRows_Recordset1 = 10;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
$pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;
$colname_Recordset1 = "-1";
if (isset($_GET['Postcode'])) {
$colname_Recordset1 = (get_magic_quotes_gpc()) ? $_GET['Postcode'] : addslashes($_GET['Postcode']);
}
mysql_select_db($database_dodo, $dodo);
$query_Recordset1 = sprintf("SELECT OrganizationName, PhoneNumber, Address2, Address3, Postcode FROM Retailer WHERE Postcode LIKE '%s%%'", $colname_Recordset1);
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $dodo) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
if (isset($_GET['totalRows_Recordset1'])) {
$totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
$all_Recordset1 = mysql_query($query_Recordset1);
$totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>Untitled Document</title>
</head>
<body>
<table width="650" border="2" cellpadding="10" cellspacing="0" bordercolor="#FF00FF">
<tr>
<td>name</td>
<td>phone</td>
<td>town</td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_Recordset1['OrganizationName']; ?></td>
<td><?php echo $row_Recordset1['PhoneNumber']; ?></td>
<td><?php echo $row_Recordset1['Address3']; ?></td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>
The following (basically the same, but without the WHERE bit), DOES work, but of course it just produces all the results, without filtering:
<?php require_once('../Connections/dodo.php'); ?>
<?php
$maxRows_Recordset1 = 10;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
$pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;
mysql_select_db($database_dodo, $dodo);
$query_Recordset1 = "SELECT OrganizationName, PhoneNumber, Address2, Address3, Postcode FROM Retailer";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $dodo) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
if (isset($_GET['totalRows_Recordset1'])) {
$totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
$all_Recordset1 = mysql_query($query_Recordset1);
$totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>Untitled Document</title>
</head>
<body>
<table width="650" border="2" cellpadding="10" cellspacing="0" bordercolor="#FF00FF">
<tr>
<td>name</td>
<td>phone</td>
<td>town</td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_Recordset1['OrganizationName']; ?></td>
<td><?php echo $row_Recordset1['PhoneNumber']; ?></td>
<td><?php echo $row_Recordset1['Address3']; ?></td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>
Thanks again
Replied 10 Apr 2007 02:25:04
10 Apr 2007 02:25:04 Alan C replied:
I agree with Jim, put in an echo line to show you exactly what the query is that is being assembled, and also to show the result that is being returned.
Once you have the query you can also use phpmyadmin, go to the sql section and paste in the query that you have and execute it, then you can look at the result and change the query slightly until you get the desired selection being made. From that you may be able to figure out what is not working.
If necessary try duplicating your file (to preserve the original) then you can hack the code about as much as you want without any fear of losing what you already had.
Once you have the query you can also use phpmyadmin, go to the sql section and paste in the query that you have and execute it, then you can look at the result and change the query slightly until you get the desired selection being made. From that you may be able to figure out what is not working.
If necessary try duplicating your file (to preserve the original) then you can hack the code about as much as you want without any fear of losing what you already had.
Replied 11 Apr 2007 19:33:39
11 Apr 2007 19:33:39 Miranda Parry replied:
Thanks, having tried various different things, I came to the conclusion it was something about the server the database was on! I can make it work on my own server, so will use that...