Retrive Data with drop down menu

jrb22250

I am very new to PHP. Just started using MySQL, but have been useing SQL for DataBases for a while. (I am also a horriablee Speler) . I also just ordered my PHP and My SQL books, 3 of them to learn.

What I am after is to make a page that I can select data from a DB that I choose the info from a Drop Down. Then submit the data and have all the info come out for an INFO page. I have two (2) items I want to have fro the Drop down. 1. Being Site the other 2. Being EPN.

So far I have read a ton of threads, and tried a bunch of code. But no luck. This is what I have started.

INDEX.PHP FILE

<?php

// open the connection
$conn = mysql_connect("localhost", "username", "password");
// pick the database to use
mysql_select_db("test",$conn);
// create the SQL statement
$sql = "SELECT * FROM Dresden";
// execute the SQL statement
$result = mysql_query($sql, $conn) or die(mysql_error());
//go through each row in the result set and display data

$options="";

while ($row=mysql_fetch_array($result))
{

$title=$row["EPN"];
$options.="<option VALUE=\"$id\">".$title.'</option>';
}



?>
<body>

<form action="insert2.php" method="get">
<SELECT NAME=id>
<OPTION VALUE=0>Please choose EPN
<?=$options?>
</SELECT>
<br />
<br />

<input type="Submit">
</form>

</body>

------------------------------------------------------------------------------------------------------------------
SELECT.PHP FILE

<?
$dbhost = 'localhost';
$username="username";
$password="password";
$database="exelon";

$EPN=$_POST['EPN'];
$Component=$_POST['Component'];
$# Of Tubes=$_POST['# Of Tubes'];

// open the connection
$conn = mysql_connect("ironbelly.com", "jason", "1qazxsw2");
// pick the database to use
mysql_select_db("exelon");

$result = mysql_query("select * from Dresden");
mysql_query($query);
        while ($row = mysql_fetch_array($result))
	echo "Site: $row[Site]  <BR>         EPN: $row[EPN]<br>";
        echo $options;
?>

What am I missing here? I would like the output to be in a table, but that will be later. I want the output to have only the records I pull from the DB to show at the end.

Thanks for any help

Jason

rincewind456

This should work but only if your select statement only returns the 2 values you need, if it doesn't I would suggest you change it so it does, or come back with more info on the data table.

<?php 

// open the connection 
$conn = mysql_connect("localhost", "username", "password"); 
// pick the database to use 
mysql_select_db("test",$conn); 
// create the SQL statement 
$sql = "SELECT * FROM Dresden"; 
// execute the SQL statement 
$result = mysql_query($sql, $conn) or die(mysql_error()); 
//go through each row in the result set and display data 

$options=""; 

while ($row=mysql_fetch_array($result)) 
{ 
    foreach ($row as $key=>$value) 

$options .='<option VALUE="'.$key.'">"'.$value.'</option>'; 
} 




?> 
<body> 

<form action="insert2.php" method="get"> 
<SELECT NAME=id> 
<OPTION VALUE=0>Please choose EPN 
<?=$options?> 
</SELECT> 
<br /> 
<br /> 

<input type="Submit"> 
</form> 

</body>

jrb22250

Your code gives me all the info from the Tabel in the drop down.
I canged line 8 to 'Select EPN From Dresden'

Other Fields in DataBase

   ID 	    	    	    	    	    	
Site 						
Unit 					
EPN 							
Saftey Related 								
Component 							
Tube Size 								
Tube Material 								
Tube Material Specification 							
Tube Length 							
Tube Type 							
# of Tubes 							
# Of Openings 								
ASME Std S/N 							
Wear Scar Std S/N 							
Pitting Std S/N 							
Thinning Std S/N 								
Reference Std S/N 							
Exam Frequencies 							
Primary Test Probe 								
Primary Fill 						
Down Sized Probe 							
DS Fill 							
Test End

I would like two drop downs one for Site and One for EPN. Then I want the select to only show the info that meets that querie. My error is in this insert2.php.

