Hello,

I'm trying to display the results of a MySQL query based on user input. I have two drop-downs that pull distinct values from columns in a table, and on submit, I'd like to display the results based on those pull-down values.

The first part, which gets the pull-down values from the table, seems to work fine:

PHP Code:

<?
$db_host = 'localhost';
$db_user = 'myuser'; 
$db_passwd = 'mypass';
$db_name = 'mydatabase';

// drop-down form for selecting column values:

  <form method='post' action="<?=$_SERVER['PHP_SELF']?>"'>
    <table align='center'>      <tr>
       <td>Select a value from column one:
          <select name="column1value">
<?
      $col1list = mysql_query("select distinct mytablecolumnone from mytable order by mytablecolumnone")
        or die("column one query failed");
      while ($record_col1 = mysql_fetch_array($col1list))
        {
          printf("           <option>%s</option>\n",$record_col1['mytablecolumnone']);
        }
?>
          </select>
       </td>
       <td>Select a value from column two:
          <select name="column2value">
<?
      $col2list = mysql_query("select distinct mytablecolumntwo from mytable order by mytablecolumntwo")
        or die("column two query failed");
      while ($record_col2 = mysql_fetch_array($col2list))
        {
          printf("           <option>%s</option>\n",$record_col2['mytablecolumntwo]);
        }
?>
          </select>
       </td>
       <td><input type="submit" name="querysubmit" value="View your results"></td>
      </tr>
    </table>
  </form>

It's the part where the results are printed that I can't seem to get right:

PHP Code:

<?
$num = mysql_numrows($result);
// build the query. start with conditions from submitted input:
if (isset($_POST['querysubmit'])) {
    if ($FilterSet)
      {
        $result = mysql_query("select * from mytable
        WHERE id > 0
        $FilterOneQuery
        $FilterTwoQuery
        order by mytablecolumntwo,mytablecolumnone");
        $resultarray = mysql_fetch_row_array($result);
        print $resultarray[0]; 
      }
    // otherwise, use the base query, without conditions from imput:
    else
      {
        $result = mysql_query("select * from mytable
        order by mytablecolumnone,mytablecolumntwo");
        $resultarray = mysql_fetch_row_array($result);
        print $resultarray[0]; 
      }

    // get specified column value information from submitted input:
    $FilterOne  = $_GET['column1value'];
    $FilterTwo = $_GET['column2value'];
    // sanitize the input (not really needed, but...):
    $FilterOne  = sanitize_filter_input($FilterOne);
    $FilterTwo = sanitize_filter_input($FilterTwo);

    // add input values as conditions to the query:
    if (!empty($FilterOne))
      {
        $FilterOneQuery = "AND mytablecolumnone =" . $FilterOne;
        $FilterSet      = TRUE;
      }
    if (!empty($FilterTwo))
      {
        $FilterTwoQuery = "AND mytablecolumntwo =" . $FilterTwo;
        $FilterSet  = TRUE;
      }

    // print results:
    $resultarray = mysql_fetch_row_array($result);
    print $resultarray[0]; 
    print "Displaying $num records.<br/>";  
    print "<table width=200 border=1>\n";
    // print table columns as headings:
    for ($i = 0; $i < mysql_num_fields($result); $i++) {
      print '<th>' . mysql_field_name($result, $i) . '</th>';
      }
    // print table rows:
    while($getrow = mysql_fetch_array($result))
      {
          print "<tr>\n";
          foreach ($getrow as $field)
          print "\t<td>$field</td>\n";
          print "</tr>\n";
      }
    print "</table>\n";
    }    

// close connection:
mysql_close(); ?>

The pull-downs contain column values form the MySQL table, and there are no errors on submitting the form, but no results are returned.

If I add a 'print $result' after each of the two column conditions, and to the print table section at the end, I get: "Resource id #5". If I change these to an array instead, as they are now:

PHP Code:

$resultarray = mysql_fetch_row_array($result);
print $resultarray[0]; 


... it's the same as before: nothing is printed.

Any thoughts on what I'm doing wrong would be greatly appreciated!

Thanks.

Two things. Firstly, where do the $_GET values come from in the second script? The form is post, so that could be one of the problems. Secondly, you have the second code out of order a bit. Try this:

PHP Code:

<?
$num = mysql_numrows($result);
// build the query. start with conditions from submitted input:
if (isset($_POST['querysubmit'])) {
    if ($FilterSet)
      {
    // get specified column value information from submitted input:
    $FilterOne  = $_GET['column1value'];
    $FilterTwo = $_GET['column2value'];
    // sanitize the input (not really needed, but...):
    $FilterOne  = sanitize_filter_input($FilterOne);
    $FilterTwo = sanitize_filter_input($FilterTwo);

    // add input values as conditions to the query:
    if (!empty($FilterOne))
      {
        $FilterOneQuery = "AND mytablecolumnone =" . $FilterOne;
        $FilterSet      = TRUE;
      }
    if (!empty($FilterTwo))
      {
        $FilterTwoQuery = "AND mytablecolumntwo =" . $FilterTwo;
        $FilterSet  = TRUE;
      }
        $result = mysql_query("select * from mytable
        WHERE id > 0
        $FilterOneQuery
        $FilterTwoQuery
        order by mytablecolumntwo,mytablecolumnone");
        $resultarray = mysql_fetch_row_array($result);
        print $resultarray[0]; 
      }
    // otherwise, use the base query, without conditions from imput:
    else
      {
        $result = mysql_query("select * from mytable
        order by mytablecolumnone,mytablecolumntwo");
        $resultarray = mysql_fetch_row_array($result);
        print $resultarray[0]; 
      }

    // print results:
    $resultarray = mysql_fetch_row_array($result);
    print $resultarray[0]; 
    print "Displaying $num records.<br/>";  
    print "<table width=200 border=1>\n";
    // print table columns as headings:
    for ($i = 0; $i < mysql_num_fields($result); $i++) {
      print '<th>' . mysql_field_name($result, $i) . '</th>';
      }
    // print table rows:
    while($getrow = mysql_fetch_array($result))
      {
          print "<tr>\n";
          foreach ($getrow as $field)
          print "\t<td>$field</td>\n";
          print "</tr>\n";
      }
    print "</table>\n";
    }    

// close connection:
mysql_close(); ?>

But that wouldn't work if you're confusing $_POST and $_GET ^_^+

Thanks very much PeachyJuice. Believe it or not, switching the order made no difference: still no errors, and still no table output. I also tried changing my $_GET assignments to $_POST, but no change there either!

Well add an 'or die' statement to the query to see if it's being executed first. If the query's not being executed, then nothing's going to work. Your query should look something like this:

PHP Code:

$result = mysql_query("select * from mytable 
        WHERE id > 0 
        $FilterOneQuery 
        $FilterTwoQuery 
        order by mytablecolumntwo,mytablecolumnone") or die(mysql_error()); 


If that doesn't output an error then we have to look at your loop structure. There's something that bugs me about that, but lets make sure that the query's actually running.

Also do you have error_reporting(); turned on? If not, add

PHP Code:

error_reporting(E_ALL); 


To the top of the handler page. It'll output all the errors that could be causing the problem =)

Thanks PeachyJuice. Not sure what's going on here, but I made all those change, and still getting the same results, or lack thereof!

Here's my complete code

PHP Code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<body>
<?
error_reporting(E_ALL); 
$db_host = 'localhost';
$db_user = 'myuser';
$db_passwd = 'mypass';
$db_name = 'mydb';

import_request_variables('gp','p_');

// establish connection using variable values:
mysql_pconnect("$db_host","$db_user","$db_passwd")
     or die("Unable to connect to SQL server");
mysql_select_db($db_name)
     or die("Unable to select database");

// count resultant rows:
$num = mysql_numrows($result);
?>

<!-- selection form: --> 
  <form method='post' action="<?=$_SERVER['PHP_SELF']?>"'>
    <table align='center'>      <tr>
       <td>Select Column One:
          <select name="ColumnOne">
<?
      $columnOneList = mysql_query("select distinct ColumnOne from mytable order by ColumnOne") 
        or die("mytable col1 query failed");
      while ($record_col1 = mysql_fetch_array($columnOneList))
        {
          printf("           <option>%s</option>\n",$record_col1['ColumnOne']);
        }
?>
          </select>
       </td>
       <td>Select Column Two:
          <select name="ColumnTwo">
<?
      $columnTwoList = mysql_query("select distinct ColumnOTwofrom mytable order by ColumnTwo") 
        or die("mytable col2 query failed");
      while ($record_col2 = mysql_fetch_array($columnTwoList))
        {
          printf("           <option>%s</option>\n",$record_col2['ColumnTwo']);
        }
?>
          </select>
       </td>
       <td><input type="submit" name="querysubmit" value="View by row and rack"></td>
      </tr>
    </table>
  </form>

<?
// build the query. start with conditions from submitted input:
if (isset($_POST['querysubmit'])) {
    if ($FilterSet)
      {
    // get specified information from submitted input:
    $FilterColOne  = $_GET['ColumnOne'];
    $FilterColTwo = $_GET['ColumnTwo'];
    // $FilterColOne  = $_POST['ColumnOne'];
    // $FilterColTwo = $_POST['ColumnTwo'];
    // sanitize the input:
    $FilterColOne  = sanitize_filter_input($FilterColOne);
    $FilterColTwo = sanitize_filter_input($FilterColTwo);


    // add row and rack values as conditions to the query:
    if (!empty($FilterColOne))
      {
        $FilterColOneQuery = "AND ColumnOne =" . $FilterColOne;
        $FilterSet      = TRUE;
      }
    if (!empty($FilterColTwo))
      {
        $FilterColTwoQuery = "AND ColumnTwo =" . $FilterColTwo;
        $FilterSet  = TRUE;
      }

        $result = mysql_query("select * from mytable
        WHERE id > 0
        $FilterColOneQuery
        $FilterColTwoQuery
        order by ColumnTwo,ColumnOne") or die(mysql_error());
        $resultarray = mysql_fetch_row_array($result);
        print $resultarray[0];
      }
    // otherwise, use the base query, without conditions from imput:
    else
      {
        $result = mysql_query("select * from mytable
        order by ColumnOne,ColumnTwo") or die(mysql_error());
        $resultarray = mysql_fetch_row_array($result);
        print $resultarray[0];
      }
    
    // print results:
    $resultarray = mysql_fetch_row_array($result);
    print $resultarray[0];
    print "Displaying $num records.<br/>";
    print "<table width=200 border=1>\n";
    // print table columns as headings:
    for ($i = 0; $i < mysql_num_fields($result); $i++) {
      print '<th>' . mysql_field_name($result, $i) . '</th>';
      }
    // print table rows:
    while($getrow = mysql_fetch_array($result))
      {
          print "<tr>\n"; 
          foreach ($getrow as $field)
          print "\t<td>$field</td>\n";
          print "</tr>\n";
      }
    print "</table>\n";
    }

// close connection:
mysql_close(); ?>
</body>
</html>

Okay so I looked a bit closer and found a few things.

1. You had the entire form processing banking on the fact that $FilterSet had been set. Since it wasn't defined before the if, nothing was happening. I think this was the main problem.

2. You're still confusing $_POST and $_GET. Everything in the script has to be consistent. Generally, $_POST is only used when there's some sort of change being made to the database, but I know I don't like that rule xD (I hate messy URL's)

3. Just a sidenote. You were counting the rows fetched from $result before actually doing the query. Moved that so it should actually return a value.

4. I hope you have sanitize_filter_input() defined somewhere. Otherwise you'll get a call to an undefined function and it won't work.

Here's the fixed code. Let me know if anything displays now.

PHP Code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<body>
<?
error_reporting(E_ALL); 
$db_host = 'localhost';
$db_user = 'myuser';
$db_passwd = 'mypass';
$db_name = 'mydb';

import_request_variables('gp','p_');

// establish connection using variable values:
mysql_pconnect("$db_host","$db_user","$db_passwd")
     or die("Unable to connect to SQL server");
mysql_select_db($db_name)
     or die("Unable to select database");

?>

<!-- selection form: --> 
  <form method='post' action="<?=$_SERVER['PHP_SELF']?>"'>
    <table align='center'>      <tr>
       <td>Select Column One:
          <select name="ColumnOne">
<?
      $columnOneList = mysql_query("select distinct ColumnOne from mytable order by ColumnOne") 
        or die("mytable col1 query failed");
      while ($record_col1 = mysql_fetch_array($columnOneList))
        {
          printf("           <option>%s</option>\n",$record_col1['ColumnOne']);
        }
?>
          </select>
       </td>
       <td>Select Column Two:
          <select name="ColumnTwo">
<?
      $columnTwoList = mysql_query("select distinct ColumnOTwofrom mytable order by ColumnTwo") 
        or die("mytable col2 query failed");
      while ($record_col2 = mysql_fetch_array($columnTwoList))
        {
          printf("           <option>%s</option>\n",$record_col2['ColumnTwo']);
        }
?>
          </select>
       </td>
       <td><input type="submit" name="querysubmit" value="View by row and rack"></td>
      </tr>
    </table>
  </form>

<?
// build the query. start with conditions from submitted input:
if (isset($_POST['querysubmit'])) {
    // get specified information from submitted input:

      $FilterColOne  = $_POST['ColumnOne'];
    $FilterColTwo = $_POST['ColumnTwo'];
    // sanitize the input:
    $FilterColOne  = sanitize_filter_input($FilterColOne);
    $FilterColTwo = sanitize_filter_input($FilterColTwo);


    // add row and rack values as conditions to the query:
    if (!empty($FilterColOne))
      {
        $FilterColOneQuery = "AND ColumnOne =" . $FilterColOne;
        $FilterSet      = TRUE;
      }
    if (!empty($FilterColTwo))
      {
        $FilterColTwoQuery = "AND ColumnTwo =" . $FilterColTwo;
        $FilterSet  = TRUE;
      }
      if ($FilterSet)
      {
        $result = mysql_query("select * from mytable
        WHERE id > 0
        $FilterColOneQuery
        $FilterColTwoQuery
        order by ColumnTwo,ColumnOne") or die(mysql_error());
        $resultarray = mysql_fetch_row_array($result);
        print $resultarray[0];
      }
    // otherwise, use the base query, without conditions from imput:
    else
      {
        $result = mysql_query("select * from mytable
        order by ColumnOne,ColumnTwo") or die(mysql_error());
        $resultarray = mysql_fetch_row_array($result);
        print $resultarray[0];
      }
    
    // print results:
    $resultarray = mysql_fetch_row_array($result);
     // count resultant rows:
      $num = mysql_numrows($result);
    print $resultarray[0];
    print "Displaying $num records.<br/>";
    print "<table width=200 border=1>\n";
    // print table columns as headings:
    for ($i = 0; $i < mysql_num_fields($result); $i++) {
      print '<th>' . mysql_field_name($result, $i) . '</th>';
      }
    // print table rows:
    while($getrow = mysql_fetch_array($result))
      {
          print "<tr>\n"; 
          foreach ($getrow as $field)
          print "\t<td>$field</td>\n";
          print "</tr>\n";
      }
    print "</table>\n";
    }

// close connection:
mysql_close(); ?>
</body>
</html>

PeachyJuice: thanks so much! I followed your suggestions, made all those changes, even changed all the POSTs to GETs, since that seems to make more sense here, and believe it or not, I still have the same problem: the pull-downs still work, and submit still returns nothing, not even a null set!

PeachyJuice was a huge help (thank you!): basically, I needed to stop trying to create mysql_* functions that don't actually exist and double-check my for/while loops to make sure they don't step on each other.