insert into data

lokstock · December 26, 2014, 2:42am

Hi all, I am using a CRUD generator for my php. The issue is I cant submit my data to my database, however all the other features work.

When a user clicks the button it should activate the function below and go to ajax/ajax.php.

[php]Submit

Successfully Perform Operation

"; } else { echo "

Failed to Perform Operation ".$mysqli->error."

"; } } else if(isset($_POST["Staff_ID"]) && isset($_POST["First_Name"]) && isset($_POST["Middle_Name"]) && isset($_POST["Last_Name"]) && isset($_POST["DOB"]) && isset($_POST["Office_ID"]) && isset($_POST["Address"]) && isset($_POST["Vehicle"]) && isset($_POST["Gender"]) && isset($_POST["Telephone"]) && isset($_POST["Position_ID"])) { $checkToInsert = $mysqli->query("SELECT Staff_ID from STAFF where Staff_ID='".$mysqli->real_escape_string($_POST["Staff_ID"])."' || First_Name='".$mysqli->real_escape_string($_POST["First_Name"])."' || Middle_Name='".$mysqli->real_escape_string($_POST["Middle_Name"])."' || Last_Name='".$mysqli->real_escape_string($_POST["Last_Name"])."' || DOB='".$mysqli->real_escape_string($_POST["DOB"])."' || Office_ID='".$mysqli->real_escape_string($_POST["Office_ID"])."' || Address='".$mysqli->real_escape_string($_POST["Address"])."' || Vehicle='".$mysqli->real_escape_string($_POST["Vehicle"])."' || Gender='".$mysqli->real_escape_string($_POST["Gender"])."' || Telephone='".$mysqli->real_escape_string($_POST["Telephone"])."' || Position_ID='".$mysqli->real_escape_string($_POST["Position_ID"])."'"); if($checkToInsert->num_rows < 1 ) { $mysqli->query("INSERT into STAFF set Staff_ID='".$mysqli->real_escape_string($_POST["Staff_ID"])."', First_Name='".$mysqli->real_escape_string($_POST["First_Name"])."', Middle_Name='".$mysqli->real_escape_string($_POST["Middle_Name"])."', Last_Name='".$mysqli->real_escape_string($_POST["Last_Name"])."', DOB='".$mysqli->real_escape_string($_POST["DOB"])."', Office_ID='".$mysqli->real_escape_string($_POST["Office_ID"])."', Address='".$mysqli->real_escape_string($_POST["Address"])."', Vehicle='".$mysqli->real_escape_string($_POST["Vehicle"])."', Gender='".$mysqli->real_escape_string($_POST["Gender"])."', Telephone='".$mysqli->real_escape_string($_POST["Telephone"])."', Position_ID='".$mysqli->real_escape_string($_POST["Position_ID"])."'"); if($mysqli->affected_rows > 0){ echo "

Successfully Perform Operation

"; } else { echo "

Failed to Perform Operation ".$mysqli->error."

"; } } else { echo "

Oh! Snap Value is already available

"; } } else if(isset($_POST["pk"]) && isset($_POST["delete"]) && $_POST["delete"] == "true") { $mysqli->query("DELETE from STAFF where Staff_ID='".$mysqli->real_escape_string($_POST["pk"])."'"); if($mysqli->affected_rows > 0){ echo "

Successfully Perform Operation

"; } else { echo "

Failed to Perform Operation ".$mysqli->error."

"; } } else { echo "

Kindly fill all the form properly

"; } ?>[/php] can anyone spot my issue? I have provided an ERD of my database. --- ![aaaaaa.jpg|690x426](upload://tGFfCKGmcRUF9tKGbJmEqYzaOOW.jpg)

Strider64 · December 26, 2014, 4:42am

OK, the first thing I notice is there is no use of functions (methods in OOP), which is severely hurting your cause. I suggest at least learning functions for it will help out greatly. Secondly, an let me state this only my recommendation I would switch over to PDO instead mysqli for it will give you more flexibility with various databases. An from the looks of your database structure I think MySQL might not cut the mustard. An with mysqli you are limit to MySQL and if you happen to need to change you then you’ll be stuck.

A true iCRUD system uses Object-Oriented Programming.

For example here is my interface for that I use in iCRUD:

[php]<?php
/* The iCrud interface.

The interface identifies four methods:
- create()
- read()
- update()
- delete()
  */
  interface iCrud {

public function create($data);

public function read();

public function update($data);

public function delete($id=NULL);

}[/php]

An here is the CRUD in the iCRUD for my CMS:
[php]<?php

