Page 1 of 1

Skipping through empty fields

Posted: Fri Jul 23, 2004 3:46 am
by saltriver
Not as feminine as it might sound...

I have a database that searches restaurants by cuisine (1st, 2nd & 3rd). I have the restaurants who have cuisine 1 listed on the top, and have 3 recordsets for the cuisines. The problem is, if there is no restaurant that has a Cuisine picked from the pull down menu as their Cuisine 1, it leaves a blank spot on the top of my list, then begins with all of the restaurants that have as their Cuisine 2 matchingt the choice made in the menu.
Is there a way to "hide" or "skip" this recordset?
I'm thinking the if/else might be the way to go, but this is my first attempt and would appreciate any guidence before I dive in.

On a related note, I have one field in the database that contains a link to another page. I want to use a graphic to represent the link, but I would like the graphic to not appear if that field is empty for that restaurant.

Thanks

Steve McIntyre

Posted: Fri Jul 23, 2004 6:44 am
by qads

Code: Select all

<?php
$query = mysql_query("select field from table where field IS NOT NULL");
?>
you can use the above query to select records which are not empty.

Nope

Posted: Fri Jul 23, 2004 8:27 pm
by saltriver
feyd | LAST WARNING :: Use

Code: Select all

and

Code: Select all

tags where approriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


That didn't do it.
I tried putting that in the line below in place of the '%s' and as follows:
...='%s' AND NOT NULL ......
Both of them kept the recordset from showing up when they should and, worse yet, still left a space where the record would show.

$query_rsmd_cuisinea2 = sprintf("SELECT restaurant, package, description, hours, cuisinea, cuisineb, cuisinec, number, street, city, `state`, zip, phone, fax, email, website FROM restaurants WHERE cuisinea = '%s' ORDER BY package DESC, RAND()", $colname_rsmd_cuisinea2);

I'll try explaining again:

Restaurant A has for its cuisine choices:

1-Mexican
2-American
3-French

Restaurant B has:

1-Thai
2-Mexican
3-Fine Dining

When I do a search for Mexican Rest. A will always come on top because I have a separate recordset for Cuisine 1, 2 & 3 with Cuisine 1 being at the top of the list.
When I do a search for American, Rest. B is at the top of the list but there is an empty space at the top of the list since nobody has "American" listed in their Cuisine 1 field.

What I'm trying to do is eliminate that space.

Did I pick the right line to put that in?

Here's the code for the entire page, to help put it into context
The recordsets are: cuisinea2, cuisineb & cuisinec:

Code: Select all

<?php require_once('../Connections/hungryusaconnection.php'); ?>
<?php require_once('../Connections/hungryusaconnection.php'); ?>
<?php
$currentPage = $_SERVER["PHP_SELF"];

$maxRows_rsmd_cuisineb = 10;
$pageNum_rsmd_cuisineb = 0;
if (isset($_GET['pageNum_rsmd_cuisineb'])) {
  $pageNum_rsmd_cuisineb = $_GET['pageNum_rsmd_cuisineb'];
}
$startRow_rsmd_cuisineb = $pageNum_rsmd_cuisineb * $maxRows_rsmd_cuisineb;

$colname_rsmd_cuisineb = "1";
if (isset($_POST['cuisinemenu'])) {
  $colname_rsmd_cuisineb = (get_magic_quotes_gpc()) ? $_POST['cuisinemenu'] : addslashes($_POST['cuisinemenu']);
}
mysql_select_db($database_hungryusaconnection, $hungryusaconnection);
$query_rsmd_cuisineb = sprintf("SELECT restaurant, description, hours, cuisinea, cuisineb, cuisinec, number, street, city, `state`, zip, phone, fax, email FROM restaurants WHERE cuisineb = '%s' ORDER BY package DESC, RAND()", $colname_rsmd_cuisineb);
$query_limit_rsmd_cuisineb = sprintf("%s LIMIT %d, %d", $query_rsmd_cuisineb, $startRow_rsmd_cuisineb, $maxRows_rsmd_cuisineb);
$rsmd_cuisineb = mysql_query($query_limit_rsmd_cuisineb, $hungryusaconnection) or die(mysql_error());
$row_rsmd_cuisineb = mysql_fetch_assoc($rsmd_cuisineb);

