CRUD in Vue JS and PHP

In this tutorial, we are going to perform a complete CRUD (create, read, update, delete) operation using Vue JS and PHP. We will be using Vue JS for the frontend and PHP for backend processing. We will also be using PDO prepared statements for preventing SQL injection.

First, you need to download Vue JS from here and Bootstrap from here. You will also need to download jQuery from here as well. After that, you need to copy-paste the JS file from Vue JS in your project. You also need to copy-paste the CSS and JS files from Bootstrap too.

After downloading and placing in your project, you need to include these files as well.

<script src="vue.min.js"></script>

<link rel="stylesheet" type="text/css" href="css/bootstrap.css" />
<script src="js/jquery-3.3.1.min.js"></script>
<script src="js/bootstrap.js"></script>

Create

First thing is that you need to insert the data into the database. For the sake of this tutorial, we have created a simple table in our MySQL database. The table name is users and it has the following columns:

  1. id (auto increment)
  2. name
  3. email
  4. password

Then you need to create a form from which you can enter the details to save in the database.

<div id="myApp">
	<div class="container">
		<h1 class="text-center">Create</h1>

		<div class="row">
			<div class="offset-md-3 col-md-6">
				<form method="POST" action="create.php" v-on:submit.prevent="doCreate">
					<div class="form-group">
						<label>Name</label>
						<input type="text" name="name" class="form-control" />
					</div>

					<div class="form-group">
						<label>Email</label>
						<input type="email" name="email" class="form-control" />
					</div>

					<div class="form-group">
						<label>Password</label>
						<input type="password" name="password" class="form-control" />
					</div>

					<input type="submit" value="Create User" class="btn btn-primary" />
				</form>
			</div>
		</div>
	</div>
</div>

After that, we need to create a Vue JS app and a method that will call an AJAX request to the server.

<script>
	// initialize Vue JS
	const myApp = new Vue({
		el: "#myApp",
		methods: {

			doCreate: function () {
				const self = this;
				const form = event.target;

				const ajax = new XMLHttpRequest();
				ajax.open("POST", form.getAttribute("action"), true);

				ajax.onreadystatechange = function () {
					if (this.readyState == 4) {
						if (this.status == 200) {
							console.log(this.responseText);
						}
					}
				};

				const formData = new FormData(form);
				ajax.send(formData);
			}
		},
	});
</script>

Now create a file named create.php that will handle the request.

<?php

// connect database
$conn = new PDO("mysql:host=localhost:8889;dbname=test", "root", "root");

// prepare insert statement
$sql = "INSERT INTO users (name, email, password) VALUES (:name, :email, :password)";
$result = $conn->prepare($sql);

// execute the query
$result->execute([
	":name" => $_POST["name"],
	":email" => $_POST["email"],
	// encrypt password in hash
	":password" => password_hash($_POST["password"], PASSWORD_DEFAULT),
]);

echo "Done";

Refresh the page now and enter new user details and hit submit. Then go to your phpMyAdmin and refresh the users table and you will see a new row inserted in the database.

Read

Now the data is being inserted in the database but you should all the inserted data when the page loads. To create an HTML table:

<h1 class="text-center">Read</h1>

<table class="table">
	<tr>
		<th>ID</th>
		<th>Name</th>
		<th>Email</th>
	</tr>

	<tr v-for="(user, index) in users">
		<td v-text="user.id"></td>
		<td v-text="user.name"></td>
		<td v-text="user.email"></td>
	</tr>
</table>

In your Vue JS instance, create a data object, and inside that object create a users array.

data: {
	users: []
},

And when the Vue JS instance is mounted, we need to call a method to call an AJAX request to get the data.

// call an AJAX to fetch data when Vue JS is mounted
mounted: function () {
	this.getData();
}

After that, create a method in your methods object in the Vue JS instance:

// get all users from database
getData: function () {
	const self = this;

	const ajax = new XMLHttpRequest();
	ajax.open("POST", "read.php", true);

	ajax.onreadystatechange = function () {
		if (this.readyState == 4) {
			if (this.status == 200) {
				const users = JSON.parse(this.responseText);
				self.users = users;
			}
		}
	};

	const formData = new FormData();
	ajax.send(formData);
},

This will send an AJAX request but we need to create a server file that will handle this request. So create a file named read.php and the following will be the code of this file:

<?php

// connect database
$conn = new PDO("mysql:host=localhost:8889;dbname=test", "root", "root");

// get all users from database sorted by latest first
$sql = "SELECT * FROM users ORDER BY id DESC";
$result = $conn->prepare($sql);
$result->execute([]);
$data = $result->fetchAll();

// send all records fetched back to AJAX
echo json_encode($data);

Refresh the page now and you will be able to view all the records added to the database. But if you insert the new record again, you again have to refresh the page to see this new entry. However, newly inserted records should automatically be prepended at the top of the table.

