Database driven Select Form Field

Greenman

Hi,

I am trying to use the following code to try and display some elements of Mysql DB in a table.

but unfortunatly my dropdown doesn't work??

Any ideas folks?

======selectform.html==============
<HTML>
<BODY>

<? echo "$option_block"; ?>

</SELECT>

</FORM>

</BODY>
</HTML>

========selectform.php=============

<html>

<?php
$db = mysql_connect("localhost", "user", "passwd")
or die ("cant connect to the DB");

print ("You have connected to database successfully");

?><html><br><br><?php

mysql_select_db("test",$db);

// create SQL statement
$query = "SELECT first FROM employees ORDER BY first";

// execute SQL query and get result
$result = mysql_query($query,$db)
or die("Couldn't execute query.");

// put data into drop-down list box
while ($row = mysql_fetch_array($result)) {

$firstname  = $row["first"];
$option_block .= "<OPTION value=\"$firstname\">$firstname</OPTION>";

}

</HTML>

khaldryck

<form action="somepage.php">
means that somepage.php will be called by the form on submit.
that's the page that will treat the data sent by the form,
not the page that will be used by the form the way you want.

what you need is to includeyour code inside your form.

this way it should work :

<HTML> 
<BODY> 

<form action="selectform.php" method="post"> 

<P>NAME:<br> 
<SELECT name="first"> 

<?php 
$db = mysql_connect("localhost", "user", "passwd") 
or die ("cant connect to the DB"); 

mysql_select_db("test",$db); 

// create SQL statement 
$query = "SELECT first FROM employees ORDER BY first"; 

// execute SQL query and get result 
$result = mysql_query($query,$db) 
or die("Couldn't execute query."); 

// put data into drop-down list box 
while ($row = mysql_fetch_array($result)) { 

$firstname = $row["first"]; 
echo "<OPTION value=\"$firstname\">$firstname</OPTION>"; 
} 

?> 

</SELECT> 

<P><INPUT type="submit" value="submit"></p> 


</FORM> 

</BODY> 
</HTML>

you could also use an include "selectform.php" in your page, but outside the <select> else your echo sayingiy could connect to the db won't be used.

Greenman

Thanks khaldryck!!!!!!!

Do you know if it is possible to use

echo "<OPTION value=\"$firstname\">$firstname</OPTION>";

AS ABOVE as opposed to using

To parse a variable to the following script.

I would like to be able to use Option select instead?

Do you know if this possible?

Regards

=========
//post name="first"

<HTML>
<?php
$first = $_REQUEST["first"];
echo "Search for <b>$first</b><p>\n\n";

include ("/var/www/html/test1/include1.php");

/ define offset value - set default if empty /
if (empty($REQUEST['offset']))
$offset = 0;
else
$offset = (int)$REQUEST['offset'];

/ number of rows to be displayed /
$num_results = 3;

$query = "SELECT * FROM employees WHERE first = '".$first."' LIMIT $offset, $num_results"; // I don't think you need to escape again - GET/POST data is usually escaped by default

$result = mysql_query($query);
$numrows = mysql_num_rows($result);

//echo "There are $numrows user(s) in the database, they are";

echo "<html><br><br>";
echo "<table border=1>";
echo "<tr><td>Name</td><td>e-mail</td><td>Department</td><td>Photo</td></tr>";

while ($myrow = mysql_fetch_row($result)) {
printf("<tr><td>%s %s</td><td>%s</td><td>%s</td> <td>%s</td>\n",
$myrow[1], $myrow[2],$myrow[3], $myrow[4], $myrow[5]);
}

echo "</table>";

echo "<a href=\"http://localhost/test1/findusers4.php?offset=".($offset+$num_results)."&first=".htmlspecialchars($first)."\">NEXT</a>";
echo "<br>";
echo "<a href=\"http://localhost/test1/findusers4.php?offset=".($offset-$num_results)."&first=".htmlspecialchars($first)."\">BACK</a>";

?>
</html>

khaldryck

it is exactly what i did in my exemple.
i use <select >
then <option>.

Greenman

Hi khaldryck

I am having trouble using the Select dropdown box.
I would like replace the following code
With a select option dropdown box to submit that I can use to submit a query like below.

Sorry, I am being a bit blond I know!!!

G

<html>
<form action="findusers6.php" method="post">
Name: <br>
<input type="text" name="first">
<br>
<input type="submit" value="FIND!"
</form>
<?php
include ("/var/www/html/test1/include1.php");

?><html><br><br><?php

$query = "SELECT * FROM employees WHERE first = '".mysql_escape_string
($first)."'";
$result = mysql_query($query);
$numrows = mysql_num_rows($result);
//
{

}

?><html><br><br><?php

