populate dropdown menu from database

[sjeggels]

Hi there,
I am still experiencing problems
to populate the dropdown menu
in my form (mysettings.php) with
the correct contents
from the database.
Herewith is what I have.
<?php
/**********************
MYSETTINGS.PHP***************
***********
This updates user settings and
password
****************************
**********************
**********/
include 'dbc.php';
page_protect();
$rs_settings = mysql_query
("select * from users where id='$
_SESSION[user_id]'");
if(@$_POST ==
'Update')
{
$rs_pwd = mysql_query("select
pwd from users where id='$_
SESSION[user_id]'");
list($old) = mysql_fetch_row($rs_
pwd);
//check for old password in md5
format
if($old == md5($_POST))
{
$newmd5 = md5(mysql_real_
escape_string($_POST
));
mysql_query("update users set
pwd='$newmd5' where id='$_
SESSION[user_id]'");
header("Location: settings.php?
msg=Your new password is
updated");
} else
{
header("Location: settings.php?
msg=Your old password is
invalid");
}
}
if(@$_POST == 'Save')
{
// Filter POST data for harmful
code (sanitize)
foreach($_POST as $key => $
value) {
$data[$key] = filter($value);
}
mysql_query("UPDATE users SET
`acc_name` = '$data[acc_name]
',`contact` = '$data[contact]',
`phone` = '$data[phone]',`fax` = '$
data[fax]',`address` = '$data
[address]',`website` = '$data
[website]',
`acc_profile` = '$data[acc_profile]
',`acc_location` = '$data[acc_
location]',`acc_directions = '$data
[acc_directions]',`acc_category` =
'$data[acc_category]',`acc_
tariffs` = '$data[acc_tariffs]
',`tourism_route` = '$data
[tourism_route]'
WHERE id='$_SESSION[user_id]'
") or die(mysql_error());
header("Location: settings.php?
msg=Profile Sucessfully saved");
}
?>
This is the SQL error I get when
trying to update user
information:
You have an error in your SQL
syntax; check the manual that
corresponds to your MySQL
server version for the right
syntax to use near 'acc_
category` = 'Guest House', `acc_
tariffs` = 'R0 - R350.00', `tourism_
' at line 10
The dropdown menu as in my
registration form (register.php)
<table width="100%" border="0"
cellspacing="0" cellpadding="5"
class="main">
<tr>
<td width="147">Acc
Category<span
class="required">*</span><br />
<select name="acc_category"
id="acc_category"
class="required">
<option value="" selected></
option>
<option value="Bed &
Breakfast">Bed & Breakfast</
option>
<option value="Guest
House">Guest House</option>
<option
value="Selfcatering">Selfcatering<
/option>
<option value="Hotel">Hotel</
option>
<option value="Lodge">Lodge</
option>
<option
value="Backpacker">Backpacker</
option>
</select></td>
</tr>
I have amended the script you
suggested to populate the
selectboxes in form
(mysettings.php) like so:
<table width="100%" border="0"
cellspacing="0" cellpadding="5"
class="main">
<tr>
<td width="147">Acc
Category<span
class="required">*</span><br />
<select name="acc_category"
id="acc_category"
class="required">
<?php
//Array of accommodation
categories for select box
$Categories = array("Bed &
Breakfast","Guest House",
"Selfcatering",
"Hotel","Lodge","Backpacker" );
$dbacc_category = "Guest
House"; //Stored in database
foreach ($Categories as $acc_
category)
{
if($acc_category == $dbacc_
category) {
echo "<option value=\"$acc_
category\"SELECTED>$acc_
category</option>";
}
else
{
echo "<option value=\"$acc_
category\">$acc_category</
option>";
}
}
?>
</select></td>
</tr>
</table>
SQL
Table structure for table `users`
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL
AUTO_INCREMENT,
`md5_id` varchar(200) NOT NULL
DEFAULT '0',
`acc_name` varchar(60) NOT
NULL,
`user_name` varchar(200) NOT
NULL,
`user_email` varchar(220) NOT
NULL,
`pwd` varchar(200) NOT NULL,
`contact` varchar(60) NOT NULL,
`address` text NOT NULL,
`phone` varchar(20) NOT NULL,
`fax` varchar(20) NOT NULL,
`website` text NOT NULL,
`date_registered` date NOT NULL
DEFAULT '0000-00-00',
`acc_profile` text NOT NULL,
`acc_location` text NOT NULL,
`acc_directions` text NOT NULL,
`acc_category` text NOT NULL,
`acc_tariffs` text NOT NULL,
`tourism_route` text NOT NULL,
`users_ip` varchar(200) NOT NULL,
`approved` int(1) NOT NULL
DEFAULT '0',
`activation_code` int(10) NOT
NULL DEFAULT '0',
`banned` int(1) NOT NULL DEFAULT
'0',
PRIMARY KEY (`id`),
UNIQUE KEY `user_email` (`user_
email`),
FULLTEXT KEY `idx_search` (`acc_
name`,`contact`,`user_
email`,`user_name`)
) ENGINE=MyISAM DEFAULT
CHARSET=utf8 AUTO_
INCREMENT=1 ;
Still can't get the dropdown
menu to populate the correct
info from the database. Please
assist me with this problem.
Thanks.

Code tags?

[sjeggels]

hi ardav. thanks for responce. can you help with my dropdown menu post? thanks.

Put your code within code tags so that we can read it.

[sjeggels]

how do i do that?

[SKANK!!!!!]

<?php
/**********************
MYSETTINGS.PHP***************
***********
This updates user settings and
password
****************************
**********************
**********/
include 'dbc.php';page_protect();
$rs_settings = mysql_query("select * from users where id='$_SESSION[user_id]'");
if(@$_POST['doUpdate'] == 'Update'){$rs_pwd = mysql_query("select pwd from users where id='$_SESSION[user_id]'");
list($old) = mysql_fetch_row($rs_pwd);
//check for old password in md5 format
if($old == md5($_POST['pwd_old'])){$newmd5 = md5(mysql_real_escape_string($_POST['pwd_new']));
mysql_query("update users set pwd='$newmd5' where id='$_SESSION[user_id]'");
header("Location: settings.php?msg=Your new password is updated");} else{header("Location: settings.php?msg=Your old password is invalid");}}
if(@$_POST['doSave'] == 'Save'){
// Filter POST data for harmfulcode (sanitize)
foreach($_POST as $key => $value){$data[$key] = filter($value);}
mysql_query("UPDATE users SET `acc_name` = '$data[acc_name]',`contact` = '$data[contact]',`phone` = '$data[phone]',`fax` = '$data[fax]',`address` = '$data[address]',`website` = '$data[website]',`acc_profile` = '$data[acc_profile]',`acc_location` ='$data[acc_location]',`acc_directions = '$data[acc_directions]',`acc_category` ='$data[acc_category]',`acc_tariffs` ='$data[acc_tariffs]',`tourism_route` = '$data[tourism_route]'WHERE id='$_SESSION[user_id]'") or die(mysql_error());
header("Location: settings.php?msg=Profile Sucessfully saved");} ?>

This is the SQL error I get whentrying to update userinformation:
You have an error in your SQLsyntax; check the manual thatcorresponds to your MySQL
server version for the rightsyntax to use near 'acc_category` = 'Guest House', `acc_tariffs` = 'R0 - R350.00', `tourism_' at line 10

The dropdown menu as in my
registration form (register.php)

<table width="100%" border="0" cellspacing="0" cellpadding="5" class="main">
<tr><td width="147">Acc Category<span class="required">*</span><br />
<select name="acc_category" id="acc_category" class="required">
<option value="" selected></option>
<option value="Bed & Breakfast">Bed & Breakfast</option>
<option value="Guest House">Guest House</option>
<option value="Selfcatering">Selfcatering</option>
<option value="Hotel">Hotel</option>
<option value="Lodge">Lodge</option>
<option value="Backpacker">Backpacker</option>
</select></td>
</tr>

I have amended the script you
suggested to populate the
selectboxes in form
(mysettings.php) like so:

<table width="100%" border="0" cellspacing="0" cellpadding="5" class="main">
<tr><td width="147">Acc Category<span class="required">*</span><br />
<select name="acc_category" id="acc_category" class="required">
<?php
//Array of accommodationcategories for select box
$Categories = array("Bed & Breakfast","Guest House","Selfcatering","Hotel","Lodge","Backpacker" );
$dbacc_category = "Guest House"; //Stored in database
foreach ($Categories as $acc_category){if($acc_category == $dbacc_category){echo "<option value=\"$acc_category\"SELECTED>$acc_category</option>";}else{echo "<option value=\"$acc_category\">$acc_category</option>";}} ?>
</select></td></tr></table>
SQL
Table structure for table `users`
CREATE TABLE `users` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`md5_id` varchar(200) NOT NULL
DEFAULT '0',`acc_name` varchar(60) NOTNULL,`user_name` varchar(200) NOTNULL,`user_email` varchar(220) NOTNULL,`pwd` varchar(200)NOTNULL,
`contact` varchar(60) NOT NULL,`address` text NOT NULL,`phone` varchar(20) NOT NULL,`fax` varchar(20) NOT NULL,`website` text NOT NULL,
`date_registered` date NOT NULL DEFAULT '0000-00-00',
`acc_profile` text NOT NULL,
`acc_location` text NOT NULL,
`acc_directions` text NOT NULL,
`acc_category` text NOT NULL,
`acc_tariffs` text NOT NULL,
`tourism_route` text NOT NULL,
`users_ip` varchar(200) NOT NULL,
`approved` int(1) NOT NULL
DEFAULT '0',
`activation_code` int(10) NOT
NULL DEFAULT '0',
`banned` int(1) NOT NULL DEFAULT
'0',
PRIMARY KEY (`id`),
UNIQUE KEY `user_email` (`user_
email`),
FULLTEXT KEY `idx_search` (`acc_
name`,`contact`,`user_
email`,`user_name`)
) ENGINE=MyISAM DEFAULT
CHARSET=utf8 AUTO_
INCREMENT=1 ;

[urtrivedi]

[ C O D E ]

WRITE YOUR CODE here

[ / C O D E ]
do not use space in [ c o d e ] as i have used

[sjeggels]

thanks everyone for help on how to post code. can you provide solution to retrieve data from database to populate dropdown menu when a user update his details. a simple script will to do this will be appreciated thank you.

Need more info - what data in, what data out? What's the table structure? Has Skank answered your question?

[SKANK!!!!!]

no i just reposted the code in readable format. i would help but i cant until i understand what the op means.

The following code will populate values from the database:

<html> 
<head> 
<basefont face="Arial"> 
</head> 
<body>
<select name="select">
<?php
	$host = "localhost"; 
	$user = "root"; 
	$pass = ""; 
	$db = "testdb"; 
	$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!"); 
	mysql_select_db($db) or die ("Unable to select database!"); 
	$query = "SELECT country FROM symbols";
	$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());
	
	print("<option select value=\"0\">Select any Option</option>");
	
	if (mysql_num_rows($result) > 0) { 
		 while ($row = mysql_fetch_row($result)) {
			if($row[0]=='India')$dtext = "selected";
			else $dtext = "";
			print("<option $dtext value=\"$row[0]\">$row[0]</option>");
		 }
	}
	else { 
      echo "No rows found!"; 
	} 
	mysql_free_result($result);
	mysql_close($connection);
?>
</select> 
</body>
</html>

Just change your query to retrieve specific field from your database.