user defined table and fields - unfinished

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
stc7outlaw
Forum Newbie
Posts: 21
Joined: Mon Jun 09, 2003 9:36 pm

user defined table and fields - unfinished

Post 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
Last edited by stc7outlaw on Tue Jun 10, 2003 10:42 pm, edited 1 time in total.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
stc7outlaw
Forum Newbie
Posts: 21
Joined: Mon Jun 09, 2003 9:36 pm

Post 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?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
stc7outlaw
Forum Newbie
Posts: 21
Joined: Mon Jun 09, 2003 9:36 pm

Post 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.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post 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.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post 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 :)
User avatar
stc7outlaw
Forum Newbie
Posts: 21
Joined: Mon Jun 09, 2003 9:36 pm

Post by stc7outlaw »

bless your souls it works
Post Reply