mukund mukund - 4 months ago 11
MySQL Question

How do i insert record when row is not exist in database within one table

I am trying to insert a new record and same time it check the record is exist or not.but the query fails to show the function please give me suggestions. when application runs it shows FAILURE ( the Data could not be inserted. Signup failed.)this message from java file.I want to display messages in application records inserted or record exist.

//java file
public class SignupActivity extends AsyncTask<String, Void, String> {

private Context context;

public SignupActivity(Context context) {
this.context = context;
}

protected void onPreExecute() {

}

@Override
protected String doInBackground(String... arg0) {
String fullName = arg0[0];
// String userName = arg0[1];
String passWord = arg0[1];
String phoneNumber = arg0[2];
String emailAddress = arg0[3];

String link;
String data;
BufferedReader bufferedReader;
String result;

try {
data = "?fullname=" + URLEncoder.encode(fullName, "UTF-8");
// data += "&username=" + URLEncoder.encode(userName, "UTF-8");
data += "&password=" + URLEncoder.encode(passWord, "UTF-8");
data += "&phonenumber=" + URLEncoder.encode(phoneNumber, "UTF-8");
data += "&emailaddress=" + URLEncoder.encode(emailAddress, "UTF-8");

link = "http://hostogen.com/mangoair10/tryrr.php" + data;
URL url = new URL(link);
HttpURLConnection con = (HttpURLConnection) url.openConnection();

bufferedReader = new BufferedReader(new InputStreamReader(con.getInputStream()));
result = bufferedReader.readLine();
return result;
} catch (Exception e) {
return new String("Exception: " + e.getMessage());
}
}
@Override
protected void onPostExecute(String result) {
String jsonStr = result;
if (jsonStr != null) {
try {
JSONObject jsonObj = new JSONObject(jsonStr);
String query_result = jsonObj.getString("query_result");
if (query_result.equals("SUCCESS")) {
Toast.makeText(context, "Data inserted successfully. Signup successfull.", Toast.LENGTH_LONG).show();

} else if (query_result.equals("FAILURE")) {
Toast.makeText(context, "Data could not be inserted. Signup failed.", Toast.LENGTH_SHORT).show();

}
else {
Toast.makeText(context, "Couldn't connect to remote database.", Toast.LENGTH_SHORT).show();
}
} catch (JSONException e) {
e.printStackTrace();
// Toast.makeText(context, "Error parsing JSON Please data Fill all the records.", Toast.LENGTH_SHORT).show();
Toast.makeText(context, "Please LogIn", Toast.LENGTH_SHORT).show();

}
} else {
Toast.makeText(context, "Couldn't get any JSON data.", Toast.LENGTH_SHORT).show();
}
}
}

//php file
<?php
$con=mysqli_connect("localhost","hosto4p7_hosto","hostogen123","hosto4p7_hostogen");
if (mysqli_connect_errno($con))
{
echo '{"query_result":"ERROR"}';
}

$fullName = $_GET['fullname'];
//$userName = $_GET['username'];
$passWord = $_GET['password'];
$phoneNumber = $_GET['phonenumber'];
$emailAddress = $_GET['emailaddress'];

$sql = "INSERT INTO users10 (fullname,password,phone,email)
SELECT * FROM (SELECT '$fullName', '$passWord', '$phoneNumber','$emailAddress') AS tmp
WHERE NOT EXISTS (
SELECT name FROM users10 WHERE phone = '$phoneNumber' OR email='$emailAddress'
) LIMIT 1 ";

$result=mysqli_query($con,$sql);

if($result == true)
{
echo '{"query_result":"SUCCESS"}';
}else{
echo '{"query_result":"FAILURE"}';
}
mysqli_close($con);
?>

Answer

As @ali already said you need to check for user existence an then go ahead and create it:

/* Connect to the DB using MySQLi class */
$mysql = new mysqli('localhost', 'user', 'password', 'database');    

/* If an error code is reported... */
if($mysql->connect_errno)
{
  echo json_encode(array(
    'query_result' => 'ERROR'
  ));
}
/* Otherwise go ahead... */
else
{
  /* Prepare a select statement to check user existence */
  $selectStmt = $mysql->prepare("SELECT * FROM `users10` WHERE `phone` = ? OR `email` = ?");

  /* Retrieve arguments */
  $fullName = $_GET['fullname'];
  //$userName = $_GET['username'];
  $passWord = $_GET['password'];
  $phoneNumber = $_GET['phonenumber'];
  $emailAddress = $_GET['emailaddress'];

  /* Binding parameters */
  $selectStmt->bind_param('ss', $phoneNumber, $emailAddress);

  /* Execute statement */
  if (!$selectStmt->execute()) {
    echo json_encode(array(
      'query_result' => 'ERROR'
    ));
  }
  else
  {
    /* If the number of returned rows is 0 */
    if(0 === $selectStmt->get_result()->num_rows)
    {
      $insertStmt = $mysql->prepare("INSERT INTO `users10` (`fullname`, `password`, `phone`, `email`) VALUES (?, ?, ?, ?)");
      $insertStmt->bind_param('ssss', $fullName, $passWord, $phoneNumber, $emailAddress);

      /* Try to insert the new user */
      if (!$insertStmt->execute()) {
        echo json_encode(array(
          'query_result' => 'FAILURE'
        ));
      }
      else
      {
        echo json_encode(array(
          'query_result' => 'SUCCESS'
        ));
      }
    }
    else
    {
      echo json_encode(array(
        'query_result' => 'FAILURE'
      ));
    }
  }
}
Comments