PHP MySQL JSON Login System

Question

I'm currently working on a login system (which works great by using two text fields and the user is then redirected). However, as I am developing a mobile app, it would be much easier to do this in JSON, and I am not entirely sure where to start. What I am basically looking to do is to use a https post request (from my app) so it would request: https://www.example.com/login.php?username=username&password=password

I have created a basic sample, but it's not what I'm looking to do with regards to passing in a parameter via the URL as my code just currently looks for users in the MySQL database and outputs the users in JSON. What I want to be able to do is pass in the username & password parameters, via the URL, and then output a "success" or "error" JSON response if the username/password is in the database or not.

<?php
$host     = "localhost";
$username = "root";
$password = "password";
$db_name  = "test";
$con = mysql_connect("$host", "$username", "$password") or die("cannot connect");
mysql_select_db("$db_name") or die("cannot select DB");
$sql    = "select * from test_table";
$result = mysql_query($sql);
$json   = array();
if (mysql_num_rows($result))
    {
      while ($row = mysql_fetch_row($result))
       {
    $json['items'] = $row;
       }
    }
mysql_close($db_name);
echo json_encode($json, JSON_PRETTY_PRINT);
?>

Edit

I have now started working on another system, which is pretty much what I am looking to do (after I found an example here: http://www.dreamincode.net/forums/topic/235556-how-to-create-a-php-login-with-data-from-mysql-database/) except every time that I request something like https://www.example.com/login.php?username=root&password=password - I get the JSON error 1 code

<?php
$host = "localhost"; // Host name
$db_username = "root"; // Mysql username
$db_password = "password"; // Mysql password
$db_name = "test"; // Database name
$tbl_name = "test_table"; // Table name

// Connect to server

mysql_connect("$host", "$db_username", "$db_password") or die("cannot connect");

// Select the database

mysql_select_db("$db_name") or die("cannot select DB");

// Get the login credentials from user

$username = $_POST['username'];
$userpassword = $_POST['password'];

// Secure the credentials

$username = mysql_real_escape_string($_POST['username']);
$userpassword = mysql_real_escape_string($_POST['password']);

// Check the users input against the DB.

$query = "SELECT * FROM test_table WHERE user = '$username' AND password = '$userpassword'";
$result = mysql_query($query) or die("Unable to verify user because " . mysql_error());
$row = mysql_fetch_assoc($result);

if ($row['total'] == 1) {

    // success

    $response["success"] = 1;

    // echoing JSON response

    echo json_encode($response);
}
else {

    // username and password not found
    // failed

    $response["failed"] = 1;

    // echoing JSON response

    echo json_encode($response);
}

?>

Solution

Try to change following lines and it should works. I get it to work, as the way you describe it.

1- Here you will get URL parameters and sanitize.

$username = $_GET['username'];
$userpassword = $_GET['password'];

$username = mysql_real_escape_string($username);
$userpassword = mysql_real_escape_string($userpassword);

2- Here you count how many rows you have

Change $row = mysql_fetch_assoc($result);

to $total_rows = mysql_num_rows($result);

3- Here you check if you have at least 1 row.

And change if ($row['total'] == 1) { to if ($total_rows == 1) {

That should give output {"success":1}

Note1: This is to solve your request and question, but does not necessary means the right approach or perfect solution in general.

Note2: I would suggest you think of password hashing, post method in stead of get, use mysqli or PDO in sted of mysql and input sensitization and not use URL to pass username and password. I would suggest you look at this link it describes some of the things I mentioned in my note1.

http://www.wikihow.com/Create-a-Secure-Login-Script-in-PHP-and-MySQL