Cant INSERT and UPDATE but can DELETE in CRUD system using jQuery, JSON and DataTables

PHP
,
1

Hi i used one of the example in sitepoint https://www.sitepoint.com/creating-a-scrud-system-using-jquery-json-and-datatables/ to create a SCRUD system but the problem is i can only delete the data in mysql and not update and create new data to the database. since its similar so i will just post my query to check wheher its correct or wrong. Additional info: My database is imported form an excel file btw and i have set my primary key as my30_rsl_id with auto increment.

ADD company query:

 // Add company
    $query = "INSERT INTO my30_rsl SET ";
    if (isset($_GET['id']))         { $query .= "id         = '" . mysqli_real_escape_string($db_connection, $_GET['id']). "', "; }
    if (isset($_GET['SupplierName'])) { $query .= "SupplierName = '" . mysqli_real_escape_string($db_connection, $_GET['SupplierName']) . "', "; }
    if (isset($_GET['Commodity']))   { $query .= "Commodity   = '" . mysqli_real_escape_string($db_connection, $_GET['Commodity']). "', ";}
    if (isset($_GET['SubCategory']))      { $query .= "SubCategory      = '" . mysqli_real_escape_string($db_connection, $_GET['SubCategory']). "', "; }
	if (isset($_GET['Location']))  { $query .= "Location  = '" . mysqli_real_escape_string($db_connection, $_GET['Location']) . "', ";   }
	if (isset($_GET['Status']))    { $query .= "Status    = '" . mysqli_real_escape_string($db_connection, $_GET['Status'])  . "', "; }
	if (isset($_GET['Supplier_Relationship_Owner']))   { $query .= "Supplier_Relationship_Owner   = '" . mysqli_real_escape_string($db_connection, $_GET['Supplier_Relationship_Owner']). "', "; }
	if (isset($_GET['Supplier_contact'])) { $query .= "Supplier_contact = '" . mysqli_real_escape_string($db_connection, $_GET['Supplier_contact']). "', "; }
	if (isset($_GET['Email_Address'])) { $query .= "Email_Address = '" . mysqli_real_escape_string($db_connection, $_GET['Email_Address']). "', "; }
	if (isset($_GET['NPI_or_Production'])) { $query .= "NPI_or_Production = '" . mysqli_real_escape_string($db_connection, $_GET['NPI_or_Production']) . "', "; }
	if (isset($_GET['ProtoLeadTime'])) { $query .= "ProtoLeadTime = '" . mysqli_real_escape_string($db_connection, $_GET['ProtoLeadTime']) . "', "; }
	if (isset($_GET['Capabilities'])) { $query .= "Capabilities = '" . mysqli_real_escape_string($db_connection, $_GET['Capabilities'])  . "', ";}
	if (isset($_GET['Strengths'])) { $query .= "Strengths = '" . mysqli_real_escape_string($db_connection, $_GET['Strengths']) . "', ";}
	if (isset($_GET['Weaknesses'])) { $query .= "Weaknesses = '" . mysqli_real_escape_string($db_connection, $_GET['Weaknesses']). "', "; }
	if (isset($_GET['Costing'])) { $query .= "Costing = '" . mysqli_real_escape_string($db_connection, $_GET['Costing']) . "', "; }
	if (isset($_GET['Comments'])) { $query .= "Comments = '" . mysqli_real_escape_string($db_connection, $_GET['Comments']). "', "; }
	
	if (isset($_GET['Indicator'])) { $query .= "Indicator = '" . mysqli_real_escape_string($db_connection, $_GET['Indicator']) . "'";   }
	

	
    $query = mysqli_query($db_connection, $query);
    if (!$query){
      $result  = 'error';
      $message = 'query error';
    } else {
      $result  = 'success';
      $message = 'query success';
    }
  
  } elseif ($job == 'edit_company'){

the EDIT Company query:

 // Edit company
    if ($id == ''){
      $result  = 'error';
      $message = 'id missing';
    } else {
      $query = "UPDATE my30_rsl SET ";
    if (isset($_GET['id']))         { $query .= "id         = '" . mysqli_real_escape_string($db_connection, $_GET['id']). "', "; }
    if (isset($_GET['SupplierName'])) { $query .= "SupplierName = '" . mysqli_real_escape_string($db_connection, $_GET['SupplierName']) . "', "; }
    if (isset($_GET['Commodity']))   { $query .= "Commodity   = '" . mysqli_real_escape_string($db_connection, $_GET['Commodity'])   . "', "; }
    if (isset($_GET['SubCategory']))      { $query .= "SubCategory      = '" . mysqli_real_escape_string($db_connection, $_GET['SubCategory'])  . "', ";}
	if (isset($_GET['Location']))  { $query .= "Location  = '" . mysqli_real_escape_string($db_connection, $_GET['Location']). "', "; }
	if (isset($_GET['Status']))    { $query .= "Status    = '" . mysqli_real_escape_string($db_connection, $_GET['Status'])  . "', "; }
	if (isset($_GET['Supplier_Relationship_Owner']))   { $query .= "Supplier_Relationship_Owner   = '" . mysqli_real_escape_string($db_connection, $_GET['Supplier_Relationship_Owner']) . "', ";}
	if (isset($_GET['Supplier_contact'])) { $query .= "Supplier_contact = '" . mysqli_real_escape_string($db_connection, $_GET['Supplier_contact']). "', "; }
	if (isset($_GET['Email_Address'])) { $query .= "Email_Address = '" . mysqli_real_escape_string($db_connection, $_GET['Email_Address']). "', ";  }
	if (isset($_GET['NPI_or_Production'])) { $query .= "NPI_or_Production = '" . mysqli_real_escape_string($db_connection, $_GET['NPI_or_Production']) . "', "; }
	if (isset($_GET['ProtoLeadTime'])) { $query .= "ProtoLeadTime = '" . mysqli_real_escape_string($db_connection, $_GET['ProtoLeadTime']) . "', "; }
	if (isset($_GET['Capabilities'])) { $query .= "Capabilities = '" . mysqli_real_escape_string($db_connection, $_GET['Capabilities'])  . "', ";}
	if (isset($_GET['Strengths'])) { $query .= "Strengths = '" . mysqli_real_escape_string($db_connection, $_GET['Strengths']) . "', ";}
	if (isset($_GET['Weaknesses'])) { $query .= "Weaknesses = '" . mysqli_real_escape_string($db_connection, $_GET['Weaknesses']). "', "; }
	if (isset($_GET['Costing'])) { $query .= "Costing = '" . mysqli_real_escape_string($db_connection, $_GET['Costing']) . "', "; }
	if (isset($_GET['Comments'])) { $query .= "Comments = '" . mysqli_real_escape_string($db_connection, $_GET['Comments']). "', "; }
	
	if (isset($_GET['Indicator'])) { $query .= "Indicator = '" . mysqli_real_escape_string($db_connection, $_GET['Indicator']) . "'";   }
	
	
	
	
      $query .= "WHERE my30_rsl_id = '" . mysqli_real_escape_string($db_connection, $id) . "'";
      $query  = mysqli_query($db_connection, $query);
      if (!$query){
        $result  = 'error';
        $message = 'query error';
      } else {
        $result  = 'success';
        $message = 'query success';
      }
    }
    
  } elseif ($job == 'delete_company'){

I hope i can find help here because i already spent hours looking for mistake but i cant seem to find it. If the full code is needed i can upload it
data.php (10.6 KB)
index.html (8.4 KB)
layout.css (14.3 KB)
webapp.js (10.8 KB)

2

i have set my primary key as my30_rsl_id with auto increment.

Why didn’t you name it just id? And, if you choose such a fancy name, why you’re still using id in your queries?

Note that error reporting you are using is absolutely unacceptable. It tells you nothing at all of the actual reason why a query failed, while it should.

Please read my article on the PHP error reporting basics and configure your PHP and mysqli accordingly. Properly configured PHP will always tell you in the full detail what your problem is. That’s a really amazing feature that helps a programmer a lot. All you need is just to start using it.

1 Like
3

i changed id to my primary key but the same problem still exist lol

4

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.