Page 1 of 1

Need help creating a table in MySQL

Posted: Sat Nov 02, 2002 6:53 am
by deado
i have been using the tutorials on http://www.phpcomplete.com and they help me understand mysql and php alot. but i as i was following a tutorial, it said to create a table and it showed the code for it.

Code: Select all

<html>
<title>Test Project</title>


<?php
$dbconn = mysql_connect("localhost", "deado", "password");
$result = mysql_select_db("deadzone", $dbconn);
if ( $result == false )
&#123;
	echo mysql_error();
&#125; else &#123;
	echo "Yay! We connected successfully!";
&#125;
?>

<?php
CREATE TABLE friends 
(
	fid TINYINT not null AUTO_INCREMENT, 
	fname VARCHAR (20) not null , 
	lname VARCHAR (20) not null , 
	birthday DATE not null , 
	address VARCHAR (100) not null , 
	city VARCHAR (20) not null , 
	state VARCHAR (2) not null , 
	zip VARCHAR (7) not null , 
	country VARCHAR (20) not null , 
	email VARCHAR (60) not null , 
	phone VARCHAR (14) not null , 
	notes MEDIUMTEXT not null , 
	PRIMARY KEY (fid)
)
?>

<?php
INSERT INTO `friends` 
(
	`fid`, 
	`fname`, 
	`lname`, 
	`birthday`, 
	`address`, 
	`city`, 
	`state`, 
	`zip`, 
	`country`, 
	`email`, 
	`phone`, 
	`notes`
) 
	VALUES 
(
	'', 
	'Jason', 
	'Lotito', 
	'1980-01-19', 
	'Somewhere', 
	'Montreal', 
	'CN', 
	'12345', 
	'Canada', 
	'jason@lehighweb.com', 
	'1234567890', 
	'These are notes and are whatever I want them to be. =))'
) 
?>

</html>
So with that i asume it works.. but when i go to retrieve that information it says that table friends does not exisit. My code for my retrieve page is:

Code: Select all

<html>
<title>Retrieve</title>

<?php

$dbconn = mysql_connect("localhost", "deado", "password");
$result = mysql_select_db("deadzone", $dbconn);
if ( $result == false )
&#123;
	echo mysql_error();
&#125; else &#123;
	$sql = "SELECT fname, lname, email FROM friends WHERE fid=1";
	$result = mysql_query( $sql );
	if ( $result != false )
	&#123;
		echo "Query was successful";
	&#125; else &#123;
		echo mysql_error();
	&#125;
&#125;
?>

</html>
please help me figure out why it is not creating the table.. thanks

Posted: Sat Nov 02, 2002 8:38 pm
by sad2891
for one thing, but im not sure if this is the killer.

but anyways

the line

Code: Select all

fid TINYINT not null AUTO_INCREMENT,
says not null, but the insert into the table tries to insert null for fid

so i would suggest chaging the line to

Code: Select all

fid TINYINT AUTO_INCREMENT,
i think that is correct, go ahead and give it a try. And someone else please confirm my thoughts on this. because if you insert data into a table and a single line is null and they are set to not null then it will not insert the information at all.

Posted: Sat Nov 02, 2002 9:09 pm
by mydimension
your problem is that you are not really executing the SQL queries. try this instead:

Code: Select all

&lt;html&gt;
&lt;title&gt;Test Project&lt;/title&gt;


&lt;?php
$dbconn = mysql_connect("localhost", "deado", "password");
$result = mysql_select_db("deadzone", $dbconn);
if ( $result == false )
{
   echo mysql_error();
} else {
   echo "Yay! We connected successfully!";
}
?&gt;

&lt;?php
$friends_create_query = "CREATE TABLE friends
(
   fid TINYINT not null AUTO_INCREMENT,
   fname VARCHAR (20) not null ,
   lname VARCHAR (20) not null ,
   birthday DATE not null ,
   address VARCHAR (100) not null ,
   city VARCHAR (20) not null ,
   state VARCHAR (2) not null ,
   zip VARCHAR (7) not null ,
   country VARCHAR (20) not null ,
   email VARCHAR (60) not null ,
   phone VARCHAR (14) not null ,
   notes MEDIUMTEXT not null ,
   PRIMARY KEY (fid)
)";

mysql_query($friends_create_query);

$friend_insert_query = "INSERT INTO `friends`
(
   `fid`,
   `fname`,
   `lname`,
   `birthday`,
   `address`,
   `city`,
   `state`,
   `zip`,
   `country`,
   `email`,
   `phone`,
   `notes`
)
   VALUES
(
   '',
   'Jason',
   'Lotito',
   '1980-01-19',
   'Somewhere',
   'Montreal',
   'CN',
   '12345',
   'Canada',
   'jason@lehighweb.com',
   '1234567890',
   'These are notes and are whatever I want them to be. =))'
)";

mysql_query($friend_insert_query);
?&gt;

&lt;/html&gt;

I guess i tried too hard

Posted: Sat Nov 02, 2002 9:15 pm
by sad2891
This is how i do mine, as far as executing SQL goes.

Code: Select all

&lt;?php
&lt;html&gt; 
&lt;title&gt;Test Project&lt;/title&gt; 


&lt;?php 
$dbconn = mysql_connect("localhost", "deado", "password"); 
$result = mysql_select_db("deadzone", $dbconn); 
if ( $result == false ) 
{ 
   echo mysql_error(); 
} else { 
   echo "Yay! We connected successfully!"; 
} 
?&gt; 

&lt;?php 
mysql_query("CREATE TABLE friends 
( 
   fid TINYINT not null AUTO_INCREMENT, 
   fname VARCHAR (20) not null , 
   lname VARCHAR (20) not null , 
   birthday DATE not null , 
   address VARCHAR (100) not null , 
   city VARCHAR (20) not null , 
   state VARCHAR (2) not null , 
   zip VARCHAR (7) not null , 
   country VARCHAR (20) not null , 
   email VARCHAR (60) not null , 
   phone VARCHAR (14) not null , 
   notes MEDIUMTEXT not null , 
   PRIMARY KEY (fid) 
)"); 


mysql_query("INSERT INTO `friends` 
( 
   `fid`, 
   `fname`, 
   `lname`, 
   `birthday`, 
   `address`, 
   `city`, 
   `state`, 
   `zip`, 
   `country`, 
   `email`, 
   `phone`, 
   `notes` 
) 
   VALUES 
( 
   '', 
   'Jason', 
   'Lotito', 
   '1980-01-19', 
   'Somewhere', 
   'Montreal', 
   'CN', 
   '12345', 
   'Canada', 
   'jason@lehighweb.com', 
   '1234567890', 
   'These are notes and are whatever I want them to be. =))' 
)"); 

?&gt; 

&lt;/html&gt; 
?&gt;
it eliminates the whole

Code: Select all

mysql_query($friend_insert_query);
thing

Posted: Sat Nov 02, 2002 9:50 pm
by mydimension
matter of preference i think. i just like to have my query in a seperate variable for debugging purposes.