Display table based on checkbox selection

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • RANJIT SINGH
    New Member
    • Jun 2011
    • 5
    #1

    Display table based on checkbox selection

    Below is a table generated by DW for displaying a table of all subjects in a MySQL database. I'm totally new to php and would be grateful for any assistance on how the table could be displayed based on the subject checkbox (line 87).



    Code:
    <?php require_once('Connections/Education.php'); ?>
<?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 (!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;
}
}

mysql_select_db($database_Education, $Education);
$query_rsMath = "SELECT * FROM maths ORDER BY Grade ASC";
$rsMath = mysql_query($query_rsMath, $Education) or die(mysql_error());
$row_rsMath = mysql_fetch_assoc($rsMath);
$totalRows_rsMath = mysql_num_rows($rsMath);

mysql_select_db($database_Education, $Education);
$query_Recordset2 = "SELECT Grade, Topic FROM maths WHERE Subject = 'English' ORDER BY Subject ASC";
$Recordset2 = mysql_query($query_Recordset2, $Education) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);
?>
<!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>Untitled Document</title>
</head>

<body>
<?php 

if (isset($_POST['english']))
{
	
	}
?>
<?php echo "Education Portal" ?>
<form id="form1" name="form1" method="post" action="">
  <p>
    <input type="checkbox" name="english" id="english" />
    English
  </p>
  <p>
    <input type="checkbox" name="science" id="science" />
  Science</p>
  <p>
    <input type="checkbox" name="maths" id="maths" />
  Mathematics</p>
  <p> 	     
    <input type="checkbox" name="malay" id="malay" />
  Bahasa Malaysia</p>
  <p>
  <input type="submit" name="submit" id="submit" value="Submit" />
  </p>
</form>
<p>&nbsp;</p>
<table border="2">
  <tr>
    <td>Category</td>
       <td>Subject</td>
    <td>Grade</td>
    <td>Topic</td>
    <td>Date</td>
    <td>Name</td>
    <td>Exam Set</td>
    <td>Author</td>
 
  </tr>
  <?php do { ?>
    <tr>
          <td><?php echo "<img src='{$row_rsMath['Image']}' width=50 height=50" ?></td>
          <td><?php echo $row_rsMath['Subject']; ?></td>
      <td><?php echo $row_rsMath['Grade']; ?></td>
      <td><?php echo $row_rsMath['Topic']; ?></td>
      <td><?php echo $row_rsMath['Date']; ?></td>
	  <td><?php echo "<a href='{$row_rsMath['Link']}'>{$row_rsMath['Link']}  </a>" ?> </td>
     
      <td><?php echo $row_rsMath['Set']; ?></td>
      <td><?php echo $row_rsMath['Author']; ?></td>
      
    </tr>
    <?php } while ($row_rsMath = mysql_fetch_assoc($rsMath)); ?>
</table>

<table border="2">
</table>
</body>
</html>
<?php
mysql_free_result($rsMath);

mysql_free_result($Recordset2);
?>
    Tags: checkbox, database, html, mysql, php
    • John Doe
      New Member
      • Jun 2011
      • 20
      #2
      Ranjit,

      there's a few things you need to do to achieve what you want.

      First, you need to add value=1 to each of your checkbox <input> tags so that a value is passed when the checkbox is ticked.

      Second, you need to change the 'echo' lines in your html so that they are taking data from the correct dataset. Instead of using echo $row_rsMath you need to change them to echo $row_Recordset2 (because your have created two recordsets in your php code).

      Third, you should delete the two functions function GetSQLValueStri ng because you're not using them and you have them defined twice.

      Fourth, you need to get the checked status of your checkboxes, so you will want something like the following at the top of your code...

      Code:
      <?php require_once('Connections/Education.php'); ?>
<?php

$dbfilter = "''"; 	// database filter var

// Get filter parameters
if ( isset($_POST["english"]) && !empty($_POST["english"]) ) {
	$dbfilter .= ",'English'";
}
if ( isset($_POST["maths"]) && !empty($_POST["maths"]) ) {
	$dbfilter .= ",'Maths'";
}
//etc.
      Here you can see that we are checking to see if the _POST variable is set for each of the checkboxes, and if it is we are building up a database filter variable $dbfilter of all the subjects checked. The values are slightly odd (a comma, then a value inside apostrophes) because we are really building a bit of SQL code.

      Lastly, you need to change your SQL command to only look for the checked subjects like so...

      $query_Recordse t2 = "SELECT Grade, Topic FROM maths WHERE Subject LIKE (" .$dbfilter. ") ORDER BY Subject ASC";

      Here we are using the SQL 'like' command to look for a range of values to get from the database and supplying it the $dbfilter variable that we created earlier.

      The full code (I haven't tested this but it's close), with a couple of other changes, would be...

      Code:
      <?php require_once('Connections/Education.php'); ?>
