building MySQL table results with PHP and user input

[deesto]

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:
<?
$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:
<?
$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:
$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?Thanks.

[JustAGuy]

Some thought -
1) Don't have your db info in your page.
Instead have a file called _db_connect.inc.php


<?php
$db_host = 'localhost';
$db_user = 'myuser';
$db_passwd = 'mypass';
$db_name = 'mydatabase';
$db = mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($db_name, $db);
?>


keep that file outside the web root if possible (not always on shared hosting) - and include it at the top of any pages that need to make a db connection. IE -

include('../_db_connect.inc.php');


-=-

second - your code uses mysql query.
You usually want to have the query string as a variable.

<?php
$sql="select distinct mytablecolumntwo from mytable order by mytablecolumntwo"
$result=mysql_query($sql);


then do your magic on $result

It makes it easier to figure out stuff.
I also find that using upper case for the SQL statements makes it easier - IE

SELECT DISTINCT field FROM table ORDER BY field

Makes it a little easier to spot mistakes in a query, such as when the query is valid but not what you really wanted to query.


The or die("death message")
isn't really necessary and should NEVER be in a production page. Production pages should not output any server side error messages at all upon failure. Get those from the apache log.

As far as what is up with your code - I'd have to look at it closer, but those issues shot out at me. Works the way you did them, but isn't really proper.