PHP and SQL: Connect to Database

What the heck is SQL?

SQL stand for structured query language. It is just a syntax created to select, update, delete and insert data into a relational database system, such as MySQL, MariaDB.

Now lets get into the game. Create a folder by the name of sql_basics in our PHP project and then Create a file by the name of insert_data.php, inside the folder.

Now lets look at the piece of code inside the file.

$conn = mysqli_connect('localhost','root','','myfirstdb');

We created a variable by the name of $conn and called a built-in function that comes with PHP known as mysqli_connect. This function does all the hardwork for us of making the conneciton. The function is given 4 values.

• Database Host
• Database User
• Database Password
• Database Name

Now once you run it, it will try to create a connection to the database.

if (!$conn) {
   die("Connection failed: ".mysqli_connect_error());
}

Next, we will check whether a connection has been made or not. Therefore, we create an if function and if it does not return true, then that means there is something wrong. We will kill the connection and throw out the error. Now if no error is produced, the code will run forth.

REMEMBER, IN PHP THE CODE RUNS FROM TOP TO DOWN EXECUTION, MEANING WHAT IS AT THE START OF THE FILE EXECUTES FIRST AND THEN AS IT GOES DOWN, DOES EVERYTHING ELSE EXECUTES

Some Fun
I will now run the following code;

$conn = mysqli_connect('localhost','fakeuser','','myfirstdb');

Look at that, taking Like a Champ!, Now that is how an error would look out, if you read it, it will make sense.

INSERT AN ENTRY INTO THE DATABASE

$sql = "INSERT INTO users (email, password) VALUES ('[e-mail]', 'hello')";

We have just created a variable by the name of $sql and in it all we have created a s string which is called a SQL. The SQL has a build in command, INSERT INTO then we specify the table name into which we want to add the entry. Now the first brackets will contain the columns in which we want to enter the data and then comes the VALUES tag and in the second brackets, we will enter values. The Frist value [e-mail] is being entered into email. The second value hello will go into password column.

ALWAYS REMEMBER THAT THE SINGLE QUOTE IS VERY IMPORTANT FOR THE QUERY TO RUN SUCCESSFUL

$conn->query($sql);

Now this statement simple asks PHP to pass the SQL into the SQL Language and run the query on the database.

DELETE

$sql = "DELETE FROM users WHERE id = '1'";

Delete is relatively simple as it is condition based, so you are going to be deleting based on a condition.

UPDATE

$sql = "UPDATE users SET password = 'hello', name = 'Mr. Yes' WHERE id = '3'";

Update is also understandable not, as you update values, based on a condition.

SELECT

$sql = "SELECT * FROM users WHERE id = '3'";
$result = $conn->query($sql);
$user =  $result->fetch_array();
print_r($user); 

//OUTTPUT
Array ( 
  [id] => 3, 
  [date] => 2019-08-08 14:44:58, 
  [email] => [e-mail], 
  [password] => hello, 
  [name] => Mr. Yes 
)

Now we have selected a user whose ID is 3, therefore we need to store the response from $conn in a $result variable and then fetch it as an array. Now when we print the array, we will get an output like this.

SELECT FOR MORE THAN ONE

$sql = "SELECT * FROM users";
$result = $conn->query($sql);

while ($row = $result->fetch_array()) {
   print_r($row);
}

Now for getting more than one results, we need to encase it into a while loop, which keeps running till it runs out of results.

NOW EVERYTHING TOGETHAR

//Create Connection
$conn = mysqli_connect('localhost','root','','noob_cms');

//CHECK CONNECTION
if (!$conn) {
    die("Connection failed: ".mysqli_connect_error());
}

//Insert
$sql = "INSERT INTO users (email, password, name) VALUES ('[e-mail]', 'test', 'Mr. Test')";

//DELETE
$sql = "DELETE FROM users WHERE id = '1'";

//UPDATE
$sql = "UPDATE users SET password = 'hello', name = 'Mr. Yes' WHERE id = '3'";

//SELECT for Multiple
$sql = "SELECT * FROM users";
$result = $conn->query($sql);
while ($row = $result->fetch_array()) {
   var_dump($row);
}

//SELECT FOR single
$sql = "SELECT * FROM users WHERE id = '3'";
$result = $conn->query($sql);
$user =  $result->fetch_array();
var_dump($user);
echo $user['name']; //Prints Name

Please Feel Free to ask any questions.

Noob Index

Comments

[André Jacques]

Do not forget that SQL is more than just the DML (Data Manipulation Language) but has DDL, DCL, and TCL, plus the possibility to create procedures and functions. In that regard, creating a user through the DML is very discourage and should use CREATE USER and GRANT command instead.

Also, in PHP, I would highly recommend using the PDO package. It helps to escape and protect for SQL Injections attack.

At last, MySQL/MariaDB offers PASSWORD() function to hash your password. So instead of comparing the plain-text password, you hash the password and compare the hashes.

[Muhammad]

Exactly your right, but i want you look at the context here.

Its a NOOB guide.

I just think, this way it easier making concepts from down to up so its possible for everyone.

What do you think?

[André Jacques]

IMHO, I would not introduce mysqli_* function to noobs since they might consider it safe. Furthermore, the PDO package is not that much complicated. The main difference is that you need to wrap it around try...catch.

For a NOOB perspective, this is perfect, but even for a noob I would use PDO and CREATE USER / GRANT command instead. But it is just my 2 cents.

Oh, and after reading back, I realized the user you were creating was in fact in your database and not the mysql inner database. My bad. Maybe just add a script to create your database with your tutorial ;)

[Muhammad]

Wow!.. Now thats why I am talking. Your literally indicating Constructive feedback and that is what I think will make this series better and more vibrant in many ways. I have taken screenshot of your comment, so i can keep it in my notes because believe me or not, i am going to use your feedback as a basis to better this.

I did mention on creating a database in the Understanding Databases tutorial. Do have a look at that and would be anxious for your feedback, maybe thats why i felt, it didnt needed to be mentioned over here.

The fact is, when creating tutorials, i have realized that I as a creator of the tutorial really much accept the fact, that people who are viewing are actually going from the start till where i am now or they have done that which i have written and coming to this. For now, i dont have a solution for this, but I think with people like you involved in the whole scenario this can be solved in one way or the other because in the end a good series is that which enables a person with no background to actually create a basis for which his background is made.

Cheer Man. Thanks for your feedback and Waiting for more.

[Muhammad]

Maybe, we can both come up with a tutorial series togethar, that would be really interesting... I guess