How to find a match by email and write data to the database from JSON?

Question

PHP

I am trying to add additional functionality for users in dle cms, more precisely, save additional information about the user to the dle cms database, while receiving additional data from JSON from a third-party service.
I want to run a cron script and check the correspondence of email from json with email of users registered on the site dle_users - email.
Sample json data :

[{"id":323,"userId":676,"createdAt":"2019-08-23T13:05:56.830Z","status":"APPROVED","currency":"RUR","paySystem":"visa","paySystemGroup":"creditCard","total":69.00,"ipAddress":"246.227.2.4","OneTimeOffer":false,"ware":{"id":812,"title":"Заголовок.","type":"DIGITAL","userId":728},"income":297.08,"customer":{"id":8832,"fio":"Петрович","email":"[email protected]"}}, {"id":324,"userId":677,"createdAt":"2019-08-23T14:05:56.830Z","status":"APPROVED","currency":"RUR","paySystem":"visa","paySystemGroup":"creditCard","total":69.00,"ipAddress":"246.227.2.4","OneTimeOffer":false,"ware":{"id":813,"title":"Заголовок.","type":"DIGITAL","userId":729},"income":297.08,"customer":{"id":8833,"fio":"Петрович","email":"[email protected]"}}, {"id":325,"userId":678,"createdAt":"2019-08-23T15:05:56.830Z","status":"APPROVED","currency":"RUR","paySystem":"visa","paySystemGroup":"creditCard","total":69.00,"ipAddress":"246.227.2.4","OneTimeOffer":false,"ware":{"id":814,"title":"Заголовок.","type":"DIGITAL","userId":730},"income":297.08,"customer":{"id":8834,"fio":"Петрович","email":"[email protected]"}}]

Json tree

There are 10 positions in json response.
I need to write to the database id, ware->id, ware->title, income if a match is found for the email address .
Those. somehow you need to take email from json and look for whether a user with a given email address is registered on the dle site and if this address is found, you need to get id, ware-> id, ware-> title, income data from json and save them in the dle_users database into the given user's table.
Several thousand users can be registered on the site. I want to access Json as often as possible via cron so that the site always has up-to-date information.
How to correctly implement such functionality so that the system does not hang and when json is accessed again, the previously received data is not added to the database again (apparently, you need to check by id from json, because it is unique)?
Also, very important, in the sample database, I did not add fields for id, ware->id, ware->title, income. I don’t know how to do it right, if you check the previously placed data, then the id from json must be saved so that it is unique...
Please help me figure it out. Point in the right direction.
Thank you.
Below is a database dump, table dle_users

--
-- Структура таблицы `dle_users`
--

