Create table and insert on same page doesn't work

Ken_in_nh

Is it possible to create a table and insert data into the table on the same page?

Here is what I am using:

THE HTML FORM:

The input form (input.html)
<form action="insert.php" method="POST" enctype="multipart/form-data">

Table Name (Which would be the address)<input type="text" name="table_name" SIZE="60"><P>

Would be the field names starting with address again<input type="text" name="fulladdress" SIZE="60"><P>

</FORM>

THE PHP PAGE

$username="";
$password="";
$database="";
$server="";

$con = mysql_connect($server,$username,$password);

if (!$con)
{
die('Could not connect: ' . mysql_error());
}

$newtable = $_POST["$table_name"];

// Create table in my_db database
mysql_select_db("buysellnh_com_showings", $con);
$sql = "CREATE TABLE $newtable IF NOT EXISTS
(
id INT,
address VARCHAR(100),
cur_timestamp TIMESTAMP(
)";
mysql_query($sql,$con);
$sql="insert into $newtable (id,address,cur_timestamp) values ('',".$_POST['fulladdress'].",NOW())";
mysql_query($sql,$con);
mysql_close($con);

Nothing happens to the DB when this is executed (There is a simple form that populates the variables)

when I remove the insert into line, I get the new table in the DB but when I add back the INSERT INTO I get no table and no rows...

What am I doing wrong?

The customer (A realtor) wants to be able to add an address and then add showings to that address. The address would be the table and the showings would be the rows....

Greatly appreciate anybody's help...

Ken

alimadzi

Could you repost your code using the PHP forum tags? You'll see the button marked PHP in the editor. Preview to make sure it's working. It'll be a lot easier for us to read once you do that.

rincewind456

You may want to change this

$newtable = $_POST["$table_name"];

to this

$newtable = $_POST['table_name];

if you're trying to acess the POST variable from your form.

Ken_in_nh

Create table and insert on same page doesn't work

Is it possible to create a table and insert data into the table on the same page?

Here is what I am using:

THE HTML FORM:

The input form (input.html)
<form action="insert.php" method="POST" enctype="multipart/form-data">

Table Name (Which would be the address)<input type="text" name="table_name" SIZE="60"><P>

Would be the field names starting with address again<input type="text" name="fulladdress" SIZE="60"><P>

</FORM>

THE PHP PAGE

<HTML> 
<HEAD> 

<? 

$username=""; 
$password=""; 
$database=""; 
$server=""; 

$con = mysql_connect($server,$username,$password); 


if (!$con) 
{ 
die('Could not connect: ' . mysql_error()); 
} 

$newtable = $_POST["$table_name"]; 

// Create table in my_db database 
mysql_select_db("buysellnh_com_showings", $con); 
$sql = "CREATE TABLE $newtable IF NOT EXISTS 
( 
id INT, 
address VARCHAR(100), 
cur_timestamp TIMESTAMP( 
)"; 
mysql_query($sql,$con); 
$sql="insert into $newtable (id,address,cur_timestamp) values ('',".$_POST['fulladdress'].",NOW())"; 
mysql_query($sql,$con); 
mysql_close($con); 

?>

Nothing happens to the DB when this is executed (There is a simple form that populates the variables)

when I remove the insert into line, I get the new table in the DB but when I add back the INSERT INTO I get no table and no rows...

What am I doing wrong?

The customer (A realtor) wants to be able to add an address and then add showings to that address. The address would be the table and the showings would be the rows....

Greatly appreciate anybody's help...

Ken

Ken_in_nh

When I remover the INSERT INTO tag, The post works fine and a table is created. When I add the INSERT INTO is added, it doesnt create the table or insert into

rincewind456

Your sql syntax for creating a table should be

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_option ...]
    [partition_options]

Or: 

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_option ...]
    [partition_options]
    select_statement

Or: 

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

so in your case it should read

$sql = "CREATE TABLE  IF NOT EXISTS `$newtable`
(
id INT,
address VARCHAR(100),
cur_timestamp TIMESTAMP(
)";

I hope this code is just as a test because it is very unsafe, as well as prone to failure due to naming rules within mysql.

You should also have some way of checking why a query has failed, something like

mysql_query($sql,$con) or die(mysql_error('Create Table Failed'));
$sql="insert into $newtable (id,address,cur_timestamp) values (',".$_POST['fulladdress'].",NOW())";
mysql_query($sql,$con) or die(mysql_error('Insert Query Failed'));

Ken_in_nh

I can create a table with no problem. The issue is INSERT INTO. Look at the cose and when I have INSERT INTO the table doesnt get created.

NogDog

You probably need quotes around the address value. Also, you can add some debug code to catch any SQL syntax errors while you are developing the code:

$sql="insert into $newtable (id,address,cur_timestamp) values ('','".$_POST['fulladdress']."',NOW())";
$result = mysql_query($sql,$con) or die("Insert failed ($sql): " . mysql_error());
if(!mysql_affected_rows($con))
{
   echo "<p class='error'>ERROR: query OK but no rows inserted ($sql)</p>\n";
}

PS: A bit safer would be:

$sql = sprintf(
   "INSERT INTO `%s` (id, address, cur_timestamp) VALUES ('', '%s', NOW())",
   $newtable,
   mysql_real_escape_string($_POST['fulladdress'])
);