Tutorial Beginners guide to MySQL Use and Installation.

Discussion in 'Webmaster Tutorials and Resources' started by DDRtists, Mar 31, 2007.

  1. DDRtists

    DDRtists ɹoʇɐɹǝpoɯ ɹǝdns Staff Member

    Basic Guide to the Setup and Use of MySQL
    Written by DDRtists for TheHelper.net​

    Software URL: http://mysql.com/

    This guide is for WIN32 Setup. Setup on Linux and other OS's is different, and a bit harder.

    What is MySQL?
    MySQL, in short, is a database system/server that makes it easy to store data sent to it by a Server SSI such as PHP, or applications. It is free for personal use, but you must pay for corprate use.
    What will I get out of this Tutorial?
    After reading this tutorial, you should be able to install MySQL, and connect to it via PHP. You should also know some basic query strings.

    Step 1 - Download:
    You are going to want the "Community Server" from here: http://dev.mysql.com/downloads/mysql/5.0.html#downloads . At the time of writing this, the current version is "5.0". You are going to want the one labeled "Windows", as "Windows x64" is for Vista, and is not covered in this guide. It may be the same, but I have no way to test it, sorry.

    The package you want is "Windows (x86) ZIP/Setup.EXE". Pick a mirror and download. Might want to go get some food or a drink, it may take awhile to download.

    Step 2 - Install:
    After you download the file, Unzip it and run "Setup.exe". You should see this:
    [​IMG]

    If so, click "Next >". If not, you have problems. :p

    After you click next, you should see this window:
    [​IMG]

    While you can select "Typical", I would recommend selecting "Complete" as the type of install. If you are installing for a certain program that requires special settings, select "Custom" ( This is not covered in this tutorial, yet. ;) ).

    After you select what you want, click "Next >". You will be prompted with information. Make sure it is correct, and then hit "Install".

    [​IMG]

    You will then be prompted to make a MySQL.com Account. I would just hit "Skip Sign-Up", but you may make one if you'd like.

    [​IMG]

    After you pass this stage, a Window will come up, with a checkbox that says "Configure the MySQL Server now!". Make sure this is checked, and click "Finish".

    Step 3 - Configure:
    After the "MySQL Server Instance Configuration Wizard" Loads, you will see this box:

    [​IMG]

    Click "Next >" and you will see this:

    [​IMG]

    Once again, if you are just installing it for general purposes ( PHP, Web Stuff ), "Standard Configuration" most times will suit just fine. "Detailed Configuration" is only needed for applications with Special needs, for the most part. Click "Next >".

    [​IMG]

    On this screen, it may look scary but it's pretty easy.
    Install as Windows Service: You want this on.
    Service Name: You can leave this alone, as long as you only have 1 install of MySQL on this computer.
    Launch the MySQL Server Automatically: Check if you want MySQL to start automatically on startup.
    Include Bin Directory in Windows PATH: Only check this if you are not planning on using a MySQL GUI Program, and plan to do everything by Command Prompt.

    I would recommend The Windows Service, Launch Automatically, and the Service name "MySQL".
    After you select the options, hit "Next >".

    This next part is VERY important, please read all of the next part!

    [​IMG]

    Select the "Modify Security Settings" Box, and type a password. Then type the same password in the Retype box.
    Only select "Enable Root access from remote Machines" if you HAVE TO. This is a big security point, if left open and an easy to guess password is set.
    UNCHECK "Create an Anonymous Account". THIS IS A HUGE SECURITY FLAW FOR PUBLIC SITES!

    After you set these, and check to make sure they are correct, hit "Next >".

    The next screen with have the text "Ready to execute" text. Hit "Execute" at the bottom. If all goes well, at the circles should get checks. If not, post and we will try to help. Hit "Finish".

    Congradulations, you now have MySQL Installed and Configured. :) See below for some easy tutorials on how to use it.

    _____________________________________

    MySQL Beginners Tutorials

    These tutorials require a Web Server ( Need one? Check out our Apache Setup Guide ) and PHP ( Tutorial Coming soon! ).

    If your MySQL database is on a different Computer as the webserver, you will need to check the box "Enable Remote Access" during configuration.

    These tutorials assume you have a basic knowledge of of PHP. If not, check out our PHP tutorial ( Comming Soon! ).

    Tutorial 1 - Connecting to your MySQL Server:
    Functions used: mysql_connect();
    Level: Beginner
    PHP:
    <?

    $host 'localhost';
    $user 'user';
    $pass 'pass';

    $con mysql_connect($host$user$pass);
    if(!
    $con) die('There was an Error: '.mysql_error());

    mysql_close();

    echo 
    'Connected to your MySQL Database!';

    ?>
    The first three variables set the host and login data. The two lines of code after that attempt to connect, and echos the error if it can't connect.
    The Last line of code echos a nice string if it's able to connect to your MySQL server. :)

    Tutorial 2 - Create/Drop Databases
    Functions Used: mysql_connect();, mysql_query();
    Level: Beginner + Tutorial 1
    PHP:
    <?

    $host 'localhost';
    $user 'user';
    $pass 'pass';

    $con mysql_connect($host$user$pass);
    if(!
    $con) die('There was an Error: '.mysql_error());

    $query 'CREATE DATABASE dbname';

    $result mysql_query($query);
    if(!
    $result) die('There was an Error creating Database: '.mysql_error());

    mysql_close();

    echo 
    'Created database!';

    ?>
    The main part we need to look at is "$query = 'CREATE DATABASE dbname';".
    In this line, we are setting the variable "$query" with a string we plan on sending to MySQL.
    The real query is "CREATE DATABASE dbname". The MySQL command used here is "CREATE DATABASE", and the database will be called "dbname".

    Also, to delete a Database, you would use the very simple command change:
    "DELETE DATABASE dbname" instead of "CREATE DATABASE dbname". ;)

    If there is already a database with the same name, you will get an error.
    There aren't many times where you would create databases with PHP, but in case you need to, you know how. :)

    Tutorial 3 - Create Table + Basic Table Column Types
    Level: Beginner/Intermediate

    In this tutorial, we are going to create a table called "clients", with rows that store the Name, Company, email, and phone number of the "client".
    You will need a database called "clientdata".
    PHP:
    <?

    $host 'localhost';
    $user 'user';
    $pass 'pass';

    $con mysql_connect($host$user$pass);
    if(!
    $con) die('There was an Error: '.mysql_error());

    $db mysql_select_db('clientdata');
    if(!
    $db) die('Error selecting Database "clientdata"');

    $query 'CREATE TABLE clients(
        name VARCHAR(30),
        company VARCHAR(30), 
        email TEXT,
        phone TEXT
        )'
    ;

    $result mysql_query($query);
    if(!
    $result) die('Error Creating Table!');

    mysql_close();

    echo 
    'Table Created!';

    ?>
    There are two things we need to look at "$db = mysql_select_db('clientdata');", and the query string.

    mysql_select_db($db_name); selects that database from the MySQL Server that you connected to via mysql_connect();.

    And for the query string, lets break it down:
    -CREATE TABLE: Pretty easy, create a table. :)
    -clients: table name to create, can be changed to create new tables.
    -name VARCHAR(30),; create a row called "name" with type "varchar(30)"
    -company VARCHAR(30),: create a row called "compnay" with type "varchar(30)"
    -email TEXT,: create a row called "email" with type "text"
    -phone TEXT: create a row called "phone" with type "text"

    Tutorial 4 - Update Table with new data
    Level: Beginner/Intermediate

    Okay, so we're going to assume you have the table from Tutorial 3 in your database already in this tutorial.

    So you have your clients table set up with your site, and bam, you get a phonecall from your buddy John. He says he quit his job at the Marketplace and now works at McDonalds.
    We better update the data with his new company so everything is up to date!
    First, heres the code. Read through it and we'll explain it a bit after.
    PHP:
    <?php

    $host 
    'localhost';
    $user 'user';
    $pass 'pass';

    $con mysql_connect($host$user$pass);
    if(!
    $con) die('There was an Error: '.mysql_error());

    $db mysql_select_db('clientdata');
    if(!
    $db) die('Error selecting Database "clientdata"');
        
    $query 'UPDATE `clients` SET `company` = "McDonalds" WHERE `name` = "John"';

    $result mysql_query($query);
    if(!
    $result) die('Error updating Table!');

    mysql_close();

    echo 
    'Table was updated!';

    ?>
    Okay, the new part of this code would be the Query string. What does the query string mean in this code snippit?

    UPDATE `clients` - We're going to update the table "clients" with the new data that follows.
    SET `company` = "McDonalds" - We want to set the column "Company" with the new data "McDonalds". This will overwrite anything stored in the column "Company".
    WHERE `name` = "John" - We want to update this data only where the data in the `name` column has the value of "John". If you don't use a "WHERE" case, it will update EVERY row of data with the company "McDonalds", and we don't want that.

    Pretty simple, just practice a bit with this concept and you'll get it in no time.

    Just a few notes:
    Always remember to use a WHERE case in your query if you don't want every row to get updated with the new data you pass to it.

    You can update more than one column in a query like follows:
    PHP:
    $query 'UPDATE `clients` SET `name` = "Johnny", `company` = "McDonalds" WHERE `name` = "John"';
    _____________________________________

    Using and Configuring your MySQL Server

    Starting and Stopping the MySQL Server via command prompt
    To start or stop your MySQL Server service, provided you installed it as a Windows Service, you will need to know your MySQL service name. It defaults to "MySQL", but if you changed it, make sure to enter the one you picked.

    To stop the service, open up Command Prompt ( Unless you changed your start menu, "Run" should be in it. Then just run "cmd" ):
    Code:
    net stop mysql
    To Start the service, if it's stopped or you have it set up to not automatically start up, open Command prompt ( See above ) and type:
    Code:
    net start mysql
    • Like Like x 1
  2. mase

    mase ____ ___ ____ __

  3. DDRtists

    DDRtists ɹoʇɐɹǝpoɯ ɹǝdns Staff Member

    I will, I just wrote that at like 2am, and I was too tired to write them all. xP
  4. ReVolver

    ReVolver Moderator Staff Member

    Very Useful! Great Job :cool:
  5. Rad

    Rad ...

    You should explain how to use MySQL Admin, and how to set up a database (Or Schema Catalog)

    Also, you should show us how to output a value. Whats the use in creating a table when we don't even know what the table contains.

    This might be PHP related but should still be answered here:

    As a beginner, my most burning question that could have been answered here was "Can PHP use input?"

    ex. say I want to put my name in a field, how do I make the table have my name in it?

    [and I know this is old, but its in your sig!]
  6. DDRtists

    DDRtists ɹoʇɐɹǝpoɯ ɹǝdns Staff Member

    When I get around to it I will.
    However, I don't really use MySQL Admin. I really just use the command prompt version of mysql to do stuff. :p
  7. bufalonas

    bufalonas Guest

    how about the other configurations

    I read your posting and im trying to put it to full use however past the installation phase I would like to know what each of these tools do and how best i can configure them to use MySQL. MySQLAdministrator, MySQLworkbench MySQLmigration tool,MySQL Query browser. I have faced a problem after installation that i can not do the necessary configurations to get started with the query browser.
    Besides I would like to know if u would recomment me to Use the command line version of MySQL for a database that is not web-based. Will it be easire for me to connect this database to an application I design using VB.net. I need you advice brother
  8. JerseyFoo

    JerseyFoo 1/g = g-1

    Not to be a critic but beginners really shouldn't even be installing MySQL.

    Hosting accounts are available for as little as $2 a month with fast bandwidth, PHP, and MySQL. That's really the best place for a beginner to go.

    Maybe a separate tutorial on installing MySQL and setting up a server for it.
    • Like Like x 1
  9. bufalonas

    bufalonas Guest

    Sure!!

    Hello Jersey does it occure to you that there is a first time to do something? If im to take your advise i think i will never be able to install because according to you there is nothing like a first time and that means i will always be a beginner. I came in here because I needed advice and to be moving forward that means that I need to learn else i never will
  10. JerseyFoo

    JerseyFoo 1/g = g-1

    By going straight to using MySQL and building your website you will be in a much better position to install it later on.

    I didn't say you shouldn't learn it, just that I don't think it should be grouped with learning MySQL, or that it should come first.

    But if you want to go ahead and go through the headache of setting up a server just to eventually lose your motivation and find out how much it costs to get a decent connection...

    Fact: I pay $15 a month for hosting. In order to support the same bandwidth to my house, I'd have to pay $700 a month, not including the hardware or setup fees.

    Fact: Reading beyond the first sentence tends to reduce confusion in the state of California.
  11. enouwee

    enouwee Non ex transverso sed deorsum

    Correct me if I'm wrong, but the original post (#7) didn't mention "hosting" anywhere. Only "learning".

    As for learning how databases work, there are better ways than MySQL and its bastard SQL, especially if you plan to earn your living as DBA or enterprise-grade application developer (i.e. the opposite of expert PHP/web developer). MySQL's SQL may at first look mostly the same, but isn't (LIMIT anyone?). In its default installation (MyISAM), foreign keys aren't enforced natively, there's no way of doing transactions, ...

Share This Page