Register FAQ / Rules Forum Spy Search Today's Posts Mark Forums Read
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 6502
 
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 6502
 
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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 06:59 PM.

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

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