• Welcome to Simple Machines Community Forum. Please login or sign up.

The Basics of MySQL...

Started by [Unknown], January 25, 2004, 01:56:52 AM

Previous topic - Next topic

[Unknown]

January 25, 2004, 01:56:52 AM Last Edit: January 27, 2004, 12:07:36 AM by [Unknown]
Now that you have installed MySQL and it's all good and working... you might want to learn how to use it.

There are a few main points to this:
   - Install phpMyAdmin.
   - What are tables, databases, and all this?
   - OPIMIZE, CHECK, REPAIR, and ANALYZE.
   - Building a simple SELECT.
   - Learning INSERT and REPLACE.
   - Understanding UPDATE and DELETE.
   - How to CREATE advanced tables.
   - How to ALTER tables.

Hopefully I won't lose you along the way.  I'm not going to cover the more advanced stuff like JOIN yet.

Please remember that this is in progress.  If you reply, please note that your reply will be deleted to "make way" for my replies.

-[Unknown]

[Unknown]

Installing phpMyAdmin

Well, first let's get this straight - what is phpMyAdmin?  Put simply, it is a very popular tool that can be used to make MySQL do your bidding.  However, it's like a henchman - we all love having henchmen, right?  phpMyAdmin actually only makes MySQL easier.  There is stuff behind MySQL.

This tutorial is not on the use of phpMyAdmin.  This on how to use MySQL - phpMyAdmin will just help.

Okay, so you're going to want to download phpMyAdmin.  To do so, go here:
http://www.phpmyadmin.net/home_page/downloads.php

You probably want what they will probably still be calling the "latest stable version."
Now, unzip/unwhatever the file into your directory for IIS, PWS, Apache, or whatever you use has its public html folder... examples can be found here.

You should now have unzipped a "phpMyAdmin-version" directory.  I recommend you rename this to simply "phpMyAdmin" for clarity's sake.  Now open Notepad or some text editor.  Go to File -> Open, and browse to the "phpMyAdmin" directory.  Look for "config.inc.php" and open it up.

Inside, search for $cfg['PmaAbsoluteUri'] = ''; and change it to $cfg['PmaAbsoluteUri'] = 'http://localhost/phpMyAdmin/';;.  If you didn't rename the directory, use that instead of just /phpMyAdmin/.

Next look for = 'config'; and change it to = 'cookie';.  You only need to change the first occurance, the others don't matter.  Lastly, find $cfg['blowfish_secret'] = ''; and change it to $cfg['blowfish_secret'] = 'YOUR NAME HERE';. (it should be more or less "secret.")

Now open your favorite browser, and tell it to go to http://localhost/phpMyAdmin/index.php.   If it works, it's installed!!

Logging in and setting a password..

To login, all you have to do is put "root" in the username field, and nothing in the password field.  Please note that this is fairly insecure.  After you login, you probably want to click Change password and type some new password twice - don't forget it!!

-[Unknown]

[Unknown]

January 25, 2004, 06:45:45 PM #2 Last Edit: January 27, 2004, 12:07:22 AM by [Unknown]
Databases...

Now that you have phpMyAdmin installed and ready to play with, we're going to want to create a database.  But, what is a database, you ask?

A database is much like a room, so to speak.  The database itself doesn't hold information - it's just a category of information.  Another example is that of a bookcase - the bookcase holds books, but those books are on shelves.  The bookcase itself is like a database.

Databases have names.  To create a new one, you can do one of two things.  The easiest way is to do it with phpMyAdmin.

Go to http://localhost/phpMyAdmin/index.php and login.  Once logged in, click on the box under "Create new database" and type "my_test".  It's important to note that database names shouldn't have spaces ( ), tildas (~), ticks (`), quotes (', "), or other special characters in them... rather just letters, numbers, dashes (-) and underscores (_).

The other way to create one is more advanced, but easier once you get the hand of it.  You just get a prompt (there are ways to do this, I'll get to it later...) and type CREATE DATABASE my_test;.
You can even do this through phpMyAdmin; it's just more annoying than using its interface.

Now that your database has been created, you have a bookcase.  But, it doesn't have any shelves or books in it yet.... or, if you'd rather - you have a room, but it is empty as yet.

Creating a simple table

Here's where we begin getting a little more advanced.  Bare with me - we're going to get the hang of it!
You should be in the "my_test" database.  If you've closed it already, this link should get you there.

You'll see something that allows you to create a new table on database my_test.... well, this is a klunky little interface, and I think it's easier to do it with just text.  So, click the SQL tab...

Now you have a friendly box in front of you, ready to have a query typed into it ;).  Let's start with a simple table... type:
CREATE TABLE simple (
  first text,
  second text
);


What does this mean?  What did we just do?

It means a lot.

-[Unknown]

[Unknown]

January 27, 2004, 12:12:04 AM #3 Last Edit: January 27, 2004, 12:23:16 AM by [Unknown]
Tables and columns

There are two parts of a table.  Those are, the table itself and the columns it has in it.

A good example is that of a forum; a forum has categories, boards, and topics.  The categories are like the databases, vast groups of tables/boards.  Boards are, of course, like tables - they store lots of columns.

But this example isn't perfect.  The best one is a table, for tables.  Forget categories for now, and we'll focus on the table you just created.  It has two columns.  Each column can have a value for each row in the database.  Confusing?  I know...


   
first  second
   1  1, 12, 1
   2  1, 22, 2

