Updating a row in a table from an array where some are added and some are updated.
I'm totally not getting this to work:
<?php
if (is_array($address)) {
// this won't update (only existing values)
// I want to test if it exists and if it does, update it, if it doesn't , add it and am not successful
// only
// get array of existing labels
$existing_ids_only = mysql_query(\"SELECT address_label_id from zc_address WHERE user_id = '$this_user_id'\");
$ids_array = mysql_fetch_array($existing_ids_only);
foreach ($ids_array AS $key=>$value) {
// loop through the form array
foreach ($address as $alabel=>$aval) {
// if these values are blank, skip this label
if (($aval['street'] == '' ) &&
($aval['city'] == '') &&
($avail['state'] == '')) {
continue;
}
// if the address_label_id already exists, update it
if (in_array($value, $ids_array)) {
// get the address_id for updating
$this_address_id = mysql_fetch_row(mysql_query(\"SELECT address_id FROM zc_address WHERE address_label_id = '$alabel' AND user_id = '$this_user_id'\"));
// update this address id
$address_qry = mysql_query(\"UPDATE zc_address SET
street='\".$aval['street'].\"',
city='\".$aval['city'].\"',
state='\".$aval['state'].\"',
zipcode='\".$aval['zipcode'].\"',
address_primary='\".($address_primary==$alabel ? 'y' : '').\"',
address_public='\".$aval['public'].\"'
WHERE
address_id = '$this_address_id'\");
}
// otherwise insert this into the db
else {
$address_qry = mysql_query(\"INSERT INTO zc_address SET
user_id='$this_user_id',
street='\".$aval['street'].\"',
city='\".$aval['city'].\"',
state='\".$aval['state'].\"',
zipcode='\".$aval['zipcode'].\"',
address_label_id='$alabel',
address_primary='\".($address_primary==$alabel ? 'y' : '').\"',
address_public='\".$aval['public'].\"'\");
}
}
}
?>I've been screwing around with it, so some of the obvious errors are likely compounded by typos and such. What am I doing wrong? How do I resolve it?
Do you need the generated HTML?
<?php
// this is from the previous thread about arrays INTO the db.
// amusingly, I can delete things just FINE.
<tr>
<th class=\"labels\">Home</th>
<td><input type=\"text\" name=\"address[1][street]\" value=\"27-50 Westmount Road West\" /></td>
<td><input type=\"text\" name=\"address[1][city]\" value=\"Kitchener\" /></td>
<td><input type=\"text\" name=\"address[1][state]\" value=\"ON\" size=\"4\" /></td>
<td><input type=\"text\" name=\"address[1][zipcode]\" value=\"N2M 1R5\" size=\"7\" /></td>
<td><input name=\"address_primary\" value=\"1\" type=\"radio\" checked=\"checked\" /></td>
<td><input name=\"address[1][public]\" value=\"y\" type=\"checkbox\" checked=\"checked\" /></td>
</tr>
?>
Suzanne posted this at 02:15 — 14th August 2003.
She has: 5,507 posts
Joined: Feb 2000
Would it work to set the existing address_id as a hidden input and if it's set, do this, if not, do the other? Instead of pulling an array and such?
Testing (but if you know in the meantime, please do post).
Suzanne posted this at 02:47 — 14th August 2003.
She has: 5,507 posts
Joined: Feb 2000
Okay, that's working for updating, now trying to add a new one at the same time.
Suzanne posted this at 02:53 — 14th August 2003.
She has: 5,507 posts
Joined: Feb 2000
Success!
<?php// added to the output for those that exist already in the initial form
<input type=\"hidden\" name=\"address[$aid][existingkey]\" value=\"$form_address_id\" />
?>
<?php
if (is_array($address)) {
// loop through the form array
foreach ($address as $alabel=>$aval) {
// if these values are blank, skip this label
if (($aval['street'] == '' ) &&
($aval['city'] == '') &&
($avail['state'] == '')) {
continue;
}
// see if the label already exists (so much easier!)
if (isset($aval[existingkey])) {
$this_address_id = $aval['existingkey'];
// update this address id
$address_qry = mysql_query(\"UPDATE zc_address SET
street='\".$aval['street'].\"',
city='\".$aval['city'].\"',
state='\".$aval['state'].\"',
zipcode='\".$aval['zipcode'].\"',
address_primary='\".($address_primary==$alabel ? 'y' : '').\"',
address_public='\".$aval['public'].\"'
WHERE
address_id = '$this_address_id'\");
}
// otherwise insert this into the db
else {
$address_qry = mysql_query(\"INSERT INTO zc_address SET
user_id='$this_user_id',
street='\".$aval['street'].\"',
city='\".$aval['city'].\"',
state='\".$aval['state'].\"',
zipcode='\".$aval['zipcode'].\"',
address_label_id='$alabel',
address_primary='\".($address_primary==$alabel ? 'y' : '').\"',
address_public='\".$aval['public'].\"'\");
}
}
}
?>
Mark Hensler posted this at 03:15 — 14th August 2003.
He has: 4,048 posts
Joined: Aug 2000
Argh. Guess I spent too long in notepad, eh?
<?php
if (is_array($address)) {
// loop through addresses, and remove any that are missing information
foreach ($address AS $key=>$aval) {
if (($aval['street'] == '' ) && ($aval['city'] == '') && ($avail['state'] == '')) {
unset($address[$key]);
}
}
$address_query = mysql_query(\"SELECT address_id, address_label_id
FROM zc_address
WHERE address_label_id IN (\".array_keys($address).\")
AND user_id = '$this_user_id'\");
// update existing records
while ($tmp=mysql_fetch_array($address_query)) {
$this_address_id = $tmp['address_id'];
$aval = $address[$tmp[address_label_id]];
$address_qry = mysql_query(\"UPDATE zc_address SET
street='\".$aval['street'].\"',
city='\".$aval['city'].\"',
state='\".$aval['state'].\"',
zipcode='\".$aval['zipcode'].\"',
address_primary='\".($address_primary==$alabel ? 'y' : '').\"',
address_public='\".$aval['public'].\"'
WHERE address_id = '$this_address_id'\");
// remove this address from the array
unset($address[$tmp[address_label_id]]);
}
// insert new records
foreach ($address AS $key=>$aval) {
$address_qry = mysql_query(\"INSERT INTO zc_address SET
user_id='$this_user_id',
street='\".$aval['street'].\"',
city='\".$aval['city'].\"',
state='\".$aval['state'].\"',
zipcode='\".$aval['zipcode'].\"',
address_label_id='$alabel',
address_primary='\".($address_primary==$alabel ? 'y' : '').\"',
address_public='\".$aval['public'].\"'\");
}
} //END if (is_array($address))
?>
Mark Hensler
If there is no answer on Google, then there is no question.
Suzanne posted this at 03:19 — 14th August 2003.
She has: 5,507 posts
Joined: Feb 2000
Which way is better? (I'm trying to be proactive and solve my own problems, but sometimes I just have to talk it out to get my head around it).
Mark Hensler posted this at 05:41 — 14th August 2003.
He has: 4,048 posts
Joined: Aug 2000
Yours may be more efficient. It doesn't have the extra loop and query that mine does.
Suzanne posted this at 05:47 — 14th August 2003.
She has: 5,507 posts
Joined: Feb 2000
Perhaps a different situation would be better served with different coding. I'm interested in figuring out OOP for this, but not right this second, lol. My brain hurts enough.
The unset information actually sparked an idea to check for blanks AND an existingkey, and delete the record.
I feel good, though. This database has 25 tables and I'm working through them nicely and not actually breaking my brain, just bending it a tad.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.