Create Table with Variabe Number of Fields

[brax23]

I'm trying to create a table using input from a form.  The form contains a list of items and each item has a check box.  I'd like to create a table where the number of rows (fields) will be equal to the number of boxes checked on the form.  Can anyone help?

[wildteen88]

could you post more details along with some example data. Not understanding you fully,

[brax23]

Certainly, here's an example of the form code:

<form name="input" action="admin_3.php" method="get">
Proposal:
<input type="checkbox" name="Ec1" value="E0010" checked="checked" />
<br />
Conceptual Planning: 
<input type="checkbox" name="Ec2" value="E0020" />
<br />
Rezoning: 
<input type="checkbox" name="EC3" value="E0030" />
<br /><br />
<input type="submit" value="Submit" />
</form> 

 

When I hit submit I'd like to create a table with the number of rows equal to however many boxes I have checked.

[wildteen88]

By table do you mean HTML table or database table?

[brax23]

Sorry, database table.

[wildteen88]

Does E0010, E0020, E0030 have some special meaning in your script? I cannot see how simply checking  checkbox and submitting the form can create a database table with so little data to go by.

[.josh]

if you make your checkbox name an array name = 'Ec[]' you can just implode $_POST['ec'] into a comma separated list and use that in your query.

[brax23]

For example, if I checked all three boxes and hit 'submit' I would be directed to another page containing php script that would create a table in my database.  I would name the table using a variable I have stored.  The table would contain three (3) fields with the following names: "EC1", "EC2", and "EC3".  The table would contain one (1) row with the following values inserted in the three (3) separate cells: "E0010", "E0020" and "E0030".

[.josh]

Well here's my take.  It's longer than it should be because I made it all on 1 script (for testing) so you're gonna have to break it down into separate pages since your action target is a diff page, etc...

 

<?php

// if we have posted info (1+ checkbox checked)
if ($_POST['Ec']) {
   // loop through each posted checkbox
   foreach($_POST['Ec'] as $key => $val) {
      // since the key starts at 0 but you want 
      // the first one to be 1 we add 1 to it
      $key += 1;

      // string for the column names for create table query.
      // this assumes the columns are varchar(10). You can change
      // the col type here. If cols are not gonna be of the 
      // same type, you're gonna have issues and you're gonna
      // want to rethink this whole variable column thing and
      // what data you're passing from the form
      $colNameCreateString .= "Ec$key VARCHAR(10) NOT NULL,";      

      // string for the column names for the insert query.
      $colNameString .= "Ec$key,";
      // string for the column values for the insert query.
      $colValString .= "'$val',";
   } // end foreach $_POST['Ec']

   // chop the comma off at the end (side effect of foreach loop)
   $colNameCreateString = substr($colNameCreateString, 0,-1);
   $colNameString = substr($colNameString, 0,-1);
   $colValString = substr($colValString, 0,-1);

   // table name
   $tablename = "blah";

   // connect to and select db (change to your info)
   $conn = mysql_connect('localhost','dbusername','dbpass') or trigger_error('sql: ', E_ALL);
   $db = mysql_select_db('dbname',$conn) or trigger_error('sql: ', E_ALL);

   // create the table using $colNameCreateString
   $sql = "CREATE TABLE $tablename ($colNameCreateString)";
   $result = mysql_query($sql, $conn) or trigger_error('sql: ', E_ALL);

   // insert info into table using $colNameString and $colValString
   $sql = "INSERT INTO $tablename ($colNameString) VALUES ($colValString)";
   $result = mysql_query($sql, $conn) or trigger_error('sql: ', E_ALL);
} // if $_POST['ec']

// echo out the form
echo <<<FORMENT
<form action = '{$_SERVER['PHP_SELF']}' method = 'post'>
   Proposal:<input type='checkbox' name='Ec[]' value='E0010' checked='checked' /><br />
   Conceptual Planning:<input type='checkbox' name='Ec[]' value='E0020' /><br />
   Rezoning:<input type='checkbox' name='Ec[]' value='E0030' /><br /><br />
   <input type="submit" value="Submit" />
</form>
FORMENT;
?>

 

[brax23]

Thank you wildteen88 and crayon violent.  crayon violent, I certainly did not mean for you to write the code for me but it certainly makes things alot easier.  I'll try this and thank you both for sharing your knowledge.

[.josh]

Well it's really only a couple lines of code. Most of it is just commentary and the form you already made and my own extra code that I used to test it which you would have already had. 

[brax23]

More than likely it's my fault, but, I'm having trouble with a bit of the code crayon violet wrote above.  I'm not getting any errors but for some reason the following bit of code:

$sql = "CREATE TABLE $tablename ($colNameCreateString)";

just doesn't seem to want to create the table.  All the variables are working fine.  I'm wondering if perhaps it might be a syntax issue in the create table function.  Any suggestions?

 

[.josh]

echo $sql and insert the output directly into your db through phpmyadmin see if it gives you an error.

[brax23]

Thanks crayon violent.  Here's what $sql echo's: CREATE TABLE (ec1 VARCHAR(10) NOT NULL,ec2 VARCHAR(10) NOT NULL)

It did not create the table thru phpmyadmin.  I'm trying to figure out where the table_name needs to come in.

[brax23]

Sorry, heres the error it gave:

#1064 - 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 '(ec1 VARCHAR(10) NOT NULL,ec2 VARCHAR(10) NOT NULL)' at line 1

[.josh]

okay well I have no idea how you have your db setup so create your table/columns directly in phpmyadmin through their menu and look at the outputted sql code it shows and compare the two and see how you need to modify it.

[.josh]

or wait...

 

CREATE TABLE (ec1 VARCHAR(10) NOT NULL,ec2 VARCHAR(10) NOT NULL)

 

needs to be

 

CREATE TABLE tablenamehere (ec1 VARCHAR(10) NOT NULL,ec2 VARCHAR(10) NOT NULL)

 

it looks like your problem is that $tablename in the query string is not being set.

[brax23]

Yes, I am having trouble with the tablename.  I'll check into this.  Thanks again for sharing your knowledge.

[.josh]

Well you said in a previous post you had the table name stored in a variable somewhere...are you using that variable?

[brax23]

ok, it's been a looong day.  I pasted the $sql echo (worked in phpmyadmin) into my code.  Syntax is correct and I manually assigned the table_name in the line but it still won't create the table.  I must have a simple connection to my database issue.  I'll see if I can fix it after I get some rest.  Again, thank you!

[brax23]

Many thanks to crayon violent and wildteen88.  It works like a charm.