mysql questions...

[Colin-uk]

ok im a total newb to this....

does anyone know how to create tables / accounts in mysql
ive downloaded some free scripts from the net and most of em say i need to create accounts and tables in mysql
but i dont know how to do it..

any ideas ?

thanks
-c0lin

[iAvalance]

Just simple SQL:

CREATE TABLE myTable (
field_one INT NOT NULL PRIMARY KEY auto_increment,
field_two VARCHAR(24) NOT NULL DEFAULT 'some default value'
);

etc, etc, etc.

But maybe you're not looking for an SQL course (although your NEED it to do some things with your mediocre DB -> PostgreSQL rules) you might want to look for several open source GUIs which automate these things.

A advice you to do it the SQL way (it's the way you need to go through anyway) and use this splendid SQL client: Aqua Data Studio

[Colin-uk]

ok u lost me now lol

where do i put that code?

how do i know what SQL i have? (PostgreSQL?)

[iAvalance]

you got that MySQL running (on of your) server(s) right?

Install ADS, and add a new MySQL server and open a new query window in your own database.

Other way:

start the mysql program by typing mysql from the command prompt.

If I remember well, MySQL has commands like

CREATE DATABASE myDatabase;

I guess you first have to do that before you can create tables.

If you got PHP installed you can also do this via an PHP page. Just do something like:

<?php
    /* Connecting, selecting database */
    $link = mysql_connect("mysql_host", "mysql_user", "mysql_password")
        or die("Could not connect");
    print "Connected successfully";
    mysql_select_db("my_database") or die("Could not select database");

    /* Performing SQL query */
    $query = "SELECT * FROM my_table";
    $result = mysql_query($query) or die("Query failed");

    /* Printing results in HTML */
    print "<table>\n";
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
        print "\t<tr>\n";
        foreach ($line as $col_value) {
            print "\t\t<td>$col_value</td>\n";
        }
        print "\t</tr>\n";
    }
    print "</table>\n";

    /* Free resultset */
    mysql_free_result($result);

    /* Closing connection */
    mysql_close($link);
?>

you should dig up the PHP manual of the web (see www.php.net) especially the MySQL part for PHP stuff, and check out the MySQL manual (from www.mysql.com) to see the exact syntax of MySQL SQL for creating/altering/deleting tables.

[Colin-uk]

ah thanks for the info, i,ll try it out

thanks
-c0lin

[fireydeviant]

user acct should be your user name and passwerd, unless its on your sever...

[Colin-uk]

k, i tried adding a new mysql server and it keeps saying access denied for user admin@127.0.0.1

how do i grant access?

sorry to be such a pain...

[iAvalance]

that's a bit a problem

You need to know what account has been created during installation of it.
UNIX uses root as master account, with no password as it seems (EEK, I just found this out!!!)
On windows they might use administrator for this purpose.

[Colin-uk]

ok, i tried it without a username and pass and it worked
so ive added new server, opened a new query window
now i think its waitin for me to type into the query box (lol)
what do i type?

thanks for helpin a noob

[iAvalance]

LOL, we're in...

Now an SQL course. hahaha

In your query analyzer, it has above the text field a dropdown box, does it show you some choices?

If not we probably need to create our own working DB. If you got anything there you should either list or decide yourself if you want to use one of those.

Suppose you want to create a new database for your own:

CREATE DATABASE colin
GO

execute this, you probably have to close the query manager and recollapse the tree on the left to show up your newly database.

Open a new query analyzer using the new database and create a new user for to use this DB.

GRANT ALL PRIVILEGES ON *.* TO <username>@localhost IDENTIFIED BY '<password>' WITH GRANT OPTION
GO
GRANT ALL PRIVILEGES ON *.* TO <username>@'%' IDENTIFIED BY '<password>' WITH GRANT OPTION
GO

and execute. You should edit the server properties or add a new server using the username and password you just added and as default database the database created first.

After that you can safely play with your DB.

[Colin-uk]

Thanks iAvalance

i did have some options in the drop down menu

central_user_db
invision
phpwebsite
test

i followed your instructiond for creating a new DB an it worked thanks! lol

just another quick q though...
how do you delete databases?
cos it kinda looks a bit messy with those ones ive created to test

thanks again
-c0lin

[iAvalance]

DROP DATABASE <name>
GO



Deleting only tables:

DROP TABLE <name>
GO

Deleting records:

DELETE FROM <tablename> WHERE <clause>

[Colin-uk]

i tried to delete a database called test and got this error message :

Error: General error, message from server: "Error dropping database (can't rmdir '.\test', errno: 13)"

[Executed: 12/09/03 22:04:18 BST ] [Execution: 0/ms]

[iAvalance]

ghe... I guess mysql is not the owner of the file... so it cannot delete the file.
You should look the file up in exporner and see who is the owner and what the permissionbits are. Change to owner to mysql or give everybody full access and try again.

[Colin-uk]

Originally posted by iAvalance
ghe... I guess mysql is not the owner of the file... so it cannot delete the file.
You should look the file up in exporner and see who is the owner and what the permissionbits are. Change to owner to mysql or give everybody full access and try again.

where do i find the exporner?