MySQL connection string with PHP and others

A MySQL connection string is a string of parameters and values used to establish a connection between a client application and a MySQL database server. It contains the necessary information to identify the server, authenticate the user, and specify additional connection options.

The exact format of a MySQL connection string can vary depending on the programming language or framework being used. However, it typically includes the following components:

Server/Host: This specifies the hostname or IP address of the machine where the MySQL server is running. For example, localhost or 127.0.0.1.
Port: This indicates the port number on which the MySQL server is listening for incoming connections. The default port for MySQL is 3306, but it can be configured differently.
Database: This specifies the name of the database you want to connect to or perform operations on.
User: This specifies the username used to authenticate with the MySQL server.
Password: This is the password associated with the username for authentication.

Here’s an example of a MySQL connection string in the commonly used format:

Host	`localhost / 127.0.0.1 (depending on language and/or connection method used)`
Port	`3306`
Username	`root`
Password	`root`
Socket	`/Applications/MAMP/tmp/mysql/mysql.sock`

This connection method can be changed by your local network configuration.

PHP

<?php
  $db_host = 'localhost';
  $db_user = 'root';
  $db_password = 'root';
  $db_db = 'information_schema';
 
  $mysqli = @new mysqli(
    $db_host,
    $db_user,
    $db_password,
    $db_db
  );
	
  if ($mysqli->connect_error) {
    echo 'Errno: '.$mysqli->connect_errno;
    echo '<br>';
    echo 'Error: '.$mysqli->connect_error;
    exit();
  }

  echo 'Success: A proper connection to MySQL was made.';
  echo '<br>';
  echo 'Host information: '.$mysqli->host_info;
  echo '<br>';
  echo 'Protocol version: '.$mysqli->protocol_version;

  $mysqli->close();
?>

2. Connect via network
<?php
  $db_host = '127.0.0.1';
  $db_user = 'root';
  $db_password = 'root';
  $db_db = 'information_schema';
  $db_port = 3306;

  $mysqli = new mysqli(
    $db_host,
    $db_user,
    $db_password,
    $db_db,
	$db_port
  );
	
  if ($mysqli->connect_error) {
    echo 'Errno: '.$mysqli->connect_errno;
    echo '<br>';
    echo 'Error: '.$mysqli->connect_error;
    exit();
  }

  echo 'Success: A proper connection to MySQL was made.';
  echo '<br>';
  echo 'Host information: '.$mysqli->host_info;
  echo '<br>';
  echo 'Protocol version: '.$mysqli->protocol_version;

  $mysqli->close();
?>

Python

1. Connect using an UNIX socket (preferred)
#!/usr/bin/env /Applications/MAMP/Library/bin/python

import mysql.connector

config = {
  'user': 'root',
  'password': 'root',
  'host': 'localhost',
  'unix_socket': '/Applications/MAMP/tmp/mysql/mysql.sock',
  'database': 'test',
  'raise_on_warnings': True
}

cnx = mysql.connector.connect(**config)

cursor = cnx.cursor(dictionary=True)

cursor.execute('SELECT `id`, `name` FROM `test`')

results = cursor.fetchall()

for row in results:
  id = row['id']
  title = row['name']
  print '%s | %s' % (id, title)

cnx.close()

2. Connect via network
#!/usr/bin/env /Applications/MAMP/Library/bin/python

import mysql.connector

config = {
  'user': 'root',
  'password': 'root',
  'host': '127.0.0.1',
  'port': 3306,
  'database': 'test',
  'raise_on_warnings': True
}

cnx = mysql.connector.connect(**config)

cursor = cnx.cursor(dictionary=True)

cursor.execute('SELECT `id`, `name` FROM `test`')

results = cursor.fetchall()

for row in results:
  id = row['id']
  title = row['name']
  print '%s | %s' % (id, title)

cnx.close()

Perl

Connect using an UNIX socket (preferred)
#!/Applications/MAMP/Library/bin/perl
use strict;
use warnings;
use DBI;

print "Content-type: text/html\n\n";

my $source = 'DBI:mysql:database=test;host=localhost;mysql_socket=/Applications/MAMP/tmp/mysql/mysql.sock';
my $user = 'root';
my $password = 'root';

my $attr = {
  PrintError => 0, # turn off error reporting via warn()
  RaiseError => 1, # turn on error reporting via die()
};

my $dbc = DBI->connect($source, $user, $password, $attr)
or die "Unable to connect to mysql: $DBI::errstr\n";

my $sql = $dbc->prepare("SELECT `id`, `name` FROM `test`");
my $out = $sql->execute()
or die "Unable to execute sql: $sql->errstr";

while ((my $id, my $name) = $sql->fetchrow_array()) {
  print "id: $id / name: $name<br>\n";
}

$dbc->disconnect();

Connect via network
#!/Applications/MAMP/Library/bin/perl
use strict;
use warnings;
use DBI;

print "Content-type: text/html\n\n";

my $source = 'DBI:mysql:database=test;host=localhost;port=3306';
my $user = 'root';
my $password = 'root';

my $attr = {
  PrintError => 0, # turn off error reporting via warn()
  RaiseError => 1, # turn on error reporting via die()
};

my $dbc = DBI->connect($source, $user, $password, $attr)
or die "Unable to connect to mysql: $DBI::errstr\n";

my $sql = $dbc->prepare("SELECT `id`, `name` FROM `test`");
my $out = $sql->execute()
or die "Unable to execute sql: $sql->errstr";

while ((my $id, my $name) = $sql->fetchrow_array()) {
  print "id: $id / name: $name<br>\n";
}

$dbc->disconnect();

Ruby

Connect using an UNIX socket (preferred)
#!/Applications/MAMP/Library/bin/ruby

require "mysql2"

@db_host = "localhost"
@db_socket = "/Applications/MAMP/tmp/mysql/mysql.sock"
@db_user = "root"
@db_pass = "root"
@db_name = "test"

client = Mysql2::Client.new(
  :host => @db_host,
  :socket => @db_socket,
  :username => @db_user,
  :password => @db_pass,
  :database => @db_name
)

result = client.query("SELECT * from `test`")

result.each do |row|
  puts row["id"].to_s() + " | " + row["name"].to_s()
end

client.close

Connect via network
#!/Applications/MAMP/Library/bin/ruby

require "mysql2"

@db_host = "localhost"
@db_port = 3306
@db_user = "root"
@db_pass = "root"
@db_name = "test"

client = Mysql2::Client.new(
  :host => @db_host,
  :port => @db_port,
  :username => @db_user,
  :password => @db_pass,
  :database => @db_name
)

result = client.query("SELECT * from `test`")

result.each do |row|
  puts row["id"].to_s() + " | " + row["name"].to_s()
end

client.close

MySQLi

<?php
  $db = new SQLite3('/Applications/MAMP/db/sqlite/mydb.db');
  $db->exec("CREATE TABLE items(id INTEGER PRIMARY KEY, name TEXT)");
  $db->exec("INSERT INTO items(name) VALUES('Name 1')");
  $db->exec("INSERT INTO items(name) VALUES('Name 2')");

  $last_row_id = $db->lastInsertRowID();

  echo 'The last inserted row ID is '.$last_row_id.'.';

  $result = $db->query('SELECT * FROM items');

  while ($row = $result->fetchArray()) {
    echo '<br>';
    echo 'id: '.$row['id'].' / name: '.$row['name'];
  }

  $db->exec('DELETE FROM items');

  $changes = $db->changes();

  echo '<br>';
  echo 'The DELETE statement removed '.$changes.' rows.';
?>

Note that all connections string above are using MAMP Pro for test. To learn more about MAMP or MAMP Pro, please click here. The actual syntax and format of the connection string may vary depending on the programming language or framework you are using.

PHP

Python

Perl

Ruby

MySQLi

Comments

Leave a Reply Cancel reply