if (isset($_GET['totalRows_rsmd_cuisineb'])) {
  $totalRows_rsmd_cuisineb = $_GET['totalRows_rsmd_cuisineb'];
} else {
  $all_rsmd_cuisineb = mysql_query($query_rsmd_cuisineb);
  $totalRows_rsmd_cuisineb = mysql_num_rows($all_rsmd_cuisineb);
}
$totalPages_rsmd_cuisineb = ceil($totalRows_rsmd_cuisineb/$maxRows_rsmd_cuisineb)-1;

$maxRows_rsmd_cuisinec = 10;
$pageNum_rsmd_cuisinec = 0;
if (isset($_GET['pageNum_rsmd_cuisinec'])) {
  $pageNum_rsmd_cuisinec = $_GET['pageNum_rsmd_cuisinec'];
}
$startRow_rsmd_cuisinec = $pageNum_rsmd_cuisinec * $maxRows_rsmd_cuisinec;

$colname_rsmd_cuisinec = "1";
if (isset($_POST['cuisinemenu'])) {
  $colname_rsmd_cuisinec = (get_magic_quotes_gpc()) ? $_POST['cuisinemenu'] : addslashes($_POST['cuisinemenu']);
}
mysql_select_db($database_hungryusaconnection, $hungryusaconnection);
$query_rsmd_cuisinec = sprintf("SELECT restaurant, description, hours, cuisinea, cuisineb, cuisinec, number, street, city, `state`, zip, phone, fax, email FROM restaurants WHERE cuisinec = '%s' ORDER BY package DESC, RAND()", $colname_rsmd_cuisinec);
$query_limit_rsmd_cuisinec = sprintf("%s LIMIT %d, %d", $query_rsmd_cuisinec, $startRow_rsmd_cuisinec, $maxRows_rsmd_cuisinec);
$rsmd_cuisinec = mysql_query($query_limit_rsmd_cuisinec, $hungryusaconnection) or die(mysql_error());
$row_rsmd_cuisinec = mysql_fetch_assoc($rsmd_cuisinec);

if (isset($_GET['totalRows_rsmd_cuisinec'])) {
  $totalRows_rsmd_cuisinec = $_GET['totalRows_rsmd_cuisinec'];
} else {
  $all_rsmd_cuisinec = mysql_query($query_rsmd_cuisinec);
  $totalRows_rsmd_cuisinec = mysql_num_rows($all_rsmd_cuisinec);
}
$totalPages_rsmd_cuisinec = ceil($totalRows_rsmd_cuisinec/$maxRows_rsmd_cuisinec)-1;

$maxRows_rsmd_cuisinea2 = 10;
$pageNum_rsmd_cuisinea2 = 0;
if (isset($_GET['pageNum_rsmd_cuisinea2'])) {
  $pageNum_rsmd_cuisinea2 = $_GET['pageNum_rsmd_cuisinea2'];
}
$startRow_rsmd_cuisinea2 = $pageNum_rsmd_cuisinea2 * $maxRows_rsmd_cuisinea2;

$colname_rsmd_cuisinea2 = "1";
if (isset($_POST['cuisinemenu'])) {
  $colname_rsmd_cuisinea2 = (get_magic_quotes_gpc()) ? $_POST['cuisinemenu'] : addslashes($_POST['cuisinemenu']);
}
mysql_select_db($database_hungryusaconnection, $hungryusaconnection);
$query_rsmd_cuisinea2 = sprintf("SELECT restaurant, package, description, hours, cuisinea, cuisineb, cuisinec, number, street, city, `state`, zip, phone, fax, email, website FROM restaurants WHERE cuisinea = '%s' ORDER BY package DESC, RAND()", $colname_rsmd_cuisinea2);
$query_limit_rsmd_cuisinea2 = sprintf("%s LIMIT %d, %d", $query_rsmd_cuisinea2, $startRow_rsmd_cuisinea2, $maxRows_rsmd_cuisinea2);
$rsmd_cuisinea2 = mysql_query($query_limit_rsmd_cuisinea2, $hungryusaconnection) or die(mysql_error());
$row_rsmd_cuisinea2 = mysql_fetch_assoc($rsmd_cuisinea2);

