PHP, MySQL help please please please

Stocktrader

macrumors member
Original poster
Oct 28, 2007
85
0
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>
 

960design

macrumors 68030
Apr 17, 2012
2,846
842
Destin, FL
Hi there!

Could you wrap your code in [ code ] tags [ /code ] ( no spaces in the brackets ):
Code:
Like
  this
  {
    please?
  }
It makes it much easier to read!

1. Please forgive me if this is way below you; but I MUST ask. Is your user name: user_name, password: mypassword, database name: db_name?
2. mysql_pconnect is deprecated and should not be used.
3. You are using <table> for structure? How old is your tutorial?

A quick glance suggests you have several structure problems. Check this:
Code:
// build the query. start with conditions from submitted input:
if (isset($_POST['querysubmit'])) {
// get specified information from submitted input:
is indeed capturing when pressing the submit button. You could through a little test line like:
Code:
echo 'yeah it works';
exit();
 

Stocktrader

macrumors member
Original poster
Oct 28, 2007
85
0
Hi there!

Could you wrap your code in [ code ] tags [ /code ] ( no spaces in the brackets ):
Code:
Like
  this
  {
    please?
  }
It makes it much easier to read!

1. Please forgive me if this is way below you; but I MUST ask. Is your user name: user_name, password: mypassword, database name: db_name?
2. mysql_pconnect is deprecated and should not be used.
3. You are using <table> for structure? How old is your tutorial?

A quick glance suggests you have several structure problems. Check this:
Code:
// build the query. start with conditions from submitted input:
if (isset($_POST['querysubmit'])) {
// get specified information from submitted input:
is indeed capturing when pressing the submit button. You could through a little test line like:
Code:
echo 'yeah it works';
exit();

Thanks,
#1 No, I swapped out the actual username, password, and DBname
#2 Will update.
#3 The table was just a simple test I was trying and forgot to remove it before posting.

I'm just starting out but haven't seen any tutorials on how to use input from dropdown to create a Select command.

I just copied and tweaked the ONE tutorial I saw but I couldn't get it working.
Thanks again
 

960design

macrumors 68030
Apr 17, 2012
2,846
842
Destin, FL
This is how I handle programmable select statements ( using a wordpress example ):

In a separate functions.php file:
Code:
/* RETURNS dropdown list of Surveys
----------------------------------------*/
function _960Design_getSurveyOptions()
{
  global $wpdb;
  $html = '';
  $surveyKeys = array('surveyID','survey_title');
  $surveyData = array_fill_keys($surveyKeys,'');
  $surveyTable = $wpdb->prefix.'960design_survey';
  $sql = "SELECT surveyID, survey_title
	     FROM $surveyTable
	     ORDER BY survey_title ASC";
  $results = $wpdb->get_results($sql, ARRAY_A);
  if($results)
  {
    foreach($results as $value)
    {
      $html .= "<option value='$value[surveyID]'>$value[survey_title]</option>";
    }
  }

  return $html;
}
Higher up in the script:
Code:
/*VARIABLE Declaration - DISPLAY PAGE Only
------------------------------------------------------------------*/
$classKeys = array('survey');  //not relative to your select
$class = array_fill_keys($classKeys,'');  //not relative to your select
$surveyOptions = _960Design_getSurveyOptions();
/*----------------------------------------------------------------*/
I return a HEREDOC:
Code:
return <<<HTML
<li class='inline-li'>
  <label class='inline-label' for='surveyID'>Survey Name</label>
  <select name='surveyID' class='inline-select $class[survey]'>
    <option value='' disabled selected>Select a Survey</option>
    $surveyOptions
  </select>
</li>
 
Last edited:

Darth.Titan

macrumors 68030
Oct 31, 2007
2,717
316
Austin, TX
There's so much wrong with that code, but I'll keep to the question asked:

I notice you're checking for $_POST['tnyname']; when your form doesn't have a field whose name="tnyname". Shouldn't you be getting $_POST['ColumnTwo'] as that's the name of your option block with your select list?
 

Stocktrader

macrumors member
Original poster
Oct 28, 2007
85
0
There's so much wrong with that code, but I'll keep to the question asked:

I notice you're checking for $_POST['tnyname']; when your form doesn't have a field whose name="tnyname". Shouldn't you be getting $_POST['ColumnTwo'] as that's the name of your option block with your select list?
See next post
 
Last edited:

Stocktrader

macrumors member
Original poster
Oct 28, 2007
85
0
There's so much wrong with that code, but I'll keep to the question asked:

I notice you're checking for $_POST['tnyname']; when your form doesn't have a field whose name="tnyname". Shouldn't you be getting $_POST['ColumnTwo'] as that's the name of your option block with your select list?

OK ALMOST there... the line
<?php echo $_POST["ColumnxTwo"];?>!<br> is giving the results of the drop down correctly. I just need to find out how to display the results from the table selecting ONLY where tourname = input from ColumnxTwo

New results show:
Hotel IS . . . . Rocket town fun Classic!
$result = mysqli_query($con,"SELECT * FROM hotels WHERE tourname=$_POST["ColumnxTwo"]"); while($row = mysqli_fetch_array($result)) { echo $row['hotel'] . " " . $row['phone']; echo "
"; }


New code is below, not sure whats missing:

<html>
<body>



Hotel IS . . . .

<?php echo $_POST["ColumnxTwo"];?>!<br>


$result = mysqli_query($con,"SELECT * FROM hotels
WHERE tourname=$_POST["ColumnxTwo"]");

while($row = mysqli_fetch_array($result))
{
echo $row['hotel'] . " " . $row['phone'];
echo "<br>";
}



</body>
</html>
 

Darth.Titan

macrumors 68030
Oct 31, 2007
2,717
316
Austin, TX
Code:
$result = mysqli_query($con,"SELECT * FROM hotels
WHERE tourname='{$_POST['ColumnxTwo']}'");
Try that. Just be aware that passing any post data directly into MySQL is a bad idea. When you get it working be sure to sanitize your input first.