Go Back   MacRumors Forums > Special Interests > Visual Media > Web Design and Development

Reply
 
Thread Tools Search this Thread Display Modes
Old Jun 3, 2013, 08:10 PM   #1
Stocktrader
macrumors member
 
Join Date: Oct 2007
PHP, MySQL help please please please

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>
Stocktrader is offline   0 Reply With Quote
Old Jun 4, 2013, 06:57 AM   #2
960design
macrumors 6502a
 
Join Date: Apr 2012
Location: 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();
__________________
TI-99/4A, tape cassette, 12" B&W Zenith
960design is offline   0 Reply With Quote
Old Jun 4, 2013, 08:27 AM   #3
Stocktrader
Thread Starter
macrumors member
 
Join Date: Oct 2007
Quote:
Originally Posted by 960design View Post
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
Stocktrader is offline   0 Reply With Quote
Old Jun 4, 2013, 08:54 AM   #4
960design
macrumors 6502a
 
Join Date: Apr 2012
Location: 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>
__________________
TI-99/4A, tape cassette, 12" B&W Zenith

Last edited by 960design; Jun 4, 2013 at 09:43 PM.
960design is offline   0 Reply With Quote
Old Jun 4, 2013, 09:58 AM   #5
Darth.Titan
macrumors 68020
 
Darth.Titan's Avatar
 
Join Date: Oct 2007
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?
Darth.Titan is offline   0 Reply With Quote
Old Jun 4, 2013, 08:14 PM   #6
Stocktrader
Thread Starter
macrumors member
 
Join Date: Oct 2007
Quote:
Originally Posted by Darth.Titan View Post
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 by Stocktrader; Jun 4, 2013 at 09:34 PM. Reason: fixing
Stocktrader is offline   0 Reply With Quote
Old Jun 4, 2013, 09:38 PM   #7
Stocktrader
Thread Starter
macrumors member
 
Join Date: Oct 2007
Quote:
Originally Posted by Darth.Titan View Post
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>
Stocktrader is offline   0 Reply With Quote
Old Jun 5, 2013, 01:29 PM   #8
Darth.Titan
macrumors 68020
 
Darth.Titan's Avatar
 
Join Date: Oct 2007
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.
Darth.Titan is offline   0 Reply With Quote

Reply
MacRumors Forums > Special Interests > Visual Media > Web Design and Development

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Similar Threads
thread Thread Starter Forum Replies Last Post
PHP & MySQL help please. Stocktrader Web Design and Development 1 Mar 9, 2014 06:04 AM
php mysql = NSString IDMah iPhone/iPad Programming 9 Dec 20, 2013 01:35 PM
Resolved: mySQL PHP Xcode first row missing. IDMah iPhone/iPad Programming 4 Nov 21, 2013 01:24 PM
PHP results from mysql array Stocktrader Web Design and Development 2 Jun 24, 2013 01:04 AM
PHP, MySQL multiple dropdowns Stocktrader Web Design and Development 1 Feb 4, 2013 05:42 PM

Forum Jump

All times are GMT -5. The time now is 01:24 PM.

Mac Rumors | Mac | iPhone | iPhone Game Reviews | iPhone Apps

Mobile Version | Fixed | Fluid | Fluid HD
Copyright 2002-2013, MacRumors.com, LLC