beginner needs MySQL help :)

[tjhilder]

for the last 30 - 45 minutes I've been trying to create a table in my database (using my brother's home server) but I can't seem to get it to work.

 

trying to get this to work:

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]$query = \"CREATE TABLE members (username VARCHAR(25) NOT NULL)\";

mysql_db_query($query);[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

 

I have all the correct connection code and database select code but it seems to be this thats giving me the headache.

 

maybe someone could point me in the right direction with this.

[azgirl]

this might help

 

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--][span style=\"color:#0000BB\"]<?php [/span][span style=\"color:#007700\"]require_once([/span][span style=\"color:#DD0000\"]\'yourdb_conn.php\'[/span][span style=\"color:#007700\"]);

 

[/span][span style=\"color:#FF8000\"]// connect to db

  [/span][span style=\"color:#0000BB\"]$conn [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]db_connect[/span][span style=\"color:#007700\"]();

  if (![/span][span style=\"color:#0000BB\"]$conn[/span][span style=\"color:#007700\"])

    return [/span][span style=\"color:#DD0000\"]\'Could not connect to database server - please try later.\'[/span][span style=\"color:#007700\"];

 

[/span][span style=\"color:#0000BB\"]$sql [/span][span style=\"color:#007700\"]=[/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#DD0000\"]\"CREATE TABLE members (

  `username` varchar(25) NOT NULL default \'\'

)\"[/span][span style=\"color:#007700\"]);

[/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$sql[/span][span style=\"color:#007700\"], [/span][span style=\"color:#0000BB\"]$conn[/span][span style=\"color:#007700\"]);

echo [/span][span style=\"color:#DD0000\"]\"Creating table: \\'members\\'....\'\"[/span][span style=\"color:#007700\"];

 

[/span][span style=\"color:#0000BB\"]?>[/span]

[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

 

[tjhilder]

thanks that did work I have a few more questions,

 

how would I..

 

1. add more then one column when creating a table?

2. delete a column?

3. edit a column?

4. add data to those colums?

 

TJ

[ryanlwh]

make sure the user connecting to the database has create priviledge, and that the table you're creating does not already exist in the database.

 

1. to create table with multiple columns [!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]CREATE TABLE members (`userid` INT(11) NOT NULL AUTO_INCREMENT,`username` VARCHAR(25) NOT NULL, `name` VARCHAR(60) NOT NULL, PRIMARY KEY (userid)) [!--sql2--][/div][!--sql3--]

 

2. delete columns [!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']ALTER TABLE[/span] members [span style=\'color:blue;font-weight:bold\']DROP[/span] username [!--sql2--][/div][!--sql3--]

 

3. edit column [!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']ALTER TABLE[/span] members CHANGE username user_name VARCHAR(25) NOT NULL [!--sql2--][/div][!--sql3--]

 

4. insert data [!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']INSERT[/span] INTO members (username) VALUES ('tjhilder') [!--sql2--][/div][!--sql3--]

 

There are many alternative syntaxes for all of the above queries that you want to check out. Also, you need to set priviledges to do ALTER TABLE and INSERT as well (most of the time a normal user has INSERT priviledge)

[tjhilder]

I been trying to put this code into practice, but it doesn't seem to want to create the table, I do have the privliges to do so, any ideas on what I'm doing wrong?

<?php
// get connection and database information
require_once('admin/mysql/mysql_connect.inc');

// Create information for Members
$query2 = mysql_query("CREATE TABLE members (
       `member_userid` INT(11) NOT NULL AUTO_INCREMENT,
       `member_username` VARCHAR(25) NOT NULL,
       `member_password` VARCHAR(25) NOT NULL,
       `member_fullname` VARCHAR(60) NOT NULL,
       `member_email` VARCHAR(60) NOT NULL,
       `member_rank` INT(11) NOT NULL AUTO_INCREMENT,
       PRIMARY KEY (member_userid)
       ");

// Create Table for Members
mysql_query($query2);

?>

[ryanlwh]

add this to mysql_query

 mysql_query($query2) or die(mysql_error());

this should give you an idea of what went wrong. from the info you posted, it's most likely that the table already exists so the very first simple query didn't work. your latest attempt was due to the fact that auto_increment only works with primary/unique keys.

[tjhilder]

Ok so I changed the code, dunno if this is right, i took out the 2nd "AUTO_INCREMENT" and added the die() part, and now it looks like this:

 

<?php
// get connection and database information
require_once('admin/mysql/mysql_connect.inc');

// Create information for Members
$query = mysql_query("CREATE TABLE members (
      `member_userid` INT(11) NOT NULL AUTO_INCREMENT,
      `member_username` VARCHAR(25) NOT NULL,
      `member_password` VARCHAR(100) NOT NULL,
      `member_fullname` VARCHAR(60) NOT NULL,
      `member_email` VARCHAR(60) NOT NULL,
      `member_rank` INT(11) NOT NULL,
      PRIMARY KEY (member_userid)
      ");

// Create Table for Members
mysql_query($query) or die(mysql_error());

?>

 

and the error returned was:

Query was empty

 

solution?

 

I figure I better mention that I found some code that lists the tables in your database, and it lists no databases. which i have on another php file.

 

[tjhilder]

*bump*

[tjhilder]

Ok I figured out what was wrong, was too many mysql_query() in the script.