Multi-dimensional array into db
Hi, everyone...
I've done it to myself again. Here's what I have:
I have an HTML table in a form, where each row is to be a row in a MySQL table in the database. There are many fields in each row in both cases.
The HTML table is generated from a different MySQL table.
How do I generate the HTML table so that I can enter the data sensibly into the MySQL table using PHP?
I'm thinking that it's some format of multi-dimensional array:
$address[$label][$street]
$address[$label][$city]
$address[$label][$state]
$address[$label][$zipcode]
The MySQL table is zc_address, and each address has a label (home, mailing, alternate, work -- more could be added, these could be removed, the labels are identified by id number only in the zc_address table), as well as the details of the address itself.
The address_label table is used to generate the HTML form (label name and label id number).
Please help, I cannot figure out how to get the information from the HTML form to the INSERT/UPDATE function.
druagord posted this at 18:39 — 30th July 2003.
He has: 335 posts
Joined: May 2003
the easiest way is to insert the data row by row (one INSERT statement by row) and like this you will be sure that even if you have a mysql version smaller then 4 it will still work. I have done something like this in the past i will look in my archives to see if i can find some code
IF , ELSE , WHILE isn't that what life is all about
Suzanne posted this at 18:53 — 30th July 2003.
She has: 5,507 posts
Joined: Feb 2000
Here's what I have, maybe you can see where I'm going so horribly wrong. Hopefully you don't mean I'll have to have each address entered singly. That I can do.
HTML TABLE (sample row, because who needs a whole table?):
<?php<tr>
<th class=\"labels\"><input type=\"checkbox\" name=\"address_array[]\" value=\"1\" /> Home</th>
<td><input type=\"text\" name=\"form_street\" value=\"\" /></td>
<td><input type=\"text\" name=\"form_city\" value=\"\" /></td>
<td><input type=\"text\" name=\"form_state\" value=\"\" size=\"4\" /></td>
<td><input type=\"text\" name=\"form_zipcode\" value=\"\" size=\"7\" /></td>
<td><input name=\"address_primary\" value=\"y\" type=\"radio\" /></td>
<td><input name=\"address_public\" value=\"y\" type=\"checkbox\" /></td>
</tr>
?>
INSERT CODE:
<?php// address(es)
if ($address_array) {
foreach ($address_array as $addressID) {
$address_qry = mysql_query(\"INSERT INTO zc_address SET user_id='$this_user_id',
street='$form_street', city='$form_city',
state='$form_state', zipcode='$form_zipcode',
address_label_id='$addressID',
address_primary='$address_primary',
address_public='$address_public'\");
}
}
?>
Obviously, the only thing that's going in cleanly is the label id and the user id. And I broke the lines so it wouldn't scroll, there are no line breaks in the real code.
Mark Hensler posted this at 21:29 — 30th July 2003.
He has: 4,048 posts
Joined: Aug 2000
You said the table (form) was being built from another db table? It looked more hard coded. Anyway, here's what I can see...
<?php
// or populate from db table
$labels = array(
'1' => 'home',
'2' => 'mailing',
'3' => 'alternate',
'4' => 'work');
foreach ($labels as $key=>$label) {
echo<<<myHTML
<tr>
<th class=\"labels\">$label</th>
<td><input type=\"text\" name=\"address[$key][street]\" value=\"\" /></td>
<td><input type=\"text\" name=\"address[$key][city]\" value=\"\" /></td>
<td><input type=\"text\" name=\"address[$key][state]\" value=\"\" size=\"4\" /></td>
<td><input type=\"text\" name=\"address[$key][zipcode]\" value=\"\" size=\"7\" /></td>
<td><input name=\"address[primary]\" value=\"$key\" type=\"radio\" /></td>
<td><input name=\"address[$key][public]\" value=\"y\" type=\"checkbox\" /></td>
</tr>
myHTML;
}
?>
and...
<?php// address(es)
if (is_array($address)) {
foreach ($address as $label=>$val) {
$address_qry = mysql_query(\"INSERT INTO zc_address SET
user_id='$this_user_id',
street='\".$val['street'].\"',
city='\".$val['city'].\"',
state='\".$val['state'].\"',
zipcode='\".$val['zipcode'].\"',
address_label_id='$label',
address_primary='\".($address['primary']==$label ? 'y' : '').\"',
address_public='\".$val['public'].\"'\");
}
}
?>
print_r($_POST);exit;
Mark Hensler
If there is no answer on Google, then there is no question.
Suzanne posted this at 21:38 — 30th July 2003.
She has: 5,507 posts
Joined: Feb 2000
Yeah, each row is written where the bit from the MySQL table is the label. Hence needing to know how to write the other bits.
<?php
while ($row = mysql_fetch_array($addresslabel_qry)) {
$addresslabel = $row['address_label'];
$addresslabelid = $row['address_label_id'];
echo \"
<tr>
<th class=\\"labels\\"><input type=\\"checkbox\\" name=\\"address_array[]\\" value=\\"$addresslabelid\\" /> $addresslabel</th>
<td><input type=\\"text\\" name=\\"form_street\\" value=\\"\\" /></td>
<td><input type=\\"text\\" name=\\"form_city\\" value=\\"\\" /></td>
<td><input type=\\"text\\" name=\\"form_state\\" value=\\"\\" size=\\"4\\" /></td>
<td><input type=\\"text\\" name=\\"form_zipcode\\" value=\\"\\" size=\\"7\\" /></td>
<td><input name=\\"address_primary\\" value=\\"y\\" type=\\"radio\\" $selected/></td>
<td><input name=\\"address_public\\" value=\\"y\\" type=\\"checkbox\\" $public_checked/></td>
</tr>
\";
}
?>
So the number of rows depends on the number of rows (in the HTML on the MySQL).
Thanks Mark, I think that makes sense, I'll see if I can make it work. For some reason I seem to be able to totally screw up the perfect coding of others!
Suzanne posted this at 21:58 — 30th July 2003.
She has: 5,507 posts
Joined: Feb 2000
I do have one question, actually:
<?php<td><input name=\"address[primary]\" value=\"$key\" type=\"radio\" /></td>
<td><input name=\"address[$key][public]\" value=\"y\" type=\"checkbox\" /></td>
?>
Primary and Public can only be 'y' or 'n'. Primary is one of the set (controlled by the HTML behaviour of the form. Should this still be as above, or rather as:
<?php<td><input name=\"address[$key][primary]\" value=\"y\" type=\"radio\" /></td>
<td><input name=\"address[$key][public]\" value=\"y\" type=\"checkbox\" /></td>
?>
Mark Hensler posted this at 01:30 — 31st July 2003.
He has: 4,048 posts
Joined: Aug 2000
Yes, because with the $key, the name will be different for each row. And that would throw off the behavior of the radio button.
Suzanne posted this at 02:50 — 31st July 2003.
She has: 5,507 posts
Joined: Feb 2000
*duh*
thanks.
Suzanne posted this at 03:36 — 31st July 2003.
She has: 5,507 posts
Joined: Feb 2000
Okay, wee problem...
<?php
$addresslabel_qry = mysql_query(\"SELECT * FROM address_lables WHERE address_label_viewable = 'y'\");
$labels = mysql_fetch_array($addresslabel_qry);
foreach ($labels AS $key=>$label) {
echo<<<myHTML
<tr>
<th class=\"labels\">$label</th>
<td><input type=\"text\" name=\"address[$key][street]\" value=\"\" /></td>
<td><input type=\"text\" name=\"address[$key][city]\" value=\"\" /></td>
<td><input type=\"text\" name=\"address[$key][state]\" value=\"\" size=\"4\" /></td>
<td><input type=\"text\" name=\"address[$key][zipcode]\" value=\"\" size=\"7\" /></td>
<td><input name=\"address[primary]\" value=\"$key\" type=\"radio\" $selected/></td>
<td><input name=\"address[$key][public]\" value=\"y\" type=\"checkbox\" $public_checked/></td>
</tr>
myHTML;
}
?>
Generates this:
<?php<tr>
<th class=\"labels\">1</th>
<td><input type=\"text\" name=\"address[0][street]\" value=\"\" /></td>
<td><input type=\"text\" name=\"address[0][city]\" value=\"\" /></td>
<td><input type=\"text\" name=\"address[0][state]\" value=\"\" size=\"4\" /></td>
<td><input type=\"text\" name=\"address[0][zipcode]\" value=\"\" size=\"7\" /></td>
<td><input name=\"address[primary]\" value=\"0\" type=\"radio\" /></td>
<td><input name=\"address[0][public]\" value=\"y\" type=\"checkbox\" /></td>
</tr> <tr>
<th class=\"labels\">1</th>
<td><input type=\"text\" name=\"address[address_label_id][street]\" value=\"\" /></td>
<td><input type=\"text\" name=\"address[address_label_id][city]\" value=\"\" /></td>
<td><input type=\"text\" name=\"address[address_label_id][state]\" value=\"\" size=\"4\" /></td>
<td><input type=\"text\" name=\"address[address_label_id][zipcode]\" value=\"\" size=\"7\" /></td>
<td><input name=\"address[primary]\" value=\"address_label_id\" type=\"radio\" /></td>
<td><input name=\"address[address_label_id][public]\" value=\"y\" type=\"checkbox\" /></td>
</tr> <tr>
<th class=\"labels\">Home</th>
<td><input type=\"text\" name=\"address[1][street]\" value=\"\" /></td>
<td><input type=\"text\" name=\"address[1][city]\" value=\"\" /></td>
<td><input type=\"text\" name=\"address[1][state]\" value=\"\" size=\"4\" /></td>
<td><input type=\"text\" name=\"address[1][zipcode]\" value=\"\" size=\"7\" /></td>
<td><input name=\"address[primary]\" value=\"1\" type=\"radio\" /></td>
<td><input name=\"address[1][public]\" value=\"y\" type=\"checkbox\" /></td>
</tr> <tr>
<th class=\"labels\">Home</th>
<td><input type=\"text\" name=\"address[address_label][street]\" value=\"\" /></td>
<td><input type=\"text\" name=\"address[address_label][city]\" value=\"\" /></td>
<td><input type=\"text\" name=\"address[address_label][state]\" value=\"\" size=\"4\" /></td>
<td><input type=\"text\" name=\"address[address_label][zipcode]\" value=\"\" size=\"7\" /></td>
<td><input name=\"address[primary]\" value=\"address_label\" type=\"radio\" /></td>
<td><input name=\"address[address_label][public]\" value=\"y\" type=\"checkbox\" /></td>
</tr> <tr>
<th class=\"labels\">y</th>
<td><input type=\"text\" name=\"address[2][street]\" value=\"\" /></td>
<td><input type=\"text\" name=\"address[2][city]\" value=\"\" /></td>
<td><input type=\"text\" name=\"address[2][state]\" value=\"\" size=\"4\" /></td>
<td><input type=\"text\" name=\"address[2][zipcode]\" value=\"\" size=\"7\" /></td>
<td><input name=\"address[primary]\" value=\"2\" type=\"radio\" /></td>
<td><input name=\"address[2][public]\" value=\"y\" type=\"checkbox\" /></td>
</tr> <tr>
<th class=\"labels\">y</th>
<td><input type=\"text\" name=\"address[address_label_viewable][street]\" value=\"\" /></td>
<td><input type=\"text\" name=\"address[address_label_viewable][city]\" value=\"\" /></td>
<td><input type=\"text\" name=\"address[address_label_viewable][state]\" value=\"\" size=\"4\" /></td>
<td><input type=\"text\" name=\"address[address_label_viewable][zipcode]\" value=\"\" size=\"7\" /></td>
<td><input name=\"address[primary]\" value=\"address_label_viewable\" type=\"radio\" /></td>
<td><input name=\"address[address_label_viewable][public]\" value=\"y\" type=\"checkbox\" /></td>
</tr>
?>
Instead of four rows, with the labels Home, Work, Mailing, Alternate.
Here is the MySQL table:
CREATE TABLE `address_lables` (
`address_label_id` int(3) NOT NULL auto_increment,
`address_label` text NOT NULL,
`address_label_viewable` enum('y','n') NOT NULL default 'n',
PRIMARY KEY (`address_label_id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;
#
# Dumping data for table `address_lables`
#
INSERT INTO `address_lables` VALUES (1, 'Home', 'y');
INSERT INTO `address_lables` VALUES (2, 'Work', 'y');
INSERT INTO `address_lables` VALUES (3, 'Mailing', 'y');
INSERT INTO `address_lables` VALUES (4, 'Alternate', 'y');
Suzanne posted this at 03:44 — 31st July 2003.
She has: 5,507 posts
Joined: Feb 2000
oooh, I need to stuff the data into an array. Shoot. How do I do that coming OUT of a db? Pfui.
Mark Hensler posted this at 04:30 — 31st July 2003.
He has: 4,048 posts
Joined: Aug 2000
It's best to avoid stuffing query results into multi-dim arrays when you can simply iterate through them.
Filling an array to use only once is just a waste of memory, and takes time for an extra unecessary loop
(one to fill the array, the next to read).
<?php
$addresslabel_qry = mysql_query(\"SELECT * FROM address_lables WHERE address_label_viewable = 'y'\");
while ($labels = mysql_fetch_array($addresslabel_qry)) {
$id = $labels['address_label_id'];
$label = $labels['address_label'];
echo<<<myHTML
<tr>
<th class=\"labels\">$label</th>
<td><input type=\"text\" name=\"address[$id][street]\" value=\"\" /></td>
<td><input type=\"text\" name=\"address[$id][city]\" value=\"\" /></td>
<td><input type=\"text\" name=\"address[$id][state]\" value=\"\" size=\"4\" /></td>
<td><input type=\"text\" name=\"address[$id][zipcode]\" value=\"\" size=\"7\" /></td>
<td><input name=\"address[primary]\" value=\"$id\" type=\"radio\" $selected/></td>
<td><input name=\"address[$id][public]\" value=\"y\" type=\"checkbox\" $public_checked/></td>
</tr>
myHTML;
}
?>
Mark Hensler
If there is no answer on Google, then there is no question.
Suzanne posted this at 04:31 — 31st July 2003.
She has: 5,507 posts
Joined: Feb 2000
<?php
while ($addressresults = mysql_fetch_array($addresslabel_qry)) {
$aid = $addressresults['address_label_id'];
$addresslabel = $addressresults['address_label'];
if (!is_array($labels)) $labels = array();
array_push($labels, array($aid,$addresslabel));
echo $aid, $addresslabel;
}
?>
Will echo properly (for each line) but will cause problems when I try the foreach ($lables AS $key=>$value, and $value is always Array.
Where am I going wrong?
Suzanne posted this at 04:36 — 31st July 2003.
She has: 5,507 posts
Joined: Feb 2000
*lol* thanks Mark.
I'm glad I learned how to do it, regardless, and much happier to know I don't need to.
Thank you!
(if you know how to do the pushing an array into an array successfully, I wouldn't mind knowing)
Suzanne posted this at 05:11 — 31st July 2003.
She has: 5,507 posts
Joined: Feb 2000
Another wee problem:
First, thank you so much, it's all going in nicely.
The problem is, I'm getting this AS WELL:
<?phpINSERT INTO `zc_address` VALUES (63, 'dfghdfg', 'dfgh', 'df', 'dhfg', '1', '', 'y', 25);
INSERT INTO `zc_address` VALUES (63, 'dfgh', 'dfgh', 'fd', '', '3', 'y', 'y', 26);
INSERT INTO `zc_address` VALUES (63, '3', '3', '3', '3', 'pri', '', '', 27);
?>
What's with the pri and the numbers? Each time I enter (clearly test) data, I get a single number repeated, pri and nothing else... ? There should only be two rows added in the above test case, but there is an extra row being added each time.
<?php
// address(es)
if (is_array($address)) {
foreach ($address as $alabel=>$aval) {
if (($aval['street'] == '' ) && ($aval['city'] == '') && ($avail['state'] == '')) continue;
$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 06:32 — 31st July 2003.
He has: 4,048 posts
Joined: Aug 2000
haha... I made a booboo. It's because of the "address[primary]". Try substituting "address_primary" (don't forget the variable as well as the input name). Sorry about that.
As for the array_push...
<?php$labels = array();
while ($addressresults = mysql_fetch_array($addresslabel_qry)) {
array_push($labels, array($addressresults['address_label_id']=>$addressresults['address_label']));
// OR:
//$labels[$addressresults[address_label_id]] = $addressresults['address_label'];
}
?>
Mark Hensler
If there is no answer on Google, then there is no question.
Suzanne posted this at 13:01 — 31st July 2003.
She has: 5,507 posts
Joined: Feb 2000
oh, of course! "pri" because I have a 3 character limit. Man. *WHY* could I not see that last night?! Ugh.
I had tried a number of variations on the array_push, but not that one, lol. I think I hit every single other option, though! Thanks again Mark for the clarification.
Suzanne posted this at 15:34 — 31st July 2003.
She has: 5,507 posts
Joined: Feb 2000
Further examination determines that it would be less hair-pulling to use checkboxes, add it to the array, and use form checking to make sure people don't use more than one primary account.
All variations that I tried gave me "pri" or failed entirely.
Mark Hensler posted this at 18:04 — 31st July 2003.
He has: 4,048 posts
Joined: Aug 2000
Was there a question in there? Are you getting an undesired result?
(I just woke up, and I'm a bit lost)
Suzanne posted this at 19:57 — 31st July 2003.
She has: 5,507 posts
Joined: Feb 2000
Ah, sorry.
I'm not having any luck with using the radio buttons. No matter what I try, I get additional rows or errors. When I moved to just using the checkbox, it worked fine (of course).
I just can't figure out how to use the radio buttons AND get the results into the db cleanly.
Mark Hensler posted this at 00:04 — 1st August 2003.
He has: 4,048 posts
Joined: Aug 2000
Did you change the name of the input field, and the name of the variable?
<td><input name="address_primary" value="$id" type="radio" $selected/></td>
AND
address_primary='".($address_primary==$alabel ? 'y' : '')."',
Mark Hensler
If there is no answer on Google, then there is no question.
Suzanne posted this at 01:41 — 1st August 2003.
She has: 5,507 posts
Joined: Feb 2000
Yes, I did, but clearly not well. I just repeated it and it's working fine?
No more programming without popcorn and coffee for me.
Thanks, Mark.
Mark Hensler posted this at 07:29 — 1st August 2003.
He has: 4,048 posts
Joined: Aug 2000
haha... is that the magic combo?
Suzanne posted this at 14:51 — 1st August 2003.
She has: 5,507 posts
Joined: Feb 2000
And chocolate chips if I have some.
It's totally coming together now, whee! I hate it when I get mostly finished only to find a major obstacle, takes my wind out, you know?
It's a good project, though, lots of MySQL learning, joining tables, complex queries and such. The php isn't overly complex (lots of switch() though), but the combination and size of it is. There are 23 tables, 15 main screens, et cetera.
I did manage to suss out only six function screens though -- add, edit, view and then their counterparts that do the adding, editing and viewing. Together with special forms for each piece that needs to be added/edited/viewed -- classes, courses, classrooms, ranges, students, instructors, documentation, completed courses... My brain hurts.
Mark Hensler posted this at 18:32 — 1st August 2003.
He has: 4,048 posts
Joined: Aug 2000
I find Cheez-It and Nestea work good too. And who would complain about Chips Ahoy! and Milk.
Suzanne posted this at 18:45 — 1st August 2003.
She has: 5,507 posts
Joined: Feb 2000
Should have known that cheese would have been in there for you! lol!
Silence works best (or really familiar music) but three kids bouncing off the walls isn't so good!
Iced tea sounds fantastic right now, yum.
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.