 |
 |
 |
 |
 |
 |
 |
 |
 MySQL In This Section:  » How do I setup and administer a MySQL Database? » How do I get started with MySQL through the control panel? » Do you have an overview of SQL? » How to access MySQL Database Remotely (eg:ODBC) » How do I Back Up and Restore MySQL Database? » Do you support Microsoft Access Database?  How do I setup and administer a MySQL Database? Top How do I Get Started? Your first MySQL database can be easily added in your Control Panel by clicking on the "MySQL" button. You can administer that database in the Control Panel under MySQL as well. If you need more that one database, then contact the technicians at the Help Desk (http://support.lainterweb.com) and request that another database be setup on your account. Be sure and include the following information in your request: 1. The following information is needed to create a MySQL Database. 1.Database name 2.Username to use for accessing the database 3.Password (Username and password can be same as login.) Once your additional database is created, from your ssh prompt you type the following command line to access your database: /usr/local/mysql/bin/mysql dbname -u username -ppassword (no space between the -p and the password) You can also access your MySQL Database from the Web using programming languages such as Perl and PHP. PHPAdmin is also a way for your to administer your databases. You can download PHPAdmin in your MySQL portion of your Control Panel as well. How do I get started with MySQL through the control panel? Top The MySQL feature inside your Control Panel is where you manage your database, including designing tables, adding, deleting, and updating records, all from within your web browser. When you first click on the MySQL feature, you will be asked to provide a name for your database and a password, you can use the same username and password that you use for your Control Panel if you so desire. Once the database is created, and you return to this feature inside your Control Panel it will then become the Welcome page for your database. A tree view is on the left. The name of your database and the version of MySQL are displayed to the right of the tree. The Tree The top entry in the tree, "Home", will return you to the Welcome page. Beneath that is your database name and a square with a plus or minus sign in it. Clicking the square will show and hide the names of the tables in the database in the tree. Clicking on the database name in the tree will display the main database management page. Clicking on one of the tables names in the tree will display the properties of that table. The Main Database Management Page This page displays a list of all the tables in your database and the number of records in each. You can also execute an SQL statement, perform advanced queries, and create new tables. The List of Tables Next to each table name are links to various actions you can perform on a table.
Execute an SQL Statement Any SQL statement can be executed on your database by typing it into the textbox labeled "Run SQL query/queries on database" and pressing the "Go" button. Query by example Advanced queries can be built and executed using a graphical interface. Advanced Queries Queries are built by selecting the fields to search on and the criteria to use for the search. The SQL statement that will be executed is displayed in the textbox in the lower right. The statement is updated to reflect the values provided in the rest of the form fields on the page by pressing the "Update Query" button. Execute the statement by pressing on the "Submit Query" button. Each column can be used to specify a field for the SQL statement. Empty columns are ignored. The fields specified in the "Fields" row are combined with criteria below it to create a WHERE clause. If the "Show" checkbox in on then the field is placed in the SELECT clause as well. The query results may be sorted on a field based on the selection in the "Sort" menu. More fields can be added by turning on the "Ins" checkbox below a column or selecting a positive number in the "Add/Delete Field Columns" menu. Fields are deleted by turning on the "Del" checkbox or selecting a negative number in the "Add/Delete Field Columns" menu. Press "Update Query" to update the page to reflect the changes. You may have to scroll your web browser to the right to see all of the field columns. The tables selected in the "Use Tables" listbox form the FROM clause. Also, the fields listed in the "Fields" menus are restricted to the fields in the selected tables. Each criteria should be placed on a separate criteria row. If the "And" radio button is selected for a criteria row, that row will be logically AND'd in the WHERE clause. If the "Or" radio button is selected, that row will be logically OR'd in the WHERE clause. Criteria are not required for any column. If not provided and the "Show" checkbox is on, the field will be shown for all records that match any other criteria. Criteria are added and deleted in a manner similar to adding/deleting fields using the checkboxes to the left of a criteria row or the "Add/Delete Criteria Row" menu. Again, press "Update Query" to update the page. View dump (schema) of database Dumping of the database displays the structure and or data contained in the database. You can then save this information to a file on your local computer for archiving or to aide in the development of your database. The contents and format of the dump are based on the radio button and check box selections you make. See also View dump (schema) of table. The "View Dump (Schema) of Database" section of the Main Database Management page is useful. Pressing the associated Go button will generate a page containing the SQL statements for recreating the database. If the "Structure and Data" radio button is selected, the SQL statements for INSERTing the data will be generated as well. Turn on the. "Add 'DROP TABLE'" checkbox and the SQL statements to DROP the tables will be included also. When you drop a table, the table is deleted. Turning on the "Send" checkbox, causes the generated SQL statements to be sent to you as a file which you can save to your hard disk. The "View Dump(Schema) of Table" section of the Table Properties page allows you to obtain a dump of a single table. The additional radio button, CVS will return the data in the table with each record as a separate line. The fields are delimited by the character specified in the "Terminated by" textbox. The dumped data can be imported into another database or a spreadsheet, or archived for backup. NOTE: None of the selections above will alter your database. Create a new table Create a new table by typing in the name of the table and the number of fields to be in the table and pressing the "Go" button. You will be shown a page which will allow you to set up the data type and attributes of each field. Do you have an overview of SQL? Top Note: MySql is a very powerful database. We do not debug programs that you use/write to access your MySql database. You should have experience programming before you setup a MySql database and attempt to edit/write programs in to access it. SQL stands for Structured Query Language. It is the most common language used for accessing a database. It has been in use for many years by many database vendors. Many consider it the best database language to use. It is used by the MySQL database feature inside your control panel. Without going into the technical details, SQL is a language which consists of a set of commands that you issue to create, make changes to, and retrieve data from a database. These commands can be issued through a Graphical User Interface or by embedding them in a computer program that you write. The MySQL Control Panel provided as part of your account is a GUI that works over the internet through your web browser. This makes it very convenient for administration of web based database applications. Setting up and managing your database will be done through the MySQL Control Panel. To allow access to your database through your web site, you will need to create Common Gateway Interface scripts. These scripts are small computer programs which run on the web hosting server and are activated by clicking on a link or a button in a web page. This will allow users of your web site to interact with your web site in a more meaningful manner. Using CGI scripts and MySQL you can maintain account information on visitors, allow people to search and browse catalogs, and much more. MySQL is an implementation of the SQL language developed by TcX. It is robust, quick, and very flexible. It provides all of the standard SQL datatypes and commands. MySQL is provided as part of your web site account at no additional charge. MySQL is pronounced .My Ess Que Ell.. Detailed documentation, licensing information, and much more can be found at the MySQL web site. Many books are available which describe SQL in detail (see References below). If you plan on doing much database development, it is recommended that you review one or more of these. Please note that LA InterWeb does not provide technical or development support for MySQL applications. MySQL and mSQL Randy Jay Yarge, George Reese, and Tim King O'Reilly & Associates ISBN 1565924347 The Practical SQL Handbook: Using Structured Query Language Judith S. Bowman, Sandra L. Emerson and Marcy Darnovsky Addison-Wesley ISBN 0201626233 Understanding SQL Martin Gruber Sybex ISBN 0895886448 Teach Yourself SQL in 21 Days Ryan K. Stephens (Editor), Ronald R. Plew, Bryan Morgan, jeff Perkins Sams Publishing ISBN 0672311100 Be sure to check for the most current edition. Web Sites The MySQL site has an SQL reference and lots of information about MySQL in particular. An SQL tutorial is available on the net at http://www.geocities.com/SiliconValley/Vista/2207/sql1.html Using PHP with MySQL: http://www.php.net Newsgroups There are various newsgroups under the comp.database group which deal with databases. Always a good place to start. Mailing Lists The MySQL site lists in their documentation page a number of mailing lists concerning MySQL and SQL. ---------------------------------------------------- How do I get started? 1. The following information is needed to create a MySQL Database. 1.Database name 2.Username to use for accessing the database 3.Password (Username and password can be same as login.) Setup your MySQL Database through your control panel. Just click on MySQL. 2.Once it's created, from your ssh prompt you type the following command line to access your database: /usr/local/mysql/bin/mysql dbname -u username -ppassword (no space between the -p and the password) You can also access your MySQL Database from the Web using programming languages such as Perl and PHP. I have installed an external MySql Database and I've gotten it set up now but when I go to the "login" page it asks for the following information: Host: , Username: , Password: what do I input for these? Hostname: localhost (just type in the word: localhost) Username: your username you use to get into your Control Panel Password: your password you use to get into your Control Panel How do I work with a MySQL database using PHP? 1.To merely display the information in your database without the use of a form to call a php script you simply create your HTML document as you would any other web page but instead of the extension of .htm or .html you need to name the file with the extension .phtml. Then within the document itself the section that you'd like to be the PHP code, you begin it with <? and end it with ?>. For instance: <P>These are the products I sell:</P> <TABLE BORDER="1"> <? mysql_connect(localhost, username, password); $result = mysql(mydatabase, "select * from products"); $num = mysql_numrows($result); $i = 0; while($i < $num) { echo "<TR>n"; echo "<TD>n"; echo mysql_result($result,$i,"prodid"); echo "</TD>n<TD>"; echo mysql_result($result,$i,"name"); echo "</TD>n<TD>"; echo mysql_result($result,$i,"price"); echo "</TD>n"; echo "</TR>n"; $i++;} ?> </TABLE> Thus having the loop in the php program create a table with the products listed. NOTE your username and password for the database are not written in the file when it's displayed on the Internet so users viewing the source of your webpage will not see your password. 2.When using a CGI script to pull information from a form which has been submitted by a browser you must have the first line of the script have this command on it (Much like perl scripts): #!/usr/local/bin/php How do I connect my Access DB with MySQL Database? Connecting MS Access to a MySQL database -- You will first need to contact support to request that we give you access to MySQL remotely, e.g., ODBC. ON LOCAL PC: 1) Download and Install MyODBC 2.50.19 (or most current version) on local Win95 machine with MS Access installed --can download at: http://www.mysql.com/download.html 2) Fill in the following settings: Windows DNS Name: You can choose the names, must be unique Server: This is your domain name or IP address MySQL Database Name: The name of your MySQL database User: Your MySQL username Password: Your MySQL password Port: leave blank for default (3306( Options; Select "Return Matching Rows" 3) To Link a Table a) File...Get External Data...Link Tables b) Under 'Files of Type:', select "ODBC Database" c) Select Machine Data Source Tab, and select the appropriate Data Source Name d) Select the tables(s) to link Let us know if you need anything else. How to access MySQL Database Remotely (eg:ODBC) Top Sometimes a user wishes to access their MySQL db remotely (instead of from localhost). To do this, we need to add an IP address to a setting in the MySQL database for remote access for your username. We strongly encourage clients to use a static IP due to security reasons. In order to enable the ODBC connectivity of your a database please put in a Help Desk ticket here requesting to have ODBC setup on your MySQL database, and be sure to include the following details: Database name Username of the database Password for the user Static IP address (This is the IP address of the Computer from which you are accessing the database). How do I Back Up and Restore MySQL Database? Top There are two main ways to back up or restore a MySQL database on our servers: via SSH Telnet access or the phpMyAdmin interface. If you need SSH Telnet access, just send us an email with your request to be enabled for it, and if you'd like more information about how to connect to our servers with it, just visit our SSH Telnet support page. To access the phpMyAdmin interface, click on the "MySQL" button in your account control panel. How to back up a MySQL database using SSH Telnet: Log into your account on our server and issue the following command: [This is all one command line!] /usr/local/mysql/bin/mysqldump $databasename -u $user -p$password > /home/$user/file.dump $ indicates a variable, so instead of entering $user, you'll enter your actual user name for your account. Your mySQL username and password are usually the same as the ones that you use to access your control panel. For example, your actual command might look like this: /usr/local/mysql/bin/mysqldump domain_com -u tommy -ptommy123 > /home/tommy/tommybackup.sql How to restore a mySQL database using SSH Telnet: Log into your account on our server and issue the following command: [This is all one command line!] /usr/local/mysql/bin/mysqldump $databasename -u $user -p$password < /home/user/file.dump How to back up a mySQL database using phpMyAdmin: Click on your database name shown on the upper left. Go to the section called "View dump (schema) of database" and choose options for backing up structure only, structure and data, or adding drop tables. To view what your backup file will look like, click on the "Go" button. When you're ready to send your back up to a file, check the send box and click "Go", then follow the prompts for how to name the file and where to send it. How to restore a mySQL database using phpMyAdmin: Click on your database name shown on the upper left. Locate your back up file for the database that you want to restore. Open it and copy the entire contents. Paste into the field called "Run SQL query/queries on database yourdomain_com" and click the "Go" button. Do you support Microsoft Access Database? Top No. We do offer MySQL database. You may find interest in the Access2MySQL Convertor. Access2MySQL Convertor is an effective application, which allows you to easily convert Microsoft Access (MDB) databases to MySQL and vice versa. This program is extremely easy to use; it is implemented like a common wizard-like application, so even an inexperienced user will be able to work with it. |
|
 |
 |
 |
 |
| Copyright © 2003 LA InterWeb. LA InterWeb, the LA InterWeb logo, and related marks are property of LA InterWeb. All other marks are the property of their respective owners. All rights reserved. Acceptable Use Policy. Privacy Statement. |
