Page 1 of 1

user defined table and fields - unfinished

Posted: Mon Jun 09, 2003 9:36 pm
by stc7outlaw
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

Posted: Tue Jun 10, 2003 3:33 am
by twigletmac
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:

Code: Select all

if ( ! $link ) { 
$dberror = "Couldn't connect to MySQL server"; 
return false; 
mysql_select_db($db); 
}
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:

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>
Mac

Posted: Tue Jun 10, 2003 10:01 am
by stc7outlaw
thank you very much for the help

but when i run that revised code i get a parse error on line 53. Does it have somethign to do with the $sql variable?

Posted: Tue Jun 10, 2003 2:17 pm
by twigletmac
That's strange - I ran the code to check it worked before posting it and didn't get any parse errors. I'll test again.

Mac

Posted: Tue Jun 10, 2003 2:21 pm
by twigletmac
I think I see why there might be a problem - the forum has eaten one of my closing PHP tags:

Code: Select all

<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ">
should be:

Code: Select all

<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ? >">
(with no space between the ? and the >) and

Code: Select all

} else { 
   echo '<p>Please enter some data.</p>'; 
} 

</body> 
</html>
should be

Code: Select all

} else { 
   echo '<p>Please enter some data.</p>'; 
} 

? > // with no space between the ? and >

</body> 
</html>
Mac

Posted: Tue Jun 10, 2003 4:23 pm
by stc7outlaw
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:

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))
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.

Posted: Wed Jun 11, 2003 3:04 am
by twigletmac
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:

Code: Select all

$sql = "SHOW TABLES LIKE '$storenumber'";
you would have:

Code: Select all

$sql = "SHOW TABLES LIKE 'store_$storenumber'";
and instead of

Code: Select all

$sql = "CREATE TABLE $storenumber (storenumber INT(5), name CHAR(50), size CHAR(50), phone CHAR(20))";
you would have

Code: Select all

$sql = "CREATE TABLE `store_$storenumber` (storenumber INT(5), name CHAR(50), size CHAR(50), phone CHAR(20))";
(note that those are backticks ` around the table name not single quotes ')

Mac

Posted: Wed Jun 11, 2003 3:14 am
by []InTeR[]
twigletmac wrote: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).
I think, i can be wrong, that this is not correct, mysql can create cells with only numbers.
But the php->mysql parser can't handle this.

Posted: Wed Jun 11, 2003 3:36 am
by twigletmac
I had a look through the manual which yielded this:
http://www.mysql.com/doc/en/Legal_names.html
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.
...
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.

Mac

Posted: Wed Jun 11, 2003 3:50 am
by []InTeR[]
Thnx for looking it up.

I will advise, never to start with a digit.
But that will be your advise to i think :)

Posted: Wed Jun 11, 2003 6:01 am
by stc7outlaw
bless your souls it works