So you need to modify your create.php and first return the newly inserted record from the database.

// get the latest record inserted
$sql = "SELECT * FROM users WHERE id = :id";
$result = $conn->prepare($sql);
$result->execute(array(
    ":id" => $conn->lastInsertId()
));
$data = $result->fetch();

// send the newly inserted record back to AJAX
echo json_encode($data);

Then in your Vue JS instance inside the doCreate method when the response is successfully received, prepend the new user in the users array.

const user = JSON.parse(this.responseText);

// prepend in local array
self.users.unshift(user);

Refresh the page now and try to insert a new user again. Now you will see that it will be prepended at the top automatically.

Update

To update the user we first must show a button to edit the user. To create a new column in your table:

<th>Actions</th>

And inside the v-for loop create a button for edit.

<td>
	<button type="button" v-bind:data-id="user.id" v-on:click="showEditUserModal" class="btn btn-primary">Edit</button>
</td>

Then you need to create a method in the methods object of your Vue JS instance that will be called when this button is clicked.

showEditUserModal: function () {
	const id = event.target.getAttribute("data-id");
	
	// get user from local array and save in current object
	for (var a = 0; a < this.users.length; a++) {
		if (this.users[a].id == id) {
			this.user = this.users[a];
			break;
		}
	}

	$("#editUserModal").modal("show");
},

Then you need to create another variable in your data object that will hold the information of the selected user.

user: null

The above-created function will display a Bootstrap modal to edit the user. Now we need to create that model in our HTML.

<!-- Modal -->
<div class="modal" id="editUserModal">
	<div class="modal-dialog" role="document">
		<div class="modal-content">
			<div class="modal-header">
				<h5 class="modal-title">Edit User</h5>
				<button type="button" class="close" data-dismiss="modal" aria-label="Close">
					<span aria-hidden="true">&times;</span>
				</button>
			</div>
			
			<div class="modal-body">
				<form method="POST" action="update.php" v-on:submit.prevent="doUpdate" id="form-edit-user" v-if="user != null">
					<input type="hidden" name="id" v-bind:value="user.id" />

					<div class="form-group">
						<label>Name</label>
						<input type="text" name="name" v-bind:value="user.name" class="form-control" />
					</div>

					<div class="form-group">
						<label>Email</label>
						<input type="email" name="email" v-bind:value="user.email" class="form-control" />
					</div>
				</form>
			</div>

			<div class="modal-footer">
				<button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
				<button type="submit" name="submit" class="btn btn-primary" form="form-edit-user">Save changes</button>
			</div>
		</div>
	</div>
</div>

When the edit form is submitted, it called a Javascript function to call an AJAX request to the server. So we need to create that method in our Vue JS instance methods object.

// update the user
doUpdate: function () {
	const self = this;
	const form = event.target;

	const ajax = new XMLHttpRequest();
	ajax.open("POST", form.getAttribute("action"), true);

	ajax.onreadystatechange = function () {
		if (this.readyState == 4) {
			if (this.status == 200) {

				const user = JSON.parse(this.responseText);

				// update in local array
				// get index from local array
				var index = -1;
				for (var a = 0; a < self.users.length; a++) {
					if (self.users[a].id == user.id) {
						index = a;
						break;
					}
				}

				// create temporary array
				const tempUsers = self.users;

				// update in local temporary array
				tempUsers[index] = user;

				// update the local array by removing all old elements and inserting the updated users
				self.users = [];
				self.users = tempUsers;
			}
		}
	};

	const formData = new FormData(form);
	ajax.send(formData);

	// hide the modal
	$("#editUserModal").modal("hide");
},

Now we need to create a new file called update.php that will handle this AJAX request and will update the data in database.

<?php

// connect database
$conn = new PDO("mysql:host=localhost:8889;dbname=test", "root", "root");

// update user name and email using his unique ID
$sql = "UPDATE users SET name = :name, email = :email WHERE id = :id";
$result = $conn->prepare($sql);

$result->execute([
	":name" => $_POST["name"],
	":email" => $_POST["email"],
	":id" => $_POST["id"],
]);

// get the updated record
$sql = "SELECT * FROM users WHERE id = :id";
$result = $conn->prepare($sql);
$result->execute(array(
    ":id" => $_POST["id"]
));
$data = $result->fetch();

// send the updated record back to AJAX
echo json_encode($data);

Refresh the page now, and you will see an “Edit” button with each row. On click, you will see a modal to update the data. Once submitted, the data will be updated in the database, the bootstrap modal will be closed and you will also see the data updated in the HTML table too.

Delete

To complete the CRUD operation in Vue JS and PHP. The final step is to create a “Delete” button. In front of each edit button, we need to create another button that will delete the user from the database and from the local array as well.

<form method="POST" action="delete.php" v-on:submit.prevent="doDelete" style="display: contents;">
	<input type="hidden" name="id" v-bind:value="user.id" />
	<input type="submit" name="submit" class="btn btn-danger" value="Delete" />
