...

View Full Version : MYSQL query result in PHP



optimus203
03-19-2010, 12:21 AM
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?

mlseim
03-19-2010, 12:53 AM
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
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>";
}
?>

optimus203
03-19-2010, 01:00 AM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum