March 18, 201016 yr 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: <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: <?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?
March 18, 201016 yr 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: <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: <?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? CHange this line WHERE `e`.`s_id` = 1 to something like this WHERE state = '".$state."'
March 18, 201016 yr Author You think the problem is in the $alAll mySql query portion? It worked fine using phpMyAdmin.
March 18, 201016 yr You think the problem is in the $alAll mySql query portion? It worked fine using phpMyAdmin. well even if it did work fine it would only select the state that has a s_id of 1 but u want it to select what state u pick well to do that u would have to use WHERE fieldname = '".$state."'
March 19, 201016 yr Author Yes, I see your point. So your saying I wouldn't need 51 if statements (1 for each state)? Getting this error code: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in 'URL path' on line 60 Line 60 code is: $alRow = mysql_fetch_array($alResult);
March 19, 201016 yr Author This query runs fine in phpMyAdmin. This query 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; Produces this result (only first 20 results listed) Year Industry MMTCO2 Emissions 1990 All 109.94 1990 Commercial 2.34 1990 Electric 50.28 1990 Industry 26.04 1990 Residential 3.16 1990 Transportation 28.11 1991 All 113.14 1991 Commercial 1.92 1991 Electric 54.17 1991 Industry 25.30 1991 Residential 3.08 1991 Transportation 28.67 1992 All 119.91 1992 Commercial 2.02 1992 Electric 56.93 1992 Industry 28.37 1992 Residential 3.26 1992 Transportation 29.32 1993 All 124.71 1993 Commercial 1.94 1993 Electric 62.82 1993 Industry 26.96 1993 Residential 3.50 1993 Transportation 29.50 1994 All 122.78 1994 Commercial 1.95 1994 Electric 58.81 1994 Industry 27.92 1994 Residential 3.38 1994 Transportation 30.72 Here is the database schema. First string represents table name. (only first couple listed for space) state s_id state 1 Alabama 2 Alaska (etc... 51 total rows) year y_id year 1 1990 2 1991 (etc... 18 total rows) industry i_id industry 1 Commercial 2 Industry 3 Residential 4 Transportation 5 Electric 6 All emissions e_id s_id y_id i_id emissions 1 1 1 1 2.34 2 1 2 1 1.92 3 1 3 1 2.02 4 1 4 1 1.94 5 1 5 1 1.95 6 1 6 1 1.85 7 1 7 1 2.05 8 1 8 1 2.30 9 1 9 1 1.81 10 1 10 1 2.01 (etc... 5508 total rows) Here is the revised HTML code for the form <form method="post" action="results-state-all.php"> <select name="state"> <option value="select">--Choose A State--</option> <option value="1">Alabama</option> <option value="2">Alaska</option> <!-- etc... --> </select> <input type="submit" value="Display Results" /> </form> Here is my revised PHP so far: <?php require_once('mysqli_connect.php'); ?> // Alias for form selection $state = $_POST["state"]; // Mysql query $query = "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` = '".$state."' ORDER BY `y`.`year` ASC, `i`.`industry` ASC"; $result = mysqli_query($connect, $query); // Query result while ($row = mysqli_fetch_array($result)) { echo '"<table><tr><td>" .$row["year"]. "</td> <td>" .$row["industry"]. "</td> <td>" .$row["emissions"]. "</td> </tr> </table>"'; } ?> Still getting this error on the mysqli_fetch_array statement: Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in ... on line 38
March 19, 201016 yr Author It looks like the 2nd code section posted above, where it says: produces this result (only first 20 results listed) Is that what you meant? That is the query result I get from phpMyAdmin
Create an account or sign in to comment