echo "<table border=1>\n";
echo "<tr><td>Name</td><td>e-mail</td><td>Department</td><td>Photo</td></tr>\n";

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

	printf("<tr><td>%s %s</td><td>%s</td><td>%s</td><td>

%s</td>\n",
$myrow[1], $myrow[2],$myrow[3], $myrow[4], $myrow[5]);

}
echo "</table>\n";

</html>

khaldryck

ok use this code then (you'll have to adapt it a little) :

form page :

<? include 'db_inc.php'; ?>
<form action="exec_page.php" method="post">
<select name='first'>
<?
$query="select distinct first from employees";
$result=mysql_query($query);
while($row=mysql_fetch_array($result)) { 
?>
<option value="<? echo $row["first"]; ?>">
<? echo $row["first"]; ?>
</option>
<? } ?>
</select>
<input type="submit">
</form>

this page will create your dropdown from the database.

now here is the exec_page.

<? include "db_inc.php"; 
$query="select * from employees where first='".$_POST['first']."'";
$result=mysql_query($query);
...

you know what to do when you find the result.
maybe you'll have to strip or addslashes ... i'm not sure ..

Greenman

Thanks khaldryck!!!!

That’s has worked a dream 🙂

Greenman

Hi,

This my be of help, it is my working code

========== find-a.php===========

<HTML>
<head>
<title>Search Users</title>
</head>
<BODY>
<form action="find-b.php" method="post">
<P>FIRST NAME:
<SELECT name="first">
<?php
$db = mysql_connect("localhost", "user", "password")
or die ("cant connect to the DB");

mysql_select_db("test",$db);

// create SQL statement
$query = "SELECT distinct first FROM employees ORDER BY first";

// execute SQL query and get result
$res1 = mysql_query($query,$db)
or die("Couldn't execute query.");

// put data into drop-down list box
while ($row = mysql_fetch_array($res1)) {
$firstname = $row["first"];
echo "<OPTION value=\"$firstname\">$firstname</OPTION>";
}
?>
</SELECT>

<P>LAST NAME:
<SELECT name="last">
<?php

// create SQL statement
$query = "SELECT distinct last FROM employees ORDER BY last";

// execute SQL query and get result
$res2 = mysql_query($query,$db)
or die("Couldn't execute query.");

// put data into drop-down list box

while ($row = mysql_fetch_array($res2)) { 
	$lastname = $row["last"]; 
	echo "<OPTION value=\"$lastname\">$lastname</OPTION>"; 
}

?>
</SELECT>
<P>DEPARTMENT:
<SELECT name="dept">
<?php

// create SQL statement
$query = "SELECT distinct dept FROM employees";

// execute SQL query and get result
$res3 = mysql_query($query,$db)
or die("Couldn't execute query.");

// put data into drop-down list box

while ($row = mysql_fetch_array($res3)) { 
	$dept = $row["dept"]; 
	echo "<OPTION value=\"$dept\">$dept</OPTION>"; 
}

?>
</SELECT>
<INPUT type="submit" value="Find">
</FORM>
</BODY>
</html>
==============find-b.php==========

<?php
$db = mysql_connect("localhost", "user", "password")
or die ("cant connect to the DB");

print ("You have connected to database successfully");

?><br><br><?php

echo date("D M d, Y", time());

?><br><br><?php

mysql_select_db("test",$db);

$first = $POST['first'];
$last = $POST['last'];
$dept = $_POST['dept'];

echo "Find user<b>$first $last</b><p>\n\n";

if($first && !$last)
$query = "SELECT FROM employees WHERE first = '".mysql_escape_string($first)."' ORDER BY first";
else if (!$first && $last)
$query = "SELECT FROM employees WHERE last = '".mysql_escape_string($last)."' ORDER BY first";
else if($first && $last)
$query = "SELECT FROM employees WHERE first = '".mysql_escape_string($first)."' AND last = '".mysql_escape_string($last)."' ORDER BY first";
else if ($dept)
$query = "SELECT FROM employees WHERE dept = '" . mysql_escape_string($dept) . "' ORDER BY first";
else
$query = "SELECT * FROM employees";
$result = mysql_query($query);
$numrows = mysql_num_rows($result);
echo "There are $numrows user(s) in the database, they are";

?><br><br><?php

echo "<table border=1><tr class='left'><td width='300'>\n";
echo "<tr><td>Name</td><td>e-mail</td><td>Department</td><td>Photo</td></tr>\n";

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

	printf("<tr><td>%s %s</td><td>%s</td><td>%s</td><td>

%s</td>\n",
$myrow[1], $myrow[2],$myrow[3], $myrow[4], $myrow[5]);

}
echo "</table>\n";

</html>