Starting a thread to share PHP News, Code, and Stuff…
Jetbrains PHP News
Starting a thread to share PHP News, Code, and Stuff…
Jetbrains PHP News
Sometimes I like to keep data lookups in a variable using a PHP Associated Array which is similar to a Xojo Dictionary.
I’ve been working on a FileMaker to SalesForce conversion. Of course, the Table and Field Names don’t match, but do have a database table with FM and SF Field Name Pairs. So I query the table and build an Associated Array.
Below is the code that the JetBrains AI returned which I never considered when I asked the best way to query the array.
The idea is simple. Load up an array with data and then call a function that loops thru to find a specific value and return a different specific value.
Here’s the code that the AI suggested. It’s a simple but assumes that the values are unique.
<?php
// Extended Data structure with more fields
$data = [
[
'identifier' => 'id1',
'fm' => 'fm_field_1',
'sf' => 'sf_field_1',
'description' => 'Field mapping for object 1',
'created_date' => '2023-10-01',
],
[
'identifier' => 'id2',
'fm' => 'fm_field_2',
'sf' => 'sf_field_2',
'description' => 'Field mapping for object 2',
'created_date' => '2023-10-05',
],
];
// Generic function to find values by key
function findValueByKey($data, $searchKey, $searchValue, $returnKey) {
foreach ($data as $row) {
if ($row[$searchKey] === $searchValue) {
return $row[$returnKey] ?? null;
}
}
return null; // Not found
}
// Examples:
// Find 'SF' field using 'identifier'
$sf_field = findValueByKey($data, 'identifier', 'id1', 'sf');
echo "SF Field: " . $sf_field . PHP_EOL;
// Find 'description' using 'SF' field
$description = findValueByKey($data, 'sf', 'sf_field_1', 'description');
echo "Description: " . $description . PHP_EOL;
// Find 'created_date' using 'FM' field
$created_date = findValueByKey($data, 'fm', 'fm_field_2', 'created_date');
echo "Created Date: " . $created_date . PHP_EOL;
?>
wouldn’t be faster do do this in SQL?
I gave the IA the structure of the two tables, and told it to make a sql insert from one table to another using similar fields names. and it worked great…
In this case, it would be calling the look ups over and over rapidly. So having the values in ram is faster.
I have a different project where I store a lot of schema data in sql, but I also cache that data in an assoc array since it’s referenced a lot.
In SQL it’s one insert to move to a table with different field names.
INSERT INTO table2 (colname1, colname2, ... )
Select (t1colname1, ...) from table one
You can also manipulate the data inside the insert statement to make adjustments for datatypes, formatting, editing the data or inserting new fields based on existing data. usually data migrations can be done in very few statements.
That’d be perfect if it was all in sql. The project I’m working on is selecting records from FileMaker and inserting records into SalesForce via an API running on an intermediary PHP box.
The data comes in with FileMaker column names, then translated to the SalesForce column names for the insert.
Just make it all SQL?
Context is I’ve spent years doing data migrations on legacy healthcare systems (and it sucked btw: half the reasons for migration were non-normalized table. Field first address, field second address, field third address etc.). The fastest way was usually to wholesale export the data from one system, and import it into the next. SQL would be the intermediary to change the data into what was expected for the next system, and then exported into whatever was needed for the final import.
When the system didn’t have an export, then we’d have to scrape from usually a telnet session , but we’d get the data in one go, that way we could test and verify it was valid, instead of converting by item.
FileMaker has a CSV export. Salesforce can import chunks using CSV or SOAP (iirc, SOAP was for stuff over 50 or 100k records?).
You have a log of good ideas!
We did that for the initial import and some update imports. I suppose we could do that with what this project is doing, except this process needs to be semi-automated. The user will import into FileMaker which then asks php to do on the fly inserts. Cool stuff, except for the SalesForce API being a bit yucky.
I didn’t realize this was a live process. I’m more comfortable with my initial reaction of WTF?
Having a loop for a data import concerns me so much, and brings bad memories LOL. And it was so painfully common among hospitals.
I’d still use SQL and likely batch things, because I don’t think I worked with any systems that could handle a constant load. Most places had daily batches, some hourly.
The few live places i worked with I think I mostly used C#. A few had a VBA requirement. My stomach turns remembering it, so I’ll save that story for the therapist.
If on the fly inserts are an option, it sounds like it could be fun to do and spend time making excessively efficient.
I think i’d gravitate to C# for this too. Admittedly, largely because the code lives in my head. If php does it, php does it. I couldn’t say if one option was better offhand without looking into it, but my bias has a very definite lean.
I’m aways on the look out for how to do things better.
When a Button is Clicked and code is to be run in PHP on the Server, I normally just AJAX which is just a way that Javascript can make a Web Request in without Reloading the Page. That’s essentially a “Xojo Web Round Trip” where you have to wait for the Web Round Trip to complete before getting any feedback.
Recently I learned about HTML Server-Sent Events API. Check out how I use it in Xanadu below.
Server-Sent Events is crazy simple. Just instantiate an EventSource where you pass a URL to a PHP page and handle EventSource.onMessage events. Then in PHP you do your work and send data back to be processed in the EventSource.onMessage handler.
Easy peasy! Just like Xojo Web 1.0 Round Trips but with the ability to send progress info back.
Some code:
https://www.w3schools.com/html/html5_serversentevents.asp
I work on Xanadu a lot. Recently, I’ve been working on the results UI.
Can now change the page of selected recs at the top select or the button group at the bottom.
Front end work is so detailed…
So i started a PHP project this morning, was supposed to be a quick and dirty, and its been a few years.
I’m making a lot of mistakes today lol.
What are you working on?
migrating client’s custom stuff off WordPress.
I keep getting into c# brain and doing things that obviously don’t work in php.
today is better tho. I’m remembering the dollar signs