MySql INNER JOIN IN not getting right result

I have this query in my php and it seems to be fetching the wrong data set from my db.

I have this query in my php and it seems to be fetching the wrong data set from my db.

 $querystring = "SELECT a.*, b.itemColour,b.itemColourName FROM itemorders 
 AS a INNER JOIN CatalogueItemsColour AS b ON a.colourID = b.colourID WHERE 
  a.colourID IN(SELECT colourID FROM itemorders where orderID = 
  61 ) ";

Here is a picture of my results

Can i know why its not selecting the specific orderID of 61?

Create Registration form with MySQL and PHP

Create Registration form with MySQL and PHP

In membership-based website registration and login page is common.User needs to create a new account and login to the website to access services and manage its account.In this tutorial, I show how you can create a signup page with MySQL and PHP.

1. Table structure

I am using users table in the tutorial example.

CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `fname` varchar(80) NOT NULL,
  `lname` varchar(80) NOT NULL,
  `email` varchar(80) NOT NULL,
  `password` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Configuration

Create a new config.php file.

Completed Code

<?php
session_start();
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
 die("Connection failed: " . mysqli_connect_error());
}

3. HTML & PHP

Create a <form method='post' action='' >.

If $error_message is not empty then display $error_message value on the screen. Similarly, if $success_message is not empty then display the $success_message value on the screen.

NOTE – Value is assigned to $error_message and $success_message variable on <form > submit according to conditions.
Add input fields for entering – first name, last name, email, password, and confirm password.

Also, add a submit button.

Completed Code

<?php 
include "config.php";
?>
<!DOCTYPE html>
<html>
  <head>
    <title>Create Registration form with MySQL and PHP</title>

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">

    <!-- jQuery library -->
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>

    <!-- Bootstrap JS -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>

  </head>
  <body>
    <div class='container'>
      <div class='row'>

        <div class='col-md-6' >

          <form method='post' action=''>

            <h1>SignUp</h1>
            <?php 
            // Display Error message
            if(!empty($error_message)){
            ?>
            <div class="alert alert-danger">
              <strong>Error!</strong> <?= $error_message ?>
            </div>

            <?php
            }
            ?>

            <?php 
            // Display Success message
            if(!empty($success_message)){
            ?>
            <div class="alert alert-success">
              <strong>Success!</strong> <?= $success_message ?>
            </div>

            <?php
            }
            ?>

            <div class="form-group">
              <label for="fname">First Name:</label>
              <input type="text" class="form-control" name="fname" id="fname" required="required" maxlength="80">
            </div>
            <div class="form-group">
              <label for="lname">Last Name:</label>
              <input type="text" class="form-control" name="lname" id="lname" required="required" maxlength="80">
            </div>
            <div class="form-group">
              <label for="email">Email address:</label>
              <input type="email" class="form-control" name="email" id="email" required="required" maxlength="80">
            </div>
            <div class="form-group">
              <label for="password">Password:</label>
              <input type="password" class="form-control" name="password" id="password" required="required" maxlength="80">
            </div>
            <div class="form-group">
              <label for="pwd">Confirm Password:</label>
              <input type="password" class="form-control" name="confirmpassword" id="confirmpassword" onkeyup='' required="required" maxlength="80">
            </div>

            <button type="submit" name="btnsignup" class="btn btn-default">Submit</button>
          </form>
        </div>

     </div>
    </div>
  </body>
</html>

4. Form Submit

Add following code in <head> section.

On <form > submit assign $_POST values in variables.

Validate the values –

To check the input values are valid or not created a $isValid = true variable. If any validation is false then assign false to $isValid and record not inserted.

  1. First, check if all values are entered or not. If not entered then assign false to $isValid and "Please fill all fields." to $error_message.
  2. Check if entered password and confirm password are equal or not. If not equal then assign false to $isValid and "Confirm password not matching." to $error_message.
  3. Check if $email variable value has valid email or not. If not valid then assign false to $isValid and "Invalid Email-ID." to $error_message.
  4. Check if email-id already exists in users table or not. If available then assign false to $isValid and "Email-ID is already existed." to $error_message.

