Control data if exist in array

Question

i have two different table in mysql and i am getting data from a json file with curl.

My first table name is "tblclients" which is this table store client data. My second table name is "tblcustomfieldsvalues" and this table using "tblclients" table's "id" value for "$relid" column. (id value is primary key and autoincrement for "tblclients")

When i am getting data from json file i can check "value" column from "tblcustomfieldsvalues" because this is only one unique value in the json file.

So, i want to check my mysql table if i have same data comes from the json file. If yes, i need to update data. If not, i have to insert new data.

And also, i want to do same progress for my "tblclients" data. But this one is really confused me because i don't know how to i check same data if is exist.

I think i should do some sql query for it. But i couldn't figure that out.

$json = json_decode($result, true);
curl_close($curl);
//print_r($result);
//print_r($json);

$inserted_rows = 0;

$stmt = $mysqli->prepare(" INSERT INTO tblclients(company,country,active,datecreated,default_currency,show_primary_contact,registration_confirmed,addedfrom,phonenumber) 
VALUES(?,?,?,?,?,?,?,?,?)");

$stmt->bind_param("siisiiiis", $company, $country, $active, $datecreated, $default_currency, $show_primary_contact, $registration_confirmed, $addedfrom, $phonenumber);

$stmt2 = $mysqli->prepare(" INSERT INTO tblcustomfieldsvalues(relid,fieldid,fieldto,value) 
VALUES(?,?,?,?)");


$stmt3 = $mysqli->prepare(" INSERT INTO tblcontacts(userid, is_primary, firstname, lastname, datecreated, email, phonenumber) 
VALUES(?,?,?,?,?,?,?)");


$stmt2->bind_param("iiss", $relid, $fieldid, $fieldto, $customercode);

$stmt3->bind_param("iisssss", $userid, $is_primary, $firstname, $lastname, $datecreated2, $email, $phonenumber);

foreach ($json['result'] as $product) {


    $company = $product['company'];
    $country = $product['country'];
    $active = $product['active'];
    $datecreated = $product['_date'];
    $default_currency = $product['crr'];
    $show_primary_contact = $product['contact'];
    $registration_confirmed = $product['confirmed'];
    $addedfrom = $product['from'];

    $stmt->execute();

    $relid = $stmt->insert_id;
    $fieldid = "1";
    $fieldto = "customers";
    $customercode = $product['customercode'];

    $stmt2->execute();


    $userid = $stmt->insert_id;
    $is_primary = "1";
    $firstname = $product['related'];

    if ($email === NULL) {
        $email = " ";
    } else {
        $email = $product['email'];
    }

    $phonenumber = $product['phone'];

    $lastname = " ";
    $datecreated2 = $product['_dates'];

    $stmt3->execute();

    $inserted_rows++;
}


?>

also sql file (related tables) below;

-- phpMyAdmin SQL Dump
-- version 5.1.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Oct 18, 2022 at 11:21 AM
-- Server version: 10.4.21-MariaDB
-- PHP Version: 8.1.1

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `test`

-- --------------------------------------------------------
--
-- Table structure for table `tblclients`
--

CREATE TABLE `tblclients` (
  `userid` int(11) NOT NULL,
  `company` varchar(191) DEFAULT NULL,
  `vat` varchar(50) DEFAULT NULL,
  `phonenumber` varchar(30) DEFAULT NULL,
  `country` int(11) NOT NULL DEFAULT 0,
  `city` varchar(100) DEFAULT NULL,
  `zip` varchar(15) DEFAULT NULL,
  `state` varchar(50) DEFAULT NULL,
  `address` varchar(191) DEFAULT NULL,
  `website` varchar(150) DEFAULT NULL,
  `datecreated` datetime NOT NULL,
  `active` int(11) NOT NULL DEFAULT 1,
  `leadid` int(11) DEFAULT NULL,
  `billing_street` varchar(200) DEFAULT NULL,
  `billing_city` varchar(100) DEFAULT NULL,
  `billing_state` varchar(100) DEFAULT NULL,
  `billing_zip` varchar(100) DEFAULT NULL,
  `billing_country` int(11) DEFAULT 0,
  `shipping_street` varchar(200) DEFAULT NULL,
  `shipping_city` varchar(100) DEFAULT NULL,
  `shipping_state` varchar(100) DEFAULT NULL,
  `shipping_zip` varchar(100) DEFAULT NULL,
  `shipping_country` int(11) DEFAULT 0,
  `longitude` varchar(191) DEFAULT NULL,
  `latitude` varchar(191) DEFAULT NULL,
  `default_language` varchar(40) DEFAULT NULL,
  `default_currency` int(11) NOT NULL DEFAULT 0,
  `show_primary_contact` int(11) NOT NULL DEFAULT 0,
  `stripe_id` varchar(40) DEFAULT NULL,
  `registration_confirmed` int(11) NOT NULL DEFAULT 1,
  `addedfrom` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tblclients`
--

INSERT INTO `tblclients` (`userid`, `company`, `vat`, `phonenumber`, `country`, `city`, `zip`, `state`, `address`, `website`, `datecreated`, `active`, `leadid`, `billing_street`, `billing_city`, `billing_state`, `billing_zip`, `billing_country`, `shipping_street`, `shipping_city`, `shipping_state`, `shipping_zip`, `shipping_country`, `longitude`, `latitude`, `default_language`, `default_currency`, `show_primary_contact`, `stripe_id`, `registration_confirmed`, `addedfrom`) VALUES
(1, 'TEST COMPANY', '', '', 0, '', '', '', '', '', '2022-10-18 12:19:49', 1, NULL, '', '', '', '', 0, '', '', '', '', 0, NULL, NULL, '', 0, 0, NULL, 1, 1);

-- --------------------------------------------------------

--
-- Table structure for table `tblcontacts`
--

CREATE TABLE `tblcontacts` (
  `id` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `is_primary` int(11) NOT NULL DEFAULT 1,
  `firstname` varchar(191) NOT NULL,
  `lastname` varchar(191) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phonenumber` text NOT NULL,
  `title` varchar(100) DEFAULT NULL,
  `datecreated` datetime NOT NULL,
  `password` varchar(255) DEFAULT NULL,
  `new_pass_key` varchar(32) DEFAULT NULL,
  `new_pass_key_requested` datetime DEFAULT NULL,
  `email_verified_at` datetime DEFAULT NULL,
  `email_verification_key` varchar(32) DEFAULT NULL,
  `email_verification_sent_at` datetime DEFAULT NULL,
  `last_ip` varchar(40) DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
  `last_password_change` datetime DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `profile_image` varchar(191) DEFAULT NULL,
  `direction` varchar(3) DEFAULT NULL,
  `invoice_emails` tinyint(1) NOT NULL DEFAULT 1,
  `estimate_emails` tinyint(1) NOT NULL DEFAULT 1,
  `credit_note_emails` tinyint(1) NOT NULL DEFAULT 1,
  `contract_emails` tinyint(1) NOT NULL DEFAULT 1,
  `task_emails` tinyint(1) NOT NULL DEFAULT 1,
  `project_emails` tinyint(1) NOT NULL DEFAULT 1,
  `ticket_emails` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tblcontacts`
--

INSERT INTO `tblcontacts` (`id`, `userid`, `is_primary`, `firstname`, `lastname`, `email`, `phonenumber`, `title`, `datecreated`, `password`, `new_pass_key`, `new_pass_key_requested`, `email_verified_at`, `email_verification_key`, `email_verification_sent_at`, `last_ip`, `last_login`, `last_password_change`, `active`, `profile_image`, `direction`, `invoice_emails`, `estimate_emails`, `credit_note_emails`, `contract_emails`, `task_emails`, `project_emails`, `ticket_emails`) VALUES
(1, 1, 1, 'TEST NAME', 'TEST LASTNAME', 'TEST@MAIL.COM', '', '', '2022-10-18 12:20:13', '$2a$08$sFmYWS6beMwnSlr90MvSwOOEFy0LIbUJ5iPePHpnN/Y0I/QWCImdO', NULL, NULL, '2022-10-18 12:20:13', NULL, NULL, NULL, NULL, NULL, 1, NULL, '', 1, 1, 1, 1, 1, 1, 1);

-- --------------------------------------------------------


-- --------------------------------------------------------

--
-- Table structure for table `tblcustomfieldsvalues`
--

CREATE TABLE `tblcustomfieldsvalues` (
  `id` int(11) NOT NULL,
  `relid` int(11) NOT NULL,
  `fieldid` int(11) NOT NULL,
  `fieldto` varchar(15) NOT NULL,
  `value` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tblcustomfieldsvalues`
--

INSERT INTO `tblcustomfieldsvalues` (`id`, `relid`, `fieldid`, `fieldto`, `value`) VALUES
(1, 1, 1, 'customers', 'TEST CUSTOMER CODE');

-- --------------------------------------------------------


-- Indexes for table `tblclients`
--
ALTER TABLE `tblclients`
  ADD PRIMARY KEY (`userid`);

--

--
-- Indexes for table `tblcustomfieldsvalues`
--
ALTER TABLE `tblcustomfieldsvalues`
  ADD PRIMARY KEY (`id`),
  ADD KEY `relid` (`relid`),
  ADD KEY `fieldto` (`fieldto`),
  ADD KEY `fieldid` (`fieldid`);


--
-- AUTO_INCREMENT for table `tblclients`
--
ALTER TABLE `tblclients`
  MODIFY `userid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--

-- AUTO_INCREMENT for table `tblcustomfieldsvalues`
--
ALTER TABLE `tblcustomfieldsvalues`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--


/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

And i am new for coding, if you see any mistake or something please let me know and i can learn more.

Answer 1

Ok, i figured out. I put this code

$stmt2 = $mysqli->prepare (" INSERT INTO tblcustomfieldsvalues(relid,fieldid,fieldto,value) VALUES(?,?,?,?) ON DUPLICATE KEY UPDATE relid = VALUES(relid),fieldid = VALUES(fieldid),fieldto = VALUES(fieldto),value = VALUES(value), id = LAST_INSERT_ID(id)"); 

and i set unique "value" column at the table. Everything is ok now.