MySQL Table creation through PHP not working for me


#1

Not an expert in PHP but here is my code:

[php]

<?php define('DB_NAME', 'fundtour_info'); define('DB_USER', 'fundtour_rick'); define('DB_PASSWORD', 'Fund.Tours'); define('DB_HOST', 'localhost'); $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD); if (!$link) { die('Could not connect: ' . mysql_error()); } $db_selected = mysql_select_db(DB_NAME, $link); if (!$db_selected) { die('Can\'t use ' . DB_NAME . ': ' . mysql_error()); } // Create a MySQL table in the selected database mysql_query("CREATE TABLE example( id INT NOT NULL, PRIMARY KEY(id), name VARCHAR(30) NOT NULL, address VARCHAR(30) NOT NULL, city VARCHAR(30) NOT NULL, state VARCHAR(30) NOT NULL, zip VARCHAR(30) NOT NULL, homephone VARCHAR(30) NOT NULL, cellphone VARCHAR(30) NOT NULL, email VARCHAR(30) NOT NULL, age VARCHAR(30) NOT NULL, maritalstatus VARCHAR(30) NOT NULL, income VARCHAR(30) NOT NULL, contact VARCHAR(30) NOT NULL, date1 VARCHAR(30) NOT NULL, date2 VARCHAR(30) NOT NULL)") or die(mysql_error()); $value1 = $_POST['name']; $value2 = $_POST['address']; $value3 = $_POST['city']; $value4 = $_POST['state']; $value5 = $_POST['zip']; $value6 = $_POST['homephone']; $value7 = $_POST['cellphone']; $value8 = $_POST['email']; $value9 = $_POST['age']; $value10 = $_POST['maritalstatus']; $value11 = $_POST['income']; $value12 = $_POST['contact']; $value13 = $_POST['date1']; $value14 = $_POST['date2']; $sql = "INSERT INTO example (name, address, city, state, zip, homephone, cellphone, email, age, maritalstatus, income, contact, date1, date2) VALUES ('$value1', '$value2', '$value3', '$value4', '$value5', '$value6', '$value7', '$value8', '$value9', '$value10', '$value11', '$value12', '$value13', '$value14')"; if (!mysql_query($sql)) { die('Error: ' . mysql_error()); } echo "Info Submitted!"; ?>

[/php]

What I have is a sign up form on a website (group, name, address email, etc.). When they hit the submit button I want it to create a main table called example with the other tables (name, address, city, etc.) and insert the info being submitted into the proper tables. It does create the table correctly in MySQL but it will not insert the info from the form into the proper tables. Nothing gets submitted.

But when I remove the code that generates the tables and change [ $sql = "INSERT INTO example ] to [ $sql = "INSERT INTO mygroup ] which was a table that I created manually in phpMyAdmin it inserts the info into the proper tables just fine. I obviously need to put something in the creation code that I am missing.

What I really want to do is create a main table name based on an input box that they fill in on the form. There is a box that is “Group” and when they submit the form I want the table that is created to be called whatever they have put in that “Group” box and then insert all the info into that same set of tables.

I can’t even get the code to spit back an error or show me “Info Submitted!”

Sorry for the length and if I am not clear enough. I will try to help be more clear in anyway I can.

Thanks for any help.


#2

if a table exists in the database it won’t be created again, also I’m struggling to understand why you would want a new table creating every time a form is submitted. I think you want is every time a form is submitted the data goes into the same table each entry will be its own record so for example 5 forms have been submitted would result in 5 rows in a table.

if that’s the case alter your table to have an auto increment on the id then each entry would have a unique id automatically

[php]
mysql_query(“CREATE TABLE IF NOT EXISTS example(
id INT NOT NULL auto_increment,
PRIMARY KEY(id),
name VARCHAR(30) NOT NULL,
address VARCHAR(30) NOT NULL,
city VARCHAR(30) NOT NULL,
state VARCHAR(30) NOT NULL,
zip VARCHAR(30) NOT NULL,
homephone VARCHAR(30) NOT NULL,
cellphone VARCHAR(30) NOT NULL,
email VARCHAR(30) NOT NULL,
age VARCHAR(30) NOT NULL,
maritalstatus VARCHAR(30) NOT NULL,
income VARCHAR(30) NOT NULL,
contact VARCHAR(30) NOT NULL,
date1 VARCHAR(30) NOT NULL,
date2 VARCHAR(30) NOT NULL)”)
or die(mysql_error());
[/php]

I’d alter your insert to:

[php]$sql = mysql_query(“INSERT INTO example (name, address, city, state, zip, homephone, cellphone, email, age, maritalstatus, income, contact, date1, date2) VALUES (’$value1’, ‘$value2’, ‘$value3’, ‘$value4’, ‘$value5’, ‘$value6’, ‘$value7’, ‘$value8’, ‘$value9’, ‘$value10’, ‘$value11’, ‘$value12’, ‘$value13’, ‘$value14’)”)or die(mysql_error());[/php]

then have the echo no need for an if/else statement as if there’s an error the die command will show you.


#3

I think you are right I will just have it always populate the info into that same table.

I have started a new thread with a new question that I have titled: “e-mailing exported sql tables in excel file format”


#4

Yes, Dave is correct! You only create a table ONCE. Then, use it to update or insert or retrieve data.
But, you usually set an index in the database that is unique so you can use it to sort thru data.
Why I mention this is that if you have a user that enters data twice, you will get duplicate data in the database.
This can cause a lot of problems for you in the future. You should learn how to check the database first for
duplicate names BEFORE you insert the data. Otherwise, you will get a lot of dups which is more work to
weed them out. Just wanted to add that thought… Good luck…