<?
$dbhost = '************';
$username="********";
$password="*********";
$database="**********";

$EPN=$_POST['EPN'];
$Component=$_POST['Component'];
$# Of Tubes=$_POST['# Of Tubes'];

// open the connection
$conn = mysql_connect("ironbelly.com", "jason", "1qazxsw2");
// pick the database to use
mysql_select_db("exelon");

$result = mysql_query("select * from Dresden");
mysql_query($query);
        while ($row = mysql_fetch_array($result))
	echo "Site: $row[Site]  <BR>         EPN: $row[EPN]<br>  Component: $row[Component]<br>  Comments:$row[Comments]<br><br>";

?>

Thanks Again

rincewind456

If you are going to SELECT *FROM then change the while loop to

while ($row=mysql_fetch_array($result)) 
{ 


$options .='<option VALUE="'.$row['site'].'">"'.$row['epn'].'</option>'; 
}

or use the previous code and change the query to

$sql ="SELECT site, epn FROM Dresden";

I also noticed when I made my DB I made differnt tables for each site, can I do a SQL like this in MySQL ( Select From 'Dresden','Byron'

Not sure what you mean here, for your original query to work Dresden needs to be a table name, so Select From 'Dresden','Byron' won't work because you cant select from 2 tables without using a JOIN of some sort and for that you need some point of reference that's the same on both tables.
You can run more than one query in a page and process them to build your page.

jrb22250

The two databases is fine. I can conbine all to one easy enough. My problem lies here now in the insert2.php Why am I gettting all the data not just the EPN I select and not just the EPN I selected on the index.php page.

jrb22250

Link to page, maybe this will help evryone understand
http://ironbelly.com/test2/index6.php

I need to change the top to the Site name
and I want the other select.php page to only show the results I select from drop down from before.

index6.php

<?php

// open the connection
$conn = mysql_connect("ironbelly.com", '*********", "**********");
// pick the database to use
mysql_select_db("exelon",$conn);
// create the SQL statement
$sql = "SELECT EPN FROM Dresden";
// execute the SQL statement
$result = mysql_query($sql, $conn) or die(mysql_error());
//go through each row in the result set and display data

$options="";

while ($row=mysql_fetch_array($result))
{
    foreach ($row as $key=>$value)
    $options .='<option VALUE="'.$key.'">"'.$value.'</option>';

}




?>
<body>


<br />
<br />
<form action="select.php" method="get">
<SELECT NAME=Site>
<OPTION VALUE=0>Please choose Site
<?=$options?>
</SELECT>
<br> <br>
<form action="select.php" method="get">
<SELECT NAME=id>
<OPTION VALUE=0>Please choose EPN
<?=$options?>
</SELECT>
<br />
<br />


<input type="Submit">
</form>

</body>

-------------------
select.php

<?
$dbhost = 'linux.ironbelly.com';
$username="****";
$password="************";
$database="exelon";

$EPN=$_POST['EPN'];
$Component=$_POST['Component'];


// open the connection
$conn = mysql_connect("ironbelly.com", "********", "***********");
// pick the database to use
mysql_select_db("exelon");

$result = mysql_query("select * from Dresden");
mysql_query($query);
        while ($row = mysql_fetch_array($result))
	echo "Site: $row[Site]  <BR>         EPN: $row[EPN]<br>  Component: $row[Component]<br> 
	Comments:$row[Comments]<br> Tube_Size:$row[Tube_Size]<br>Tube_Material:$row[Tube_Material]<br>Tube_Material_Specifications:$row[Tube_Material_Specifications]<br>
	Tube_Length:$row[Tube_Length]<br> Tube_Type:$row[Tube_Type]<br>
	Num_of_Openings:$row[Num_of_Openings]<br>Num_of_Tubes:$row[Num_of_Tubes]<br>ASME_Std_SN:$row[ASME_Std_SN]<br>Wear_Scar_Std_SN:$row[Wear_Scar_Std_SN]
	<br>Pitting__Std_SN_SN:$row[Pitting__Std_SN]<br>Thinning_Std_SN:$row[Thinning_Std_SN]<br>Reference_Std_SN:$row[Reference_Std_SN]
        <br>Exam_Frequencies:$row[Exam_Frequencies]<br>Primary_Test_Probe:$row[Primary_Test_Probe]<br>Primary_Fill:$row[Primary_Fill]<br> <br>";
?>

rincewind456

You'll have to elaborate I don't know what your refering to.

rincewind456

What data are you expecting in each drop down, it might have helped if you said you wanted two in the first place.

jrb22250

I want the Site in the top drop down, and the EPN in the second

rincewind456

try this for you selection page

<?php 
// open the connection
$conn = mysql_connect("ironbelly.com", "*********", "**********");
// pick the database to use
mysql_select_db("exelon",$conn);
// create the SQL statement
$sql = "SELECT EPN FROM Dresden";
// execute the SQL statement
$result = mysql_query($sql, $conn) or die(mysql_error());
//go through each row in the result set and display data

$options="";

while ($row=mysql_fetch_row($result))
{

$options .='<option VALUE="'.$row[1].'">"'.$row[1].'</option>'."\n";

}
?> 
<body> 
<br /><br /> 
<form action="select.php" method="post"> 
<SELECT NAME=Site> 
<OPTION VALUE=0>Please choose Site 
<?=$options?> </SELECT> 
<br><br> 
<?php 
$sql = "SELECT Site FROM Dresden";
// execute the SQL statement
$result = mysql_query($sql, $conn) or die(mysql_error());
//go through each row in the result set and display data

$options="";

while ($row=mysql_fetch_row($result))
{

$options .='<option VALUE="'.$row[1].'">"'.$row[1].'</option>';

}
?>
<form action="select.php" method="post"> 
<SELECT NAME=id> 
<OPTION VALUE=0>Please choose EPN 
<?=$options?> 
</SELECT> 
<br /> 
<br /> 
<input type="Submit"> 
</form> 
</body>

and this for your display page for EPN you can alter it for a display page for Site info.

<?php
$dbhost = 'linux.ironbelly.com';
$username="jason";
$password="1qazxsw2";
$database="exelon";

function quote_smart($value)
{
	// Stripslashes
	if (get_magic_quotes_gpc()) {
		$value = stripslashes($value);
	}
	// Quote if not integer
	if (!is_numeric($value)) {
		$value = "'" . mysql_real_escape_string($value) . "'";
	}
	return $value;
}

$EPN=$_POST['EPN'];
$Component=$_POST['Component'];


// open the connection
$conn = mysql_connect("ironbelly.com", "********", "***********");
// pick the database to use
mysql_select_db("exelon");

$result = mysql_query("select * from Dresden WHERE EPN =". quote_smart($EPN));
mysql_query($query);
while ($row = mysql_fetch_array($result))

echo "Site: $row[Site]
\n\n<br>EPN: $row[EPN]
\n<br>Component: $row[Component]
\n<br> Comments:$row[Comments]
\n<br> Tube_Size:$row[Tube_Size]
\n<br>Tube_Material:$row[Tube_Material]
\n<br>Tube_Material_Specifications:$row[Tube_Material_Specifications]
\n<br> Tube_Length:$row[Tube_Length]
\n<br> Tube_Type:$row[Tube_Type]
\n<br>Num_of_Openings:$row[Num_of_Openings]
\n<br>Num_of_Tubes:$row[Num_of_Tubes]
\n<br>ASME_Std_SN:$row[ASME_Std_SN]
\n<br>Wear_Scar_Std_SN:$row[Wear_Scar_Std_SN] 
\n<br>Pitting__Std_SN_SN:$row[Pitting__Std_SN]
\n<br>Thinning_Std_SN:$row[Thinning_Std_SN]
\n<br>Reference_Std_SN:$row[Reference_Std_SN] 
\n<br>Exam_Frequencies:$row[Exam_Frequencies]
\n<br>Primary_Test_Probe:$row[Primary_Test_Probe]
\n<br>Primary_Fill:$row[Primary_Fill]
\n<br>\n<br>"; 
?>

rincewind456

try this for you selection page

<?php 
// open the connection
$conn = mysql_connect("ironbelly.com", "*********", "**********");
// pick the database to use
mysql_select_db("exelon",$conn);
// create the SQL statement
$sql = "SELECT Site FROM Dresden";
// execute the SQL statement
$result = mysql_query($sql, $conn) or die(mysql_error());
//go through each row in the result set and display data

$options="";

while ($row=mysql_fetch_row($result))
{

$options .='<option VALUE="'.$row[1].'">"'.$row[1].'</option>'."\n";

}
?> 
<body> 
<br /><br /> 
<form action="select.php" method="post"> 
<SELECT NAME=Site> 
<OPTION VALUE=0>Please choose Site 
<?=$options?> </SELECT> 
<br><br> 
<?php 
$sql = "SELECT EPN FROM Dresden";
// execute the SQL statement
$result = mysql_query($sql, $conn) or die(mysql_error());
//go through each row in the result set and display data

$options="";

while ($row=mysql_fetch_row($result))
{

$options .='<option VALUE="'.$row[1].'">"'.$row[1].'</option>';

}
?>
<form action="select.php" method="post"> 
<SELECT NAME=id> 
<OPTION VALUE=0>Please choose EPN 
<?=$options?> 
</SELECT> 
<br /> 
<br /> 
<input type="Submit"> 
</form> 
</body>

and this for your display page for EPN you can alter it for a display page for Site info.

<?php
$dbhost = 'linux.ironbelly.com';
$username="jason";
$password="1qazxsw2";
$database="exelon";

function quote_smart($value)
{
	// Stripslashes
	if (get_magic_quotes_gpc()) {
		$value = stripslashes($value);
	}
	// Quote if not integer
	if (!is_numeric($value)) {
		$value = "'" . mysql_real_escape_string($value) . "'";
	}
	return $value;
}

$EPN=$_POST['EPN'];
$Component=$_POST['Component'];


// open the connection
$conn = mysql_connect("ironbelly.com", "********", "***********");
// pick the database to use
mysql_select_db("exelon");

$result = mysql_query("select * from Dresden WHERE EPN =". quote_smart($EPN));
mysql_query($query);
while ($row = mysql_fetch_array($result))

echo "Site: $row[Site]
\n\n<br>EPN: $row[EPN]
\n<br>Component: $row[Component]
\n<br> Comments:$row[Comments]
\n<br> Tube_Size:$row[Tube_Size]
\n<br>Tube_Material:$row[Tube_Material]
\n<br>Tube_Material_Specifications:$row[Tube_Material_Specifications]
\n<br> Tube_Length:$row[Tube_Length]
\n<br> Tube_Type:$row[Tube_Type]
\n<br>Num_of_Openings:$row[Num_of_Openings]
\n<br>Num_of_Tubes:$row[Num_of_Tubes]
\n<br>ASME_Std_SN:$row[ASME_Std_SN]
\n<br>Wear_Scar_Std_SN:$row[Wear_Scar_Std_SN] 
\n<br>Pitting__Std_SN_SN:$row[Pitting__Std_SN]
\n<br>Thinning_Std_SN:$row[Thinning_Std_SN]
\n<br>Reference_Std_SN:$row[Reference_Std_SN] 
\n<br>Exam_Frequencies:$row[Exam_Frequencies]
\n<br>Primary_Test_Probe:$row[Primary_Test_Probe]
\n<br>Primary_Fill:$row[Primary_Fill]
\n<br>\n<br>"; 
?>

jrb22250

Fatal error: Call to undefined function: mysql_real_escape_string() in index8.php on line 15

LINE 15 $value = "'" . mysql_real_escape_string($value) . "'";

Could you also remove username and password from code - I messed up and left it. Thanks For all your help so far.
Jason

rincewind456

what version of php are you using mysql_real_escape_string() was available from 4.3.0 onwards.

jrb22250

Not sure - Is there an easy way to check I know it is 4 something. Last Time I checked

jrb22250

PHP Version 4.2.2 Will upgrade in one sec

jrb22250

jrb22250 wrote:
PHP Version 4.2.2 Will upgrade in one sec

Now using PHP Version 5.0.4.

I come up with a drop down with nothing but "".

I do not unserstand the

//go through each row in the result set and display data
$options="";

What do i need in here?

Index.php

<?php
// open the connection
$conn = mysql_connect("localhost", "**********", ",**********");
// pick the database to use
mysql_select_db("exelon",$conn);
// create the SQL statement
$sql = "SELECT Site FROM Dresden";
// execute the SQL statement
$result = mysql_query($sql, $conn) or die(mysql_error());
//go through each row in the result set and display data

$options="";

while ($row=mysql_fetch_row($result))
{

$options .='<option VALUE="'.$row[1].'">"'.$row[1].'</option>'."\n";

}
?>
<body>
<br /><br />
<form action="select.php" method="post">
<SELECT NAME=Site>
<OPTION VALUE=0>Please choose Site
<?=$options?> </SELECT>
<br><br>
<?php
$sql = "SELECT EPN FROM Dresden";
// execute the SQL statement
$result = mysql_query($sql, $conn) or die(mysql_error());
//go through each row in the result set and display data

$options="";

while ($row=mysql_fetch_row($result))
{

$options .='<option VALUE="'.$row[1].'">"'.$row[1].'</option>';

}
?>
<form action="select.php" method="post">
<SELECT NAME=id>
<OPTION VALUE=0>Please choose EPN
<?=$options?>
</SELECT>
<br />
<br />
<input type="Submit">
</form>
</body>

Select.php

<?php
$dbhost = 'localhost';
$username="*********";
$password="************";
$database="exelon";

function quote_smart($value)
{
    // Stripslashes
    if (get_magic_quotes_gpc()) {
        $value = stripslashes($value);
    }
    // Quote if not integer
    if (!is_numeric($value)) {
        $value = "'" . mysql_real_escape_string($value) . "'";
    }
    return $value;
}

$EPN=$_POST['EPN'];
$Component=$_POST['Component'];


// open the connection
$conn = mysql_connect("localhost", "************", ",************");
// pick the database to use
mysql_select_db("exelon");

$result = mysql_query("select * from Dresden WHERE EPN =". quote_smart($EPN));
mysql_query($query);
while ($row = mysql_fetch_array($result))

echo "Site: $row[Site]
\n\n<br>EPN: $row[EPN]
\n<br>Component: $row[Component]
\n<br> Comments:$row[Comments]
\n<br> Tube_Size:$row[Tube_Size]
\n<br>Tube_Material:$row[Tube_Material]
\n<br>Tube_Material_Specifications:$row[Tube_Material_Specifications]
\n<br> Tube_Length:$row[Tube_Length]
\n<br> Tube_Type:$row[Tube_Type]
\n<br>Num_of_Openings:$row[Num_of_Openings]
\n<br>Num_of_Tubes:$row[Num_of_Tubes]
\n<br>ASME_Std_SN:$row[ASME_Std_SN]
\n<br>Wear_Scar_Std_SN:$row[Wear_Scar_Std_SN]
\n<br>Pitting__Std_SN_SN:$row[Pitting__Std_SN]
\n<br>Thinning_Std_SN:$row[Thinning_Std_SN]
\n<br>Reference_Std_SN:$row[Reference_Std_SN]
\n<br>Exam_Frequencies:$row[Exam_Frequencies]
\n<br>Primary_Test_Probe:$row[Primary_Test_Probe]
\n<br>Primary_Fill:$row[Primary_Fill]
\n<br>\n<br>";
?>

Thanks Jason