class OnlineJournal implements iCrud {

protected $query = NULL;
protected $stmt = NULL;
protected $result = NULL;
protected $query_params = NULL;
protected $row = NULL;
protected $status_message = NULL;
protected $category = ‘home_page’;

public function __construct(array $data = NULL) {
if (is_array($data) && !empty($data[‘title’]) && !empty($data[‘message_post’])) {
$db = Database::getInstance();
$pdo = $db->getConnection();

  /* Set the query variable */
  $this->query = 'INSERT INTO blog (category, client_id, created_by, title, message_post, date_added) VALUES (:category, :client_id, :created_by, :title, :message_post, NOW())';

  /* Set the query variable */
  $this->stmt = $pdo->prepare($this->query);
 
  /* Execute the Query */
  $this->result = $this->stmt->execute(array(':category' => $this->category, ':client_id' => $data['client_id'], ':created_by' => $data['created_by'], ':title' => $data['title'], ':message_post' => $data['message_post']));

  return 'Data Successfully Inserted!';
}

}

public function create($data) {
return self::__construct($data);
}

public function setCategory($category) {
$this->category = $category;
}

public function getCategory() {
return $this->category;
}

public function read() {
$db = Database::getInstance();
$pdo = $db->getConnection();
$this->query = ‘SELECT id, category, client_id, created_by, title, message_post, date_updated, date_added FROM blog WHERE category=:category ORDER BY date_added DESC’;
$this->stmt = $pdo->prepare($this->query);
$this->stmt->execute([’:category’ => $this->category]);
return $this->stmt; // if there is data in the database table then return:
}

public function update($data) {

$db = Database::getInstance();
$pdo = $db->getConnection();
/* Update the edited blog */
$this->query = 'UPDATE blog SET title=:title, message_post=:message_post, date_updated=NOW() WHERE id=:id';
/* Prepare the statement */
$this->stmt = $pdo->prepare($this->query);
/* Execute the statement */
$this->result = $this->stmt->execute(array(':title' => $data['title'], ':message_post' => $data['message_post'], ':id' => $data['id']));

return ($this->result) ? true : false;

}

public function delete($id = NULL) {
$db = Database::getInstance();
$pdo = $db->getConnection();
$this->stmt = $pdo->prepare(‘DELETE FROM blog WHERE id=:id’);
$this->result = $this->stmt->execute(array(’:id’ => $id));
return ($this->result) ? true : false;
}

}
[/php]

and for my login class:
[php]class LoginClass implements iCrud {

protected $query = NULL;
protected $stmt = NULL;
protected $result = NULL;
protected $query_params = NULL;
protected $row = NULL;
protected $loginStatus = false;
public $user = NULL;

// Constructor takes an array of data:
public function __construct(array $data = NULL) {
if (is_array($data)) {
$db = Database::getInstance();
$pdo = $db->getConnection();

  /* Secure the Password */
  $data['password'] = password_hash($data['password'], PASSWORD_BCRYPT, array("cost" => 15));
  
  /* Set the query variable */
  $this->query = 'INSERT INTO users (username, password, confirmation_code, security_level, first_name, last_name, email, date_added) VALUES (:username, :password, :confirmation_code, :security_level, :first_name, :last_name, :email, NOW())';

  /* Prepare the query */
  $this->stmt = $pdo->prepare($this->query);
  try {
    $this->stmt->execute(array(':username' => $data['username'], ':password' => $data['password'], ':confirmation_code' => $data['confirmation_code'], ':security_level' => $data['security_level'], ':first_name' => $data['first_name'], ':last_name' => $data['last_name'], ':email' => $data['email']));
  } catch (PDOException $error)
  {
    // Check to see if name is already exists:
    $errorCode = $error->errorInfo[1];
    if ($errorCode == MYSQL_ERROR_DUPLICATE_ENTRY) {
      error_log("Duplicate Name was Enter", 1, "[email protected]");
    } else {
      throw $error;
    }
  }
}

}

// End of constructor.
// This method also takes an array of data:
public function create($data) {
self::__construct($data);
}

// Function for returning information about the current object:
public function read(array $data = NULL) {
$db = Database::getInstance();
$pdo = $db->getConnection();
$this->query = ‘SELECT userId, username, password, security_level, first_name, last_name, email FROM users WHERE username=:username’;
$this->query_params = [’:username’ => $data[‘username’]];

try {
  $this->stmt = $pdo->prepare($this->query);
  $this->result = $this->stmt->execute($this->query_params);
} catch (Exception $ex) {
  die("Failed to run query: " . $ex->getMessage());
}

$this->stmt->setFetchMode(PDO::FETCH_CLASS, 'Users');
$this->user = $this->stmt->fetch();
if ($this->user) {
  $this->loginStatus = password_verify($data['password'], $this->user->getPassword());
  unset($data['password']); // Unset the password:
  $this->user->clearPassword();
}

if ($this->loginStatus) {
  return $this->user;
}

}

// Function for updating the object:
public function update($data) {

}

// Functin for getting rid of the current object:
public function delete($id = NULL) {
unset($id);
unset($this->user);
unset($_SESSION[‘user’]);
}[/php]

As you can see it’s very flexible by that I mean it can be modify very easily and it forces you to keep your code structured.

I suggest “PHP Objects, Patterns, and Practice” by Matt Zandstra to learn PHP OOP, but first I would get functions down pat. Just trying to help… Merry Christmas John

lokstock · December 26, 2014, 5:08am

Thanks for the feedback, my assignment requires me to have a simple working crud which can be generated, in my case i generated the code. next I am only using MySQL database and thats the only server provided by my university. I can see the structure is very tidy, however im very short on time to learn anything new. I am stuck with multiple assignments and dissertation and the deadlines are closing in on me Thanks for the information though, maybe when I finish these year I will take a deeper look into it.

Thanks