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.

Member Avatar

diafol

Code tags?

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

Member Avatar

diafol

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

<?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 ;

[ 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

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.

Member Avatar

diafol

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

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.