PHP Form - populate dropdown from tables [Archive]

View Full Version : PHP Form - populate dropdown from tables

sonic10

02-18-2005, 04:10 PM

I am trying to build an email newsletter system for a friend and need some help on how to populate a dropdown menu from table fields.

This is the table structure:

CREATE TABLE `contacts` (
`contact_id` int(11) NOT NULL auto_increment,
`first_name` varchar(30) NOT NULL default '',
`last_name` varchar(30) NOT NULL default '',
`street_addy` varchar(50) NOT NULL default '',
`city` varchar(40) NOT NULL default '',
`state` char(2) NOT NULL default 'MD',
`postal_code` int(5) NOT NULL default '0',
`county` varchar(30) default NULL,
`district` varchar(20) default NULL,
`precinct` varchar(20) default NULL,
`phone` varchar(15) NOT NULL default '',
`cell` varchar(20) default NULL,
`work_phone` varchar(20) default NULL,
`email` varchar(40) NOT NULL default '',
`email2` varchar(40) default NULL,
`category` int(11) default NULL,
`church` varchar(50) default NULL,
`church_addy` varchar(60) default NULL,
`church_phone` int(11) default NULL,
`church_cnty` varchar(25) default NULL,
`church_email` char(1) default NULL,
`pastor` char(1) default NULL,
`pastors_email` varchar(40) default NULL,
PRIMARY KEY (`contact_id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

CREATE TABLE `groups` (
`group_id` int(11) NOT NULL auto_increment,
`group_name` varchar(35) default NULL,
PRIMARY KEY (`group_id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;

#
# Dumping data for table `groups`
#

INSERT INTO `groups` VALUES (1, 'Pastor');
INSERT INTO `groups` VALUES (2, 'Advisor');
INSERT INTO `groups` VALUES (3, 'Community Leader');
INSERT INTO `groups` VALUES (4, 'Youth Mentor');

This is what I have which works fine to send an email to everyone but she wants to be able to select which groups she emails from a pulldown menu like so.
<option>Send to All
<option>Pastor
<option>Advisor

How can I add a dropdown in the form with the selections from the groups table?

<?php
if ($_POST[op] != "send") {
//the form show it
print "
<HTML>
<HEAD>
<TITLE>Send a Newsletter</TITLE>
</HEAD>
<BODY>
<h1>Send a Newsletter</h1>
<form method=\"post\" action=\"$_SERVER[PHP_SELF]\">
<P><strong>Subject:</strong><br>
<input type=\"text\" name=\"subject\" size=30></p>
<P><strong>Mail Body:</strong><br>
<textarea name=\"message\" cols=50 rows=10 wrap=virtual></textarea>
<input type=\"hidden\" name=\"op\" value=\"send\">
<p><input type=\"submit\" name=\"submit\" value=\"Send It\"></p>
</FORM>
</BODY>
</HTML>";

} else if ($_POST[op] == "send") {
//want to send form, so check for required fields
if (($_POST[subject] =="") || ($_POST[message] == "")) {
header("Location: sendletter.php");
exit;
}

//connect to database
$conn = mysql_connect("localhost", "user", "passwd") or die(mysql_error());
mysql_select_db("estore",$conn) or die(mysql_error());

//get emails from db list
$sql = "select email from contacts";
$result = mysql_query($sql,$conn) or die(mysql_error());

//create a From: mailheader
$headers = "From: you <myemail@myisp.net>\n";

//loop through results and send mail
while ($row = mysql_fetch_array($result)) {
set_time_limit(0);
$email = $row['email'];
mail("$email", stripslashes($_POST[subject]), stripslashes($_POST[message]), $headers);
print "newsletter sent to: $email<br>";
}
}
?>

Any help is greatly appreciated

stormraven

02-18-2005, 06:33 PM

Okay, there's a couple of things you'll need to do. First, I'm assuming a contact can be a member of more than one group.

That being the case, you'll need to create yet another table, call it "contact-group".

It will basically contain three fields - an auto-increment key, a group_id, and a contact_id. To add a contact to a group, insert the proper data into a row in this table.

Next, you'll have to populate a drop-down list somewhere in your form.. (note: you'll have to move your db connection code up near the top of the form)

<select name='contact_group'>
<?php
$sql_text = "select * from groups";
$results = mysql_query($sql_text);
while ($row = mysql_fetch_array($results)
{
echo ("<option value='" . $row['group_id'] . "'>" . $row['group_name'] . "</option">;
}
?>
</select>

Now, you'll have to change your selection code of which contacts to get, from:

$sql = "select email from contacts";

to:

$group = htmlentities($_REQUEST['contact_group']);
$sql = "select email from contacts INNER JOIN contact-group ON contacts.contact_id = contact-group.contact_id WHERE contact-group.group_id = $group";

It may not be exactly right, but it should get you started.

patrick24601

02-18-2005, 08:15 PM

ALso: After the initial option line before the query loop you might want to add a default options of "Select Group Name" so they initially see that when the form is displayed.

sonic10

02-18-2005, 08:27 PM

Thanks,

Not sure how to place this within the form I get paerse error:

<select name='contact_group'>
<?
$sql_text = "select * from groups";
$results = mysql_query($sql_text);
while ($row = mysql_fetch_array($results)
{
echo ("<option value='" . $row['group_id'] . "'>" . $row['group_name'] . "</option">;
}
</select>

<form method=\"post\" action=\"$_SERVER[PHP_SELF]\">
<P><strong>Subject:</strong><br>
<input type=\"text\" name=\"subject\" size=30></p>
<P><strong>Mail Body:</strong><br>
<textarea name=\"message\" cols=50 rows=10 wrap=virtual></textarea>
<input type=\"hidden\" name=\"op\" value=\"send\">
<select name='contact_group'>
$sql_text = "select * from groups";
$results = mysql_query($sql_text);
while ($row = mysql_fetch_array($results)
{
echo ("<option value='" . $row['group_id'] . "'>" . $row['group_name'] . "</option">;
}
?>
</select>
<p><input type=\"submit\" name=\"submit\" value=\"Send It\"></p>
</FORM>

Can you show format of what the form should be.
Thanks much

patrick24601

02-18-2005, 08:34 PM

You are intermingly PHP and HTML. THat is OK. But you need to be clear where each starts and ends:

<form method=\"post\" action=\"$_SERVER[PHP_SELF]\">
<P><strong>Subject:</strong><br>
<input type=\"text\" name=\"subject\" size=30></p>
<P><strong>Mail Body:</strong><br>
<textarea name=\"message\" cols=50 rows=10 wrap=virtual></textarea>
<input type=\"hidden\" name=\"op\" value=\"send\">
<select name='contact_group'>
<? <<<<---------------------------------------------
$sql_text = "select * from groups";
$results = mysql_query($sql_text);
while ($row = mysql_fetch_array($results)
{
echo ("<option value='" . $row['group_id'] . "'>" . $row['group_name'] . "</option">;
}
?>
</select>
<p><input type=\"submit\" name=\"submit\" value=\"Send It\"></p>
</FORM>

That might help.

sonic10

02-18-2005, 09:17 PM

"You are intermingly PHP and HTML. THat is OK. But you need to be clear where each starts and ends"
Yes this is where I need help..still getting parse error line 15. Thanks for your assistance thus far.

<?
if ($_POST[op] != "send") {
//haven't seen the form, so show it
print "
<HTML>
<HEAD>
<TITLE>Send a Newsletter</TITLE>
</HEAD>
<BODY>
<h1>Send a Newsletter</h1>
<form method=\"post\" action=\"$_SERVER[PHP_SELF]\">
<P><strong>Group:</strong><br>
<select name='contact_group'>
<?
$sql_text = "select * from groups";
$results = mysql_query($sql_text);
while ($row = mysql_fetch_array($results)
{
echo ("<option value='" . $row['group_id'] . "'>" . $row['group_name'] . "</option">;
}
?>
</select>
<P><strong>Subject:</strong><br>
<input type=\"text\" name=\"subject\" size=30></p>
<P><strong>Mail Body:</strong><br>
<textarea name=\"message\" cols=50 rows=10 wrap=virtual></textarea>
<input type=\"hidden\" name=\"op\" value=\"send\">
<p><input type=\"submit\" name=\"submit\" value=\"Send It\"></p>
</FORM>
</BODY>
</HTML>";

} else if ($_POST[op] == "send") {
//want to send form, so check for required fields
if (($_POST[subject] =="") || ($_POST[message] == "")) {
header("Location: newsletter.php");
exit;
}

//connect to database
$conn = mysql_connect("localhost", "user", "passwd") or die(mysql_error());
mysql_select_db("este",$conn) or die(mysql_error());

//get emails from db list
$sql = "select email from contacts";
$result = mysql_query($sql,$conn) or die(mysql_error());

//create a From: mailheader
$headers = "From: DDM <myeamil@sdddd.net>\n";

//loop through results and send mail
while ($row = mysql_fetch_array($result)) {
set_time_limit(0);
$email = $row['email'];
mail("$email", stripslashes($_POST[subject]), stripslashes($_POST[message]), $headers);
print "newsletter sent to: $email<br>";
}
}
?>

stormraven

02-19-2005, 12:23 AM

Try this one out for size.. it should suit your needs.

<?php

if ($_POST['submit'] == '')
{
//haven't seen the form, so show it
?>
<HTML>
<HEAD>
<TITLE>Send a Newsletter</TITLE>
</HEAD>
<BODY>
<h1>Send a Newsletter</h1>
<form method='POST' action='<?php echo $_SERVER[PHP_SELF]; ?>'>
<P><strong>Group:</strong><br>
<select name='contact_group'>
<option value=''>(All)</option>
<?php
//connect to database
$conn = mysql_connect("localhost", "user", "passwd") or die(mysql_error());
mysql_select_db("este",$conn) or die(mysql_error());
$sql_text = "select * from groups";
$results = mysql_query($sql_text);
while ($row = mysql_fetch_array($results))
{
echo ("<option value='" . $row['group_id'] . "'>" . $row['group_name'] . "</option>");
}
?>
</select>
<P><strong>Subject:</strong><br>
<input type=\"text\" name=\"subject\" size=30></p>
<P><strong>Mail Body:</strong><br>
<textarea name=\"message\" cols=50 rows=10 wrap=virtual></textarea>
<input type=\"hidden\" name=\"op\" value=\"send\">
<p><input type=\"submit\" name=\"submit\" value=\"Send It\"></p>
</FORM>
</BODY>
</HTML>

<?php
}
else if ($_POST[op] == "send")
{
//want to send form, so check for required fields
if (($_POST[subject] =="") || ($_POST[message] == ""))
{
header("Location: newsletter.php");
exit;
}

//get emails from db list
$group = htmlentities($_REQUEST['contact_group']);
$sql = "select email from contacts INNER JOIN contact-group ON contacts.contact_id = contact-group.contact_id";
if ($group != '') $sql .= "WHERE contact-group.group_id = $group";

$result = mysql_query($sql,$conn) or die(mysql_error());

//create a From: mailheader
$headers = "From: DDM <myeamil@sdddd.net>\n";

//loop through results and send mail
while ($row = mysql_fetch_array($result))
{
set_time_limit(0);
$email = $row['email'];
mail("$email", stripslashes($_POST[subject]), stripslashes($_POST[message]), $headers);
print "newsletter sent to: $email<br>";
}
}
?>

Burhan

02-19-2005, 04:46 AM

If you are posting PHP code, please use the tags to allow for syntax highlighting, which helps debugging syntax errors. For example:

[php]
<?php

if ($_POST['submit'] == '')
{
//haven't seen the form, so show it
?>
<HTML>
<HEAD>
<TITLE>Send a Newsletter</TITLE>
</HEAD>
<BODY>
<h1>Send a Newsletter</h1>
<form method='POST' action='<?php echo $_SERVER[PHP_SELF]; ?>'>
<P><strong>Group:</strong><br>
<select name='contact_group'>
<option value=''>(All)</option>
<?php
//connect to database
$conn = mysql_connect("localhost", "user", "passwd") or die(mysql_error());
mysql_select_db("este",$conn) or die(mysql_error());
$sql_text = "select * from groups";
$results = mysql_query($sql_text);
while ($row = mysql_fetch_array($results))
{
echo ("<option value='" . $row['group_id'] . "'>" . $row['group_name'] . "</option>");
}
?>
</select>
<P><strong>Subject:</strong><br>
<input type=\"text\" name=\"subject\" size=30></p>
<P><strong>Mail Body:</strong><br>
<textarea name=\"message\" cols=50 rows=10 wrap=virtual></textarea>
<input type=\"hidden\" name=\"op\" value=\"send\">
<p><input type=\"submit\" name=\"submit\" value=\"Send It\"></p>
</FORM>
</BODY>
</HTML>

<?php
}
else if ($_POST[op] == "send")
{
//want to send form, so check for required fields
if (($_POST[subject] =="") || ($_POST[message] == ""))
{
header("Location: newsletter.php");
exit;
}

//get emails from db list
$group = htmlentities($_REQUEST['contact_group']);
$sql = "select email from contacts INNER JOIN contact-group ON contacts.contact_id = contact-group.contact_id";
if ($group != '') $sql .= "WHERE contact-group.group_id = $group";

$result = mysql_query($sql,$conn) or die(mysql_error());

//create a From: mailheader
$headers = "From: DDM <myeamil@sdddd.net>\n";

//loop through results and send mail
while ($row = mysql_fetch_array($result))
{
set_time_limit(0);
$email = $row['email'];
mail("$email", stripslashes($_POST[subject]), stripslashes($_POST[message]), $headers);
print "newsletter sent to: $email<br>";
}
}
?>

sonic10

02-19-2005, 11:10 AM

Thanks Guys - The dropdown is now populated Yeah. Now I get this error when clicking send.
Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/www/testing/newsletter.php on line 56 -->
$result = mysql_query($sql,$conn) or die(mysql_error());

<?php

if ($_POST['submit'] == '')
{
//haven't seen the form, so show it
?>
<HTML>
<HEAD>
<TITLE>Send a Newsletter</TITLE>
</HEAD>
<BODY>
<h1>Send a Newsletter</h1>
<form method='POST' action='<?php echo $_SERVER[PHP_SELF]; ?>'>
<P><strong>Group:</strong><br>
<select name='contact_group'>
<option value=''>(All)</option>
<?php
//connect to database
$conn = mysql_connect("localhost", "user", "passwd") or die(mysql_error());
mysql_select_db("databasename",$conn) or die(mysql_error());
$sql_text = "select * from groups";
$results = mysql_query($sql_text);
while ($row = mysql_fetch_array($results))
{
echo ("<option value='" . $row['group_id'] . "'>" . $row['group_name'] . "</option>");
}
?>
</select>
<p><strong>Subject:</strong><br />
<input type="text" name="subject" size="30" /></p>
<p><strong>Mail Body:</strong><br />
<textarea name="message" cols="50" rows="10" wrap="virtual"></textarea></p>
<input type="hidden" name="op" value="send" />
<p><input type="submit" name="submit" value="Send It" /></p>
</form>
</BODY>
</HTML>

<?php
}
else if ($_POST[op] == "send")
{
//want to send form, so check for required fields
if (($_POST[subject] =="") || ($_POST[message] == ""))
{
header("Location: newsletter.php");
exit;
}

//get emails from db list
$group = htmlentities($_REQUEST['contact_group']);
$sql = "select email from contacts INNER JOIN contact_group ON contacts.contact_id = contact_group.contact_id";
if ($group != '') $sql .= "WHERE contact_group.group_id = $group";

$result = mysql_query($sql,$conn) or die(mysql_error());

//create a From: mailheader
$headers = "From: DDM <inquiry@tkathyllc.org>\n";

//loop through results and send mail
while ($row = mysql_fetch_array($result))
{
set_time_limit(0);
$email = $row['email'];
mail("$email", stripslashes($_POST[subject]), stripslashes($_POST[message]), $headers);
print "newsletter sent to: $email<br>";
}
}
?>

stormraven

02-19-2005, 06:44 PM

Move these two lines:

$conn = mysql_connect("localhost", "user", "passwd") or die(mysql_error());
mysql_select_db("databasename",$conn) or die(mysql_error());

to just after the first <?php statement.