PHP & MySQL useful snippets

For us, working with database in web development is a mandatory task. Here in this post I’m going to list, as much as I know, PHP & MySQL snippets that I frequently use.

In this post, I frequently mention $connection variable and I assume it contains database connection credentials, like username and password to access database.

Preventing SQL Injection

To protect our database, I must check what our users are inserting into db. I assign what users typed into a variable and I make it secure by calling this function:

$userinput = mysqli_real_escape_string($connection, $userinput);

Or you can use this function:

//Esc sql
function escsql($data){
global $connection;
return mysqli_real_escape_string($connection, $data);
}

SELECT Query

I have a table called “users” and it contains usernames and passwords. If I want to select a user that his username is “habibie”, than I do this:

mysqli_query($connection, "SELECT * FROM users WHERE username= 'habibie' LIMIT 1");

I did limit it “1” because I only have one user with that username.

Check if there any user with that username

I need to check do I have a user with that username. So I do:

$sql = mysqli_query($connection, "SELECT * FROM users WHERE username= 'habibie' LIMIT 1");
if(mysqli_num_rows($sql) > 0){
	echo "I have.";
}else{
	echo "I don't have."
}

Function to create table if not existed

This function takes a string parameter which is the table name that you want to create. It also assumes that you have $connection variable which is a valid mysqli database connection. It will automatically a column with name id as primary key.

//Make table if not exists
function maketable($table){
 global $connection;
 mysqli_query($connection, "CREATE TABLE IF NOT EXISTS $table (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY
 )");
}

Create and add a column to a table if not existed

//Check column and make it if not exist
function makecolumn($columnname, $tablename, $ctype){
global $connection;
if(!mysqli_query($connection, "SELECT $columnname FROM $tablename")){
mysqli_query($connection, "ALTER TABLE $tablename ADD $columnname $ctype");
}
}

$ctype is the column data type, for example VARCHAR(300) NOT NULL or INT(6) NOT NULL, etc.

Get current milliseconds

//Get current millisecond
function getCurrentMillisecond(){
return round(microtime(true) * 1000);
}

Convert any string format to a url friendly string

It will remove any spaces and unwanted symbols in a string to make it url friendly

function urlfriendly($string){
$r = preg_replace('/\W+/', '-', strtolower(trim($string)));
return $r;
}

To be continued…