</form>

Then we need to create a method in Vue JS that will call an AJAX request to delete the user.

// delete user
doDelete: function () {
	const self = this;
	const form = event.target;

	const ajax = new XMLHttpRequest();
	ajax.open("POST", form.getAttribute("action"), true);

	ajax.onreadystatechange = function () {
		if (this.readyState == 4) {
			if (this.status == 200) {
				
				// remove from local array
				for (var a = 0; a < self.users.length; a++) {
					if (self.users[a].id == form.id.value) {
						self.users.splice(a, 1);
						break;
					}
				}
			}
		}
	};

	const formData = new FormData(form);
	ajax.send(formData);
},

Lastly, we need to create a file named delete.php that will handle this request and will actually delete the user from the database.

<?php

// connect database
$conn = new PDO("mysql:host=localhost:8889;dbname=test", "root", "root");

// delete the user from database
$sql = "DELETE FROM users WHERE id = :id";
$result = $conn->prepare($sql);
$result->execute(array(
    ":id" => $_POST["id"]
));

// send the response back to AJAX
echo "Done";

Refresh the page now and you will see a “Delete” button too with each row. On clicking, will delete that record from the database, and also it will delete the HTML table row as well.

Congratulations! You just completed your CRUD operation in Vue JS and PHP.

Download CRUD in Vue JS and PHP source code:

[wpdm_package id=’1511′]

Create a single page application in MEVN stack

Free tutorial

Instagram feed without API – PHP, MySQL

In this tutorial, we are going to teach you how can you show instagram feed in your PHP website without using any API.

Video tutorial:

First, we need to create a table in our database where we will save all the latest posts from our Instagram page or profile.

<?php

    // connect with database
    $conn = new PDO("mysql:host=localhost:8889;dbname=test", "root", "root");

    // create table to store latest instagram feeds
    $sql = "CREATE TABLE IF NOT EXISTS instagram (
        id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
        url TEXT DEFAULT NULL,
        image_path TEXT DEFAULT NULL,
        video_path TEXT DEFAULT NULL,
        caption TEXT DEFAULT NULL,
        likes TEXT DEFAULT NULL,
        comments TEXT DEFAULT NULL
    )";
    $result = $conn->prepare($sql);
    $result->execute();

    [handle form submission]

?>

[form goes here]

This will create a table named “Instagram” in your database named “test”. The table will have the following columns:

  1. id (primary key)
  2. URL shortcode of Instagram post
  3. Path of the image stored in your server
  4. Path of video stored in your server
  5. Caption (if any) of post
  6. Number of likes on a post
  7. Number of comments on a post

Saving Instagram Feed in MySQL using PHP

Now you need to show a form that will show a link that you need to open in your browser. That link will output the JSON string, you need to copy-paste that JSON string into a textbox in the form.

The following code goes in the [form goes here] section:

<form method="POST" action="index.php">
    <p>
        <a href="https://www.instagram.com/{your_page_name}/?__a=1">Goto this link and paste the JSON string in textbox below</a>
    </p>

    <p>
        <label>Paste JSON</label>
        <textarea name="json" rows="10" cols="30" required></textarea>
    </p>

    <input type="submit" name="submit" class="Save" />
</form>

[show instagram feed]

Refresh the page and you will see a link, a textbox, and a submit button. Click on the link and you will see a JSON string, paste that JSON in the textbox and hit submit. Now we need to write the code to save that JSON in our database.

The following code goes in the [handle form submission] section:

// check if form is submitted
if (isset($_POST["submit"]))
{
    // get JSON from textarea
    $json = $_POST["json"];

    // decode JSON into arrays and objects
    $content = json_decode($json);

    // get all the latest posts
    $edges = $content->graphql->user->edge_owner_to_timeline_media->edges;

    mkdir("instagram-media");

    // delete previous posts from our database
    $sql = "DELETE FROM instagram";
    $result = $conn->prepare($sql);
    $result->execute();

    // loop through all posts
    foreach ($edges as $edge)
    {
        // get single post
        $node = $edge->node;

        // get URL shortcode of post
        $url = $node->shortcode;

        // get caption, if any
        $caption = $node->edge_media_to_caption->edges[0]->node->text;

        // get number of likes
        $likes = $node->edge_liked_by->count;

        // get total number of comments
        $comments = $node->edge_media_to_comment->count;

        // save image in our server if uploaded
        $image_path = "";
        if (!is_null($node->display_url))
        {
            $image_path = "instagram-media/" . $url . ".png";
            file_put_contents($image_path, file_get_contents($node->display_url));
        }

        // save video in our server if uploaded
        $video_path = "";
        if (!is_null($node->video_url))
        {
            $video_path = "instagram-media/" . $url . ".mp4";
            file_put_contents($video_path, file_get_contents($node->video_url));
        }

        // insert in database
        $sql = "INSERT INTO instagram(url, image_path, video_path, caption, likes, comments) VALUES (:url, :image_path, :video_path, :caption, :likes, :comments)";
        $result = $conn->prepare($sql);
        $result->execute([
            ":url" => $url,
            ":image_path" => $image_path,
            ":video_path" => $video_path,
            ":caption" => $caption,
            ":likes" => $likes,
            ":comments" => $comments
        ]);
    }

    echo "<p>Done</p>";
}

