Php webservice循环

Question

I am about to lose my mind.I dont have any php experince and I am struggling about php web service.

Here is my code;

<?php

    private $username2 = "";
    private $password2 = "";

    private $DB_CONNECTION;
    private $servername = "localhost";
    private $username = "root";
    private $password = "";
    private $dbname = "dptest";

    function __construct()
    {
        $this->DB_CONNECTION = mysqli_connect($this->servername, $this->username,
            $this->password, $this->dbname);
    }
function getUserType(){
        $sql = "SELECT usertype FROM `login_test` WHERE username = '". $this->username2."'AND password = '".$this->password2."'";

        $result = mysqli_query($this->DB_CONNECTION,$sql);
        //$value = mysqli_fetch_array($result);
        while(!is_null($value = mysqli_fetch_array($result))){
            return $value['usertype'];
        }


    }

}

This is my function code.The other is my login code;

<?php

include_once 'Authentication.php';
use user\Authentication;

$auth = new Authentication();
$auth->prepare($_POST);
$userStatus = $auth->isUserValidToLogIn();


if ($userStatus) {
    // user existed
    // So log him to main page
    $json['success'] = 1;
    $json['message'] = 'access granted';
    $json['usertype'] = $auth->getUserType();


    echo json_encode($json);
} else {

    $json['success'] = 0;
    $json['message'] = 'error!';


    echo json_encode($json);
}

I am trying to get the user's type but when try to get the data form phpmyadmin local database it only gives the first column's usertype.When I try to get 2nd,3rd,4th so on.. user's usertype it doesnt return anything and blank page shows up on postman app.

Also my database looks like this;

usertype username password
admin     despro    1234
client     test     1234
client    despro2   1234
client    despro3   1234

Answer 1

The reason you are only getting one column back is because you only request the one column. In order to get the columns you want you need to explicitly request them in your query or use '*' in order to get all columns back. So your query should look like this in order to get all columns from the data table:

$sql = "SELECT * FROM `login_test` WHERE username = '". $this->username2."'AND password = '".$this->password2."'";

In general, I highly recommend that you stop using MySQLi extension and start using PHP Data Objects (PDO). It makes it easy to use prepared statements. Which also makes your code safer.

Then your query could look something like this (this is NOT the complete code):

// connecting to db
$pdo = new PDO($dsn, $user, $pass, $opt);
    $sql = 'SELECT * 
            FROM login_test 
            WHERE userName = :username
            AND pass = :password;';

    $stmt = $pdo->prepare($sql);

    $stmt->bindParam(':username', $username2, PDO::PARAM_STR);
    $stmt->bindParam(':password', $password2, PDO::PARAM_STR);

    $res = $stmt->execute();

    if ($res) {
        $response["userdata"] = array();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
            $myData = array();
            $myData["usertype"] = $row["usertype"];
            $myData["username"] = $row["username"];

            array_push($response["userdata"], $myData);
        }
    }

Note that the code above is for returning multiple rows of data. If you just want the one row then use something like this:

if ($res) {
    $response["userdata"] = array();
    $myData = array();
    $myData["usertype"] = $row["usertype"];
    $myData["username"] = $row["username"];

    array_push($response["userdata"], $myData);
}

removing the 'while' statement.

You might want to take a look at this answer I gave, recently. It is a comprehensive example of using a webservice from an Android app.

How to insert all the SQL table data into an array in java [android studio]