How to create and access MYSQl database

Write your "How To" regarding anything you are comfortable with and you feel it will help the forum members.

NOTE :: All threads started here will appear only after the approval from Administrator
Post Reply
anish
Posts: 353
Joined: Fri Apr 27, 2007 12:34 pm
Contact:

How to create and access MYSQl database

Post by anish »

Hi,

I have been developing a MySQl to Access link for a couple of months now and can help you get started.

These are the steps that got me success...

1) Create your MySQL database
In Brief - Login to your control panel > Then Select MySQL Databases
2) Create your new Database as per the tutorial; **** 'Create Database'
3) Create your User and password as per the tutorial; **** 'Create User'
4) Assign your new user name to the New Database as per the tutorial;Select the User and Database and then **** 'Add User To Database'
5) Assign your access hosts:If you have a dedicate IP address then add it here else, Type in "%" (wildcard) (although not as secure) into the input box and **** 'Add Host'

That's it for now, because you can create tables in Access and export them to your MySQL. But first you need to create a DNS connection on your local computer in order to comunicate to your webserver her weWhat's really handy is if you download the MySQL DNS driver and instal it located here:

dev.mysql.com/downloads/connector/odbc/3.51.html

(This version works best for me)

Once downloaded and installed you then need to create your OBDC / DNS connection. You do this through your control panel > Administrative Tools > Data Sources (ODBC)

Choose a User DNS or System DNS (One is limited for just the user, one for the machine ie - if you have different xp accounts setup on your computer like 'Mary' or 'Jane' etc)

1) **** Add
2) Select the MySQL ODBC Driver 3.51 at the bottom
3) Give it a Data Source Name
4) Description (Optional)
5) Server - This is the IP address of the webhosting company IE ****.****.****.****
6) User - This is the User name you gave to the database - May begin with your usual login to your website like login_username (Refer back to the MySQL page where you created the account)
7) Password - This is the password you set for the Username when you created it. Again it may have the Login Name followed by underscore like login_password

8) Next you should be able to slelect from the drop down menu the database you just created
You can **** test to test the connection. If all worked well then you now have a DNS set up

Note - You may need to select the advanced tab and Debug and check the 'Trace Driver Calls to myodbc,log'

10) **** Ok to Finish

Ok next you have to create an Access Database.
1) Presuming you know how to do this then create the table you wish to create.
2) Then select File>Export
3) Select from the File Types Input underneath the input for naming the file, the ODBC
3) Select your DNS to MySQL database

Done - Now you are goin to create a link back to MySQL database

1) Choose File > Get External Data > Link Tables
2) Select from the File Types Input underneath the input for naming the file, the ODBC
3) Select the DNS connection you created as before
4) You should now be allowed to connect to your exported table

This is your live table on the web. Normally you should be able to create pass through queries straight to MySQL, but when writing the query I have had some Syntax problems and other probs. So it's best to either amend data directly in the linked table or create update, delete and append queries to amend data in the linked table from a database driven from your local hard drive.

You can now use this in your main web page to connect to your My SQL DataBase [Items in Brackets are input By You]

# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_[YourWebNameWithout.or.com/.co.uketc] = "****.****.****.****";
$database_[YourDBName] = "[YourUsaualLogin]_[YourDBName]";
$username_[YourDBName] = ""[YourUsaualLogin]_[YourDBUserName]
$password_[YourDBName] = "[YourPassword]";
$Bar163 = mysql_pconnect($hostname_[YourDBName], $username_[YourDBName], $password_[YourDBName]) or trigger_error(mysql_error(),E_USER_ERROR);
?>

This is normally stored in a seperate file and the connection string at the top of everywebpage is something like this:



All of these must be saved with a PHP extension. You need to remeber that you have to create your recordset queries. If you have a program like dreamweaver it is able to do this for you. The top of your page may look like this:


mysql_select_db($database_Bar163, $Bar163);
$query_Thumbnails = "SELECT Images_Video_tbl.Object_TypeID, Images_Video_tbl.Images_Url, Images_Video_tbl.Thumbnails_Url, Images_Video_tbl.Image_Description, Images_Video_tbl.Active, Images_Video_tbl.`Delete` FROM Images_Video_tbl WHERE Images_Video_tbl.Object_TypeID = 'Thumbnail'";
$Thumbnails = mysql_query($query_Thumbnails, $Bar163) or die(mysql_error());
$row_Thumbnails = mysql_fetch_assoc($Thumbnails);
$totalRows_Thumbnails = mysql_num_rows($Thumbnails);
?>


SHAdmin
Posts: 2089
Joined: Sat Dec 18, 2004 11:28 am
Contact:

Post by SHAdmin »

Your 'How To' has been approved and you have been credited 15 points for sharing it.
Post Reply