POST - howto help with arrays 1

[yamy]

I have a form with differing inputs: text, checkbox, radio, select option.

(

http://www.sutryn.com/2007webscripting/contact_form_includes.php)

All the data goes via PHP into MySQL db with no trouble EXCEPT in the case of the checkbox array.

I found information about "serialize", but i've missed some key point since it doesn't send anything into the db.

i have the db field set as ENUM, the field values have been double checked for accuracy.

then some more research and I believe that I've mixed up when to use ENUM and when to use SET. so i've switched them around. (ENUM - pick one and only one) (SET - pick one or more) Except that does not work either.

here is the form and following is the handler script.(with enum and set at the db in their original positions)

on the formhandler page, the chosen checkbox values will print with an echo command, so some part of this must be correct.

I have looked very hard for typos that would account for this; doesn't mean I didn't miss one of course!

all help and ideas appreciated.
thanks
amy

formfields:

<h6 class="altfont">Check all that apply.</h6>
<input class="formfields" type="checkbox" name="market[]" value="mfg" />Manufacturing/OEM
<input class="formfields" type="checkbox" name="market[]" value="assykit"/>Assembly/Kitting
<input class="formfields" type="checkbox" name="market[]" value="retail"/>Retail Sale
<input class="formfields" type="checkbox" name="market[]" value="firePro"/>Fire Protection
<input class="formfields" type="checkbox" name="market[]" value="energy" />Energy
<input class="formfields" type="checkbox" name="market[]" value="utility"/>Utility
<input class="formfields" type="checkbox" name="market[]" value="maint"/>Maintenance
<input class="formfields" type="checkbox" name="market[]" value="toolcrib" />Tool Crib


formhandler:

<?php
$inquiry = $_REQUEST['inquiry'];
$companyName = $_REQUEST['companyName'];
$firstName = $_REQUEST['firstName'];
$lastName = $_REQUEST['lastName'];
$officePhone = $_REQUEST['officePhone'];
$cellPhone = $_REQUEST['cellPhone'];
$email = $_REQUEST['email'];
$type = $_REQUEST['type'];
$market = serialize($_REQUEST['market']);
if (isset($_POST['submit'])) {
mysql_connect("mysql.xxxx.com","xxxxdb","xxxguest");
mysql_select_db("xxxtest_db");
$query="INSERT INTO test_table (inquiry,companyName,firstName,lastName,officePhone,cellPhone,email,type,market)";
$query.=" VALUES('$inquiry','$companyName','$firstName','$lastName','$officePhone','$cellPhone','$email','$type','$market')";

 

[sleipnir214]

What type of column is market?

If it's an ENUM or a SET, you can't use serialize here. You must look through $_POST['market'] ($_POST is less prone to variable poisoning than $_REQUEST) and create a parenthesized, comma-delineated quoted list of values. The string should eventuall look like:

('mfg','assykit','retail')


Keep in mind that the MySQL online manual recommends in general against using the SET datatype. See

http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html

Want the best answers? Ask the best questions! TANSTAAFL!

 

[yamy]

Thanks for the reply. I read thru the link you sent.

Is my alternative to create individual columns for each variable?

In this instance I want the user to be able to select more than one option and the database should hold it all.

i am answering my own question as I write this - yes, of course I do want separate columns, especially if I want any meaningful queries to occur on the data I collect.

thanks for listening!
this is a great forum and i'm off to make my annual contribution to the coffers.

regards
amy

 

[sleipnir214]

You should create individual columns for values, not variables. And you should put those extra columns in two additional tables.

Here's my take on what you're trying to do. I use three tables, not one. The first is "contact":

[tt]+--------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+----------------+
| PKID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| company_name | varchar(50) | YES | | NULL | |
| fname | varchar(25) | YES | | NULL | |
| lname | varchar(35) | YES | | NULL | |
| o_phone | varchar(12) | YES | | NULL | |
| c_phone | varchar(12) | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+--------------+------------------+------+-----+---------+----------------+
[/tt]

Notice there is no information for market interests in this table. I have a second table "market_interests:"
[tt]+--------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+----------------+
| PKID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| market_description | varchar(35) | YES | | NULL | |
+--------------------+------------------+------+-----+---------+----------------+[/tt]

At the start of this whole process, the table "market_interests" has in it:

[tt]+------+--------------------+
| PKID | market_description |
+------+--------------------+
| 1 | Manufacturing |
| 2 | Assembly/Kitting |
| 3 | Retail Sales |
| 4 | Fire Protection |
| 5 | Energy |
| 6 | Utility |
| 7 | Maintenance |
| 8 | Tool Crib |
+------+--------------------+[/tt]

I have a third table "contact2market":

[tt]+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| c_id | int(10) unsigned | YES | | NULL | |
| m_id | int(10) unsigned | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+[/tt]

This simple example consists of two scripts, "get_contact.php" and "submit_contact.php". The first script outputs the form and the second puts the data submitted into the database.

"get_contact.php":

<?php
$dbh = mysql_connect ('localhost', 'test', 'test');
mysql_select_db ('test', $dbh);

print '<html><body>
<form method="post" action="submit_contact.php">
	Company:<input type="text" name="company"><br/>
	Last name:<input type="text" name="last_name"><br/>
	First name:<input type="text" name="first_name"><br/>
	Office Phone:<input type="text" name="o_phone"><br/>
	Cell Phone:<input type="text" name="c_phone"><br/>
	email:<input type="text" name="email"><br/>
	Check all that apply:';
	
$rh = mysql_query ('SELECT * from market_interests');

while ($row = mysql_fetch_assoc($rh))
{
	print '<input type="checkbox" name="market[]" value="' . $row['PKID'] . '">' . $row['market_description'] . '<br>';
}

print '<input type="submit"></form></body></html>';
?>

Notice that since the markets are in a table, the output of the interest categories can now be database-driven.

Here is "submit_contact.php":

<?php
$dbh = mysql_connect ('localhost', 'test', 'test');
mysql_select_db ('test', $dbh);

$query = "insert into contact (company_name, fname, lname, 	o_phone, c_phone, email) values (";
$query .= "'" . $_POST['company'] . "',";
$query .= "'" . $_POST['last_name'] . "',";
$query .= "'" . $_POST['first_name'] . "',";
$query .= "'" . $_POST['o_phone'] . "',";
$query .= "'" . $_POST['c_phone'] . "',";
$query .= "'" . $_POST['email'] . "'";
$query .= ")";

mysql_query ($query);

$contact_id = mysql_insert_id($dbh);

foreach ($_POST['market'] as $market)
{
	$query = "insert into contact2market values (" . $contact_id . "," . $market . ")";
	mysql_query($query);
}

?>

It inserts the contact record, gets the auto_increment ID assigned to it, and uses that ID to insert interests associated with that contact. The script will add one new row to the "contact" table for each contact. The script will add one row to the "contact2market" table for each market interest for each contact.

I strongly recommend this way of doing it and you'll find the MySQL manual agrees with me. If you use SETs for market interests, you'll have to modify your database schema and your code to account for the change. If you use the schema I propose, all you will have to do is add a new row to the "market_interests" table and the code will automatically take care of the rest.




Want the best answers? Ask the best questions! TANSTAAFL!

 

[yamy]

I realized all of what you are saying halfway through writing my last post. More to the point, I understood that I could go no further without analyzing and establishing the table structure.

I've got a terminal case of hurry up and want it now disease but I do try hard to find my answers in the pile of books i own and online searching.

I have five logical tables set up and I just came back to search around the mysql forum for some ideas and stopped here on the way.

I think a bit of code from your reply is just the thing I was looking for. Many thanks for pointing me to the mysql_fetch_assoc function. That's where I was headed!

Regards
Amy