Jump to content

beginner needs MySQL help :)


tjhilder

Recommended Posts

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.

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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

?>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.