if (isset($_GET['totalRows_rsmd_cuisinea2'])) {
  $totalRows_rsmd_cuisinea2 = $_GET['totalRows_rsmd_cuisinea2'];
} else {
  $all_rsmd_cuisinea2 = mysql_query($query_rsmd_cuisinea2);
  $totalRows_rsmd_cuisinea2 = mysql_num_rows($all_rsmd_cuisinea2);
}
$totalPages_rsmd_cuisinea2 = ceil($totalRows_rsmd_cuisinea2/$maxRows_rsmd_cuisinea2)-1;

$queryString_rsmd_cuisinea2 = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_rsmd_cuisinea2") == false && 
        stristr($param, "totalRows_rsmd_cuisinea2") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_rsmd_cuisinea2 = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_rsmd_cuisinea2 = sprintf("&totalRows_rsmd_cuisinea2=%d%s", $totalRows_rsmd_cuisinea2, $queryString_rsmd_cuisinea2);

$queryString_rsmd_cuisinec = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_rsmd_cuisinec") == false && 
        stristr($param, "totalRows_rsmd_cuisinec") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_rsmd_cuisinec = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_rsmd_cuisinec = sprintf("&totalRows_rsmd_cuisinec=%d%s", $totalRows_rsmd_cuisinec, $queryString_rsmd_cuisinec);

$queryString_rsmd_cuisineb = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_rsmd_cuisineb") == false && 
        stristr($param, "totalRows_rsmd_cuisineb") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_rsmd_cuisineb = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_rsmd_cuisineb = sprintf("&totalRows_rsmd_cuisineb=%d%s", $totalRows_rsmd_cuisineb, $queryString_rsmd_cuisineb);

$queryString_rsmd_cuisinea = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_rsmd_cuisinea") == false && 
        stristr($param, "totalRows_rsmd_cuisinea") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_rsmd_cuisinea = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_rsmd_cuisinea = sprintf("&totalRows_rsmd_cuisinea=%d%s", $totalRows_rsmd_cuisinea, $queryString_rsmd_cuisinea);

$queryString_rscuis_aresults = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_rscuis_aresults") == false && 
        stristr($param, "totalRows_rscuis_aresults") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_rscuis_aresults = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_rscuis_aresults = sprintf("&totalRows_rscuis_aresults=%d%s", $totalRows_rscuis_aresults, $queryString_rscuis_aresults);
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>



