PHP Code Help Needed - Populate drop down from db [Archive]

Click to See Complete Forum and Search --> : PHP Code Help Needed - Populate drop down from db

blizz

02-18-2005, 01:33 PM

HellO,

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") {
//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>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

blizz

02-18-2005, 01:42 PM

I found this piece of code but don't know how to incorparate it into the form. I get the dropdown but it is empty.

echo '<select name="pickgroup" size="1">';
$sql = "SELECT group_name FROM groups";
$result = query($sql);
while($row = fetch_assoc($result))
{ echo "<option value=\"{$row['name']}\">{$row['name']}</option>\n"; }
echo "</select>";

bwkaz

02-18-2005, 07:45 PM

I believe you need to change this:

Originally posted by blizz
{ echo "<option value=\"{$row['name']}\">{$row['name']}</option>\n"; } to this:

{ echo "<option value=\"{$row['group_name']}\">{$row['group_name']}</option>\n"; }

(In other words, replace "name" with the correct name for your table's column: "group_name".)

blizz

02-18-2005, 08:10 PM

Thanks bwkaz

But how do I insert this into the form?

echo '<select name="pickgroup" size="1">';
$sql = "SELECT group_name FROM groups";
$result = query($sql);
while($row = fetch_assoc($result))
{ echo "<option value=\"{$row['group_name']}\">{$row['group_name']}</option>\n"; }
echo "</select>";

If I try this I get a parse error

<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\">
echo '<select name="pickgroup" size="1">';
$sql = "SELECT group_name FROM groups";
$result = query($sql);
while($row = fetch_assoc($result))
{ echo "<option value=\"{$row['group_name']}\">{$row['group_name']}</option>\n"; }
echo "</select>";
<p><input type=\"submit\" name=\"submit\" value=\"Send It\"></p>
</FORM>

Any suggestions...

nouse66

02-18-2005, 11:12 PM

maybe you're forgetting to wrap the php in <? ?> tags

try this:

<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\">
<?
echo '<select name="pickgroup" size="1">';
$sql = "SELECT group_name FROM groups";
$result = query($sql);
while($row = fetch_assoc($result)) {
echo "<option value=\"{$row['group_name']}\">{$row['group_name']}</option>\n"; }
echo "</select>";
?>

<p><input type=\"submit\" name=\"submit\" value=\"Send It\"></p>
</FORM>

bwkaz

02-19-2005, 10:05 AM

Actually, based on the fact that almost all of your quotes are escaped (plus you refer to $_SERVER[PHP_SELF], which is a PHP thing), I'm guessing that that whole thing is supposed to go inside <? ?> tags.

Also, this will never validate against any useful (and by "useful", I mean "XHTML", which isn't strictly correct but I don't care :p) doctype. You need to use all lowercase for your tags, everywhere, and you need to quote all your attribute values, not just the string ones, to get that to work. (And I highly doubt that "wrap=virtual" is a valid attribute on a text area. Sure enough, the textarea element definition page (http://www.w3.org/TR/html4/interact/forms.html#edef-TEXTAREA) doesn't list "wrap" as one of the attributes.)

blizz

02-19-2005, 11:28 AM

Thanks bwkaz & nouse66 - 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>";
}
}
?>