Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder optimus203's Avatar
    Join Date
    Sep 2008
    Location
    CT
    Posts
    317
    Thanks
    22
    Thanked 16 Times in 15 Posts

    MYSQL query result in PHP

    Hey everyone. Still learning PHP and have a ways to go. Was hoping for some help.

    I've got a database in mySql, and trying to get results printed in PHP from a drop-down menu selection.

    Here is my html code for the form:

    Code:
    <form method="post" action="results.php">
          <select name="state">
          <option value="select">--Choose A State--</option>
          <option value="al">Alabama</option>
          <option value="ak">Alaska</option>
          <!-- etc... -->
          </select>
          <input type="submit" value="Display Results" />
    </form>
    Here is the basic layout of results.php so far:

    Code:
    <?php
    ini_set('display_errors',1);
    
    // Connect to server
    $connect = mysql_connect("url","user","pass");
    // Connect to database
    mysql_select_db("dbname",$connect);
    
    // Kill command
    if (!$connect)
      {
      die('Could not connect to database.');
      };
    
    // Aliases for form selection
    $state = $_POST["state"];
    
    // Mysql query
    $alAll = "SELECT `y`.`year` AS `Year`, `i`.`industry` AS `Industry`, `e`.`emissions` AS `MMTCO2 Emissions`
              FROM `emissions` AS `e`
    		  INNER JOIN `year` AS `y`  ON  `y`.`y_id` = `e`.`y_id`
    		  INNER JOIN `industry` AS `i`  ON  `i`.`i_id` = `e`.`i_id`
    		  WHERE `e`.`s_id` = 1
    		  ORDER BY `y`.`year` ASC, `i`.`industry` ASC";
    $alResult = mysql_query($alAll, $connect);
    $alRow = mysql_fetch_array($alResult);
      
    if ($state = "al") {
            echo "<table>";
            echo "<tr>";
              echo "<td>";
              echo $alRow["year"];
              echo "</td>";
              echo "<td>";
              echo $alRow["industry"];
              echo "</td>";
              echo "<td>";
              echo $alRow["emissions"];
              echo "</td>";
            echo "</tr>";
            echo "</table>";
          }
    ?>
    I've tried doing this in multiple ways, but can't figure out how to print out mySql query based on state selection in PHP. MySql code works fine, as I have tested on MAMP. Any ideas?
    Always thank those CF Users who help you solve issues...
    Connecticut Web Design

  • #2
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,538
    Thanks
    8
    Thanked 1,093 Times in 1,084 Posts
    No way to test my ideas on this ... so I'm just winging it.
    We don't know what your "state" variable is called in your database.
    So, fix my example below (save your original in a safe place).

    PHP Code:
    <?php
    ini_set
    ('display_errors',1);

    // Connect to server
    $connect mysql_connect("url","user","pass");
    // Connect to database
    mysql_select_db("dbname",$connect);

    // Kill command
    if (!$connect)
      {
      die(
    'Could not connect to database.');
      };

    // Aliases for form selection
    $state $_POST['state'];
    // Sanitize any outside variables used in a query.
    $state mysql_real_escape_string($state);

    // We don't know what you are calling "state" in your database (variable name).

    // Mysql query
    $alAll "SELECT `y`.`year` AS `Year`, `i`.`industry` AS `Industry`, `e`.`emissions` AS `MMTCO2 Emissions`,`state`
              FROM `emissions` AS `e`
              INNER JOIN `year` AS `y`  ON  `y`.`y_id` = `e`.`y_id`
              INNER JOIN `industry` AS `i`  ON  `i`.`i_id` = `e`.`i_id`
              WHERE `e`.`s_id` = 1 AND `state` = `$state`
              ORDER BY `y`.`year` ASC, `i`.`industry` ASC"
    ;
    $alResult mysql_query($alAll$connect);
    $alRow mysql_fetch_array($alResult);
      
    if (
    $state "al") {
            echo 
    "<table>";
            echo 
    "<tr>";
              echo 
    "<td>";
              echo 
    $alRow["year"];
              echo 
    "</td>";
              echo 
    "<td>";
              echo 
    $alRow["industry"];
              echo 
    "</td>";
              echo 
    "<td>";
              echo 
    $alRow["emissions"];
              echo 
    "</td>";
            echo 
    "</tr>";
            echo 
    "</table>";
          }
    ?>

  • #3
    Regular Coder optimus203's Avatar
    Join Date
    Sep 2008
    Location
    CT
    Posts
    317
    Thanks
    22
    Thanked 16 Times in 15 Posts
    The state variable in my database is not part of the query. From the HTML drop down combo field, different mySQL queries will run depending on state selected.

    From my understanding (which may be completely wrong), the:
    $state = $_POST["state"]; is calling the selection from the drop down combo box

    $alAll is the SQL query that runs if the "al" option is selected from drop down combo box

    if ($state = "al") will run the $alRow mysql query is the "al" option is selected from drop down combo box

    There would be different if statements for each state to be selected. Does that make sense? Sorry, I know PHP terminology is still elementary at best.
    Always thank those CF Users who help you solve issues...
    Connecticut Web Design


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •