Cannot fetch data from function in PDO to ajax

Question

I have made a class employee and have function of select that should return the values to add_data.php and using that data , it should be in option tag of select that is the ajax for country state and city.

employee.php

 <?php
class employees{
    function __construct(){
        try {
                $this->con= new PDO("mysql:host=myserver;dbname=dhruv_thakkar", 'uname', 'pass');
                $this->con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                }

        catch(PDOException $e)
                {
                echo "Connection failed: " . $e->getMessage();
                }
      }

      function insert($name,$email,$password,$birth_date,$gender,$postcode,$phno,$street_address){
          try {

              $name=$this->name=$name;
              $email=$this->email=$email;
              $password=$this->password=$password;
              $birth_date=$this->birth_date=$birth_date;
                $gender=$this->gender=$gender;
                $postcode=$this->postcodde=$postcode;
                $phno=$this->phno=$phno;
                $street_address=$this->street_address=$street_address;

                $d1 = date('Y-m-d',(strtotime($birth_date)));
                $sql = "INSERT INTO employees(name, email,gender,password,street_address,postcode,phone_number,profile_pic,status,register_date,birth_date)
                VALUES ( '$name', '$email','$gender ','$password','$street_address','$postcode','$phno','pic123','1',NOW(),'$d1')";
              $this->con->exec($sql);
              echo "New record created successfully";
          }
          catch(PDOException $e)
          {
              echo $sql . "<br>" . $e->getMessage();
          }

          $this->con= null;
      }

      function select_country(){
         $sql= "SELECT *from country";
         $result =$this->con->query($sql);
        return $result;
      }
}

$obj= new employees();
?>

add_data.php

<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

include 'employee.php';
    session_start();
    $username=$_SESSION['username'];
    $password=$_SESSION['password'];

    if(isset($_SESSION['username'])){

        if(isset($_POST['logout'])){
                session_destroy();
                header('location:index.php');
        }

        if(isset($_POST['submit'])){

           $name=$_POST['name'];
           $email=$_POST['email'];
           $password=$_POST['password'];
           $birth_date=$_POST['birth_date'];
          echo  $gender=$_POST['gender'];
           $postcode=$_POST['postcode'];
           $phno=$_POST['phno'];
           $street_address=$_POST['street_address'];

           $obj->insert($name,$email,$password,$birth_date,$gender,$postcode,$phno,$street_address);

        }

    }
    else {
        header('location:index.php');
    }

?>
    <!doctype html>
    <html lang="en">
      <head>
      <style>

.sidebar-sticky{
      border-right: 3px solid #aaacaf;
    height:1000px;
}
    .col-centered{
      align-items: center;
        }
    </style>
        <!-- Required meta tags -->
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.8.1/css/all.css" integrity="sha384-50oBUHEmvpQ+1lW4y57PTFmhCaXp0ML5d60M1M7uH2+nqUivzIebhndOJK28anvf" crossorigin="anonymous">
        <!-- Bootstrap CSS -->  
        <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">

      </head>
      <body>
    <nav class="navbar navbar-expand-lg navbar-dark bg-dark">
  <a class="navbar-brand" href="#">Admin Panel</a>
  <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarSupportedContent" aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
    <span class="navbar-toggler-icon"></span>
  </button>

  <div class="collapse navbar-collapse" id="navbarSupportedContent">
    <ul class="navbar-nav mr-auto">
      <li class="nav-item active">
        <a class="nav-link" href="#">Home <span class="sr-only">(current)</span></a>
      </li>

    </ul>

    <form class="form-inline my-2 my-lg-0">
      <input class="form-control mr-sm-2" type="search" placeholder="Search" aria-label="Search">
      <button class="btn btn-outline-success my-2 my-sm-0" type="submit">Search</button>
    </form>
    &nbsp;&nbsp;&nbsp;
    <form action="" method="post"  class="form-inline my-2 my-lg-0">
        <input type="submit" value="logout" name="logout"  class="btn btn-outline-danger my-2 my-sm-0 ">
    </form>

      </div>  
</nav>

  <!--Side Bar Now  -->
      <div class="container-fluid sidea">
      <div class="row">

        <nav class="col-md-2 d-none d-md-block bg-light sidebar">
        <?php 
            include 'sidebar.php';
        ?>
      </nav>
        <div class="col-sm-10">
            <div class="container">

            <h2>Add User Here</h2>
            <br>
            <form action="" method="post" enctype="multipart/form-data">

                    <input type="text" class="form-control" placeholder="Enter Name" name="name"> 
                    <br><br>

                    <input type="text" class="form-control" placeholder="Enter Email"  name="email"> 
                    <br><br>


                    <input type="text" class="form-control" placeholder="Enter Password"  name="password"> 
                    <br><br>

                    <label>Upload Your Profile Picture Here</label>
                    <input type="file"  name="photo"> 
                    <br><br>

                    <label>Enter Your BirthDate</label>
                     <input type="date" class="form-control"  name="birth_date"> 
                    <br><br>

                    <label>Select Gender</label>&nbsp;&nbsp;<br>
                    <label><input type="radio" value="M" name="gender">Male</label> <br>
                    <label><input type="radio" value="F" name="gender">Female</label>
                    <br><br>


                    <input type="text" class="form-control" placeholder="Enter Street Address"  name="street_address"> 
                    <br><br>

<?php
?>              
<select value="" id="country">
    <?php 
    $result=$obj->select_country();
    print_r($data);
    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) { 
            ?>
    <option value="<?php echo $row['country_id']; ?>">
<?php
        echo $row['country_name'];
?>
   </option>
    <?php
    }

}
else{
    echo '<option value="">Country not available</option>';
}

?>
</select>        
  <select id="state">
    <option value="">Select country first</option>
</select>

<select id="city">
    <option value="">Select state first</option>
</select>


                    <br><br>
                    <input type="text" class="form-control" placeholder="Postcode"  name="postcode"> 
                    <br><br>

                    <input type="text" class="form-control" placeholder="Phone No"  name="phno"> 
                    <br><br>

                    <input type="submit" class="btn btn-success"  value=" Add Data" name="submit"> 

            </form>
            </div>
        </div>
    </div>


    </div>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>

<script type="text/javascript">
$(document).ready(function(){
    $("#country").change(function(){
        var countryID = $(this).val();
        if(countryID){
            $.ajax({
                type:'POST',
                url:'ajaxData.php',
                data:'country_id='+countryID,
                success:function(html){
                    $('#state').html(html);
                    $('#city').html('<option value="">Select state first</option>'); 
                }
            }); 
        }else{
            $('#state').html('<option value="">Select country first</option>');
            $('#city').html('<option value="">Select state first</option>'); 
        }
      });

    $('#state').on('change',function(){
        var stateID = $(this).val();
        if(stateID){
            $.ajax({
                type:'POST',
                url:'ajaxData.php',
                data:'state_id='+stateID,
                success:function(html){
                    $('#city').html(html);
                }
            }); 
        }else{
            $('#city').html('<option value="">Select state first</option>'); 
        }
    });
});
</script>    
        <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
        <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
      </body>
    </html>

Here I country are not getting displayed. It always goes in else loop and shows country not available.

Are your DB records created OK? You have shown a big block of code for `add_data.php` - is that really all one file, or is that `add_data.php` **and** `index.php`? — Don't Panic, Apr 16 '19 at 07:23
**Warning:** You are wide open to [SQL Injections](http://php.net/manual/en/security.database.sql-injection.php) and should really use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](http://php.net/manual/en/pdo.prepared-statements.php) or by [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input, especially that which comes from the client side. Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). — Dharman, Apr 16 '19 at 18:16
**Never store passwords in clear text or using MD5/SHA1!** Only store password hashes. Use PHP's [`password_hash()`](http://php.net/manual/en/function.password-hash.php) and [`password_verify()`](http://php.net/manual/en/function.password-verify.php) . If you're running a PHP version lower than 5.5 (which I really hope you aren't), you can use the [password_compat](https://github.com/ircmaxell/password_compat) library to get the same functionality. — Dharman, Apr 16 '19 at 18:16

Rajdeep Paul · Accepted Answer · 2019-04-16T08:31:48.743

1

You are mixing up mysqli and PDO database extensions. Pick one and be consistent.

Change the select() method of employees class and <select>...</select> block in the following way,

class employees{
    ...
    function select_country(){
        $sql= "SELECT *from country";
        $result =$this->con->query($sql);
        return $result->fetchAll();
    }
    ...
}

and

<select value="" id="country">
    <option value="">Select a country<option>
    <?php 
    $result = $obj->select_country();
    $count = count($result);
    // print_r($data);
    if ($count > 0) {
        // output data of each row
        foreach($result as $row){ 
            ?>
            <option value="<?php echo $row['country_id']; ?>">
            <?php
                echo $row['country_name'];
            ?>
            </option>
            <?php
        }
    }else{
        echo '<option value="">Country not available</option>';
    }
    ?>
</select>

Sidenote: Learn about prepared statement because right now your query is susceptible to SQL injection attack. Also see how you can prevent SQL injection in PHP.

edited Apr 16 '19 at 08:31

answered Apr 16 '19 at 07:25

Rajdeep Paul

16,887
3
18
37

not working it shows "Warning: mysqli::__construct(): (HY000/1045): Access denied for user" – Dhruv Thakkar Apr 16 '19 at 07:52
although using my code, insert function is working but select is not working – Dhruv Thakkar Apr 16 '19 at 07:53
@DhruvThakkar Check your database settings. Have you appropriately changed *myserver*, *dhruv_thakkar*, *uname* and *pass* in $this->con = new mysqli(...); statement? – Rajdeep Paul Apr 16 '19 at 07:55
yes sir and can u show me where am i mixing pdo and mysqli – Dhruv Thakkar Apr 16 '19 at 07:57
@DhruvThakkar I have completely updated my answer, please check again. Just change the `select()` method of `employees` class and `` block, and keep everything as it is. Now it should work fine for you. Since few parts of your code is already working with PDO, stick to PDO throughout your code, and the suggested changes are based on that. – Rajdeep Paul Apr 16 '19 at 08:33
i have another doubt, i get option values in id like 1 , 2 in database. i want it in text like INdia, but my ajax is working on id , do u have any approach? – Dhruv Thakkar Apr 16 '19 at 08:49
@DhruvThakkar Having *id* values are fine. Any change in the dropdown list will send the id value of *that* country to backend PHP page through AJAX. You just have to query the states based on *that* country id and then populate the state dropdown list accordingly. – Rajdeep Paul Apr 16 '19 at 08:53

Cannot fetch data from function in PDO to ajax

1 Answers1