Jump to content


Photo

beginner needs MySQL help :)


  • Please log in to reply
8 replies to this topic

#1 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 28 October 2005 - 02:15 AM

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.

#2 azgirl

azgirl
  • Members
  • Pip
  • Newbie
  • 1 posts

Posted 28 October 2005 - 10:21 PM

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


#3 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 29 October 2005 - 09:01 PM

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

#4 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 29 October 2005 - 09:34 PM

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)
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#5 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 29 October 2005 - 10:29 PM

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

?>


#6 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 29 October 2005 - 10:38 PM

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.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#7 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 29 October 2005 - 11:40 PM

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.


#8 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 02 November 2005 - 06:05 PM

*bump*

#9 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 04 November 2005 - 12:44 AM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users