[Resolved] if statement ( I think)

[dcjones]

Hi all,

I have a problem with the following code.

I have a FORM with a LIST/MENU that gets its data from a table.

When a user selects from the LIST/MENU the script runs a filter and displays only the record that match the select from the list.

That part works OK.

What I want to do, is to have a selectable option on the LIST/MENU with a lable of "All" so that the users can select "All" from the LIST/MENU and all the record are shown. I have been playing around with this for hours. I did not want to come here and post before I had done my head in. Can you help or advise.

The code I am useing is:

<form action="booklist_filter.php" method="get" name="category" target="_self" class="bodytext" id="category"> 
    <table width="100%"  border="0" cellspacing="0" cellpadding="0"> 
    <tr> 
    <td width="93%"><select name="category" class="bodytext"onChange="submit()">
      <option value="" <?php if (!(strcmp("", "category"))) {echo "SELECTED";} ?>>All</option>
      <?php 
do {   

?>
      <option value="<?php echo $row_category['category_id']?>"<?php if (!(strcmp($row_category['category_id'], "category"))) {echo "SELECTED";} ?>><?php echo $row_category['category']?></option>
      <?php 
} while ($row_category = mysql_fetch_assoc($category)); 
  $rows = mysql_num_rows($category); 
  if($rows > 0) { 
      mysql_data_seek($category, 0); 
      $row_category = mysql_fetch_assoc($category); 
  } 
?> 
    </select> 
    </td>
    <td width="7%"><div align="right"><a href="index.htm" target="_self"><img src="images/Previous.gif" alt="Back to home page" width="14" height="13" border="0"></a></div></td> 
    </tr> 
    </table> 
    </form> 

Kind regards, keep safe and well.

Dereck.

[lazzerous]

Most of your changes will have to occur on booklist_fileter.php

In the script below, add a value to the 'all' option:

<option value="all" <?php if (!(strcmp("", "category"))) {echo "SELECTED";} ?>>All</option> 

Have an 'if' statement in booklist_filter.php that determines which sql query to use based on whether or not the user selected the 'all' option:

if ($category == "all")
    {
    $query = "select * from table";
    }
else
    {
    $query = "select * from table where category='$category'";
    }

$do_query = mysql_query($query);

[dcjones]

Hi lazzerous,

Thanks for your reply, I will work on the scripts as you have stated.

Many thanks

Dereck

[dcjones]

Hi me again, back for more advice,

The booklist.php script now has the following form:

<form action="booklist_filter.php" method="get" name="category" target="_self" class="bodytext" id="category"> 
    <table width="100%"  border="0" cellspacing="0" cellpadding="0"> 
    <tr> 
    <td width="93%"><select name="category" class="bodytext"onChange="submit()">
      <option value="all" <?php if (!(strcmp("", "category"))) {echo "SELECTED";} ?>>All</option>
      <?php 
do {   

?>


  <option value="<?php echo $row_category['category_id']?>"<?php if (!(strcmp($row_category['category_id'], "category"))) {echo "SELECTED";} ?>><?php echo $row_category['category']?></option>
  <?php 
} while ($row_category = mysql_fetch_assoc($category)); 
  $rows = mysql_num_rows($category); 
  if($rows > 0) { 
      mysql_data_seek($category, 0); 
      $row_category = mysql_fetch_assoc($category); 
  } 
?> 
    </select> 
    </td>
    <td width="7%"><div align="right"><a href="index.htm" target="_self"><img src="images/Previous.gif" alt="Back to home page" width="14" height="13" border="0"></a></div></td> 
    </tr> 
    </table> 
    </form> 

I have rewitten the book_filter.php script as follows:

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

$maxRows_record_filter = 4;
$pageNum_record_filter = 0;
if (isset($_GET['pageNum_record_filter'])) {
  $pageNum_record_filter = $_GET['pageNum_record_filter'];
}
$startRow_record_filter = $pageNum_record_filter * $maxRows_record_filter;

$colname_record_filter = "1";
if (isset($_GET['category'])) {
  $colname_record_filter = (get_magic_quotes_gpc()) ? $_GET['category'] : addslashes($_GET['category']);
}

if ($category == "all") 
    { 
	mysql_select_db($database_Chandos, $Chandos);
    $query_record_filter = sprintf("SELECT * FROM chandos_books WHERE category = '$s' ORDER BY 'book_title'", $colname_record_filter);
	$query_limit_record_filter = sprintf("%s LIMIT %d, %d", $query_record_filter, $startRow_record_filter, $maxRows_record_filter);
	$record_filter = mysql_query($query_limit_record_filter, $Chandos) or die(mysql_error());
	$row_record_filter = mysql_fetch_assoc($record_filter); 
    } 
else

mysql_select_db($database_Chandos, $Chandos);
$query_record_filter = sprintf("SELECT * FROM chandos_books WHERE category = '%s' ORDER BY 'book_title'", $colname_record_filter);
$query_limit_record_filter = sprintf("%s LIMIT %d, %d", $query_record_filter, $startRow_record_filter, $maxRows_record_filter);
$record_filter = mysql_query($query_limit_record_filter, $Chandos) or die(mysql_error());
$row_record_filter = mysql_fetch_assoc($record_filter);