If $isValid has true value then insert a new record in the users table and assign "Account created successfully." to $success_message.

Completed Code

<?php 
$error_message = "";$success_message = "";

// Register user
if(isset($_POST['btnsignup'])){
   $fname = trim($_POST['fname']);
   $lname = trim($_POST['lname']);
   $email = trim($_POST['email']);
   $password = trim($_POST['password']);
   $confirmpassword = trim($_POST['confirmpassword']);

   $isValid = true;

   // Check fields are empty or not
   if($fname == '' || $lname == '' || $email == '' || $password == '' || $confirmpassword == ''){
     $isValid = false;
     $error_message = "Please fill all fields.";
   }

   // Check if confirm password matching or not
   if($isValid && ($password != $confirmpassword) ){
     $isValid = false;
     $error_message = "Confirm password not matching";
   }

   // Check if Email-ID is valid or not
   if ($isValid && !filter_var($email, FILTER_VALIDATE_EMAIL)) {
     $isValid = false;
     $error_message = "Invalid Email-ID.";
   }

   if($isValid){

     // Check if Email-ID already exists
     $stmt = $con->prepare("SELECT * FROM users WHERE email = ?");
     $stmt->bind_param("s", $email);
     $stmt->execute();
     $result = $stmt->get_result();
     $stmt->close();
     if($result->num_rows > 0){
       $isValid = false;
       $error_message = "Email-ID is already existed.";
     }

   }

   // Insert records
   if($isValid){
     $insertSQL = "INSERT INTO users(fname,lname,email,password ) values(?,?,?,?)";
     $stmt = $con->prepare($insertSQL);
     $stmt->bind_param("ssss",$fname,$lname,$email,$password);
     $stmt->execute();
     $stmt->close();

     $success_message = "Account created successfully.";
   }
}
?>

5. Demo 6. Conclusion

In this tutorial, I only cover the registration system and if you want to know how to create login page then you can view the following tutorial.

Recommended Reading

Laravel Repository Pattern Implementation

Laravel 6 Release New Features and Upgrade

Instructions to Create your first Laravel package

Upgrading Laravel To 6.0 From 5.8

Laravel Custom Casts Package

Top 12 Array Functions In PHP

Putting a Laravel App into Production

Why we use Laravel & Wink

PHP with Vue.js & MySQL: REST API CRUD Tutorial

PHP with Vue.js & MySQL: REST API CRUD Tutorial

PHP with Vue.js & MySQL: REST API CRUD Tutorial - In this tutorial, we'll build a RESTful CRUD application with PHP & MySQL in the backend and Vue.js in the frontend. We'll also be using Axios for sending Ajax request to PHP from Vue.

The Vue.js library, Axios client and Ajax technology allows you to fetch and display data in your application without the need to refresh the whole page each time.

For database we'll be using MySQL, the most popular database used by PHP developers.

Creating the MySQL Database

In your terminal, start the MySQL client using:

mysql -u root -p

Enter your password when prompted and hit Enter.

Next, create a database using the following SQL statement:

mysql> create database vuedb;

Next, create the following SQL table in your vuedb database:

mysql> use vuedb;
mysql> CREATE TABLE `contacts` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `city` varchar(100),
  `country` varchar(100),
  `job` varchar(100)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Create The PHP & MySQL CRUD App

Now, let's create a PHP and MySQL CRUD application. Open a new terminal, navigate to your working directory then create a folder for your project:

$ cd ~/demos
$ mkdir php-vuejs-crud

Next, navigate in your project's folder and add an index.php file:

$ cd php-vuejs-crud
$ touch index.php

Open the index.php file and add the following code:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>PHP| MySQL | Vue.js | Axios Example</title>
    <script src="https://cdn.jsdelivr.net/npm/vue"></script>
    <script src="https://unpkg.com/axios/dist/axios.min.js"></script>

</head>
<body>
</body>
</html>

We first include Vue.js and Axios from their CDNs.

Next, in the body of the document, add a <table> to display fetched data:

<h1>Contact Management</h1>
<div id='vueapp'>

<table border='1' width='100%' style='border-collapse: collapse;'>
   <tr>
     <th>Name</th>
     <th>Email</th>
     <th>Country</th>
     <th>City</th>
     <th>Job</th>

   </tr>

   <tr v-for='contact in contacts'>
     <td>{{ contact.name }}</td>
     <td>{{ contact.email }}</td>
     <td>{{ contact.country }}</td>
     <td>{{ contact.city }}</td>
     <td>{{ contact.job }}</td>
   </tr>
 </table>

We use the v-for directive to iterate over the contacts array and display each contact.

Next, add a <form> tag:

</br>

    <form>
      <label>Name</label>
      <input type="text" name="name" v-model="name">
</br>
      <label>Email</label>
      <input type="email" name="email" v-model="email">
      </br>
      <label>Country</label>
      <input type="text" name="country" v-model="country">
      </br>
      <label>City</label>
      <input type="text" name="city" v-model="city">
      </br>
      <label>Job</label>
      <input type="text" name="job" v-model="job">
      </br>
      <input type="button" @click="createContact()" value="Add">
    </form>

</div>

We use the v-model directive to bind the input fields to their corresponding variables in the Vue instance we'll be creating next. And we use the @click event to bind the click event of the button to the createContact() method that will be defined in the Vue instance.

Next, add a <script> tag and create a Vue app:

<script>
var app = new Vue({
  el: '#vueapp',
  data: {
      name: '',
      email: '',
      country: '',
      city: '',
      job: '',
      contacts: []
  },
  mounted: function () {
    console.log('Hello from Vue!')
    this.getContacts()
  },

  methods: {
    getContacts: function(){
    },
    createContact: function(){
    },
    resetForm: function(){
    }
  }
})    
</script>
</body>
</html>    

We declared three methods, let's implement them!

The getContacts() method gets contacts from the PHP endpoint using Axios:

    getContacts: function(){
        axios.get('api/contacts.php')
        .then(function (response) {
            console.log(response.data);
            app.contacts = response.data;

        })
        .catch(function (error) {
            console.log(error);
        });
    }

The createContact() methods creates a new contact in the MySQL database by sending a POST request with Axios and FormData:

    createContact: function(){
        console.log("Create contact!")

        let formData = new FormData();
        console.log("name:", this.name)
        formData.append('name', this.name)
        formData.append('email', this.email)
        formData.append('city', this.city)
        formData.append('country', this.country)
        formData.append('job', this.job)

        var contact = {};
        formData.forEach(function(value, key){
            contact[key] = value;
        });

        axios({
            method: 'post',
            url: 'api/contacts.php',
            data: formData,
            config: { headers: {'Content-Type': 'multipart/form-data' }}
        })
        .then(function (response) {
            //handle success
            console.log(response)
            app.contacts.push(contact)
            app.resetForm();
        })
        .catch(function (response) {
            //handle error
            console.log(response)
        });
    }

The resetForm() method resets the form:

    resetForm: function(){
        this.name = '';
        this.email = '';
        this.country = '';
        this.city = '';
        this.job = '';
    }

Create an API Endpoint

Now, let's create an endpoint that provides contacts data in a JSON format to our Vue frontend.

Create an api folder inside your project's root folder:

$ mkdir api

Navigate inside the api folder and create a contacts.php file and add the following content:

<?php
$host = "localhost"; 
$user = "root"; 
$password = "YOUR_MYSQL_DB_PASSWORD"; 
$dbname = "vuedb"; 
$id = '';

$con = mysqli_connect($host, $user, $password,$dbname);

$method = $_SERVER['REQUEST_METHOD'];
$request = explode('/', trim($_SERVER['PATH_INFO'],'/'));
//$input = json_decode(file_get_contents('php://input'),true);


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