[fetch all instagram feeds from database]

This will fetch the latest posts from your instagram page or profile. If there is any image or video attached to it, it will be saved in a folder named “instagram-media”. And it’s path will be stored in the database. If all goes well, it will display a message “Done”.

Fetching Instagram Feed from MySQL via PHP

Now we can easily fetch all latest instagram feeds from our database. Replace the section [fetch all instagram feeds from database] with the following code:

// get all posts from database
$sql = "SELECT * FROM instagram ORDER BY id ASC";
$result = $conn->query($sql);
$instagram_feed = $result->fetchAll();

Finally, we need to show them in our HTML. Replace the section [show instagram feed] with the following code:

<main>
    <div class="container">
        <div class="gallery">

            <!-- loop through all rows from database -->
            <?php foreach ($instagram_feed as $feed): ?>
                <div class="gallery-item" tabindex="0">
                    <!-- wrap with anchor tag, when clicked will go to instagram detail post page -->
                    <a href="https://www.instagram.com/p/<?php echo $feed['url']; ?>" target="_blank" style="color: white;">

                        <!-- thumbnail of post -->
                        <img src="<?php echo $feed['image_path']; ?>" class="gallery-image" />

                        <div class="gallery-item-info">
                            <ul>
                                <!-- show no. of likes -->
                                <li class="gallery-item-likes">
                                    <i class="fa fa-heart"></i>
                                    <?php echo $feed["likes"]; ?>
                                </li>
                                
                                <!-- show no. of comments -->
                                <li class="gallery-item-comments">
                                    <i class="fa fa-comment"></i>
                                    <?php echo $feed["comments"]; ?>
                                </li>
                            </ul>

                            <!-- show caption -->
                            <p><?php echo $feed["caption"]; ?></p>
                        </div>
                    </a>
                </div>
            <?php endforeach; ?>

        </div>
    </div>
</main>

<!-- style CSS -->
<link rel="stylesheet" type="text/css" href="instagram.css?v=<?php echo time(); ?>" />

<!-- font awesome -->
<script src="https://use.fontawesome.com/b8680c3f3d.js"></script>

Comments has been added with each line for explanation. Each post will be wrapped in an anchor tag, so when you clicked on it, you will be redirected to the post’s page on instagram.

Styles

Last thing you are going to need is CSS styles. So create a file named “instagram.css” and paste the following code in it:

/*

All grid code is placed in a 'supports' rule (feature query) at the bottom of the CSS (Line 310). 
        
The 'supports' rule will only run if your browser supports CSS grid.

Flexbox and floats are used as a fallback so that browsers which don't support grid will still recieve a similar layout.

*/

/* Base Styles */

*,
*::before,
*::after {
    box-sizing: border-box;
}

body {
    font-family: "Open Sans", Arial, sans-serif;
    min-height: 100vh;
    background-color: #fafafa;
    color: #262626;
    padding-bottom: 3rem;
}

img {
    display: block;
}

.container {
    max-width: 93.5rem;
    margin: 0 auto;
    padding: 0 2rem;
}

.btn {
    display: inline-block;
    font: inherit;
    background: none;
    border: none;
    color: inherit;
    padding: 0;
    cursor: pointer;
}

.btn:focus {
    outline: 0.5rem auto #4d90fe;
}

.visually-hidden {
    position: absolute !important;
    height: 1px;
    width: 1px;
    overflow: hidden;
    clip: rect(1px, 1px, 1px, 1px);
}

/* Profile Section */

.profile {
    padding: 5rem 0;
}

.profile::after {
    content: "";
    display: block;
    clear: both;
}

.profile-image {
    float: left;
    width: calc(33.333% - 1rem);
    display: flex;
    justify-content: center;
    align-items: center;
    margin-right: 3rem;
}

.profile-image img {
    border-radius: 50%;
}

.profile-user-settings,
.profile-stats,
.profile-bio {
    float: left;
    width: calc(66.666% - 2rem);
}

.profile-user-settings {
    margin-top: 1.1rem;
}

.profile-user-name {
    display: inline-block;
    font-size: 3.2rem;
    font-weight: 300;
}

.profile-edit-btn {
    font-size: 1.4rem;
    line-height: 1.8;
    border: 0.1rem solid #dbdbdb;
    border-radius: 0.3rem;
    padding: 0 2.4rem;
    margin-left: 2rem;
}

.profile-settings-btn {
    font-size: 2rem;
    margin-left: 1rem;
}

