如何将最后一个自动增量值作为外键到php中的另一个表

Question

How to get data from one table to the foreign key of another table in object oriented php, i have two table one called users which has two fields : id and phonenumber . another table is profile which has id,user_id profile_image, username, businessname and town where user_id is a foreign key of id in users table . and i wish i insert phone number in users table i get foreign key of user_id in profile table . please i need your help i am a beginer in oophp . Thanks any help will be greatly appreciated

Dbhandler.php which handles all functions

<?php
class DbHandler {
    private $conn;

    function __construct() {
        require_once dirname(__FILE__) . '/DbConnect.php';
        // opening db connection
        $db = new DbConnect();
        $this->conn = $db->connect();
    }

    public function createUser($phone) {
        $response = array();

        // insert query
        $stmt = $this->conn->prepare("INSERT INTO users(phone) values(?)");
        $stmt->bind_param("s",$phone );

        $result = $stmt->execute();

        $new_profile_id = $stmt->insert_id;

        $stmt->close();

        // Check for successful insertion
        if ($result) {
            $profile_result = $this->createProfile_userId($new_profile_id);

            // User successfully inserted
            return USER_CREATED_SUCCESSFULLY;
        } else {
            // Failed to create user
            return USER_CREATE_FAILED;
        }
    }
    return $response;
}


public function createProfile_userId($user_id) {
    //getUser's FNK
    $stmt = $this->conn->prepare("DELETE FROM profile where user_id = ?");
    $stmt->bind_param("i", $user_id);
    $stmt->execute();
    $result = $stmt->execute();

    $stmt->close();
    return $result;
}

public function createProfile($profile_image, $username, $businessname,$town) {

    $response = array();

    $stmt =$this->conn->query("SELECT  profile_id FROM profile ORDER BY profile_id ASC") ;

    $profile_id = 0;

    while($row = $stmt->fetch_array()){
        $profile_id = $row['profile_id'];
    }

    $path = "uploads/$profile_id.png";
    $actualpath = "https://localhost/GoodPriceApi/uploadImage/$path";

    // insert query 
    $stmt = $this->conn->prepare("INSERT INTO profile(profile_image, username, businessname, town) values(?, ?, ?, ?)");
    $stmt->bind_param("isss",$profile_image, $username, $businessname, $town);

    $result = $stmt->execute();

    $stmt->close();

    // Check for successful insertion
    if ($result) {
        file_put_contents($path,base64_decode($image));

        // User successfully inserted
        return USER_CREATED_SUCCESSFULLY;
    } else {
        // Failed to create user
        return USER_CREATE_FAILED;
    }

    return $response;
}
?>

The following file is used to call function.

create.php

<?php 
include './DbHandler.php';
$db = new DbHandler();

$response = array();

if ((isset($_POST['profile_image']) && isset($_POST['username']) &&       isset($_POST['businessname']) && isset($_POST['town']))!= '') {
    $profile_image = $_POST['profile_image'];
    $username = $_POST['username'];
    $businessname = $_POST['businessname'];
    $town = $_POST['town'];

    $res = $db->createProfile($profile_image, $username, $businessname,$town);

    if ($res == USER_CREATED_SUCCESSFULLY) {      
        $response["error"] = false;
        $response["message"] = "Profile created successfully";
    } else if ($res == USER_CREATE_FAILED) {
        $response["error"] = true;
        $response["message"] = "Sorry! Error occurred during profile creation.";
    } 
} 
?>

Answer 1

This question is an android backend php API , so to get user_id easily in profile table , we don't need this function:

  public function createProfile_userId($user_id) {
//getUser's FNK
$stmt = $this->conn->prepare("DELETE FROM profile where user_id = ?");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$result = $stmt->execute();

$stmt->close();
return $result;

}

But what i could do is to echo or to passs id and phonenumber with json to the interface of android after insertion to users and among parameters you got , you could pick out id as user_id and finally you set it to textview or texfield and finally use it as input to be inserted in profile table.

The following file is DbHandler.php which includes all functions of insertion in two tables :

<?php
 class DbHandler {
private $conn;
  function __construct() {
    require_once dirname(__FILE__) . '/DbConnect.php';
    // opening db connection
    $db = new DbConnect();
    $this->conn = $db->connect();
   }
  public function createUser($phone) {
    // insert query
    $stmt = $this->conn->prepare("INSERT INTO users(phone) values(?)");
    $stmt->bind_param("s",$phone );
    $result = $stmt->execute();
    $stmt->close();

    // Check for successful insertion
    if ($result) {

        // User successfully inserted
   return $result;
    } else {
        // Failed to create user
        return NULL;
    }
}
}
public function createProfile($user_id $profile_image, $username,     $businessname,$town) {


$stmt =$this->conn->query("SELECT  profile_id FROM profile ORDER BY profile_id ASC") ;

$profile_id = 0;

while($row = $stmt->fetch_array()){
    $profile_id = $row['profile_id'];
}

$path = "uploads/$profile_id.png";
$actualpath = "https://localhost/GoodPriceApi/uploadImage/$path";

// insert query 
$stmt = $this->conn->prepare("INSERT INTO profile(user_id, profile_image, username, businessname, town) values(?, ?, ?, ?, ?)");
$stmt->bind_param("issss",$user_id, $profile_image, $username, $businessname, $town);

$result = $stmt->execute();

$stmt->close();
// Check for successful insertion
if ($result) {
    file_put_contents($path,base64_decode($image));

    // User successfully inserted
   return $result;

   } else {
    // Failed to create user
    return NULL;
}

} ?>

