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…