.profile-stats {
    margin-top: 2.3rem;
}

.profile-stats li {
    display: inline-block;
    font-size: 1.6rem;
    line-height: 1.5;
    margin-right: 4rem;
    cursor: pointer;
}

.profile-stats li:last-of-type {
    margin-right: 0;
}

.profile-bio {
    font-size: 1.6rem;
    font-weight: 400;
    line-height: 1.5;
    margin-top: 2.3rem;
}

.profile-real-name,
.profile-stat-count,
.profile-edit-btn {
    font-weight: 600;
}

/* Gallery Section */

.gallery {
    display: flex;
    flex-wrap: wrap;
    margin: -1rem -1rem;
    padding-bottom: 3rem;
}

.gallery-item {
    position: relative;
    flex: 1 0 22rem;
    margin: 1rem;
    color: #fff;
    cursor: pointer;
}

.gallery-item:hover .gallery-item-info,
.gallery-item:focus .gallery-item-info {
    display: inline-block;
    justify-content: center;
    align-items: center;
    position: absolute;
    top: 0;
    width: 100%;
    height: 100%;
    background-color: rgba(0, 0, 0, 0.3);
}
.gallery-item-info ul {
    text-align: center;
    position: relative;
    top: 50%;
    transform: translateY(-50%);
    padding-left: 0px;
    margin-top: 0px;
    margin-bottom: 0px;
}
.gallery-item-info p {
    position: relative;
    top: 50%;
    text-align: center;
    margin-top: 0px;
    padding-left: 10px;
    padding-right: 10px;
}

.gallery-item-info {
    display: none;
}

.gallery-item-info li {
    display: inline-block;
    font-size: 1.7rem;
    font-weight: 600;
}

.gallery-item-likes {
    margin-right: 2.2rem;
}

.gallery-item-type {
    position: absolute;
    top: 1rem;
    right: 1rem;
    font-size: 2.5rem;
    text-shadow: 0.2rem 0.2rem 0.2rem rgba(0, 0, 0, 0.1);
}

.fa-clone,
.fa-comment {
    transform: rotateY(180deg);
}

.gallery-image {
    width: 100%;
    height: 100%;
    object-fit: cover;
}

/* Media Query */

@media screen and (max-width: 40rem) {
    .profile {
        display: flex;
        flex-wrap: wrap;
        padding: 4rem 0;
    }

    .profile::after {
        display: none;
    }

    .profile-image,
    .profile-user-settings,
    .profile-bio,
    .profile-stats {
        float: none;
        width: auto;
    }

    .profile-image img {
        width: 7.7rem;
    }

    .profile-user-settings {
        flex-basis: calc(100% - 10.7rem);
        display: flex;
        flex-wrap: wrap;
        margin-top: 1rem;
    }

    .profile-user-name {
        font-size: 2.2rem;
    }

    .profile-edit-btn {
        order: 1;
        padding: 0;
        text-align: center;
        margin-top: 1rem;
    }

    .profile-edit-btn {
        margin-left: 0;
    }

    .profile-bio {
        font-size: 1.4rem;
        margin-top: 1.5rem;
    }

    .profile-edit-btn,
    .profile-bio,
    .profile-stats {
        flex-basis: 100%;
    }

    .profile-stats {
        order: 1;
        margin-top: 1.5rem;
    }

    .profile-stats ul {
        display: flex;
        text-align: center;
        padding: 1.2rem 0;
        border-top: 0.1rem solid #dadada;
        border-bottom: 0.1rem solid #dadada;
    }

    .profile-stats li {
        font-size: 1.4rem;
        flex: 1;
        margin: 0;
    }

    .profile-stat-count {
        display: block;
    }
}

/*

The following code will only run if your browser supports CSS grid.

Remove or comment-out the code block below to see how the browser will fall-back to flexbox & floated styling. 

*/

@supports (display: grid) {
    .profile {
        display: grid;
        grid-template-columns: 1fr 2fr;
        grid-template-rows: repeat(3, auto);
        grid-column-gap: 3rem;
        align-items: center;
    }

    .profile-image {
        grid-row: 1 / -1;
    }

    .gallery {
        display: grid;
        grid-template-columns: repeat(auto-fit, minmax(22rem, 1fr));
        grid-gap: 2rem;
    }

    .profile-image,
    .profile-user-settings,
    .profile-stats,
    .profile-bio,
    .gallery-item,
    .gallery {
        width: auto;
        margin: 0;
    }

    @media (max-width: 40rem) {
        .profile {
            grid-template-columns: auto 1fr;
            grid-row-gap: 1.5rem;
        }

        .profile-image {
            grid-row: 1 / 2;
        }

        .profile-user-settings {
            display: grid;
            grid-template-columns: auto 1fr;
            grid-gap: 1rem;
        }

        .profile-edit-btn,
        .profile-stats,
        .profile-bio {
            grid-column: 1 / -1;
        }

        .profile-user-settings,
        .profile-edit-btn,
        .profile-settings-btn,
        .profile-bio,
        .profile-stats {
            margin: 0;
        }
    }
}

