posts connecting to mysql
Posted on: September 14, 2017Updated: January 5, 2018
How to connect to MYSQL database?
This tutorial will contain information about how to connect to MYSQL database using different programming languages and different techniques. In the first part, we will take a look at PHP mysqli, both procedural and object-oriented programming(OOP) methods and PDO(OOP only) and its prepared statements. In the second part, we'll discuss 2 Python modules that you can use to make a connection. The final part will be about how you can connect manually to database using shell, specifically bourne again shell(BASH), and also how to make it work using a shell script.
First thing first, a table needs to be created first for this tutorial. The easiest way is through phpmyadmin. Just select a database, click on SQL tab and paste the following:
CREATE TABLE `test` (
`id` int (11 ) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`column1` varchar (100 ) NOT NULL ,
`column2` varchar (100 ) NOT NULL ,
`column3` varchar (100 ) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
After clicking GO at the bottom right corner, you are good to go and should now be able to see the structure.
PART 1 - Using PHP(MYSQLI, PDO)
I know you can still use mysql to connect to database but this is not safe anymore. The function has been abandoned a while ago and replaced by mysqli so we'll start with this particular method.
<?php
// mysqli PROCEDURAL
// Make sure to change the example values
$dbhost = 'localhost' ;
$dbuser = 'db_example_user' ;
$dbpass = 'db_example_pass' ;
$dbname = 'db_example_name' ;
// It always has to be in this order when using mysqli_connect() function
// Use mysqli_error() only in an offline development mode to see the errors. Remove the function when the project goes live for security reasons
$mysqli = mysqli_connect($dbhost , $dbuser , $dbpass , $dbname ) or die ("Connection error: " .mysqli_error($mysqli ));
// To test the connection, we are going to insert a row into the newly created table
// A button will be created below that will activate the following condition
if (isset ($_POST['add' ])) {
$var1 = 'test1' ;
$var2 = 'test2' ;
$var3 = 'test3' ;
// inserting into table test $var1 into the 1st column, $var2 into the 2nd and $var3 into the third
mysqli_query($mysqli , "INSERT INTO test (column1, column2, column3) VALUES (' $var1 ', ' $var2 ', ' $var3 ')" );
$display = "Variables successfuly added!" ;
?>
<!-- Create html form, empty action means it will be executed in the same file. -->
<form action="" method="post" ><input type="submit" name="add" value="Add" /></form>
<?php echo $display ; ?>
What you should do is to save the above code into a file and then access file via browser and click on the button. It should add a new row to the above created MYSQL table. Same can be done with the code below.
<?php
// mysqli OBJECT-ORIENTED
// Make sure to change the example values
$dbhost = 'localhost' ;
$dbuser = 'db_example_user' ;
$dbpass = 'db_example_pass' ;
$dbname = 'db_example_name' ;
$mysqli = new mysqli($dbhost , $dbuser , $dbpass , $dbname ) or die ('Connection Error (' .mysqli_connect_errno().' )' .mysqli_connect_error());
if (isset ($_POST['add' ])) {
$var4 = 'test4' ;
$var5 = 'test5' ;
$var6 = 'test6' ;
// Calling the queries
$mysqli ->query("INSERT INTO test (column1, column2, column3) VALUES (' $var4 ', ' $var5 ', ' $var6 ')" );
$display = "Variables successfuly added!" ;
}
?>
<form action="" method="post" ><input type="submit" name="add" value="Add" /></form>
<?php echo $display ; ?>
One thing to note is that we are inserting defined variables into database which is safe but anything using $_POST or $_GET methods to store user input would have to be sanitized. This particular topic will be discussed in some other post. Alternatively, to avoid making mistakes, you could use PDO's prepared statements.
<?php
// PDO, OBJECT-ORIENTED, prepared statements when inserting into database
// Make sure to change the example values
$dbhost = 'localhost' ;
$dbuser = 'db_example_user' ;
$dbpass = 'db_example_pass' ;
$dbname = 'db_example_name' ;
$pdo = new PDO("mysql:host= $dbhost ;dbname= $dbname " , $dbuser , $dbpass );
// Set error mode
$pdo ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
if (isset ($_POST['add' ])) {
try {
$var7 = 'test7' ;
$var8 = 'test8' ;
$var9 = 'test9' ;
// Preparing statement and binding parameters
$sql = $pdo ->prepare("INSERT INTO test (column1, column2, column3) VALUES (:column1, :column2, :column3)" );
$sql ->bindParam(':column1' , $var7 );
$sql ->bindParam(':column2' , $var8 );
$sql ->bindParam(':column3' , $var9 );
// Executing the prepared statement, this will add a row into the table.
$sql ->execute();
$display = "Variables successfuly added!" ;
}
catch (PDOException $error ) {echo $error ->getMessage();}
}
?>
<form action="" method="post" ><input type="submit" name="add" value="Add" /></form>
<?php echo $display ; ?>
PART 2 - Using Python
There are two widespread Python modules that you should be able to use for a database connection on most of the servers. First is MySQLdb that unfortunately only works for Python 2x. The second, however, does support both Python 2x and Python 3x and is exclusively written in Python. Note that you'll need to install the modules beforehand, otherwise you'll get import error.
# MySQLdb module
# import the module
import MySQLdb
# Make sure to change these
dbhost = 'localhost'
dbuser = 'db_example_user'
dbpass = 'db_example_pass'
dbname = 'db_example_name'
# Connect to sql
sql = MySQLdb.connect(dbhost, dbuser, dbpass, dbname)
# Set a cursor
cur = sql.cursor()
var10 = 'test10'
var11 = 'test11'
var12 = 'test12'
# Execute the statement
cur.execute("INSERT INTO test (column1, column2, column3) VALUES (%s, %s, %s)" , (var10, var11, var12))
# And save changes to the database
sql.commit()
All that we need now is to execute the python script(I'll name it sqldb.py)
python sqldb.py
It's the same procedure with PyMysql module. All you really need to do is to replace the import module and database connection.
# PyMysql module
# import the module
import pymysql
# Make sure to change these
dbhost = 'localhost'
dbuser = 'db_example_user'
dbpass = 'db_example_pass'
dbname = 'db_example_name'
# Connect to sql
sql = pymysql.connect(dbhost, dbuser, dbpass, dbname)
# Set a cursor
cur = sql.cursor()
var13 = 'test13'
var14 = 'test14'
var15 = 'test15'
# Execute the statement
cur.execute("INSERT INTO test (column1, column2, column3) VALUES (%s, %s, %s)" , (var13, var14, var15))
# And save changes to the database
sql.commit()
PART 3 - Using Shell(BASH)
In this part, we will take a look at how to use shell to access MYSQL account, list databases, use our test database and insert a row into the test table that we created above. Later on, we will also insert a row into MYSQL using shell script.
So open up a shell/terminal and type in:
mysql -u yourmysqluser -p
It will prompt you for a password. Alternatively, you could add password in there but note that there is no space between -p and password like there is between -u and username. Also note that this is not a safe method since the password will be displayed in plain text.
Next we will list all databases:
mysql> show databases;
It will display all the account's databases. We will use the database created above:
mysql> use test;
To check if we have the right database selected:
mysql> select database();
It should display one row with our database in it. So now we can insert a new row into the table:
mysql> INSERT INTO test (column1, column2, column3) VALUES ('test16', 'test17', 'test18');
Sure that's just an example that we had set to accomplish but for more stuff using shell on MYSQL, you would have to check the command line manual. But right now, we will take a look at how to access MYSQL and insert a row into our table using shell script.
#!/bin/bash
# Make sure to change these
dbhost='localhost'
dbuser='db_example_user'
dbpass='db_example_pass'
dbname='db_example_name'
var19='test19'
var20='test20'
var21='test21'
// One-liner to insert variables into database.
echo "INSERT INTO test (column1, column2, column3) VALUES (' $var19', ' $var20', ' $var21')" | mysql -u $dbuser -p$dbpass -h $dbhost $dbname
That's it. All we now need to do is to make the script(I'll name it insertshell) executable(chmod +x /path/to/script), go to the folder where the script is located and execute it:
./insertshell
or
bash insertshell
