Store part of array when array_search is successful

mongoose00318 · May 8, 2020

Hello,

I'm trying to store the match I get in $result to a variable so that I can easily compare its other values against other values. Here is my code..

//loop through new orders array
foreach ( $orders as $order ) {
	
	//compare job number and line item against database information for a match
	if ( array_search ( $order[ 'job_number' ], array_column ( $result, 'job_number' ) ) !== false && array_search ( $order[ 'line_item' ], array_column ( $result, 'line_item' ) ) !== false ) {
		
		$db_match = key($result);
		
		echo '<br><br>'.$db_match.'<br><br>';
		
		echo 'Job: ' . $order['job_number'] . '/Line Item: ' . $order['line_item'] . ' was found<br>';
	
	//new order which doesn't exist in database
	} else {
		echo 'Job: ' . $order['job_number'] . '/Line Item: ' . $order['line_item'] . ' was not found<br>';
	}
}

When I do $db_match = key($result); I get 0 each time and when I do:

$db_match = array_search ( $order[ 'job_number' ], array_column ( $result, 'job_number' ) ) !== false && array_search ( $order[ 'line_item' ], array_column ( $result, 'line_item' ) ) !== false;

I get 1 each time...which I assume is the number of matches which meet that criteria instead of the array key I found a match on.

Edited May 8, 2020 by mongoose00318

Barand · May 8, 2020

Databases were designed for matching and searching. Any particular reason why you pull data from the DB then do the searching?

That is not the way to use key()

array_search returns the key of the found item, not a count.

mac_gyver · May 8, 2020

if you index (pivot) the $result data using the job_number and the line_item as the array indexes when you retrieve it, you can DIRECTLY test/access the data. no searching is needed.

what is the overall goal of doing this? if its to find if data already exists in a database, in order to decide if you should perform some operation on it, you can (probably, depending on what overall goal you are trying to accomplish) do this all in a query, by setting up a unique composite index consisting of the job_number and line_item.

mongoose00318 · May 8, 2020

@Barand It's just a lot of values to compare against. I was trying to store all the rows of the particular table in an array and use the array to do the comparisons on; then when certain criteria is met, make an update on the specific record a match was found on.

Edited May 8, 2020 by mongoose00318

mongoose00318 · May 8, 2020

7 minutes ago, mac_gyver said:

if you index (pivot) the $result data using the job_number and the line_item as the array indexes when you retrieve it, you can DIRECTLY test/access the data. no searching is needed.

what is the overall goal of doing this? if its to find if data already exists in a database, in order to decide if you should perform some operation on it, you can (probably, depending on what overall goal you are trying to accomplish) do this all in a query, by setting up a unique composite index consisting of the job_number and line_item.

Exactly what I'm trying to do. I've been learning about arrays and how much faster it is to do large complicated checks using them instead of hitting the database with repeated queries...which is why I'm trying to do it this way. If I wrote this with a query, it would probably result in 40,000-50,000 queries before it finished running...at least how I know to write it lol.

So I did get the key from the matched array and the documentation mentions how array_search returns the key of it's first match. So on some matchs I am getting the wrong keys returned....maybe this is the wrong way to go about this...

Basically, if the DB already has a job number and a line item which matches the new data; I want to check each field of that DB row against the new data to see if anything is different. If there is a difference I want to update those records and backup the old records in a different table.

I am comparing a large CSV file against a table with almost 5,000 records.

Edited May 8, 2020 by mongoose00318

Barand · May 8, 2020

Show us the table structure/s you are working with and the csv layout. Tell us precisely what the goal is and let's see if we can come up with a more efficient way than 40,000 queries.

mongoose00318 · May 8, 2020

foreach ($result as $row) {
	if ( $order['job_number'] == $row['job_number'] && $order['line_item'] == $row['line_item'] ) {
		echo 'Found match on ' . $row['id'] . '<br><br>';
	} else {
		echo $order['job_number'] . ' LN: ' . $order['line_item'] . ' has not match.<br>';
	}
}

That loop does essentially what I was trying to do. It's just slow...looping through all records in the table for each record in the CSV.

Okay, here is my table structure:

--
-- Table structure for table `production_data`
--