switch ($method) {
    case 'GET':
      $id = $_GET['id'];
      $sql = "select * from contacts".($id?" where id=$id":''); 
      break;
    case 'POST':
      $name = $_POST["name"];
      $email = $_POST["email"];
      $country = $_POST["country"];
      $city = $_POST["city"];
      $job = $_POST["job"];

      $sql = "insert into contacts (name, email, city, country, job) values ('$name', '$email', '$city', '$country', '$job')"; 
      break;
}

// run SQL statement
$result = mysqli_query($con,$sql);

// die if SQL statement failed
if (!$result) {
  http_response_code(404);
  die(mysqli_error($con));
}

if ($method == 'GET') {
    if (!$id) echo '[';
    for ($i=0 ; $i<mysqli_num_rows($result) ; $i++) {
      echo ($i>0?',':'').json_encode(mysqli_fetch_object($result));
    }
    if (!$id) echo ']';
  } elseif ($method == 'POST') {
    echo json_encode($result);
  } else {
    echo mysqli_affected_rows($con);
  }

$con->close();

Finally, you can serve your PHP application using the following command from the root of your project:

$ php -S 127.0.0.1:8080

This is a screenshot of the application, after posting some data using the form:

For the same styling, add the following CSS:

<style>
    input {
  width: 100%;
  padding: 2px 5px;
  margin: 2px 0;
  border: 1px solid red;
  border-radius: 4px;
  box-sizing: border-box;
}