if (isset($_GET['totalRows_record_filter'])) {
  $totalRows_record_filter = $_GET['totalRows_record_filter'];
} else {
  $all_record_filter = mysql_query($query_record_filter);
  $totalRows_record_filter = mysql_num_rows($all_record_filter);
}
$totalPages_record_filter = ceil($totalRows_record_filter/$maxRows_record_filter)-1;

mysql_select_db($database_Chandos, $Chandos);
$query_Cat_filter = "SELECT * FROM chandos_book_category";
$Cat_filter = mysql_query($query_Cat_filter, $Chandos) or die(mysql_error());
$row_Cat_filter = mysql_fetch_assoc($Cat_filter);
$totalRows_Cat_filter = mysql_num_rows($Cat_filter);

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

When I run the booklist.php script with the "all" option selcted it return zero records.

Can you spot where I have gone wronge.

Thanks for all your time.

Kind regards, keep safe and well.

Dereck

[lazzerous]

you didn't change your sql query for the 'all' option:

if ($category == "all")  

    {  

    mysql_select_db($database_Chandos, $Chandos); 
    $query_record_filter = sprintf("SELECT * FROM chandos_books ORDER BY 'book_title'", $colname_record_filter); 

[dcjones]

Hi, sorry to be a pain.

below is just a part of the script that deals with the IF STAEMENT.

if ($category == "All") 
    {   

	mysql_select_db($database_Chandos, $Chandos);
    $query_record_filter = sprintf("SELECT * FROM chandos_books WHERE category = 'All' ORDER BY 'book_title'", $colname_record_filter);
	$query_limit_record_filter = sprintf("%s LIMIT %d, %d", $query_record_filter, $startRow_record_filter, $maxRows_record_filter);
	$record_filter = mysql_query($query_limit_record_filter, $Chandos) or die(mysql_error());
	$row_record_filter = mysql_fetch_assoc($record_filter); 
    } 
else

I have

if ($category == "All")

set along with

WHERE category = 'All' 

Where am I not changeing mysql query for the 'all' option:

If you could point it out it would be of great help.

Many thanks in advance.

Dereck

[lazzerous]

if you are selecting from all categories, then why use a where statement in your sql query

If you want everything: select from table;
If you want only one thing: select from table WHERE category='$category'

get what I'm saying?
By selecting 'All Categories', they are telling you to NOT limit your query by category.

[dcjones]

Hi,

I see what you are saying, sorry. I changed the script to

if ($category == "All")   

    {   

    mysql_select_db($database_Chandos, $Chandos);  

    $query_record_filter = sprintf("SELECT * FROM chandos_books ORDER BY 'book_title'", $colname_record_filter);

$query_limit_record_filter = sprintf("%s LIMIT %d, %d", $query_record_filter, $startRow_record_filter, $maxRows_record_filter);
$record_filter = mysql_query($query_limit_record_filter, $Chandos) or die(mysql_error());
$row_record_filter = mysql_fetch_assoc($record_filter); 
} 
else

When I run this and select "all" from the booklist.php script it return zero records.

I have been looking at this script two long, you know whats its like, you can't see the past the end of your nose after a while.

Still no records returned.

Kind regards.

Dereck

[lazzerous]

have you populated the database with any data yet?

try running that sql query directly in the db

[dcjones]

Hi,

Yes there is data in the tables over 50 records.

http://www.chandospublishing.com/catalogue/booklist.php

I just cannot see what is wronge.

Kind regards and thank you for you help.

Dereck

[lazzerous]

try this - if it doesn't work, I'm not sure what to tell you...

if ($category == "all")  

    {  

    $query = "SELECT * FROM chandos_books WHERE category = '$s' ORDER BY 'book_title'";
    }
else
    {
    $query = "SELECT * FROM chandos_books WHERE category = '%s' ORDER BY 'book_title'";
    }


mysql_select_db($database_Chandos, $Chandos); 
$query_record_filter = sprintf($query, $colname_record_filter); 
$query_limit_record_filter = sprintf("%s LIMIT %d, %d", $query_record_filter, $startRow_record_filter, $maxRows_record_filter); 
$record_filter = mysql_query($query_limit_record_filter, $Chandos) or die(mysql_error()); 
$row_record_filter = mysql_fetch_assoc($record_filter); 

[dcjones]

Hi, it is I agin, with a result.

I used your part of the script with no joy. So I made an adjustment as follows:

if ($category == "")   

    {   

    $query = "SELECT * FROM chandos_books  ORDER BY 'book_title'"; 
    } 
else

$query = "SELECT * FROM chandos_books WHERE category = '%s' ORDER BY 'book_title'"; 


mysql_select_db($database_Chandos, $Chandos);  

$query_record_filter = sprintf($query, $colname_record_filter);  

$query_limit_record_filter = sprintf("%s LIMIT %d, %d", $query_record_filter, $startRow_record_filter, $maxRows_record_filter);  

$record_filter = mysql_query($query_limit_record_filter, $Chandos) or die(mysql_error());  

$row_record_filter = mysql_fetch_assoc($record_filter);

I changed "if ($category == "All")" to "if ($category == "")"
removed "WHERE category = '$s'" and changed select option in the booklist.php script so that the "All" option has a lable but no value.

And it works. This has been a strange one. But like always in this forum, when there is someone to bounce your problems off a result is found.

I must thank you for all the time you have spent looking at this script.

This forum is full of stars.

Kind regards, keep safe and well.

Dereck, going for a beer or two.