DROP TABLE IF EXISTS `production_data`;
CREATE TABLE IF NOT EXISTS `production_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `job_number` int(8) NOT NULL,
  `enterprise` tinytext NOT NULL,
  `part_number` text NOT NULL,
  `description` text NOT NULL,
  `qty` int(11) NOT NULL,
  `line_item` varchar(11) NOT NULL,
  `as400_ship_date` date DEFAULT NULL,
  `hold_status` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
COMMIT;

And here is a list of the columns I'm actually using from the CSV. It has other columns I'm not using though.

//setup col vars for easier editing
$enterprise = $column[2];
$part_num = $column[3];
$desc = $column[4];
$qty = $column[5];
$line_item = $column[6];
$job_num = $column[7];
$work_order = $column[8];
$psm = $column[9];
$date_change_flag = $column[10];
$scheduled_ship_date = $column[11];
$on_hold = $column[12];
$on_hold_reason = $column[13];
$total_hours = $column[14];
$worfc = $column[15];

After I find a match I'd like to compare certain fields in DB record against CSV row to see which ones have differences then make a backup of DB record and update the DB record with the new information from the CSV.

Edited May 8, 2020 by mongoose00318

mongoose00318 · May 8, 2020

Lol...just did a count on how many times that loop cycled through...I was off a bit from 40-50k....

It was more like 4,587,564....

Just a bit off...

Barand · May 9, 2020

That isn't the most comprehensive requirements specification that I have worked from so there is a bit of guesswork involved, such as the comparison criteria being job number and line item matching, and which columns are to be updated from the csv data.

If this is the case, your "production_data" table should have an index on these columns (as my temp table below has).

Anyway, given those caveats, the processing would be along the lines below (4 queries instead of 40,000) and should give you a guide.

<?php
##
## This initial section would normally be in an included file
##
                const HOST = 'localhost';
                const USERNAME = '???';
                const PASSWORD = '???';
                const DATABASE = '???';

                function pdoConnect($dbname=DATABASE) 
                {
                    $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD);
                    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
                    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
                    $db->setAttribute(PDO::MYSQL_ATTR_LOCAL_INFILE, true);
                    return $db;
                }

##
##  Connect to DB server
##

$db = pdoConnect();

##
##  create a temporary table to store the csv data
##
$db->exec("CREATE TEMPORARY TABLE production_csv (
           id int not null auto_increment primary key,
           enterprise          tinytext,        
           part_num            text,        
           description         text,        
           qty                 int,        
           line_item           varchar(11),        
           job_num             int,        
           work_order          varchar(50),        
           psm                 varchar(50),        
           date_change_flag    tinyint,  
           scheduled_ship_date date,
           on_hold             tinyint,       
           on_hold_reason      varchar(50),    
           total_hours         decimal(10,2),       
           worfc               varchar(50),
           INDEX job_line (job_num, line_item)      
          )");

##
##  load the csv data into the table
##          
$db->exec("LOAD DATA LOCAL INFILE 'prod_data.csv'
          INTO TABLE production_csv
          FIELDS TERMINATED BY ','
          LINES TERMINATED BY '\n'
          IGNORE 1 LINES
          (@dummy, @dummy, enterprise, part_num, desc, qty, line_item, 
          job_num, work_order, psm, date_change_flag, scheduled_ship_date, 
          on_hold, on_hold_reason, total_hours, worfc)
          ");

##
##  write matching production_data records to archive
##
$db->exec("INSERT INTO production_archive
             SELECT pd.*
             FROM production_data pd
                  JOIN 
                  production_csv USING (job_num, line_item)
          ");

##
##  update the production_data table from the production_csv table
##
$db->exec("UPDATE production_data d
                  JOIN
                  production_csv c USING (job_num, line_item)
           SET d.enterprise = c.enterprise,
               d.part_number = c.part_num,
               d.description = c.description,
               d.qty = c.qty,
               d.as400_ship_date = c.scheduled_ship_date,
               d.hold_status = c.on_hold
          ")
?>

mongoose00318 · May 11, 2020

@Barand Wow that's awesome. I'm going give it a try shortly. I've been doing the same thing with SQL as I have been with arrays. It's obvious to me that there are a lot of things I used to do years ago in PHP which I could have been handling with SQL statements themselves. I recently looked at some books on SQL on Amazon but didn't order one. Maybe I need to...often times when I'm looking online I don't find comprehensive examples. I'm sure they are out there but I just haven't come across them.

One question I have about it: Is it just doing a mass update of the records in production_data? Here is what I programmed by the end of Friday:

//loop through new orders array
foreach ( $orders as $order ) {
	
	foreach ($result as $row) {
		
		//job already exists in DB
		if ( $order['job_number'] == $row['job_number'] && $order['line_item'] == $row['line_item'] ) {
			
			//check description field
			if ( $order['description'] != $row['description'] ) {
				//echo $order['job_number'] . ' : description is different. New: '. $order['description'] .' | Old: '. $row['description'] .'<br>';
			} else {
				//echo $order['job_number'] . ' : description is same<br>';
			}
			
			//check AS400 ship date
			if ( $order['scheduled_ship_date'] != $row['as400_ship_date'] ) {
				echo $order['scheduled_ship_date'] . ' : ship date is different. New: '. $order['scheduled_ship_date'] .' | Old: '. $row['as400_ship_date'] .'<br>';
			} else {
				echo $order['scheduled_ship_date'] . ' : ship date is same New: '. $order['scheduled_ship_date'] .' | Old: '. $row['as400_ship_date'] .'<br>';
			}
			
			$silly_count++;
			
		//job does not exist
		} else {
			//echo $row['as400_ship_date'].'<br>';
			//echo $order['job_number'] . ' LN: ' . $order['line_item'] . ' has not match.<br>';
			$silly_count++;
		}
	}
	$silly_count++;
	
}

I'm checking for two things before I archive/update a record; is the description or date from AS400 different than what is stored in MySQL. Then I use the archives to show the user past changes made to the order in the AS400. Sometimes those changes can be very small; like a dimensional change (ie 32 1/4" goes to 32 3/4"). So I built this to help the user more easily see changes to an order:

https://imgur.com/yC4sNce

Sorry I was more specific in my requirements. I look forward to trying this out and getting back to you with the results.

Do you have any reading suggestions when it comes to learning how to better harness the power of SQL?

Edited May 11, 2020 by mongoose00318

Barand · May 11, 2020

1 hour ago, mongoose00318 said:

Is it just doing a mass update of the records in production_data?

Yes, however conditions could be added.

The archiving step could be removed completely and replaced with a trigger function that fires only in the event of a change to either the date or description during the update.

1 hour ago, mongoose00318 said:

Do you have any reading suggestions

There's a link in my sig.

mongoose00318 · May 11, 2020

Ugh...the way we store information in the AS400 is a nightmare.

https://imgur.com/8gIpgg1

I have no real way to uniquely identify a job/line item...I'm trying to figure out a way to cross check the information in my database with what comes out of the AS400. I thought I could do that with the line item but see those orders in the screenshot? Order 23606661 has two line items of Q with completely different part numbers, qty, and description...

Very cool tutorial. I look forward to doing it.

Any ideas on how to cross check the info like I am trying to do?

For example, today those orders could look like that but tomorrow a revision could have been done to one or more of those orders. I have other data tied to the order using it's id when I do the initial insert. I'm trying to post the revisions, archive the old info, and retain the ability to identify the order due to it's relationship with other data in the DB.

Edited May 11, 2020 by mongoose00318

Barand · May 11, 2020

20 minutes ago, mongoose00318 said:

Ugh...the way we store information in the AS400 is a nightmare.

https://imgur.com/8gIpgg1

I have no real way to uniquely identify a job/line item

What's the problem? Job and line item are both there in that image.

mongoose00318 · May 11, 2020

See the entry on the bottom though? It's two entries with the same job number and same line item but a different description and quantity all together.

Barand · May 11, 2020

Is part number unique to a job? It's hard for me to sort the image and check for myself.

mongoose00318 · May 11, 2020

The same part number can be used in several orders and the part number can be revised on occasion if the order was entered with a bad part number...I know I'm not making this easy...

Our system is ridiculous.

Barand · May 11, 2020

Is it a common occurence to have two line items the same within a job number, or have you accidentally hit upon a rare glitch? (Needs fixing either either way).

mongoose00318 · May 11, 2020

I'm trying to figure out how common it is now. I just wrote this code but it's super slow. I'm trying to find out how many orders have more than one of the same line item:

##
## Figure out how many orders exist which have duplicate line items
##

$records = $pdo->query("SELECT * FROM production_data");
$orders = [];

foreach ($records as $r) {
	
	//is not in array so create it
	if ( !in_array( $r['job_number'], $orders ) )
		$orders[ $r['job_number'] ] = [];
	
	foreach ( $orders as $key => $value ) {
		$get_lines = $pdo->query("SELECT line_item FROM production_data WHERE job_number = $key");
		
		foreach ( $get_lines as $line ) {
			
			if ( !isset ( $orders[$key][$line['line_item']] ) )
				$orders[$key][$line['line_item']] = 1;
			else
				$orders[$key][$line['line_item']] + 1;

		}

	}
	
}

echo '<pre>';
print_r($orders);
exit();

Barand · May 11, 2020

SELECT job_number
     , line_item
     , COUNT(*) as total
FROM production_data
GROUP BY job_number, line_item
HAVING total > 1;

Edited May 11, 2020 by Barand

mongoose00318 · May 11, 2020

Okay I get 12 entries out of 1293 rows

Array
(
    [job_number] => 22149221
    [enterprise] => SHRVIE
    [line_item] => E
    [total] => 2
)
Array
(
    [job_number] => 22992071
    [enterprise] => SHRVIE
    [line_item] => E
    [total] => 2
)
Array
(
    [job_number] => 23518411
    [enterprise] => LIDLUS
    [line_item] => C
    [total] => 2
)
Array
(
    [job_number] => 23518431
    [enterprise] => LIDLUS
    [line_item] => C
    [total] => 2
)
Array
(
    [job_number] => 23565391
    [enterprise] => LIDLUS
    [line_item] => B
    [total] => 2
)
Array
(
    [job_number] => 23586751
    [enterprise] => SONIC
    [line_item] => 
    [total] => 3
)
Array
(
    [job_number] => 23595111
    [enterprise] => LIDLUS
    [line_item] => A
    [total] => 2
)
Array
(
    [job_number] => 23598551
    [enterprise] => LIDLUS
    [line_item] => A
    [total] => 2
)
Array
(
    [job_number] => 23598551
    [enterprise] => LIDLUS
    [line_item] => B
    [total] => 2
)
Array
(
    [job_number] => 23598561
    [enterprise] => LIDLUS
    [line_item] => B
    [total] => 2
)
Array
(
    [job_number] => 23598561
    [enterprise] => LIDLUS
    [line_item] => C
    [total] => 2
)
Array
(
    [job_number] => 23606661
    [enterprise] => BURGER
    [line_item] => Q
)

Barand · May 11, 2020

And a couple of jobs are really badly afflicted ...

image.png.0a53fa3025953ac385b7ad65ab544c2e.png

Looks like a job for your AS400 team to fix it.

The question is "Can you live with it for now?"

mongoose00318 · May 11, 2020

Lol our AS400 team is one guy and they never do much to improve it...so I'm thinking that's not going happen. Its only a percentage of .92% of all the orders (granted that information is based off of only 1 day of orders)....

Do you see a way that it could be accounted for somehow? Assuming the AS400 tech won't do anything about it that is...

Barand · May 11, 2020

Having found them, is it possible to apply a manual edit to the AS400 data to correct them?

mongoose00318 · May 11, 2020

Hmm...not through an automated method (such as an OBDC connection). They won't allow me to do that. But, maybe an email to the right person and someone could manually change it...that's possible..my biggest concern is when it goes to update any changes to orders (daily) that it could overwrite important information that's necessary for the order..

https://imgur.com/Lo8a2uT

In that example, I tell it to find orders with that job # and line item C and look for any difference between the fields, if there is a difference, backup the previous line item, and overwrite with new changes.

But in this case, it would overwrite both rows 843 and 845

Barand · May 11, 2020

We could use a query to find them, report them and stop them being used in the update.

Sign In

Store part of array when array_search is successful

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information