Insert phone number into users and Echo id with json. the file is called NewUser.php :

     <?php
  include './DbHandler.php';
 $db = new DBHandler();
/ json response array
  $response = array("error" => FALSE); 
   if (isset($_POST['phone'])) { 
    // receiving the post params
     $phone = $_POST['phone'];
  // get the user by phone
     $user = $db->createUser($phone);
     if ($user != false) {
        // use is found
    $response["error"] = FALSE;
    //  $response["uid"] = $user["unique_id"];
      $response["user"]["id"] = $user["id"];
      $response["user"]["phone"] = $user["phone"];
        echo json_encode($response);
   } else {
    // user is not found with the credentials
    $response["error"] = TRUE;
      $response["error_msg"] = "Sorry we could not find you ";
      echo json_encode($response);
    }
   } else {
// required post params is missing
$response["error"] = TRUE;
$response["error_msg"] = "Required parameter phone is missing!";
  echo json_encode($response);
 }
  ?>

Android Java method used to get parameters passed by json and insert phone to users:

     private void createuser(final String phone) {
        // Tag used to cancel the request
        String tag_string_req = "req_Verfication";
        StringRequest strReq = new StringRequest(Request.Method.POST,
                Urls."127.0.0.1/myproject/NewUser.php", new Response.Listener<String>() {

            @Override
            public void onResponse(String response) {
                Log.d(TAG, "cerfication Response: " + response.toString());
                hideDialog();

                try {
                    JSONObject jObj = new JSONObject(response);
                    boolean error = jObj.getBoolean("error");

                    // Check for error node in json
                     if (!error) {

                        JSONObject user = jObj.getJSONObject("user");
                       String phone = user.getString("phone");
                        String id = user.getString("id");

                   // pass id to another activity so that will be used as    user_id 
                   //     abfter being set on textView or on textfield and 
                    //you hide it on interface  then you convert it toString as user_id 
                        // finally you insert it in profile table with other parameters
                        // for more details send me email on :    [e-mail]
                     Intent i = new Intent(createuserAccount.this,     createuserprofile.class);
                                i.putExtra("id", id);
                                startActivity(i);
                                finish();

                           }

                           } else {
                    // Error in verfication error message
                    String errorMsg = jObj.getString("error_msg");

                   Toast.makeText(getApplicationContext(),
                           errorMsg, Toast.LENGTH_LONG).show();            }
               } catch (JSONException e) {
                // JSON error
                e.printStackTrace();
                Toast.makeText(getApplicationContext(), "Json error: " +   e.getMessage(), Toast.LENGTH_LONG).show();
            }

        }
    }, new Response.ErrorListener() {

        @Override
        public void onErrorResponse(VolleyError error) {
            Log.e(TAG, "Verfication error Error: " + error.getMessage());
            Toast.makeText(getApplicationContext(),
                    error.getMessage(), Toast.LENGTH_LONG).show();
        }
    })

    {

        @Override
        protected Map<String, String> getParams() {

            // Posting parameters to verfication url
            Map<String, String> params = new HashMap<String, String>();
            params.put("phone",phone);
            return params;
        }
        };
 //
 //        // Adding request to request queue
       AppController.getInstance().addToRequestQueue(strReq,  tag_string_req);
    }

Insert all into profile all parameters with create.php file :

<?php 
 include './DbHandler.php';
 $db = new DbHandler();
$response = array();
 if ((isset($_POST['user_id']) && (isset($_POST['profile_image']) &&     isset($_POST['username']) &&       isset($_POST['businessname']) &&   isset($_POST['town']))!= '') {
    $user_id= $_POST['user_id'];
    $profile_image = $_POST['profile_image'];
     $username = $_POST['username'];
    $businessname = $_POST['businessname'];
   $town = $_POST['town'];

$res = $db->createProfile($user_id, $profile_image, $username, $businessname,$town);

if ($res == USER_CREATED_SUCCESSFULLY) {      
    $response["error"] = false;
    $response["message"] = "Profile created successfully";
} else if ($res == USER_CREATE_FAILED) {
    $response["error"] = true;
    $response["message"] = "Sorry! Error occurred during profile creation.";
} 
} 
?>