input[type=button]{
  background-color: #4CAF50;
  border: none;
  color: white;
  padding: 4px 7px;
  text-decoration: none;
  margin: 2px 1px;
  cursor: pointer;
}
th, td {
  padding: 1px;
  text-align: left;
  border-bottom: 1px solid #ddd;
  
}
tr:hover {background-color: #f5f5f5;}

</style>

Conclusion

In this tutorial, we've used PHP, MySQL, Vue.js and Axios to create a simple REST API CRUD example application.

Getting started with Directus 7 and Docker (PHP & MySQL & Nginx)

Getting started with Directus 7 and Docker (PHP & MySQL & Nginx)

Setup a docker PHP container being able to run the latest Directus version. Setup a docker container hosting a MySQL DB to use with Directus in this post "Getting started with Directus 7 and Docker (Nginx, PHP & MySQL)"

I recently wanted to try out the very promising Directus 7, a PHP based Headless CMS (Content Management System, headless meaning it creates an API to work with single page apps), without a doubt explained better on the Documentation.

To get started, I went to visit the very well written documentation, which tells that the best way to start is to git clone [https://github.com/directus/directus.git](https://github.com/directus/directus.git) and configure a local Apache Web Server and MySQL database to try it.

Not wanting to install it locally (because… well, who does that anymore? Local environments are the less reproducible things of all, everyone does not have the same OS, OS version, packages installed, etc.), I turned to my best development friend: Docker!

I found that there is an official Docker repository for directus (Yay!), but sadly couldn’t make it work properly, was out of date and just wasn’t easy to use.

I finally decided to write this article to help dear developper friends in this inquiry.

Our goal :

  • Setup a docker PHP container being able to run the latest Directus version
  • Setup a docker container hosting a MySQL DB to use with Directus
  • Making them work together and letting us try it

Requirements to achieve that goal :

TL;DR : You can find the result of this article here , get it and run the Start stuff instructions to try out Directus 7 in Docker 🙂

Setup a docker PHP container being able to run the latest Directus version

I have to say my favorite stack does not include Apache, I way prefer Nginx working with PHP-FPM, so that is what this example with showcase.

First, let’s create a folder for our experiment: mkdir directus-test/

Inside that folder ( cd directus-test/ ), let’s create a Dockerfile and start from an image that’s gonna help us out:

FROM wyveo/nginx-php-fpm:latest

Then, let’s install a very simple NGINX config allowing us to serve the directus code:

COPY default_nginx_conf /etc/nginx/conf.d/default.conf

The default_nginx_conf is the following:

server {
  listen 80;  root /app/public/;
  index index.php index.html index.htm;
  server_name _; 
  location / {
    try_files $uri $uri/ /index.php;
  }
  
  # pass the PHP scripts to FastCGI server 
  location ~ \.php$ {
    try_files $uri =404;
    fastcgi_pass unix:/run/php/php7.3-fpm.sock;
    fastcgi_index index.php;
    fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
    include fastcgi_params;
  }
}

Hence, a very simple NGINX configuration looking for an index.php in the /app/public folder of our container, and forwarding any request to a *.php file to PHP-FPM through a socket ( /run/php/php7.3-fpm.sock ) which is already set up in the container.

We’re just missing the directus code!

Let’s get it from github, as it is advised in the documentation :

git clone https://github.com/directus/directus.git

Let’s build our container!

docker build -t directus_img .
Setup a docker container hosting a MySQL DB to use with Directus

You know what? I feel lazy and would prefer to spend my time trying the damn thing, so let’s just get an image from Docker hub!

For example, the default MySQL image looks nice ! https://hub.docker.com/_/mysql

Making them work together and letting us try it

Let’s run both containers and make sure they can interact with each other thanks to a Docker Network:

docker network create directus_test_network

Then let’s run our PHP container and give it access to the directus code :

docker run -dit --name directus_php --network directus_test_network -p 8000:80 -v "$(pwd)/directus/":/app/ --rm directus_img

In case you are not familiar with docker, the options allow us to:

  • directus_img is the image we built just before, including all we need
  • -d detach the process from the command line, i.e. your bash is not “stuck” in the process
  • -i keeps the shell interactive even if detached, which means you can still interact with the container
  • -t allocates a pseudo-TTY
  • --name names the container so we can find it easily with docker ps later, or to interact with it
  • --network specifies that container should be available in the network created just before
  • -p allows us to bind ports from the container (80 : the default web port) to a port of the host (your machine), here the 8000 , making it possible for you to go to localhost:8000 and see what the container serves on its port 80
  • -v mounting a volume (here: $(pwd)/directus/ , $(pwd) being the current folder) as a folder in the container ( /app/ )
  • --rm Automatically removes the container when it exits

The fact that directus_img is a the very end of the command is NOT because it is linked to --rm , but simply because it is necessary to pass all the options to docker run before specifying which image to run.

Then, let’s run our MySQL container:

docker run -d --name directus_mysql --network directus_test_network -p 3306:3306 -e MYSQL_DATABASE=directus -e MYSQL_ROOT_PASSWORD=root -v data:"$(pwd)/data" --rm mysql:5.7

The only new thing here is the -e option, allowing you to pass an environment variable to the container.

You can see here that we also bind the port 3306 on the host… Why? This allows you to connect you favorite SQL Manager tool (ex: Sequel Pro) to the DB on localhost:3306 and browse your data if need be.

We can see that our containers are running thanks to docker ps , which should give you something like the following:

Result of docker ps

You can now go to http://localhost:8000/admin and setup directus :

Ready to install!

The first step is to choose your login and password:

Fill the settings for the admin login

Then to specify the database credentials and settings:

Fill the database settings

The only parameters to change here are:

  • the host , which is going to be directus_mysql
  • the user and password : root and root

Which is where we see the influence of having the two containers in the same network. Indeed, having them in the same network makes them “aware” of each other, giving access in the MySQL container to the PHP container on the “host name” directus_php and giving the PHP container access to the MySQL one on directus_mysql .

The docker network with default driver actually includes a DNS resolver so the nslookup done to resolve the IP from the hostnames (e.g. directus_mysql) finds the other container within the same subnet based on the same container name.

Submit the login form with the credentials set and BOOM! You’re in!

Boom, you’re in!

You can now access the Admin Settings at the bottom left of the screen and get started creating your collections, etc. 🚀

Admin settings at the bottom left of the screen

I have to say that after that, Directus gives a great feeling, everything is pretty smooth with a few hiccups, like some buttons not working, but you get it, it’s still a Work in Progress :)

You can find “all of this code” plus a few helpers here if you don’t want to waste your time trying all of that out.

Thank you for reading !