<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {font-family: Arial, Helvetica, sans-serif}
body,td,th {
	font-family: Arial, Helvetica, sans-serif;
	color: #666666;
	font-size: 14px;
}
body {
	background-color: #CCCCCC;
	background-image: url(../images/lilgradation.jpg);
}
-->
</style>
<link href="../css/hungrycss01.css" rel="stylesheet" type="text/css">
<style type="text/css">
<!--
a:link {
	color: #CC6600;
}
a:visited {
	color: #CC6633;
}
a:hover {
	color: #CC6633;
}
a:active {
	color: #CC9900;
}
-->
</style>
<link href="../css/hungrycss02.css" rel="stylesheet" type="text/css">
</head>
<body>
<?php do { ?>
<table width="500" height="80" border="0">
  <tr>
    <td width="20" rowspan="5">&nbsp;</td>
    <td width="426"><p>


<a href="javascript:" class="bigbrown" onClick="window.open('details.php?recordID=<?php echo $row_rsmd_cuisinea2['restaurant']; ?>','Details','screenX=500,width=400,height=400')"><?php echo $row_rsmd_cuisinea2['restaurant']; ?></a> <br>

   
       

 <span class="smallblacknorm"><?php echo $row_rsmd_cuisinea2['description']; ?><br>
 </span><span class="smallbrown"><?php echo $row_rsmd_cuisinea2['hours']; ?></span><span class="smallblacknorm"><br>
 </span><span class="smallblack"><?php echo $row_rsmd_cuisinea2['number']; ?></span>&nbsp;<span class="smallblack"><?php echo $row_rsmd_cuisinea2['street']; ?></span>&nbsp;<span class="smallblack"><?php echo $row_rsmd_cuisinea2['city']; ?> <?php echo $row_rsmd_cuisinea2['state']; ?></span> &nbsp;<span class="smallblack"><?php echo $row_rsmd_cuisinea2['zip']; ?></span><br>
        <span class="medbrown"><?php echo $row_rsmd_cuisinea2['phone']; ?></span><br>
        <span class="smallblack"><?php echo $row_rsmd_cuisinea2['email']; ?></span>      <br>
    </p>
    </td>
    <td width="36" height="10" colspan="3">&nbsp;</td>
  </tr>
</table>
<?php } while ($row_rsmd_cuisinea2 = mysql_fetch_assoc($rsmd_cuisinea2)); ?>
<?php do { ?>
<table width="500" height="80" border="0">
  <tr>
    <td width="20" rowspan="5">&nbsp;</td>
    <td width="426"><p><span class="bigbrown">
	
	
	
<a href="javascript:" class="bigbrown" onClick="window.open('details.php?recordID=<?php echo $row_rsmd_cuisineb['restaurant']; ?>','Details','screenX=500,width=400,height=400')"><?php echo $row_rsmd_cuisineb['restaurant']; ?></a><br>
    
	
	
	    </span><span class="smallblacknorm"><?php echo $row_rsmd_cuisineb['description']; ?><br>
	    </span><span class="smallbrown"><?php echo $row_rsmd_cuisineb['hours']; ?></span><span class="smallblacknorm"><br>
	    </span><span class="smallblack"><?php echo $row_rsmd_cuisineb['number']; ?> &nbsp;<?php echo $row_rsmd_cuisineb['street']; ?> <?php echo $row_rsmd_cuisineb['city']; ?> <?php echo $row_rsmd_cuisineb['state']; ?> <?php echo $row_rsmd_cuisineb['zip']; ?><br>
            </span><span class="medbrown"><?php echo $row_rsmd_cuisineb['phone']; ?></span><br>
            <span class="smallblack"><?php echo $row_rsmd_cuisineb['email']; ?></span></p>      
    </td>
    <td width="36" height="10" colspan="3">&nbsp;</td>
  </tr>
</table>
<?php } while ($row_rsmd_cuisineb = mysql_fetch_assoc($rsmd_cuisineb)); ?>
<?php do { ?>
<table width="500" height="100" border="0">
  <tr>
    <td width="20" rowspan="5">&nbsp;</td>
    <td width="426"><p><span class="bigbrown">
	
	
	<a href="javascript:" class="bigbrown" onClick="window.open('details.php?recordID=<?php echo $row_rsmd_cuisinec['restaurant']; ?>','Details','screenX=500,width=400,height=400')"><?php echo $row_rsmd_cuisinec['restaurant']; ?><br>
    
	
	
	    </a></span><span class="smallblacknorm"><?php echo $row_rsmd_cuisinec['description']; ?><br>
	    </span><span class="smallbrown"><?php echo $row_rsmd_cuisinec['hours']; ?></span><span class="smallblacknorm"><br>
	    </span><span class="smallblack"><?php echo $row_rsmd_cuisinec['number']; ?>  <?php echo $row_rsmd_cuisinec['street']; ?> <?php echo $row_rsmd_cuisinec['city']; ?> <?php echo $row_rsmd_cuisinec['state']; ?> <?php echo $row_rsmd_cuisinec['zip']; ?><br>
        </span><span class="medbrown"><?php echo $row_rsmd_cuisinec['phone']; ?></span><br>
        <span class="smallblack"><?php echo $row_rsmd_cuisinec['email']; ?></span></p>      
    </td>
    <td width="36" colspan="3">&nbsp;</td>
  </tr>
</table>
<?php } while ($row_rsmd_cuisinec = mysql_fetch_assoc($rsmd_cuisinec)); ?>
<p>
</body>
</html>
<?php
mysql_free_result($rsmd_cuisineb);

mysql_free_result($rsmd_cuisinec);

mysql_free_result($rsmd_cuisinea2);
?>




Is what I'm trying to possible, given they way its set up?

Steve


feyd | LAST WARNING :: Use

Code: Select all

and

Code: Select all

tags where approriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

SOLVED

Posted: Fri Jul 30, 2004 12:16 am
by saltriver
I was correct about the if/else part. I was blind too, because Dreamweaver has a nice little tool called "Show if...". I choose field not empty, and DW takes care of it.

Doh!

Posted: Fri Jul 30, 2004 12:25 am
by d3ad1ysp0rk
DW.. no wonder your code looks so long and unecesarilly complicated :P