CREATE TABLE `dle_users` (
  `email` varchar(50) NOT NULL DEFAULT '',
  `password` varchar(255) NOT NULL DEFAULT '',
  `name` varchar(40) NOT NULL DEFAULT '',
  `user_id` int(11) NOT NULL,
  `news_num` mediumint(8) NOT NULL DEFAULT '0',
  `comm_num` mediumint(8) NOT NULL DEFAULT '0',
  `user_group` smallint(5) NOT NULL DEFAULT '4',
  `lastdate` varchar(20) NOT NULL DEFAULT '',
  `reg_date` varchar(20) NOT NULL DEFAULT '',
  `banned` varchar(5) NOT NULL DEFAULT '',
  `allow_mail` tinyint(1) NOT NULL DEFAULT '1',
  `info` text NOT NULL,
  `signature` text NOT NULL,
  `foto` varchar(255) NOT NULL DEFAULT '',
  `fullname` varchar(100) NOT NULL DEFAULT '',
  `land` varchar(100) NOT NULL DEFAULT '',
  `favorites` text NOT NULL,
  `pm_all` smallint(5) NOT NULL DEFAULT '0',
  `pm_unread` smallint(5) NOT NULL DEFAULT '0',
  `time_limit` varchar(20) NOT NULL DEFAULT '',
  `xfields` text NOT NULL,
  `allowed_ip` varchar(255) NOT NULL DEFAULT '',
  `hash` varchar(32) NOT NULL DEFAULT '',
  `logged_ip` varchar(46) NOT NULL DEFAULT '',
  `restricted` tinyint(1) NOT NULL DEFAULT '0',
  `restricted_days` smallint(4) NOT NULL DEFAULT '0',
  `restricted_date` varchar(15) NOT NULL DEFAULT '',
  `timezone` varchar(100) NOT NULL DEFAULT '',
  `news_subscribe` tinyint(1) NOT NULL DEFAULT '0',
  `comments_reply_subscribe` tinyint(1) NOT NULL DEFAULT '0',
  `twofactor_auth` tinyint(1) NOT NULL DEFAULT '0',
  `cat_add` varchar(500) NOT NULL DEFAULT '',
  `cat_allow_addnews` varchar(500) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

--
-- Дамп данных таблицы `dle_users`
--

INSERT INTO `dle_users` (`email`, `password`, `name`, `user_id`, `news_num`, `comm_num`, `user_group`, `lastdate`, `reg_date`, `banned`, `allow_mail`, `info`, `signature`, `foto`, `fullname`, `land`, `favorites`, `pm_all`, `pm_unread`, `time_limit`, `xfields`, `allowed_ip`, `hash`, `logged_ip`, `restricted`, `restricted_days`, `restricted_date`, `timezone`, `news_subscribe`, `comments_reply_subscribe`, `twofactor_auth`, `cat_add`, `cat_allow_addnews`) VALUES
('[email protected]', '$2y$10$HizMAqnS70XtPyOAhEOBZunFUr0sTf6BxuhAF.jF5064WmVSiInS2', 'Admin', 1, 5, 2, 1, '1566571668', '1566008190', '', 1, '', '', '', '', '', '', 0, 0, '', '', '', 'a5d5341fa03117f2046f5a0b8f1589d0', '33.212.78.120', 0, 0, '', '', 0, 0, 0, '', '');

--
-- Индексы сохранённых таблиц
--

--
-- Индексы таблицы `dle_users`
--
ALTER TABLE `dle_users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `name` (`name`),
  ADD UNIQUE KEY `email` (`email`);

--
-- AUTO_INCREMENT для сохранённых таблиц
--

--
-- AUTO_INCREMENT для таблицы `dle_users`
--
ALTER TABLE `dle_users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
COMMIT;

Answer 1

one.

I need to write to the database id, ware->id, ware->title, income if a match is found for the email address.

- add columns for this data to the dle_users table (with a prefix to distinguish them from other columns, for example paym_id, paym_ware_id, paym_title, paym_income) or create a new payment_info table with the same columns plus user_id as the foreign key on the id from the dle_users table.
2.

Several thousand users can be registered on the site.

- several thousand - how much?) Up to 10 thousand? Up to 100 thousand? It matters, it affects the decisions made in point 1. For a large table with active data and frequent writes, it is better not to do alter table.
3.

I want to access Json as often as possible via cron

- the smallest unit of time in krone is 1 minute. How often do you need? Sometimes once every half an hour is enough - there will be no extra load, but here the question is about business requirements - what is the relevance of this data.
4. By what criteria do you initiate a request to a third-party service? As I can see that you are pulling data for one payment / user, it is not known how a third-party service will respond to many requests at once.
Cron scripts have no execution time limit, but can fail due to resource consumption or stability of responses from a third-party service. In general, the idea is this:
1. Pull a third-party api, get a json response. By what criterion not to make extra requests - I do not understand from your task, but presumably by paym_id, which have not yet been processed.
2. Parse the response via json_decode into an array.
3. We find the email in the array, check if it is in dle_users. If there is, add paym_id, paym_ware_id, paym_title, paym_income to the database.