PHP, MySQL help please please please

Discussion in 'Web Design and Development' started by Stocktrader, Jun 3, 2013.

  1. macrumors member

    Joined:
    Oct 28, 2007
    #1
    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>
     
  2. macrumors 65816

    Joined:
    Apr 17, 2012
    Location:
    Destin, FL
    #2
    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();
     
  3. thread starter macrumors member

    Joined:
    Oct 28, 2007
    #3

    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
     
  4. 960design, Jun 4, 2013
    Last edited: Jun 4, 2013

    macrumors 65816

    Joined:
    Apr 17, 2012
    Location:
    Destin, FL
    #4
    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>
     
  5. macrumors 68030

    Darth.Titan

    Joined:
    Oct 31, 2007
    #5
    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?
     
  6. Stocktrader, Jun 4, 2013
    Last edited: Jun 4, 2013

    thread starter macrumors member

    Joined:
    Oct 28, 2007
    #6
    See next post
     
  7. thread starter macrumors member

    Joined:
    Oct 28, 2007
    #7

    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>
     
  8. macrumors 68030

    Darth.Titan

    Joined:
    Oct 31, 2007
    #8
    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.
     

Share This Page