If you refresh the page now, you will be able to view all the posts from your database in a grid format. There will be 3 posts in each row. Now you have all the code with the style, you can customize it as per your needs.

So that’s how you can show your Instagram feed without API using simple PHP and MySQL.

Socket IO emit an event to specific users – Node JS

Introduction

Sockets are used for real-time communication. They are now being used in chat apps, team collaboration tools, and many more. Socket IO emit events to the receivers and the receivers are constantly listening to that event. When the event is received on the client-side, they can perform the necessary action. You can attach as many event listeners as you want and perform different actions for each event.

Users are connected with a Node JS server using a client-side library called Socket IO. Users can also join the room which will be helpful if you are creating a group chat app. There are 4 ways in which socket events are fired.

  1. Send event to all connected users, including the sender.
  2. Send event to all users, except the sender.
  3. Emit event to all users in a room.
  4. Send event to specific users.

In this tutorial, we will be covering the 4th part i.e. send socket events to specific users.

Video tutorial:

Problem

Suppose you have a chat app where you want 2 people to have a private chat. Now you want to have a real-time effect i.e. to show the new messages without having to refresh the page. This requires sockets that send the data in real-time. And we can show the data on the client-side in real-time too. Now when a sender sends a message to a specific user, we need to send the socket event to that specific user only.

Solution

We will create a simple script that allows us to send events to a specific user only. You can then integrate and customize that logic in your project. First, you need to download and install Node JS. You also need to download the Socket IO JS client-side library. We will have a simple database from where we can show all the users in a list, with a button to send an event to that user only. So we need to create a database with a simple users table, you can use your own database as well.

Database

In your phpMyAdmin, create a database named “send_socket_event_to_specific_users”. In that database, create a users table:

CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` text NOT NULL
);

Add few rows in that table so we can show them in a list or a table.

INSERT INTO `users` (`id`, `name`) VALUES
(1, 'Adnan'),
(2, 'Afzal'),
(3, 'John'),
(4, 'Doe');

Then in your index.php, show all users.

<?php
	$conn = new PDO("mysql:host=localhost:3306;dbname=send_socket_event_to_specific_users", "root", "");
	$sql = "SELECT * FROM users";
	$result = $conn->prepare($sql);
	$result->execute([]);
	$users = $result->fetchAll();
?>

<table>
	<thead>
		<tr>
			<th>ID</th>
			<th>Name</th>
			<th>Action</th>
		</tr>
	</thead>

	<tbody>
		<?php foreach ($users as $user): ?>
			<tr>
				<td><?php echo $user['id']; ?></td>
				<td><?php echo $user['name']; ?></td>
				<td>
					<form method="POST" onsubmit="return sendEvent(this);">
						<input type="hidden" name="id" value="<?php echo $user['id']; ?>" required />
						<input type="submit" value="Send Message" />
					</form>
				</td>
			</tr>
		<?php endforeach; ?>
	</tbody>
</table>

It will show all users in a table with a button to send message. Now when the page loads, we need to get the ID of the user, you can also get it from PHP sessions.

Include Socket IO library

Before that, we need to include the Socket IO JS library. You can download it from here.

<script src="socket.io.js"></script>

<script>
	var userId = prompt("Enter user ID");

	var socketIO = io("http://localhost:3000");
	socketIO.emit("connected", userId);
</script>

It will store your ID in userId variable. And it will connect with Node JS server and emit an event “connected” with your ID.

Now we need to create a simple Node server. Create an empty folder and create a file named “server.js” in it. Then open the CMD in that folder and run the following commands one-by-one:

npm init
npm install express http socket.io mysql
npm install -g nodemon
nodemon server.js

Write the following code in your server.js file:

var express = require("express");
var app = express();

var http = require("http").createServer(app);
var socketIO = require("socket.io")(http, {
	cors: {
		origin: "*"
	}
});

var users = [];

socketIO.on("connection", function (socket) {

	socket.on("connected", function (userId) {
		users[userId] = socket.id;
	});

    // socket.on("sendEvent") goes here
});

http.listen(process.env.PORT || 3000, function () {
	console.log("Server is started.");
});

This will start the server at port 3000, creates a users array and store all connected user’s socket ID in it.

Send event using socket IO emit function

Back in index.php, we need to create a JS function to send the event when the user click the “Send message” button:

function sendEvent(form) {
	event.preventDefault();

	var message = prompt("Enter message");
	socketIO.emit("sendEvent", {
		"myId": userId,
		"userId": form.id.value,
		"message": message
	});
}

Now in server.js, we need to listen to that event and send the message to that user only. But before that, we need to include mysql module because the user’s names are stored in mysql database. At the top of your server.js:

var mysql = require("mysql");
var connection = mysql.createConnection({
	host: "localhost",
	port: 3306,
	user: "root",
	password: "",
	database: "send_socket_event_to_specific_users"
});

connection.connect(function (error) {
	console.log("Database connected: " + error);
});

And after the socket connected event:

socket.on("sendEvent", async function (data) {
	connection.query("SELECT * FROM users WHERE id = " + data.userId, function (error, receiver) {
		if (receiver != null) {
			if (receiver.length > 0) {

				connection.query("SELECT * FROM users WHERE id = " + data.myId, function (error, sender) {
					if (sender.length > 0) {
						var message = "New message received from: " + sender[0].name + ". Message: " + data.message;
						socketIO.to(users[receiver[0].id]).emit("messageReceived", message);
					}
				});
			}
		}
	});
});

This will search the sender and receiver by ID, and emit the event to the receiver with the name of the sender.

Listen to socket IO events

Now we need to listen to that event in our index.php and show a message in a list when that event is received. First, create a ul where all messages will be displayed:

<ul id="messages"></ul>

Then attach that event in JS:

socketIO.on("messageReceived", function (data) {
	var html = "<li>" + data + "</li>";
	document.getElementById("messages").innerHTML = html + document.getElementById("messages").innerHTML;
});

So that’s how you can use the socket IO emit function to send the event to a specific user only.

Check out realtime chat app tutorial using socket IO.

[wpdm_package id=’1295′]

PHP PDO prepared statement – CRUD Operation

PHP PDO (PHP Data Objects) is basically an abstraction to database access, which means it is not limited to only MySQL or any other database system. If you are building a website in MySQL and suddenly you are required to change the database from MySQL to PostgreSQL, if you are using PDO at backend then only minor changes needs to be done in order to switch database. However, in other cases, you might have to re-write all the queries again in order to switch database.

PDO also provides a very neat way to write prepared statements which helps to prevent SQL injection in your database. In this tutorial, we will be performing completed CRUD (Create, Read, Update, Delete) operation using PHP PDO & MySQL. For sending data from forms, we will using AJAX so you also learn to fetch data from database without having to refresh the page.

Create data using PHP PDO prepared statement

First we will create a form from where we will get the input from user, and when user click the submit button, it will send an AJAX request with all form data. At the server end, server will first connect with our sample database called classicmodels using PDO. Then we will get all fields attached in AJAX request, prepare a query to prevent from SQL injection. Finally execute the query to add a new row in database.

You need to create a new file named save-data.php which will store the record in database. As this is an insert operation, so we might need the newly inserted ID on client side. So we can get the newly inserted ID by calling the $conn->lastInsertId() method from PDO connection object.

index.php

<form onsubmit="return addData();">
    <p>
        <input id="firstName" placeholder="Enter first name">
    </p>

    <input type="submit">
</form>

<script>
	function addData() {
        var firstName = document.getElementById("firstName").value;

        var ajax = new XMLHttpRequest();
        ajax.open("POST", "save-data.php", true);
        ajax.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
        ajax.send("firstName=" + firstName);

        ajax.onreadystatechange = function () {
            if (this.readyState == 4 && this.status == 200) {
                console.log(this.responseText);
            }
        };

        return false;
    }
</script>

save-data.php

<?php

// Connecting with database
$conn = new PDO("mysql:host=localhost;dbname=classicmodels", "root", "");

// SQL query string
$sql = "INSERT INTO employees (firstName) VALUES(:firstName)";

// Preparing the statement
$result = $conn->prepare($sql);

// Actually executing the query in database
$result->execute(array(
	":firstName" => $_POST["firstName"]
));

// Sending inserted ID back to AJAX
echo $conn->lastInsertId();
?>

Remember that you should not write variables directly in SQL query string. You just have to mark the places where you wanted to put variables like :firstName. This will tell that we are going to map this with our variable later. Then in execute() function, you have to pass an array and map each string with corresponding variable.

Read data using PHP PDO prepared statement

Now that we have saved the data in database, now we need to display it to the user. First we will create table tag where we will display all records from database in tabular form. Then we will call an AJAX to request the server to pull records from database and return them as JSON. When data is returned from server, we will convert the JSON back to Javascript objects and display that in table rows and columns.

index.php

<table>
    <tbody id="data"></tbody>
</table>

<script>
	var ajax = new XMLHttpRequest();
	ajax.open("GET", "get-data.php", true);
	ajax.send();

	ajax.onreadystatechange = function () {
	    if (this.readyState == 4 && this.status == 200) {
	        var data = JSON.parse(this.responseText);

	        var html = "";
	        for (var a = 0; a < data.length; a++) {
	        	var id = data[a].employeeNumber;

	            html += "<tr>";
	                html += "<td>" + data[a].firstName + "</td>";
	            html += "</tr>";
	        }

	        document.getElementById("data").innerHTML += html;
	    }
	};
</script>

On server side, we will connect with database using PDO same way as we did for insert operation. When you are working on projects, make sure to use only one PDO object. You can do that by either using singleton objects or sending persistent attribute in PDO constructor after password field as below.

Using singleton

if ($conn == null)
{
	$conn = new PDO("mysql:host=localhost;dbname=classicmodels", "root", "");
}

Connect with database, execute the query to get all records from database. In this query, we are not using any variable input which is received from user, so no need to use prepared statement. But if you are required to use user input variable in query, you should follow the same method as we did for insert operation.

get-data.php

<?php

$conn = new PDO("mysql:host=localhost;dbname=classicmodels", "root", "", array(
	PDO::ATTR_PERSISTENT => true
));

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

echo json_encode($result->fetchAll());

?>

Adding persistent attribute in PHP PDO constructor will make your website faster by caching the connection. So the next time some script request for database connection, it will not create a new connection again but will return the cached connection. This will result in removing the overhead on database for making multiple connection on same database. Persistent connections are not closed at the end of script but will remain open for other scripts too.

Update data using PDO

Updating data consists of 3 parts.

  1. Create an edit button which when clicked will redirect to new page.
  2. Get all data of selected record from database and auto-populate that in form.
  3. Update the data in database when that form submits.

In your read operation AJAX where you are displaying data in table, add the below line inside the loop along with other <td> tags. This will redirect to edit page along with ID of selected record. That should be the unique ID in your table. We were already getting this ID variable in read operation.

index.php

html += "<td><a href='edit.php?id=" + id + "'>Edit</a></td>";

The code used for update operation is almost similar to the one we used for insert operation. First we will get the record from database using ID which we will be receiving from URL. User might temper that URL so we must bind that in prepared statement to prevent SQL injection.

The form will be similar to insert operation’s form, except we add a new hidden field for sending ID of selected record. It will also be appended in AJAX request. The rest will remains same.

edit.php

<?php

    $conn = new PDO("mysql:host=localhost;dbname=classicmodels", "root", "");

    $id = $_GET["id"];

    $sql = "SELECT * FROM employees WHERE employeeNumber = :id";
    $result = $conn->prepare($sql);
    $result->execute(array(
        ":id" => $id
    ));
    $data = $result->fetch();

?>

<form onsubmit="return editData();">
    <input type="hidden" id="id" value="<?= $id; ?>">
    <input id="firstName" placeholder="Enter first name" value="<?= $data["firstName"]; ?>">

    <input type="submit">
</form>

<script>
    function editData() {
        var firstName = document.getElementById("firstName").value;
        var id = document.getElementById("id").value;

        var ajax = new XMLHttpRequest();
        ajax.open("POST", "edit-data.php", true);
        ajax.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
        ajax.send("firstName=" + firstName + "&id=" + id);

        ajax.onreadystatechange = function () {
            if (this.readyState == 4 && this.status == 200) {
                console.log(this.responseText);
            }
        };

        return false;
    }
</script>

Delete data using PDO

Deleting data consists of 2 parts.

  1. Create a delete button which when clicked will send an AJAX request.
  2. Server will run the delete query on selected record’s unique ID.

In your read operation AJAX where you are displaying data in table, add the below line inside the loop along with other <td> tags. This will call our Javascript function along with ID of selected record. That should be the unique ID in your table. We were already getting this ID variable in read operation.

A new function deleteData(id) will be created which will accept the ID of selected record and pass that ID with the AJAX request to the server.

index.php

html += "<td><button onclick='deleteData(\"" + id + "\")'>Delete</button></td>";

function deleteData(id) {
    if (confirm("Are you sure ?")) {
        var ajax = new XMLHttpRequest();
        ajax.open("POST", "delete-data.php", true);
        ajax.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
        ajax.send("id=" + id);

        ajax.onreadystatechange = function () {
            if (this.readyState == 4 && this.status == 200) {
                console.log(this.responseText);
            }
        };
    }
}

Delete operation will be almost similar to update operation. You just create a prepared statement and map the ID which we are receiving from AJAX request and execute the query.

delete-data.php

<?php

$conn = new PDO("mysql:host=localhost;dbname=classicmodels", "root", "");

$sql = "DELETE FROM employees WHERE employeeNumber = :id";
$result = $conn->prepare($sql);
$result->execute(array(
	":id" => $_POST["id"]
));

echo "Done";

?>

If you want to delete the <tr> tag from <table> when the delete button is pressed, you need to give a custom data ID attribute to TR tag and call the remove() function to remove the TR node.

// In read operation AJAX loop
html += "<tr data-id='" + id + "'>";

// In deleteData() function
document.querySelector("tr[data-id='" + id + "']").remove();

So that’s the completed CRUD operation using PHP PDO & MySQL and AJAX. If you face any problem, feel free to ask in the comment’s section below.

[wpdm_package id=’203′]