[SOLVED] need help with list menu

jim.davidson · May 22, 2007

I'm an old DOS programmer that has written many a clipper data entry applications.

I know that if it's possible for a user to enter wrong data, at sometime, someone will.

I'm new to PHP and mySQL but learning...I have a problem I've been going nuts over for days, I can't find an example anywhere

I have an update form where the user can update customer data (name,address,city,state,etc..). Here's the scenario where I'm stuck:

The user want's to change the customer's last name and the state(which come from a list menu) from Jones to Smith and Ohio to Pennsylvania.

User blanks out last name selects Pennsylvania from the menu,and clicks on submit without entering Smith.

Oh oh error! Customer needs a last name, send user back to form for name.

No problem except that the state has reversed back to Ohio instead of remaining at Pennsylvania.

How can I get the updated state to appear on the list menu?

I have three record sets

getCustomer (Gets customer info from customer table)

getStates (Gets all state names and abbreviations from states table)

findState (Gets state info from state table from data in customer table)

Here's the code for the list menu

<?php

do {

?>

<?php

} while ($row_GetStates = mysql_fetch_assoc($GetStates));

$rows = mysql_num_rows($GetStates);

if($rows > 0) {

mysql_data_seek($GetStates, 0);

$row_GetStates = mysql_fetch_assoc($GetStates);

}

?>

</select>

B34ST · May 22, 2007

Do you need to store the state names for the list in the db? or just store each users state? I would do something like this:

<?php
$users_state = $row_findStateName['abbreviation']; //not sure if this is the correct row this needs to be the users current state
$states = array('state1', 'state2', 'state3', 'etc');

$select_state = '<select name="abbreviation" id="abbreviation" class="text_background">';
for($i = 0; $i < count($states); $i++)
{
if (isset($_POST['abbreviation']))
{ $selected = ' selected="selected"'; }
else
{ $selected = ( $users_state == $states[$i] ) ? ' selected="selected"' : ''; }
$select_state .= '<option value="' . $states[$i] . '"' . $selected . '>' . $states[$i] . '</option>';
}
$select_state .= '</select>';

echo $select_state;
?>

jim.davidson · May 22, 2007

I need to put all updated info into the selected record from customer table. So I need to store the state abbreviation, not the state name.

In my code $row_findStateName['abbreviation'] would contain OH

Customer Table

customer_id int 10 auto increment

first_name varchar 50 Example JOE

last_name varchar 50 JONES

address_1 varchar 50 PO BOX 1

address_2 varchar 50

city varchar 50 SOMEWHERE

state varchar 2 OH

zip varchar 10 90210

contact_name varchar 60 JOHN DOE

contact_phone varchar 25 (555) 123-4567

contact_email varchar 100 jdoe@hiscompany.com

States Table

state_id int 10 auto increment

state_name varchar 50 Example: Ohio

abbreviation varchar 2 Example: OH

B34ST · May 22, 2007

its hard to understand your script looking at a small part of it could you show more?

$row_GetStates['abbreviation'] - is this from states table?

$row_findStateName['abbreviation'] - is this from users table?

jim.davidson · May 23, 2007

Here's the code

1. Recordsets (3)

2. Form

3. Update code

1.

getCustomer recordset contains info of a customer

$colname_getCustomer is the customer id pass from a previous page

mysql_select_db($database_imcrecycle, $imcrecycle);

$query_getCustomer = sprintf("SELECT * FROM customers WHERE customer_id = %s", GetSQLValueString($colname_getCustomer, "int"));

$getCustomer = mysql_query($query_getCustomer, $imcrecycle) or die(mysql_error());

$row_getCustomer = mysql_fetch_assoc($getCustomer);

$totalRows_getCustomer = mysql_num_rows($getCustomer);

getStates is a recordset of all state names and abbreviations (used to build drop down list menu)

mysql_select_db($database_imcrecycle, $imcrecycle);

$query_GetStates = "SELECT states.state_name, states.abbreviation FROM states ORDER BY states.state_name";

$GetStates = mysql_query($query_GetStates, $imcrecycle) or die(mysql_error());

$row_GetStates = mysql_fetch_assoc($GetStates);

$totalRows_GetStates = mysql_num_rows($GetStates);

findStateName recordset contains state info on the customer from getCustomer (I needed to find the state name to display first on the drop down menu)

$colname_findStateName contains the state abbreviation from the customer recordset

mysql_select_db($database_imcrecycle, $imcrecycle);

$query_findStateName = sprintf("SELECT * FROM states WHERE abbreviation = %s", GetSQLValueString($colname_findStateName, "text"));

$findStateName = mysql_query($query_findStateName, $imcrecycle) or die(mysql_error());

$row_findStateName = mysql_fetch_assoc($findStateName);

$totalRows_findStateName = mysql_num_rows($findStateName);

2.

Here's the form

<form action="<?php echo $editFormAction; ?>" method="POST" name="customer_form" id="customer_form" onsubmit="MM_validateForm('contact_areacode','','NisNum','contact_phone_prefix','','NisNum','contact_phone_last4','','NisNum','contact_email','','NisEmail');return document.MM_returnValue">