This might help.  The 1 and 2 on the left represent rows.  Every column has a "value" for each row.  Here, I've put "1, 1" and the like to represent those values.

Columns are usually used for naming things.  Like, you might say "firstName", "lastName", and "position".  This would look like this:


   
firstName   lastName   position
   1  AlanGreenspanAll-powerful
   2  JohnDoeNo one
   3  CloudStrifeFF7 Hero
   4  JeffLewisProject Manager

As you can see, each row represents one "element" or, in other words, one person/place/thing.  One noun, one object.  A row describes a specific thing, whatever that is.

Columns on the other hand describe one specific attribute of the thing.  The firstName column in the table above is used to describe the first given name of the person.  While the first names don't neccessarily relate, they all describe the same thing.

Tables, now it can be said, are used to describe a certain list or group of things.  And then categories group tables.  Lots of grouping, eh?

-[Unknown]

Bartrainer

When I click on the "change password" link this is what i get:

Server localhost
Error

    You don't have enough rights to be here right now!


Any suggestions?

Thanks,
YUK
Everyone that says "it can't be done" is followed by someone that just did it.  The possibilities are as Powerful as your imagination...

"There are No bad cocktails, just bad drinkers."

[Unknown]

Strange.  Try doing it from the command line - open up the MySQL Command Line Client, log in, and issue:

SET PASSWORD = OLD_PASSWORD('xyz');

-[Unknown]

Bartrainer

Well I upgraded to phpMyAdmin 2.6, and mysql 4.18 and now i get this:

Welcome to phpMyAdmin 2.6.0-pl3

phpMyAdmin tried to connect to the MySQL server, and the server rejected the connection. You should check the host, username and password in config.inc.php and make sure that they correspond to the information given by the administrator of the MySQL server.

Error
MySQL said: 

#1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client


The pw is correct and it's the default 'root' user. Not sure what to do, i should've left it as it was...lol. What do you say?

Thanks,
YUK

Everyone that says "it can't be done" is followed by someone that just did it.  The possibilities are as Powerful as your imagination...

"There are No bad cocktails, just bad drinkers."

[Unknown]

Umm... do the command line thing.  You have to set it to use the OLD_PASSWORD encryption method.

-[Unknown]

Bartrainer

This is really wierd...When i open the command prompt it asks for a pw. I put the pw in, it beeps and closes the prompt window. I know it's the correct pw because when change the comfig.ini setting to '', then phpmyadmin says the following:

Welcome to phpMyAdmin 2.6.0-pl3

phpMyAdmin tried to connect to the MySQL server, and the server rejected the connection. You should check the host, username and password in config.inc.php and make sure that they correspond to the information given by the administrator of the MySQL server.

Error
MySQL said: 

#1045 - Access denied for user 'root'@'localhost' (using password: NO)



Everyone that says "it can't be done" is followed by someone that just did it.  The possibilities are as Powerful as your imagination...

"There are No bad cocktails, just bad drinkers."

[Unknown]

Uninstall MySQL, delete the MySQL folder under Program Files, and reinstall.  It may have been related to upgrading... otherwise I'm not sure.

Try using a simpler password, like "root".

-[Unknown]

Bartrainer

Awesome dude!! After uninstalling it, deleting the files and folders, then reinstalling in, it works. I was able to edit the pw from the command prompt!

THANKS Unknown!!!!   ;D
Everyone that says "it can't be done" is followed by someone that just did it.  The possibilities are as Powerful as your imagination...

"There are No bad cocktails, just bad drinkers."

dufas

I tried to follow this and cant get it to work so I started looking
at the details you say "nside, search for $cfg['PmaAbsoluteUri'] = ''; and change it to $cfg['PmaAbsoluteUri'] = 'http://localhost/phpMyAdmin/' [nofollow];;.  If you didn't rename the directory, use that instead of just /phpMyAdmin/."

Do I put a DOT at the end http://localhost/phpMyAdmin/' [nofollow];;. <----DOT ?

How about "Next look for = 'config'; and change it to = 'cookie';. "

I search for 'config'; and find 3 of them do I change the first one or
all of them?

Other suggestions I forgot my password so had to apply for another one
you ask for "user\email" only took me 10 minutes to figure out you
want either the user OR the email no both why not change it to OR
then the user would know you want either or...

Thanks in advance

[Unknown]

Quote from: dufas on July 26, 2005, 07:54:14 AM
Do I put a DOT at the end http://localhost/phpMyAdmin/';;. <----DOT ?

No dot.  I'm just one of those insane punctuation freak people.  Don't mind us.

QuoteI search for 'config'; and find 3 of them do I change the first one or
all of them?

First first one, but you could change them all if you like.  Again, no dot.

QuoteOther suggestions I forgot my password so had to apply for another one
you ask for "user\email" only took me 10 minutes to figure out you
want either the user OR the email no both why not change it to OR
then the user would know you want either or...

Hmm... I guess an "or" might be less confusing.

-[Unknown]

dæmon

So will this lesson be completed?  If not, where can I find another good tutorial on the subject?

zande_vakil

sorry guys i have a problem when fill the blank of the first step of smf im mean where ask for sql name and password then when i click procced then it doset show any thing els just show smf installer logo at the top of page and dosent go for thet step 2.

Advertisement: