PHP & Mysql

Hi there! just let's go to write a connection a procedural way.

$servername = "localhost";
$username = "username";
$password = "password";
$db = "dbname";
// Create connection
$conn = mysqli_connect($servername, $username, $password,$db);
// Check connection
if (!$conn) {
   die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

We can use PDO too.

$servername = "localhost";
$username = "username";
$password = "password";
$db = "dbname";
try {
   $conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password, []);
   // set the PDO error mode to exception
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   echo "Connected successfully";
} catch(PDOException $e) {
   echo "Connection failed: " . $e->getMessage();
}

Of course!, you have to have installed the extensions to communicate php with mysql.

You can run this command line to see the extension you have installed in your server (localhost).

php -m | grep mysql

The result could be the following.

mysqli
mysqlnd
pdo_mysql

Tip: If you do not see mysqli nor pdo_mysql, you have to install them.

But, let's jump into some pieces of code.

To prevent unexpected results we need to sanitize the input data.

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$mysqli->query("CREATE TEMPORARY TABLE myCity LIKE City");

$city = "'s Hertogenbosch";

/* this query will fail, cause we didn't escape $city */
if (!$mysqli->query("INSERT into myCity (Name) VALUES ('$city')")) {
    printf("Error: %s\n", $mysqli->sqlstate);
}

$city = $mysqli->real_escape_string($city);

/* this query with escaped $city will work */
if ($mysqli->query("INSERT into myCity (Name) VALUES ('$city')")) {
    printf("%d Row inserted.\n", $mysqli->affected_rows);
}

$mysqli->close();

Other tricks we can use is call filter_var() function.

$string = "<h1>Hello World!&nbsp;</h1>";
$resut; = filter_var($string, FILTER_SANITIZE_STRING);
echo $resut; // "Hello World!"

We can use the filter_input() function also.

$name = filter_var($_POST['name'], FILTER_SANITIZE_STRING);
...
$email = filter_var($_POST['email'], FILTER_VALIDATE_EMAIL);
if ( $email === false ) {
 // Handle invalid emails here
 }

Other way to protect the data is using filter_input() function.

$name = filter_input(INPUT_POST, "name", FILTER_SANITIZE_STRING);
$email= filter_input(INPUT_POST, "email", FILTER_VALIDATE_EMAIL);
$search = filter_input(INPUT_GET, "s", FILTER_SANITIZE_STRING);

Other good practice is bind parameters into your queries. It prevent sql injection.

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// prepare and bind
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();

$firstname = "Mary";
$lastname = "Moe";
$email = "mary@example.com";
$stmt->execute();

$firstname = "Julie";
$lastname = "Dooley";
$email = "julie@example.com";
$stmt->execute();

echo "New records created successfully";

$stmt->close();
$conn->close();

This function binds the parameters to the SQL query and tells the database what the parameters are. The "sss" argument, in this example, lists the types of data that the parameters are. The s character tells mysql that the parameter is a string.