<?php if (isset($error['contact_last_name'])) { ?>

</tr>

<tr>

<td align="right" class="style3"><div align="right">* Required fields </div></td>

</tr>

<tr>

<td width="215" align="right" class="formlabel">Contact First Name<span class="style3">*</span>: </td>

<td><input name="contact_first_name" class="text_background" id="contact_first_name" value="<?php if($error) { echo $first_name;}

else { if (isset($_POST['first_name'])) { echo $_POST['first_name'];} else { echo $row_getCustomer['first_name'];}} ?>" size="50" maxlength="50" type="text" /></td>

</tr>

<tr>

<td width="413"><input name="contact_last_name" class="text_background" id="contact_last_name" value="<?php if($error) { echo $last_name;}

else { if (isset($_POST['last_name'])) {echo $_POST['last_name'];} else { echo $row_getCustomer['last_name'];}} ?>" size="50" maxlength="50" type="text" /></td>

</tr>

<tr>

</tr>

<tr>

<td align="right" valign="top" class="formlabel">Mailing Address<span class="style3">*</span>:</td>

<td><input name="contact_address1" class="text_background" id="contact_address1" value="<?php if($error) { echo $address1;}

else { if (isset($_POST['contact_address1'])) {echo $_POST['contact_address1'];} else { echo $row_getCustomer['address_1'];}} ?>" size="50" maxlength="50" type="text" /></td>

</tr>

<tr>

<td><input name="contact_address2" class="text_background" id="contact_address2" value="<?php if($error) { echo $address2;}

else { if (isset($_POST['contact_address2'])) {echo $_POST['contact_address2'];} else { echo $row_getCustomer['address_2'];}} ?>" size="50" maxlength="50" type="text" /></td>

</tr>

<tr>

<td><input name="contact_city" class="text_background" id="contact_city" value="<?php if($error) { echo $city;}

else { if (isset($_POST['contact_city'])) {echo $_POST['contact_city'];} else { echo $row_getCustomer['city'];}} ?>" size="30" maxlength="50" type="text" /></td>

</tr>

<tr>

<td align="right" valign="top" class="formlabel">State<span class="style3">*</span>:</td>

<?php

do {

?>

<?php

} while ($row_GetStates = mysql_fetch_assoc($GetStates));

$rows = mysql_num_rows($GetStates);

if($rows > 0) {

mysql_data_seek($GetStates, 0);

$row_GetStates = mysql_fetch_assoc($GetStates);

}

?>

</select>

</td>

</tr>

<tr>

<td><input name="contact_zip" class="text_background" id="contact_zip" value="<?php if($error) { echo $zip;}

else { if (isset($_POST['contact_zip'])) {echo $_POST['contact_zip'];} else { echo $row_getCustomer['zip'];}} ?>" size="10" maxlength="10" type="text" /></td>

</tr>

<tr>

</tr>

<tr>

</tr>

</table>

</form>

3. Heres the update code

// validate the input, beginning with name

$MM_flag="MM_update";

if (isset($_POST[$MM_flag])) {

$first_name = trim($_POST['contact_first_name']);

$last_name = trim($_POST['contact_last_name']);

$address1 = trim($_POST['contact_address1']);

$address2 = trim($_POST['contact_address2']);

$city = trim($_POST['contact_city']);

$state = trim($_POST['abbreviation']);

$zip = trim($_POST['contact_zip']);

$error = array();

if (empty($first_name)) {

$error['contact_first_name'] = 'Please enter your contact first name';

}

if (empty($last_name)) {

$error['contact_last_name'] = 'Please enter your contact last name';

}

if (empty($address1)) {

$error['contact_address1'] = 'Please enter your mailing address';

}

if (empty($city)) {

$error['contact_city'] = 'Please enter your city';

}

// start of update record

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "customer_form")) {

$updateSQL = sprintf("UPDATE customers SET first_name=%s, last_name=%s, address_1=%s, address_2=%s, city=%s, `state`=%s, zip=%s WHERE customer_id=%s",

GetSQLValueString(strtoupper($_POST['contact_first_name']), "text"),

GetSQLValueString(strtoupper($_POST['contact_last_name']), "text"),

GetSQLValueString(strtoupper($_POST['contact_address1']), "text"),

GetSQLValueString(strtoupper($_POST['contact_address2']), "text"),

GetSQLValueString(strtoupper($_POST['contact_city']), "text"),

GetSQLValueString($_POST['abbreviation'], "text"),

GetSQLValueString($_POST['contact_zip'], "text"),

GetSQLValueString($_POST['cust_id'], "int"));

mysql_select_db($database_imcrecycle, $imcrecycle);

$Result1 = mysql_query($updateSQL, $imcrecycle) or die(mysql_error());

$updateGoTo = "recycle_list_customers.php";

if (isset($_SERVER['QUERY_STRING'])) {

$updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";

$updateGoTo .= $_SERVER['QUERY_STRING'];

}

header(sprintf("Location: %s", $updateGoTo));

}

Any help you can give will be greatly appreciated, even if it's just reference to a good PHP book. I've been stuck for days and need to get this working very soon.

jim.davidson · May 24, 2007

Got the answer from friendsfored forum

If an error is triggered, the $error variable will be set, so you should use the value from the $_POST array to set the selected value in the drop-down menu. If no error is detected, you use the strcmp() code inserted by Dreamweaver. In pseudocode:

if (isset($error) && $_POST['state'] == current value) {

echo 'selected="selected"';

}

elseif (recordset value == current value) {

echo 'selected="selected"';

}

Sign In

[SOLVED] need help with list menu

Recommended Posts

jim.davidson

Link to comment

Share on other sites

B34ST

Link to comment

Share on other sites

jim.davidson

Link to comment

Share on other sites

B34ST

Link to comment

Share on other sites

jim.davidson

Link to comment

Share on other sites

jim.davidson

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information