user defined table and fields - unfinished
Moderator: General Moderators
- stc7outlaw
- Forum Newbie
- Posts: 21
- Joined: Mon Jun 09, 2003 9:36 pm
user defined table and fields - unfinished
Hello all,
I am quite new to PHP and kinda suck at it. I have just thrown together this code that I am using for my father's company to help their company aid other companies.
In my code, I am trying to open the server, then check if the table inputted already exists and if it doesnt, create the new table in mysql.
Next, I am taking the inputted data from the user and storing it on the database with the newly made table and its fields.
The $storenumber variable is used as an input from the user and also for means of creating the new table. I also want to use the newly inputted $storenumber as a field in mysql. I seem to be plagued with errors. I was able to get the code working a while back, but it didnt create a table or any of the fields. I think i screwed it up even more since then.
Code is as follows:
<HTML>
<body>
<form method="post" action="<?php print $PHP_SELF ?>">
<BR>Enter the Taco Bell Store Number
<BR><input type="text" name="storenumber">
<BR>Enter Your Name
<BR><input type="text" name="name">
<BR>Enter The Size
<BR><input type="text" name="size">
<BR>Enter Your Phone Number
<BR><input type="text" name="phone">
<input type="submit" value="Load Store">
<input type="reset">
</form>
<?php
if( isset ( $storenumber) isset ( $name) isset ( $size) isset ( $phone)) {
$user = "oprods";
$pass = "breakin";
$db = "storedbtest";
$link = mysql_connect("localhost", "$user", "$pass");
if ( ! $link ) {
$dberror = "Couldn't connect to MySQL server";
return false;
mysql_select_db($db);
}
$tbl_exists = mysql_query("DESCRIBE $storenumber");
if ($tbl_exist) {
print "Store Already exists";
} elseif (! $tbl_exist) {
mysql_query("CREATE TABLE $storenumber (storenumber INT, name VARCHAR, size VARCHAR, phone
VARCHAR)");
//query goes here
print "Store database Created";
} else {
printf ("Error creating database: %s\n", mysql_error());
}
if ( ! mysql_select_db( $db, $link ) ) {
$dberror = mysql_error();
return false;
}
$query = "INSERT INTO $storenumber ( storenumber, name, size, phone )//table stuff
values( '$storenumber', '$name', '$size', '$phone')";
if ( ! mysql_query( $query, $link ) ) {
$dberror = mysql_error();
return false;
}else {
mysql_query($query, $link);
print "<BR>Database info complete";
}
?>
</body>
</html>
Thank you,
and please help the new programmer in dispair.
STC7OUTLAW
I am quite new to PHP and kinda suck at it. I have just thrown together this code that I am using for my father's company to help their company aid other companies.
In my code, I am trying to open the server, then check if the table inputted already exists and if it doesnt, create the new table in mysql.
Next, I am taking the inputted data from the user and storing it on the database with the newly made table and its fields.
The $storenumber variable is used as an input from the user and also for means of creating the new table. I also want to use the newly inputted $storenumber as a field in mysql. I seem to be plagued with errors. I was able to get the code working a while back, but it didnt create a table or any of the fields. I think i screwed it up even more since then.
Code is as follows:
<HTML>
<body>
<form method="post" action="<?php print $PHP_SELF ?>">
<BR>Enter the Taco Bell Store Number
<BR><input type="text" name="storenumber">
<BR>Enter Your Name
<BR><input type="text" name="name">
<BR>Enter The Size
<BR><input type="text" name="size">
<BR>Enter Your Phone Number
<BR><input type="text" name="phone">
<input type="submit" value="Load Store">
<input type="reset">
</form>
<?php
if( isset ( $storenumber) isset ( $name) isset ( $size) isset ( $phone)) {
$user = "oprods";
$pass = "breakin";
$db = "storedbtest";
$link = mysql_connect("localhost", "$user", "$pass");
if ( ! $link ) {
$dberror = "Couldn't connect to MySQL server";
return false;
mysql_select_db($db);
}
$tbl_exists = mysql_query("DESCRIBE $storenumber");
if ($tbl_exist) {
print "Store Already exists";
} elseif (! $tbl_exist) {
mysql_query("CREATE TABLE $storenumber (storenumber INT, name VARCHAR, size VARCHAR, phone
VARCHAR)");
//query goes here
print "Store database Created";
} else {
printf ("Error creating database: %s\n", mysql_error());
}
if ( ! mysql_select_db( $db, $link ) ) {
$dberror = mysql_error();
return false;
}
$query = "INSERT INTO $storenumber ( storenumber, name, size, phone )//table stuff
values( '$storenumber', '$name', '$size', '$phone')";
if ( ! mysql_query( $query, $link ) ) {
$dberror = mysql_error();
return false;
}else {
mysql_query($query, $link);
print "<BR>Database info complete";
}
?>
</body>
</html>
Thank you,
and please help the new programmer in dispair.
STC7OUTLAW
Last edited by stc7outlaw on Tue Jun 10, 2003 10:42 pm, edited 1 time in total.
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
Hi, start by reading:
viewtopic.php?t=8815
viewtopic.php?t=511
http://php.net/language.operators.logical.php (for info on how to have multiple statements in an if statement)
http://www.mysql.com/doc/en/CREATE_TABLE.html (how to create a table in MySQL)
There are a couple of important things to note about why your code may not have been working, firstly by having the mysql_select_db() call within this if statement block:
it would not run.
Secondly, you need to take care with variable names - you go from using $tbl_exists to using $tbl_exist which, well, doesn't exist.
Try this code, it's not entirely complete (check the comments for more info) but will hopefully help:
Mac
viewtopic.php?t=8815
viewtopic.php?t=511
http://php.net/language.operators.logical.php (for info on how to have multiple statements in an if statement)
http://www.mysql.com/doc/en/CREATE_TABLE.html (how to create a table in MySQL)
There are a couple of important things to note about why your code may not have been working, firstly by having the mysql_select_db() call within this if statement block:
Code: Select all
if ( ! $link ) {
$dberror = "Couldn't connect to MySQL server";
return false;
mysql_select_db($db);
}Secondly, you need to take care with variable names - you go from using $tbl_exists to using $tbl_exist which, well, doesn't exist.
Try this code, it's not entirely complete (check the comments for more info) but will hopefully help:
Code: Select all
<html>
<body>
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<BR>Enter the Taco Bell Store Number
<BR><input type="text" name="storenumber">
<BR>Enter Your Name
<BR><input type="text" name="name">
<BR>Enter The Size
<BR><input type="text" name="size">
<BR>Enter Your Phone Number
<BR><input type="text" name="phone">
<input type="submit" value="Load Store">
<input type="reset">
</form>
<?php
if (!empty($_POST['storenumber']) && !empty($_POST['name']) && !empty($_POST['size']) && !empty($_POST['phone'])) {
$user = 'oprods';
$pass = 'breakin';
$db = 'storedbtest';
$storenumber = $_POST['storenumber'];
$name = $_POST['name'];
$size = $_POST['size'];
$phone = $_POST['phone'];
@$link = mysql_connect('localhost', $user, $pass) or die('<p>Couldn''t connect to MySQL server.</p>');
@mysql_select_db($db) or die('<p>Could not select '.$db.' database');
$sql = "SHOW TABLES LIKE '$storenumber'";
@$tbl_exists = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
if (mysql_num_rows($tbl_exists) == 1) {
echo '<p>Store database already exists.</p>';
} else {
// you're going to have to modify the size of each field to fit your requirements
$sql = "CREATE TABLE $storenumber (storenumber INT(5), name VARCHAR(50), size VARCHAR(50), phone VARCHAR(20))";
@mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
echo '<p>Store database created.</p>';
}
// you need to do a lot more validation of data before putting it into the database
// e.g. you should definitely look at using is_numeric() to check that the store number
// is a number
// depending on your PHP setup you may also have to use addslashes() on all your data
$sql = "INSERT INTO $storenumber (storenumber, name, size, phone) VALUES('$storenumber', '$name', '$size', '$phone')";
@mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
echo '<p>Database info complete.</p>';
} else {
echo '<p>Please enter some data.</p>';
}
?>
</body>
</html>- stc7outlaw
- Forum Newbie
- Posts: 21
- Joined: Mon Jun 09, 2003 9:36 pm
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
I think I see why there might be a problem - the forum has eaten one of my closing PHP tags:
should be:
(with no space between the ? and the >) and
should be
Mac
Code: Select all
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ">Code: Select all
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ? >">Code: Select all
} else {
echo '<p>Please enter some data.</p>';
}
</body>
</html>Code: Select all
} else {
echo '<p>Please enter some data.</p>';
}
? > // with no space between the ? and >
</body>
</html>- stc7outlaw
- Forum Newbie
- Posts: 21
- Joined: Mon Jun 09, 2003 9:36 pm
THe input boxes are now up. IT looks fine, but still a problem. When i enter things in the text boxes it gives me this error:
Thank you for being a big help. There must be 1 error somewhere and i have been looking for a long time but cant pin point it. We almost have this wrapped up.
Code: Select all
You have an error in your SQL syntax near '66666 (storenumber INT(5), name CHAR(50), size CHAR(50), phone CHAR(20))' at line 1
CREATE TABLE 66666 (storenumber INT(5), name CHAR(50), size CHAR(50), phone CHAR(20))- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
I think that the problem is that you can't have a table name that starts with a digit (or one which is only digits). Perhaps it would be better to call the table 'store_66666' and adjust the SQL statements accordingly, so instead of:
you would have:
and instead of
you would have
(note that those are backticks ` around the table name not single quotes ')
Mac
Code: Select all
$sql = "SHOW TABLES LIKE '$storenumber'";Code: Select all
$sql = "SHOW TABLES LIKE 'store_$storenumber'";Code: Select all
$sql = "CREATE TABLE $storenumber (storenumber INT(5), name CHAR(50), size CHAR(50), phone CHAR(20))";Code: Select all
$sql = "CREATE TABLE `store_$storenumber` (storenumber INT(5), name CHAR(50), size CHAR(50), phone CHAR(20))";Mac
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
I had a look through the manual which yielded this:
http://www.mysql.com/doc/en/Legal_names.html
Mac
http://www.mysql.com/doc/en/Legal_names.html
I'm not sure whether stating versions prior to 3.23.6 means that they've changed the rules for newer versions but it's probably best to start the name with a letter and it does explicitly say that all digits aren't allowed.In MySQL versions prior to 3.23.6, the name rules are as follows:
......
- A name may start with any character that is legal in a name. In particular, a name may start with a digit (this differs from many other database systems!). However, a name cannot consist only of digits.
Mac