Level 122
the query that you say works in phpmyadmin does not contain where
Feb 16, 2024
9
Level 2
Not working the update query in dynamic loop
Dear Friends, I am using a static loop before master table creation and it worked fine. But when master table implemented I need to run that process in dynamic loop. So I have a for loop code like below
foreach ($phaseMasterData as $ph) {
$phase = $ph->phase;
$start_date = $ph->start_date;
$end_date = $ph->end_date;
$versionPhaseCount = VersionMasterModel::where('phase', $phase)->select('version')->count();
if ($versionPhaseCount != 0) {
$versionMasterData = VersionMasterModel::where('phase', $phase)->select('version')->get();
foreach ($versionMasterData as $vmd) {
$phver[] = "'" . $vmd['version'] . "'";
}
$phaseVersions = implode(',', $phver);
$query = "update " . $gen_table_name . " set phase='$phase'
where eid_timestamp >'2019-12-31' and version in(" . $phaseVersions . ")";
$genUpdate1 = DB::update($query);
echo $query;
}
}
In loop I got SQL query in echo statement as
update gen_Jan2025_Mar2025 set phase='C3' where eid_timestamp >'2019-12-31' and version in('5.4.3.2','3.3.1.0','3.3.2.0','3.3.2.5','3.3.3.0','3.3.5.2','3.3.6.0','3.3.6.1','3.3.7.0','3.3.7.2','3.3.7.3','3.3.7.5','3.3.7.6','3.3.7.8')
that query is worked in my phpmyadmin fine . But in update query that is not working .
My table schema is
CREATE TABLE `gen_jan2025_mar2025` (
`sl` int(10) UNSIGNED NOT NULL,
`eid` varchar(40) NOT NULL,
`reg_id` varchar(5) DEFAULT NULL,
`EA` int(11) DEFAULT NULL,
`State_Name` varchar(20) DEFAULT NULL,
`State_code` varchar(20) DEFAULT NULL,
`District_Name` varchar(20) DEFAULT NULL,
`District_code` varchar(20) DEFAULT NULL,
`pincode` varchar(10) DEFAULT NULL,
`version` varchar(10) DEFAULT NULL,
`mcid` varchar(5) NOT NULL,
`month` int(11) NOT NULL,
`year` int(11) NOT NULL,
`phase` char(2) DEFAULT '0',
`eid_timestamp` date NOT NULL,
`cut_date` date DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `gen_jan2025_mar2025`
ADD PRIMARY KEY (`sl`),
ADD KEY `Index_1_gen_Jan2025_Mar2025` (`year`,`month`,`phase`,`mcid`,`EA`);
How it can solve ?
Thanks,
Anes P.A
Level 2
@Snapey Sorry it's just a typo .. now corrected .. Please advise
Level 122
is this seriously your best description of the error?
But in update query that is not working .
Level 2
@Snapey No, I need solution of my problem . No body gave a solution yet.
Level 122
@insight we don't know what "not working" means
Nobody can help you if you do not explain what happens and what you have tried
Level 2
@Snapey my problem is table update query is fine, but NOT updated in database table.
Level 25
Why not:
DB::table($gen_table_name)
->where('eid_timestamp', '>', '2019-12-31')
->whereIn('version', $phver)
->update(['phase' => $phase]);
You shouldn't be constructing query by concatenating strings. It's asking for trouble.
Level 2
Dear Friends, I got solution at last ... Please see my solution
foreach ($phaseMasterData as $ph) {
$phase = $ph->phase;
$start_date = $ph->start_date;
$end_date = $ph->end_date;
$versionMasterData = VersionMasterModel::where('phase', $phase)->select('version')->get();
$phver = $versionMasterData->pluck('version')->toArray();
if (!empty($phver)) {
$phaseVersions = implode("','", $phver);
$query = "update " . $gen_table_name . " set phase = ? where eid_timestamp > '2019-12-31' and version in ('" . $phaseVersions . "')";
$genUpdate1 = DB::update($query, [$phase]);
//echo $query;
$genUpdate2 = DB::table($gen_table_name)
->where('eid_timestamp', '>=', $start_date)
->where('eid_timestamp', '<=', $end_date)
->whereNotIn('phase', [$phase])
->update(['phase' => $phase]);
}
}
Thanks alot @aleahy @snapey @ictvision
Anes P A
1 like
Please or to participate in this conversation.