PHP and mySQL Create Table Please Help!

HelloWorld · #1 (**permalink**) 07-29-2007, 09:02 PM

Here's the current code that I have:

PHP Code:

  $link = mysql_connect($host,$username,$password) or die(mysql_error());
$query = 'CREATE TABLE mycms ('.
         'id INT NOT NULL AUTO_INCREMENT'.
         'username VARCHAR(10) NOT NULL, '.
         'password VARCHAR(10) NOT NULL, '. 
         'PRIMARY KEY (id))';
         
$result = mysql_query($query);

Is there anything wrong with the code that I have? There's no error that PHP gives me, it seems that's all right, but when I double check on the localhost database, it doesn't have the table that I specified "mycms"

rpgfan3233 · #2 (**permalink**) 07-29-2007, 10:14 PM

Try using the following:

PHP Code:

  $result = mysql_query($query) or die(mysql_error());

Note that mysql_error() is only for your benefit. You don't want that to actually be shown to users (and potential script kiddies and others with the intent to breach your security).

The "or die(mysql_error())" part simply says if mysql_query($query) returns FALSE (means there was an error), output whatever mysql_error() returns and then stop processing the page after that. Also, try this for your query:

PHP Code:

  $query = 'CREATE TABLE mycms ('.
         'id INT NOT NULL AUTO_INCREMENT KEY'.
         'username VARCHAR(10) NOT NULL, '.
         'password VARCHAR(10) NOT NULL)';

HelloWorld · #3 (**permalink**) 07-29-2007, 10:31 PM

Quote:

Note that mysql_error() is only for your benefit. You don't want that to actually be shown to users (and potential script kiddies and others with the intent to breach your security).

Thanx a lot for this rpgfan3233!
but it seems not working... probably I should give the full source code:

admin/index.php

PHP Code:

  <?php
$username = 'username';         // mySQL username
$password = 'password';     // mySQL password
$host = 'localhost';    // mySQL host

$link = mysql_connect($host,$username,$password) or die(mysql_error());
$query = 'CREATE TABLE mycms ('.
         'id INT NOT NULL AUTO_INCREMENT KEY'.
         'username VARCHAR(10) NOT NULL, '.
         'password VARCHAR(10) NOT NULL)'; 
         
$result = mysql_query($query);
?>

I also tried to use:

PHP Code:

  $host = 'localhost:3306';

rpgfan3233 · #4 (**permalink**) 07-29-2007, 11:09 PM

Did you try adding the die(mysql_error()) part to the line with $result like you have it on the line with $link ? What does it say if you tried that?

molotov · #5 (**permalink**) 07-29-2007, 11:45 PM

Um, you're not selecting the database to use. Add this after the connection, but before your SQL:

PHP Code:

  $db = 'your_db';
mysql_select_db($db);

Like this:

PHP Code:

  $username = 'username';         // mySQL username
$password = 'password';     // mySQL password
$host = 'localhost';    // mySQL host

// this is new
$db = 'your_db';        // mySQL db name

$link = mysql_connect($host,$username,$password) or die(mysql_error());

// this is also new
mysql_select_db($db);

$query = 'CREATE TABLE mycms ('.
         'id INT NOT NULL AUTO_INCREMENT KEY'.
         'username VARCHAR(10) NOT NULL, '.
         'password VARCHAR(10) NOT NULL)'; 
         
$result = mysql_query($query);

Edit: Also, I didn't look through the rest of your mySQL, but once you have the DB selected, your mysql_error() output should be more telling if you still have errors.

HelloWorld · #6 (**permalink**) 07-29-2007, 11:53 PM

I just knew that you can SELECT a database, is that part of the SQL query? Because from what I know of, you need to have the table first before you can SELECT a table

well, also called schema..?

PHP Code:

  mysql_select_db($db);

I'm still confused on what should I put as the name of the database? I don't have the table yet... nothing to be SELECT ed.. So, I need to create one

TeraTask · #7 (**permalink**) 07-30-2007, 01:04 AM

OK. Here's how it goes, HelloWorld:

1) You have users.
2) You have databases.
3) You have tables.

1 user can have access to 0 or more databases. Each database can have 0 or more tables.

You first connect to MySQL using your user. Since the user can access a number of databases, you must then tell MySQL which database you want to use (this user must have access to that database). After you have connected and indicated the database you can then perform operations on the selected database (such as creating a table).

This type of setup has a nice advantage - users can have limited privileges on a database to protect the database from undesired changes by that user (root, for example, can do whatever it wants, but you don't want any ole user to be able to have that same power).

molotov · #8 (**permalink**) 07-30-2007, 08:12 AM

Let me try to epand on what TeraTask explained.

Ok, you have a server oh which your website is kept and served to the internet. Your server has what are called dameon processes (or depending on your setup, 'services') that help it act like a web server. One of these dameons is the MySQL server. The MySQL dameon operates on the entire server, for all of it's users that have the correct privileges or are in the correct group. Now as you know, MySQL deals with databases - but a single MySQL dameon can handle LOTS of databases. You have to tell it which one to use! This is where mysql_select_db() comes in; it has nothing to do with your actual SQL statements except that it tells the MySQL daemon on which database to perform the statements.

Ok, so you've got a server, with a MySQL daemon running and now you have your databases, when you connect to a database in PHP, you have to tell it the server to which you want to connect, the user* you are connecting as, as password for that user, and a database on the MySQL server that you want to use. Once you connect to the server and MySQL daemon and tell it which database to use, you can start sending it SQL statements and they will be executed on your database, and not someone else's.

Any questions?

*side note: You don't need to worry about this, but just for your information, there are two different 'types' or users here: OS users, and MySQL users. Presumably, your host has assigned you to the MySQL group, or given you the appropriate permissions. Once your OS-level user has permission to access the MySQL daemon, MySQL further requires authentication on a database-per-database level.

HelloWorld · #9 (**permalink**) 07-30-2007, 11:11 AM

Quote:

You first connect to MySQL using your user.

I got the first part when you say connect to MySQL, alright, that one is with the username and password at the first time I setup MySQL.

Quote:

Since the user can access a number of databases, you must then tell MySQL which database you want to use (this user must have access to that database). After you have connected and indicated the database you can then perform operations on the selected database (such as creating a table).

I don't really get this, can you give me some example? Are you talking about schema? Ok, so $hostname is the database (the first step that told me), and then we have to choose which schema to use right? So, there's no way we can build this? Well, actually I can just run query on my SQL saying CREATE some_table and it will build the schema (which is probably the database that you're talking about).... And usually I use these tables by using the SELECT query as explained by molotov

molotov · #10 (**permalink**) 07-30-2007, 12:58 PM

First of all, you have to have created a database in which to put a table. If you want to think of it in a hierarchy:

Code:

Internet
V
servers
V
databases
V
tables
V
fields
V
rows

You're skipping the 'databases' section and trying to go right to tables.

I drew up an AWESOME graphic for you

If you're testing locally, you're just viewing your HOST file and skipping the internet step, and instead of a lot of servers, you only have one.

Is that ANY clearer?