Missing data update

Beginners - Learning PHP
#1

Hello,
I have a code like below.
If the data from the form is not saved in the database, save it to the database.
If the data from the form is saved in the database, continue.
No problem so far

My question is:
The data is saved but there is new data for someone in the empty column how do I update this empty column?

CREATE TABLE IF NOT EXISTS `table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_email` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `company_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `company_authorized` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `company_mobile_phone` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `company_phone` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `company_address` varchar(250) NOT NULL,
  `group` int NOT NULL,
  `userid` int NOT NULL,
  `created` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_email` (`company_email`),
  UNIQUE KEY `company_name` (`company_name`),
  UNIQUE KEY `company_mobile_phone` (`company_mobile_phone`),
  UNIQUE KEY `company_phone` (`company_phone`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3;

    $company_email        	= filter_input(INPUT_POST,'company_email');
    $company_name          	= filter_input(INPUT_POST,'company_name');
    $company_authorized     = filter_input(INPUT_POST,'company_authorized');
    $company_mobile_phone	= filter_input(INPUT_POST,'company_mobile_phone');
    $company_phone         	= filter_input(INPUT_POST,'company_phone');
    $company_address      	= filter_input(INPUT_POST,'company_address');
	
        $company = $db->prepare(" SELECT * FROM table WHERE company_email=? AND company_name=? AND company_authorized=? AND company_mobile_phone=? AND company_phone=? ");
        $company->execute([$company_email,$company_name,$company_authorized,$company_mobile_phone,$company_phone]);
		
    if($company->rowCount() == '0'){

    $sql = $PDOdb->prepare("INSERT INTO table (
      company_email, 
      company_name, 
      company_authorized, 
      company_mobile_phone, 
      company_phone,
      company_address,
      group,
      userid,
      created)
        VALUES (
      :company_email, 
      :company_name, 
      :company_authorized, 
      :company_mobile_phone, 
      :company_phone,
      :company_address,
      :group
      :userid,
      :created)");

        $sql->bindValue(':company_email', !empty($company_email) ? $company_email : null, PDO::PARAM_STR);
        $sql->bindValue(':company_name', !empty($company_name) ? $company_name : null, PDO::PARAM_STR);
        $sql->bindValue(':company_authorized', !empty($company_authorized) ? $company_authorized : null, PDO::PARAM_STR);
        $sql->bindValue(':company_mobile_phone', !empty($company_mobile_phone) ? $company_mobile_phone : null, PDO::PARAM_STR);
        $sql->bindValue(':company_phone', !empty($company_phone) ? $company_phone : null, PDO::PARAM_STR);
        $sql->bindValue(':company_address', !empty($company_address) ? $company_address : null, PDO::PARAM_STR);
        $sql->bindValue(':group', isset($_SESSION['group']) && !empty($_SESSION['group']) ? $_SESSION['group'] : 0, PDO::PARAM_INT);
        $sql->bindValue(':userid', isset($_SESSION['userid']) && !empty($_SESSION['userid']) ? $_SESSION['userid'] : 0, PDO::PARAM_INT);
        $sql->bindValue(':created', time(), PDO::PARAM_INT);
        $sql->execute();
#2

Updating existing data involves the R and U part of CRUD (Create, Read, Update, Delete.) You would query for and list existing data (R), perhaps with a search feature to limit how many records are displayed, with an ‘edit’ link for each record, with the row’s id in each link. When an edit link is clicked, the code would query for that specific row of data (R), populate a form with the existing values, then when that form is submitted, trim, validate and update (U) the row with the new data values.

But first there are some problems with the C (Create) code. It doesn’t trim the data, so data consisting of all white-space characters will get inserted, it doesn’t validate data, setting up unique and helpful error messages for each validation error, so that the user will know which required fields are empty or fields having a specific format are not valid, and it doesn’t have ‘working’ duplicate value error handling for the insert query.

The use of filter_input, without any filter flag, only acts like an isset() statement. This is a lot of typing for nothing that just hides typo programming mistakes between your form and your form processing code. Your code should instead detect if a post method form was submitted. All the always-set fields will be set. Only unchecked checkbox/radio fields won’t be set. This is the only case where you should use an isset() statement.

The SELECT query is trying to find an exact match in all the fields in the WHERE clause (you actually want to find if any of the fields are duplicates - read on to find out how to do that.) What happens if someone enters a different email address or has a typo in one of the values? The select query won’t match an existing row and the code will try to insert a new row of data. However, the columns with UNIQUE KEY constraints will produce an error and the INSERT query will fail. Any query that has any UNIQUE KEY defined must have error handling to detect and report duplicates to the user. Since you must do this, there’s no point in having the SELECT query, just attempt to run the insert query and detect any duplicate errors. If there is more than one unique key defined, it is at this point that you would run a select query to find which fields have duplicate values and you would use an OR between the terms in the WHERE clause, to find all the rows with a duplicate in any of the unique key fields.

Once you do the above, by detecting is a post method form was submitted, trimming, then validating all the input data, there’s no point in all the logic and the bindValue statements. You can simply supply an array of the input values to the ->execute([…]) call, the way you are doing for the existing SELECT query.

#3

I’m assuming having an internal (one second). 1st of all. We have zero clue why you are making this, but if you are posting to the database and it is creating a mystery column, then most likely you should define that column as autogenerated. which is probably a key.

2ndly I assume i disagree. The posting should be line by line, you can always define what is going on later with more data and make a smarter adjustment.

3rdly “Qualitatively displaying data” in my opinion can be done by filtering or querying data instead of denying data. The reason why i say this is because you can run into a problem where you cannot for the life of you figure out why the information isnt showing up in the database. Because you big dummy put a validation too early. Instead i would leave the data post and just have yourself a fancy filter or validation on the backend.

4thly which means i disagree with the logic, prefer the duplicate records for added information later such as an address change where the phone number is the same. And of course i like to throw the post date so if that does happen where they have a new address,… you can look and see the most recent record with your eyeballs. GL Have fun. And remeber to go outside. - Sparxx

#4

To clarify 2ndly. I have a mystery column. I probably shouldn’t delete it. I probably shouldn’t change the code because I’m not exactly sure how long a 5 second fix will take. An hour, day, week etc. The better option is sometimes wait. So after 10 records you look and its incrementing or its a zip code… Then you can slap a label on the column.

You actually asked how to edit the column I believe. When you add more records and figure out its a zip code. Then you can make the ‘edit’ at that time. - Eddie Sparxx

#5

Thanks everyone for the reply
I did some research on what “CRUD” is but thought it was a whole operation but understood it as separate Create, Read, Update, and Delete separate operations.
I think we write our codes like that anyway, I couldn’t understand the difference, maybe because I’m not an advanced coder.

Sponsor our Newsletter | Privacy Policy | Terms of Service