PHP insert into MySQL database with REST

Question

I am attempting to test inserting in a database using a restful web service. I followed this tutorial https://trinitytuts.com/build-first-web-service-php/ Whenever I post the data I get back successful but the database doesn't display the information (IE it created an entry but all the fields are blank). I am 75% sure it is the Advanced Rest Client but I don't know whats wrong with it. Here's the code/Post command.

Post string is name=Apple&email=banna%40orange.com&pwd=12345&status=ok, Picture of how I send it using Advanced Rest Client.

enter image description here

confi.php file

 <?php
 $conn = mysqli_connect("localhost", "root", "", 'tuts_rest');
 ?>

Rest of the code

        <?php

include_once('confi.php');

if($_SERVER['REQUEST_METHOD'] == "POST"){
$name = isset($_POST['name']) ? mysqli_real_escape_string($_POST['name']) : "";
$email = isset($_POST['email']) ? mysqli_real_escape_string($_POST['email']) : "";
$password = isset($_POST['pwd']) ? mysqli_real_escape_string($_POST['pwd']) : "";
$status = isset($_POST['status']) ? mysqli_real_escape_string($_POST['status']) : "";

 // Insert data into data base
 $sql = "INSERT INTO users (ID, name, email, password, status) VALUES ('' , '" . $name . "', '" . $email . "', '" . $password . "', '" . $status . "');";
 $qur = $conn->query($sql);
 if($qur){
 $json = array("status" => 1, "msg" => "Done User added!");
 }else{
 $json = array("status" => 0, "msg" => "Error adding user!");
 }
}else{
 $json = array("status" => 0, "msg" => "Request method not accepted");
}

mysqli_close($conn);

/* Output header */
 header('Content-type: application/json');
 echo json_encode($json);
?>

Thank you!

If you can, you should stop using mysql_* functions. They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and consider using PDO, it's really not hard. — Jay Blanchard
– Jay Blanchard, Commented Jul 7, 2015 at 15:03
$sql = "INSERT INTO users (ID, name, email, password, status) VALUES ('' , '" . $name . "', '" . $email . "', '" . $password . "', '" . $status . "');"; — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Jul 7, 2015 at 15:03
Please don't post screenshots, especially illegible ones, unless the presentation is relevant to the question. Also that tutorial is teaching you extremely bad habits, that style of PHP is straight out of the 1990s, so please don't use it. A guide like PHP the Right Way can help explain modern best practices. Learning and using a development framework like Laravel will significantly help your productivity. — tadman
– tadman, Commented Jul 7, 2015 at 15:52
WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you will create severe SQL injection bugs. NEVER put $_POST data directly into a query and avoid using manual escaping because a single mistake can create total chaos. — tadman
– tadman, Commented Jul 7, 2015 at 15:52

Md. Ashraful Islam · Accepted Answer · 2020-03-30 16:29:32Z

Instead of this use my code(I change all of your code and add extra security whether you can remove the header as you wish). here you can easily insert the data from json format into database. in Advance rest client you see there is a button Raw where you can write json data type. For this example,

{
      "name": "Ashraf",
      "email":"[email protected]",
      "pwd": "1234",
      "status": "nice"
  }

Now click send button and see your data will be inserted in your database

<?php

// Include confi.php
include_once('confi.php');
// required headers
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json");
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
	// Get data
	$content=trim(file_get_contents("php://input"));
	$data = json_decode($content, true);

if(
    !empty($data['name']) &&
    !empty($data['email']) &&
    !empty($data['pwd']) &&
    !empty($data['status'])
){
	$name=$data['name'];
	$email=$data['email'];
	$password=$data['pwd'];
	$status=$data['status'];


	// Insert data into data base
	$sql = "INSERT INTO users (name, email, password, status) VALUES (? , ?, ?, ?)";
	$qur=mysqli_query($conn,$sql);
	
//sanitize
	  $name=htmlspecialchars(strip_tags($name));
       $email=htmlspecialchars(strip_tags($email));
       $password=htmlspecialchars(strip_tags($password));
       $status=htmlspecialchars(strip_tags($status));
  	//bind values 
   $stmt = $conn->prepare($sql);
  	if($stmt)
  	{	
	  	$stmt->bind_param("ssss", $name, $email, $password,$status);
		
		if($stmt->execute())
		{
			$json = array("status" => 1, "msg" => "Done User added!");
		}
		else
		{
			$json = array("status" => 0, "msg" => "Error adding user!");
		}
	}
	else
	{
		$json = array("status" => 0, "msg" => "Request method not accepted");
	}
}


@mysqli_close($conn);

/* Output header */
	header('Content-type: application/json');
	echo json_encode($json);
?>

Pratik Soni · Accepted Answer · 2015-07-07 17:12:49Z

1

Because you are using mysql_real_escape_string. The function is deprecated. You may use mysqli_real_escape_string instead of that.

edited Jul 7, 2015 at 17:12

answered Jul 7, 2015 at 15:08

Pratik Soni

2,57618 silver badges26 bronze badges

3 Comments

Pratik Soni Over a year ago

Try to catch all errors and you may get the error message. by error_reporting(E_ALL);

Walorn Over a year ago

Good catch, tried to update to all mysqli but seems I missed that important one. Didn't fix it though sadly, I think it has something to do with my _Post payload.

tadman Over a year ago

NO. Do not use addslashes for anything related to SQL.

Walorn · Accepted Answer · 2015-07-07 16:03:46Z

0

Thanks for all the help guys! I have it working though I do not understand it. The problem was indeed in my Advanced Rest Client application. I was sending the payload as application/json and it wasn't getting the POST data but when I changed the Content-Type to application/x-www-form-urlencoded it worked. If anyone wants to explain why that fixed it I will appreciate it and I'm sure others will as well.

answered Jul 7, 2015 at 16:03

Walorn

1511 gold badge4 silver badges12 bronze badges

Collectives™ on Stack Overflow

PHP insert into MySQL database with REST

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related