I've got a problem that I hope you can help.
My goal is:
1. Create a dropdown list with unique results from a MySql table field called tnyname in the table called hotels.
2. Create a table that shows results (select * where tnyname = Dropdown selection) with fields tnyname, fielda, field, field, etc..
I got the dropdown list working great, but on selecting, I can get the results to display.
Code I'm using is below.
Thanks.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<body>
<?
error_reporting(E_ALL);
$db_host = 'localhost';
$db_user = 'user_name';
$db_passwd = 'mypassword';
$db_name = 'db_name';
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>Choose Tourney:
<select name="ColumnTwo">
<?
$columnTwoList = mysql_query("select distinct tnyname from hotels order by tnyname")
or die("hotels col2 query failed");
while ($record_col2 = mysql_fetch_array($columnTwoList))
{
printf(" <option>%s</option>\n",$record_col2['tnyname']);
}
?>
</select>
</td>
<td><input type="submit" name="querysubmit" value="Submit Tourney"></td>
</tr>
</table>
</form>
<?
// build the query. start with conditions from submitted input:
if (isset($_POST['querysubmit'])) {
// get specified information from submitted input:
$FilterColTwo = $_POST['tnyname'];
// 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 hotel =" . $FilterColOne;
$FilterSet = TRUE;
}
if (!empty($FilterColTwo))
{
$FilterColTwoQuery = "AND tnyname =" . $FilterColTwo;
$FilterSet = TRUE;
}
if ($FilterSet)
{
$result = mysql_query("select * from hotels
WHERE id > 0
$FilterColOneQuery
$FilterColTwoQuery
order by tnyname,hotel") 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 hotels
order by hotel,tnyname") 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";
}
$resultx = mysqli_query($con,"SELECT * FROM hotels ORDER BY tnyname");
echo "<table border='2'>
<tr>
<th>Hotels</th>
<th>Tournament</th>
<th>Team Name</th>
<th>Email</th>
</tr>";
while($row = mysqli_fetch_array($resultx))
{
echo "<tr>";
echo "<td nowrap>" . $row['hotel'] . "</td>";
echo "<td nowrap>" . $row['tourname'] . "</td>";
echo "<td nowrap>" . $row['tmname'] . "</td>";
echo "<td nowrap> " . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
// close connection:
mysql_close(); ?>
</body>
</html>
My goal is:
1. Create a dropdown list with unique results from a MySql table field called tnyname in the table called hotels.
2. Create a table that shows results (select * where tnyname = Dropdown selection) with fields tnyname, fielda, field, field, etc..
I got the dropdown list working great, but on selecting, I can get the results to display.
Code I'm using is below.
Thanks.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<body>
<?
error_reporting(E_ALL);
$db_host = 'localhost';
$db_user = 'user_name';
$db_passwd = 'mypassword';
$db_name = 'db_name';
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>Choose Tourney:
<select name="ColumnTwo">
<?
$columnTwoList = mysql_query("select distinct tnyname from hotels order by tnyname")
or die("hotels col2 query failed");
while ($record_col2 = mysql_fetch_array($columnTwoList))
{
printf(" <option>%s</option>\n",$record_col2['tnyname']);
}
?>
</select>
</td>
<td><input type="submit" name="querysubmit" value="Submit Tourney"></td>
</tr>
</table>
</form>
<?
// build the query. start with conditions from submitted input:
if (isset($_POST['querysubmit'])) {
// get specified information from submitted input:
$FilterColTwo = $_POST['tnyname'];
// 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 hotel =" . $FilterColOne;
$FilterSet = TRUE;
}
if (!empty($FilterColTwo))
{
$FilterColTwoQuery = "AND tnyname =" . $FilterColTwo;
$FilterSet = TRUE;
}
if ($FilterSet)
{
$result = mysql_query("select * from hotels
WHERE id > 0
$FilterColOneQuery
$FilterColTwoQuery
order by tnyname,hotel") 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 hotels
order by hotel,tnyname") 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";
}
$resultx = mysqli_query($con,"SELECT * FROM hotels ORDER BY tnyname");
echo "<table border='2'>
<tr>
<th>Hotels</th>
<th>Tournament</th>
<th>Team Name</th>
<th>Email</th>
</tr>";
while($row = mysqli_fetch_array($resultx))
{
echo "<tr>";
echo "<td nowrap>" . $row['hotel'] . "</td>";
echo "<td nowrap>" . $row['tourname'] . "</td>";
echo "<td nowrap>" . $row['tmname'] . "</td>";
echo "<td nowrap> " . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
// close connection:
mysql_close(); ?>
</body>
</html>