23) How to use MySQL in Proline???

This is a simple tutorial on how to use mySQL commands in Proline.

a) Firstly, log in to the server using putty.exe in Windows or SSH in BioSLAX using your username and password. (See post 2 if you have forgotten how to login through BioSLAX)


b) Next, log in to mysql (it may be a different username and password). See the screenshot below. You will directly enter the database denvdb.



c) Before proceeding, type "\h"(help) to take note of important commands required for navigation through mySQL. See the screenshot below:



Some important commands are:

1) \g, \G : type either of these commands to send an SQL query to the server. \G displays results vertically.

Example: \g SELECT * FROM DENV (display all data in the table DENV). Note that you need to enter the same command twice. See the screenshot below:





2) \c : Clear cache. After an SQL query, use this command if you want to enter another query.


3) \q: Quit mySQL to log back to proline.



Notes:

For uploading a table with data to mySQL, you can use the method on post 4. Otherwise, you can create an empty table with just your fields in the columns using the CREATE keyword. Note that there are many keywords to use in mySQL. You can refer to here for more tutorials on mySQL.



Two keywords that may be important are:

a) Syntax for changing the data types of a column:

ALTER TABLE table_name MODIFY column_name type

Example: \g ALTER TABLE Registration MODIFY contactno INT(10)


b) Syntax for showing column fields, data type, field attributes, primary key and other information in a table:

SHOW columns from table_name

Example: \g SHOW columns from DENV