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', '[email protected]', '', '', '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.

INSERT INTO ON DUPLICATE KEY UPDATE see dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html — nbk
– nbk, Commented Oct 16, 2022 at 22:40
I couldn't figure that out how to do with an array. Could you please help me with my code? — imp
– imp, Commented Oct 16, 2022 at 22:56
I don't think you can use ON DUPLICATE KEY UPDATE for this. If the row already exists, you need to use a SELECT query to get its ID, otherwise you use $stmt->insert_id to get the ID of the new row. @Nbk — Barmar
– Barmar, Commented Oct 16, 2022 at 23:23
Is $userid supposed to be the same $stmt->insert_id as $relid? Or should that be $stmt2->insert_id? — Barmar
– Barmar, Commented Oct 16, 2022 at 23:31
Yes it supposed to be same. $userid does same thing with $relid. I am adding data tblcustomfieldsvalues table and tblcontacts table according to tblclients id — imp
– imp, Commented Oct 17, 2022 at 0:16

imp · Accepted Answer · 2022-10-18 22:47:32Z

2

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.

answered Oct 18, 2022 at 22:47

imp

736 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ken Lee Over a year ago

great ! have a good day

Collectives™ on Stack Overflow

Control data if exist in array

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related