<?php

$dbfilter = "''"; 	// database filter var

// Get filter parameters
if ( isset($_POST["english"]) && !empty($_POST["english"]) ) {
	$dbfilter .= ",'English'";
}
if ( isset($_POST["maths"]) && !empty($_POST["maths"]) ) {
	$dbfilter .= ",'Maths'";
}
//ADD THE OTHERS HERE etc.

mysql_select_db($database_Education, $Education);
$query_Recordset2 = "SELECT Grade, Topic FROM maths WHERE Subject LIKE (" .$dbfilter. ") ORDER BY Subject ASC";
$Recordset2 = mysql_query($query_Recordset2, $Education) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);
?>
<!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>Untitled Document</title>
</head>
 
<body>
<?php echo "Education Portal" ?>
<form id="form1" name="form1" method="post" action="">
  <p>
    <input type="checkbox" name="english" id="english" value="1" />
    English
  </p>
  <p>
    <input type="checkbox" name="science" id="science" value="1" />
  Science</p>
  <p>
    <input type="checkbox" name="maths" id="maths" value="1" />
  Mathematics</p>
  <p>          
    <input type="checkbox" name="malay" id="malay" value="1" />
  Bahasa Malaysia</p>
  <p>
  <input type="submit" name="submit" id="submit" value="Submit" />
  </p>
</form>
<p>&nbsp;</p>
<table border="2">
  <tr>
    <td>Category</td>
       <td>Subject</td>
    <td>Grade</td>
    <td>Topic</td>
    <td>Date</td>
    <td>Name</td>
    <td>Exam Set</td>
    <td>Author</td>
 
  </tr>
  <?php do { ?>
    <tr>
          <td><?php echo "<img src='{$row_rsMath['Image']}' width=50 height=50" ?></td>
          <td><?php echo $row_Recordset2['Subject']; ?></td>
      <td><?php echo $row_Recordset2['Grade']; ?></td>
      <td><?php echo $row_Recordset2['Topic']; ?></td>
      <td><?php echo $row_Recordset2['Date']; ?></td>
      <td><?php echo "<a href='{$row_rsMath['Link']}'>{$row_rsMath['Link']}  </a>" ?> </td>
 
      <td><?php echo $row_Recordset2['Set']; ?></td>
      <td><?php echo $row_Recordset2['Author']; ?></td>
 
    </tr>
    <?php } while ($row_Recordset2 = mysql_fetch_assoc($Recordset2)); ?>
</table>
 
<table border="2">
</table>
</body>
</html>
<?php
mysql_free_result($Recordset2);
?>

        Comment

        • RANJIT SINGH
          New Member
          • Jun 2011
          • 5
          #3
          Hi john....

          Tried running the code, however when english check box is ticked & SUMBIT is pressed - the browser displays "Operand should contain 1 column(s)"

          Before english check box is ticked, browser already displays some errors as shown below.

            Comment

            • John Doe
              New Member
              • Jun 2011
              • 20
              #4
              Ranjit,

              " Second, you need to change the 'echo' lines in your html so that they are taking data from the correct dataset. Instead of using echo $row_rsMath you need to change them to echo $row_Recordset2 (because your have created two recordsets in your php code). "

              I can see that I missed some replacements in the full code I posted. Just make sure you change all the row_rsMath to row_Recordset2.

              Not sure about the Operand error. What code line is it complaining about?

                Comment

                • John Doe
                  New Member
                  • Jun 2011
                  • 20
                  #5
                  Ah, oops, I see the problem with the Operand...

                  In this line:

                  $query_Recordse t2 = "SELECT Grade, Topic FROM maths WHERE Subject LIKE (" .$dbfilter. ") ORDER BY Subject ASC";

                  Change the word LIKE to IN, giving:

                  $query_Recordse t2 = "SELECT Grade, Topic FROM maths WHERE Subject IN (" .$dbfilter. ") ORDER BY Subject ASC";

                  Sorry 'bout that.

                    Comment

                    Previous template Next
                    Working...