Suputnik Web Tricks

PHP — Working with Databases

PHP is a very handy tool to work with the databases. As a programming language, it is very similar to jS, but it has an additional advantage — it is entirely compatible with the HTML. As a matter of fact, rendering of the PHP page can be indistinguishable from the HTML page on the surface.

However, what makes PHP very powerful is the ability to combine the HTML structure with databases. In that sense, we can harvest the power of security and efficiency and add it to our webpage.

The most important element in that sense is to have a functioning database already running on our server. Once we have that, we are ready to use those access data and connect to it. In order to do that, we need to ensure that our web-editor contains all the necessary blocks to access the webpage, insert, update, access, or delete data. Here are the necessary steps:

  1. CONNECTING TO THE DATABASE:
//1. Creating a function to connect to the database:function makeConnection(){//1. preparing credentials$dbHost = "****hostname";$dbUsername = "****username";$dbPassword = "****password";$dbName = "****databse name";//2. sending credentials to the database$connection = mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName);//3. checking the connectionif ($connection == false) {die("Connection failed");}return $connection;}//4. Make the connection by using the function:$conn = makeConnection();

CLOSING THE DATBASE:

function closeConnection($connection){$connection -> close();}

GETTING AN ARRAY FROM THE DATABASE:

function getAssocArray($sqlQuery){//1.make connection$connection = makeConnection();//2.prepare the sql atatement$result = mysqli_query($connection, $sqlQuery);//3. closing the connectioncloseConnection($connection); //alternative: mysqli_close($connection);//4. returning the associated array received during this connectionreturn $result->fetch_all(MYSQLI_ASSOC);}

LOOPING THROUGH THE DATA:

$sql = "SELECT * FROM eployees";
$result = mysqli_query($connection, $sql_statemet);
foreach ($employee as $result ->fetch_all(MYSQLI_ASSOC)){echo $employee['name'];
echo $employee['age'];
}

RECEIVING A QUERY FROM THE DATABASE AND PUTTING IT INTO A VARIABLE:

function sendQuery($sqlQuery){//1.make connection$connection = makeConnection();//2.prepare the sql atatement$result = mysqli_query($connection, $sqlQuery);//3. closing the connectioncloseConnection($connection); //alternative: mysqli_close($connection);//4. returning the associated array received during this connectionreturn $result;}

Combined:

//1. DATA
$dbServername = "servername";
$dbUsername = "username";
$dbPassword = "passss";
$dbName = "ID5464565e";
//2. CONNECTING:
$conn = mysqli_connect($dbServername, $dbUsername, $dbPassword, $dbName);
//3. ERROR CHECK
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
//4. QUERY:
$sql = "SELECT * FROM bets;";
$result = $conn->query($sql);
//5. LOOP
foreach ($result->fetch_all(MYSQLI_ASSOC) as $item) {
echo $item["username"];
}
//6. CLOSING
mysqli_close($conn);

At the same time, we can se the Form option on the HTML to populate our database with new data. Every time we need to send something to the database, we use a <form> tag with the input type “submit” at the end. At the beginning we define that the form has an action to send data to the database we defined via a specific method which can be wither GET (less secure) or POST (more secure):

<form action="./includes/sendData.php" method="GET">//HERE WE ADD DIFFERENT INPUT FIELDS, E.G.:<label for="Age">Age:</label><br/><input type="string" id="userName" name="name" value="" required/>
<input type="number" id="age" name="age" value="" required/>
<br/><br/><input type="submit" value="Create a new profile" id="submitButton"/></form>

After the user press the submit button, we have to go to out file that is linked with the form action that we defined above. In this case, it is sendData.php. There, we will have to define each field from the input as a separate php variable. After we do that, we need to make an SQL query for sending all those data to the database that we created:

We are now in the document “./includes/sendData.php”:

<body><h2><?php //here we define every field as a new variable$firstName = $_GET["userName"]; //variable $firstname comes form the field with the ID "userName"
$age = $_GET["age"];
insertQuery("INSERT INTO users (userName, userAge)VALUES ('$firstname', '$age')");?></h2></body>