Update variable in array with result from foreach loop

General PHP Help
#1

Hi all

I developed and run a PHP application for my company which displays a front end to a MySQL database so suppliers can upload their stock lists to my FTP server and it is displayed on the front end if a salesman searches for something.

I am in the process or rewriting it from the ground up as over the years where bits have been added and deleted, it is 2000 lines of hell!

So, to tidy it up I am trying to recode using arrays, as a lot of processes are just repeated multiple times for different suppliers - of which there are about 30. So rather than writing the same process over and over for each suppliers file, an array seemed the best way.

One process I have is to check if the datafile uploaded from the supplier is newer than the data in the application and if it is, set a variable to 1 so that suppliers data can be uploaded. I have created an array which contains SQL scripts to obtain the timestamp from the database, variables to hold the timestamp of the latest file provided by the supplier and a flag variable to show 0 for no update required, or 1 for update needed.

When the script runs… it works perfectly in as much as it correctly compares the database time stamp to the FTP file timestamp and reports whether an update is needed, or not. The flag variable in application is $import_supplier1, $import_supplier2, etc. In the array this variable is called $import_supplier and in the foreach loop correctly shows 1 if an update is needed and 0 if not needed.

But I am struggling to work out how to store the result obtained in the loop back to $import_supplier1, $import_supplier2, etc so that further on in the script I can perform the update if needed.

The only way I can see to do this is to incorporate the update process in the loop as well… I have looked at the PHP Help, but havent worked it out. Can anyone offer any simple advice? I am not a proficient coder, so simple as possible would be good :wink:

Below is an excerpt of the script I am working on…

<?PHP

// Define variable for file timestamp
	$file_timestamp_supplier1 = filemtime ("/var/www/clients/client1/web1/web/distcheck/datafiles/FTP/supplier1/datafile.csv");
	$file_timestamp_supplier2 = filemtime ("/var/www/clients/client1/web1/web/distcheck/datafiles/FTP/supplier2/datafile.csv");

// Define flag variable to be 0 for no update required
	$import_supplier1 = 0;
	$import_supplier2 = 0;

// Connect to SQL database

         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'password';
         $dbname = 'database';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli->connect_errno) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully to database.</br></br>');

// Array to compare timestamp of existing file from SQL Database and timestamp of supplier file on FTP server to determine whether update is neccessary - set $import_supplier1 / $import_supplier2 to 1 if needed

$array = [

	["SELECT `timestamp` FROM `timestamp` WHERE `supplier` = 'Supplier 1'", &$db_timestamp_supplier1, &$file_timestamp_supplier1, &$import_supplier1, "Supplier 1"], //Supplier 1
	["SELECT `timestamp` FROM `timestamp` WHERE `supplier` = 'Supplier 2'", &$db_timestamp_supplier2, &$file_timestamp_supplier2, &$import_supplier2, "Supplier 2"], //Supplier 2

];

foreach ($array as list($sql_code, $db_timestamp_supplier, $file_timestamp_supplier, $import_supplier, $supplier)) {

	$result=mysqli_query($mysqli,$sql_code);
	$row=mysqli_fetch_array($result,MYSQLI_ASSOC);
	$db_timestamp_supplier = $row["timestamp"];

	if ($db_timestamp_supplier != $file_timestamp_supplier) {
		$import_supplier = 1 ;
		echo "<b><font color= 'red' face='Arial'></br>".$supplier." needs updating</b> (file timestamp = ".$file_timestamp_supplier." / database timestamp = ".$db_timestamp_supplier."  / update flag = ".$import_supplier.")</face></br></br>";
		
	} else {
		
		$import_supplier = 0;
		echo "<font color= 'green' face='Arial'>".$supplier." is up to date (file timestamp = ".$file_timestamp_supplier." / database timestamp = ".$db_timestamp_supplier."  / update flag = ".$import_supplier.")</face></br>";

		}

	}

?>
#2

Don’t use a series of named-numbered variables. Use arrays for all the ‘dynamic’ values
per supplier. Everything that is variable/different for each supplier, such as the supplier name, uploaded file path/name, … would be pulled out of the code and put into a defining array. If the updating of the data is separate from the current process of displaying which needs to be updated, you would just query again at the point of performing the update.

One question, is the sql query statement consistent across all the suppliers, and just has a WHERE supplier = supplier name?

Some points about the database specific code -

Current standards use exceptions for errors. When using exceptions, any discrete error checking logic won’t ever get executed upon an error and should be removed. The only time you should catch and handle a database exception in your code is for a user recoverable error, such as when inserting/updating duplicate user submitted data. For all other errors and all other type of queries, simply do nothing in your code and let php catch and handle the database exceptions.

You need to use prepared queries when supplying external, unknown, dynamic values to a query when it gets executed. If it seems like using the mysqli extension is overly complicated and inconsistent when dealing with prepared queries, it is. This would be a good time to switch to the much simpler and better designed PDO extension.

Depending on the answer to the sql query question, I’ll post an example of how I would dynamically do what you have shown.

#3

I assumed that the SELECT query structure is the same for every supplier, only specifying the supplier name in the WHERE term. Here is what I would do based on what you have shown -

<style>
.error {
  color: red;
}

.success {
  color: green;
}
</style>

<?php

// define the supplier information
$suppliers[] = ['name'=>'Supplier 1', 'file'=>"/var/www/clients/client1/web1/web/distcheck/datafiles/FTP/supplier1/datafile.csv"];
$suppliers[] = ['name'=>'Supplier 2', 'file'=>"/var/www/clients/client1/web1/web/distcheck/datafiles/FTP/supplier2/datafile.csv"];
// ... add definitions for remaining suppliers


// Connect to SQL database
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'database';
$DB_ENCODING = 'utf8mb4'; // db character encoding. set to match your database table's character set

// this example uses the much simpler and better designed PDO extension

$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // set the error mode to exceptions
			PDO::ATTR_EMULATE_PREPARES => false, // run real prepared queries
			PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // set default fetch mode to assoc
			PDO::MYSQL_ATTR_LOCAL_INFILE => true // assuming you are using a LOAD LOCAL INFILE ... query to get the data into the database
			];

$pdo = new pdo("mysql:host=$dbhost;dbname=$dbname;charset=$DB_ENCODING",$dbuser,$dbpass,$options);

// with exceptions, your main code will only 'see' error free execution
// if execution continues past a statement that can throw an exception, you know there was no error without needing any conditional logic
echo 'Connected successfully to database.<br><br>';

// find which supplier files need to be updated

// prepare the SELECT query
$sql = "SELECT `timestamp` FROM `timestamp` WHERE `supplier`=?";
$stmt = $pdo->prepare($sql);

// loop over the supplier definitions
foreach($suppliers as $arr)
{
	if(!file_exists($arr['file']))
	{
		echo "{$arr['name']} uploaded file does not exist.<br>";
	}
	else
	{
		// file exists, get row of data from database
		
		$file_timestamp = filemtime($arr['file']);
		
		$stmt->execute([ $arr['name'] ]);
		if(!$db_timestamp = $stmt->fetchColumn())
		{
			// could not fetch data, either there's no matching row of data (normally curring for a new supplier) or there's no timestamp value in the row (only due to a programming mistake)
			echo "Could not fetch timestamp for {$arr['name']}.<br>";
			
			// do whatever you want for this case, such as updating the data and inserting a row in the timestamp table

			// at the end of successful completion of the update processing
			// insert a new row into the database table
			$sql = "INSERT INTO `timestamp` (`timestamp`,supplier) VALUE (?,?)";
			$stmti = $pdo->prepare($sql);
			$stmti->execute([ $file_timestamp, $arr['name'] ]);
			
			echo "{$arr['name']} data successfully updated.<br><br>";			
		}
		else
		{
			// found a matching row of data, compare timestamps
			if ($db_timestamp != $file_timestamp)
			{
				echo "<div class='error'><b>{$arr['name']} needs updating</b> (file timestamp = $file_timestamp / database timestamp = $db_timestamp)<br><br></div>";
				
				// if this is part of the update processing, you would simply perform the update here...
				
				// at the end of successful completion of the update processing
				// update the row in the database table
				$sql = "UPDATE `timestamp` SET `timestamp`=? WHERE supplier=?";
				$stmtu = $pdo->prepare($sql);
				$stmtu->execute([ $file_timestamp, $arr['name'] ]);
				
				echo "{$arr['name']} data successfully updated.<br><br>";
			}
			else
			{
				echo "<div class='success'>{$arr['name']} is up to date (file timestamp = $file_timestamp / database timestamp = $db_timestamp)<br><br></div>";
			}
		}
	}
}
#4

Yes, this is correct.

Many thanks for your thoughts, I am very much a novice and am away for a few days but will try when I get home.

Something which did just occur to me is that I could add an “update” column to the table I use to hold the suppliers timestamp from the MySQL database - that way I can update with a 1 or 0 from the loop to indicate whether it needs updating.

I certainly have a few things to try - many thanks and I will report back in a few days when I have been able to try this :wink:

#5

Yes, it is - only the supplier name changes… I do it this way because the main MySQL database is nearly 3 million lines of data and to save time when it updates, I just delete the suppliers data which is not the most current and re-import the most current version. Obviously deleting all 3m lines and re-importing the whole lot every time (hourly via CRON job) would be simpler… but a lot slower.

Thanks

#6

Why not just update the data at the appropriate point in the posted code?

You should actually have a ‘supplier’ database table, with an id, name, and any other unique information for each supplier. You would then put the upload file path/name value into this table. You would query the ‘supplier’ table instead of having an array in the code. The timestamp table would hold the supplier_id, not the supplier name. This will result in the least amount of data storage, the fastest queries, and allow a supplier name to be changed without affecting any of the stored data.

#7

I have been trying to do that - but keep getting an error, so am still playing with that as a solution… although I really like your idea about storing the variables in a table on the database… its something I hadnt thought about, but think that would be a much better way of doing it - so when I get time next week I will try some of these suggestions :wink:

#8

If you have code that produces an error, post the code and the error to get help with it.

#9

If you move the supplier data into a database table, the only change to the example code I posted would be to query for and fetch all the supplier data into the $suppliers variable (see the PDO fetchAll() method.)

Edit: though if you move the supplier data into a database table, you would have a timestamp column in this table instead of the separate timestamp table. You would just query the supplier table and loop over the result from this single query.

#10

If you move the supplier data into a database table, with a timestamp column, instead of a separate table for the timestamps, it simplifies the code because you can get all the necessary data in one query. Here’s what the example code would become -

<style>
.error {
  color: red;
}

.success {
  color: green;
}
</style>

<?php

// Connect to SQL database
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'database';
$DB_ENCODING = 'utf8mb4'; // db character encoding. set to match your database table's character set

// this example uses the much simpler and better designed PDO extension

$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // set the error mode to exceptions
			PDO::ATTR_EMULATE_PREPARES => false, // run real prepared queries
			PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // set default fetch mode to assoc
			PDO::MYSQL_ATTR_LOCAL_INFILE => true // assuming you are using a LOAD LOCAL INFILE ... query to get the data into the database
			];

$pdo = new pdo("mysql:host=$dbhost;dbname=$dbname;charset=$DB_ENCODING",$dbuser,$dbpass,$options);

// with exceptions, your main code will only 'see' error free execution
// if execution continues past a statement that can throw an exception, you know there was no error without needing any conditional logic
echo 'Connected successfully to database.<br><br>';

// query for and fetch the supplier data
$sql = "SELECT id, name, file, `timestamp` FROM suppliers";
$stmt = $pdo->query($sql);
$suppliers = $stmt->fetchAll();

// prepare the UPDATE query
$sql = "UPDATE suppliers SET `timestamp`=? WHERE id=?";
$stmtu = $pdo->prepare($sql);

// loop over the supplier definitions
foreach($suppliers as $row)
{
	if(!file_exists($row['file']))
	{
		echo "{$row['name']} uploaded file does not exist.<br>";
	}
	else
	{
		// file exists, compare timestamps
		$file_timestamp = filemtime($row['file']);
		if ($row['timestamp'] != $file_timestamp)
		{
			echo "<div class='error'><b>{$row['name']} needs updating</b> (file timestamp = $file_timestamp / database timestamp = {$row['timestamp']})<br><br></div>";
			
			// if this is part of the update processing, you would simply perform the update here...
			
			// at the end of successful completion of the update processing
			// update the row in the database table
			$stmtu->execute([ $file_timestamp, $row['id'] ]);
			
			echo "{$row['name']} data successfully updated.<br><br>";
		}
		else
		{
			echo "<div class='success'>{$row['name']} is up to date (file timestamp = $file_timestamp / database timestamp = $db_timestamp)<br><br></div>";
		}
	}
}
#11

@phdr Many thanks for this script… I have never used PDO and know nothing about how to use them, but I have adapted your script and managed to get this working basically - I do need to write the “update” script to delete the existing data and import the new data - but I will play around with what you have done and get it working. I extended the timestamp table and renamed it “suppliers” so it now holds the file_timestamp and database_timestamp information as well as the source filename etc.

It works smoothly and not being a script bunny I have never used the tags but really like how easy this makes things.

With your help, my script which was nearly 2000 lines, is now under 100! It will grow a bit as I need to write the data delete & import section - but it is a LOT shorter and hopefully I will learn a bit more as I develop it.

Thanks

#12

I now have columns in my ‘suppliers’ table (formerly ‘timestamp’) which show the database time stamp (‘db_timestamp’) and file timestamp (‘file_timestamp’). In this table is also a column ‘supplier_name’.

Part of the update process is when ‘db_timestamp’ != ‘file_timestamp’ to run the update.

However I am trying to work out how (using PDO) to delete data from a table called ‘distcheck’ where the column ‘Disti’ matches the column ‘supplier_name’ in table ‘suppliers’ if ‘db_timestamp’ != ‘file_timestamp’ also in table ‘suppliers’… is this possible?

This basic requirement will be the same when I import the updated data from the supplier datafile… I will need to import data from the suppliers stockfile (‘filename’ in table ‘suppliers’) to replace the data deleted from the ‘distcheck’ table above!

Is it possible to perform the test on one table and DELETE / LOAD data in another based on the outcome of the test?

#13

In fact - I just re-read your solution and see that you mention bringing the timestamp database and main database together, so it just struck me I can add and update an ‘update_needed’ column in the main database depending on the outcome of ‘db_timestamp’ != ‘file_timestamp’ in the ‘suppliers’ table and that way I can use PDO to check the ‘update_needed’ column in the main database and then DELETE & LOAD new data if needed.

I will try this first - thanks again :smiley:

#14

You don’t need to have two timestamps stored in a database table or have an ‘updated_needed’ column. Every time this code runs it compares the current filemtime() timestamp with the stored (last) timestamp value. If they are different the code to update the data will be executed. Once the uploaded file data has been updated into the database, the stored (last) timestamp is updated to the current filemtime() timestamp, which prevents the code from trying to update the data until the uploaded file gets replaced with a new data file.

Any database data related to a supplier should use the supplier id (autoincrement primary index from the supplier(s) table) not the supplier name -

#15

Thanks - I did finally realise that the “update_needed” and “file_timestamp” were not required and am using the auto increment “id” field on the suppliers table as the unique identifier - I am just trying to work out how to DELETE the out of date rows from the ‘distcheck’ table using PDO… previously I have always used something like;

DELETE FROM distcheck WHERE Disti LIKE ‘$supplier’

and also working out how to reimport the replacement data from the latest file stored locally on the server. Previously I have always used something like;

LOAD DATA INFILE '$filename' INTO TABLE `distcheck` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (@dummy, `Sup_Pt_Nr`, `Manufacturer`, `Man_Pt_Nr`, @dummy, `Cost`, `Qty`, `RRP`, @dummy, @dummy, `Description`, `Weight`) SET `Disti` = 'Supplier1', `shipping` = '£8', `CreatedTime` = '$timestamp_supplier1';

So its a case of working out how to include all this in an array and then use the statement in PDO.

But, I am slowly getting there and am amazed at how much more efficient your way is - thanks :slight_smile:

#16

There are two reasons for using prepared queries -

  1. To prevent any sql special characters in a value from being able to break the sql query syntax.
  2. When executing the same query more than once, to save on the time taken to send the query to the database server and for the database server to parse and plan the execution of the query.

The DELETE query, as a prepared query, would look like (note: a LIKE comparison without any wildcard characters should just use an = comparison) -

// prepare the query, once, before the start of any looping
$sql = "DELETE FROM `distcheck` WHERE `Disti` = ?";
$stmtd = $pdo->prepare($sql);

// at the point of executing this query in the example code
$stmtd->execute([ $row['id'] ]); // using the supplier id

For the LOAD DATA … query, I’m not sure if the filename can be supplied via a prepared query place holder. Assuming it can be, that query would look like -

// prepare the query, once, before the start of any looping
$sql = "LOAD DATA INFILE ? INTO TABLE `distcheck`
 FIELDS TERMINATED BY ','
 ENCLOSED BY '\"'
 ESCAPED BY '\"'
 LINES TERMINATED BY '\r\n'
 IGNORE 1 LINES
 (@dummy, `Sup_Pt_Nr`, `Manufacturer`, `Man_Pt_Nr`, @dummy, `Cost`, `Qty`, `RRP`, @dummy, @dummy, `Description`, `Weight`)
 SET `Disti` = ?, `shipping` = '£8', `CreatedTime` = ?";
$stmtl = $pdo->prepare($sql);

// at the point of executing this query
$stmtl->execute([
	$row['file'], // the supplier data filename
	$row['id'], // the supplier id
	$timestamp_supplier1 // if this is the current file's timestamp, this would be $file_timestamp
 ]);
#17

Thanks - I’ll take a look at this when I have a chance and see how it goes.

I think I need to restructure the database as at the moment have a separate table containing an autoincrement unique ‘id’, ‘db_filestamp’, ‘supplier_name’ and have added a ‘supplier_id’… but think you mean me to add these to the main database with all the stock code (2 million+ lines)… so will do this and play.

Many thanks for all your advice @phdr

#19

I have tried to adapt this, but it doesn’t seem to work and the other issue is that each suppliers import script is slightly different - some have different FIELDS TERMINATED BY, ENCLOSED BY, ESCAPED BY, LINES TERMINATED BY characters, etc.

So, I did wonder whether I can add an ‘import_script’ field in my ‘suppliers’ table to store the working SQL command and then, after the existing out of date data has been deleted, run the correct script (selected by the ‘supplier_id’ variable from the array) using a ‘mysqli_query’ in the loop?

The script I am trying to run is;

// prepare the IMPORT query
$sql = "LOAD DATA INFILE ? INTO TABLE `distcheck` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (`Sup_>
$stmtl = $pdo->prepare($sql);

// execute the IMPORT query
$stmtl->execute([
$row['file'], // the supplier data filename
$row['supplier_id'], // the supplier id
$file_timestamp // if this is the current file's timestamp, this would be $file_timestamp

The error I get is;

PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘? INTO TABLE distcheck FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’ ESCAPED BY …’ at line 1 in /var/www/clients/client1/web1/web/distcheck/import2.php:54

Any more help greatly appreciate… if you need me to post the whole script please let me know and I will anonymize it and post.

Thanks

#20

The error indicates that my assumption was incorrect. You cannot supply the filename for a LOAD DATE … query via a prepared query place-holder.

The simplest change would be to enable emulated prepared queries for that query. Unfortunately, I don’t know how php handles a mix of emulated and real prepared queries.

I would start by switching to use an emulated prepared query before you call the ->prepare() method for that query, then switch back to using a true prepared query afterwards. Add the following lines before and after the ->prepare() call for that query -

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true); // use emulated prepared queries

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // use real prepared queries

If there are a small number of different LOAD DATA … queries, you should store them in a database table, then store the id of the query in the suppliers table. You would use a JOIN query to get the matching sql query statement. If almost all of them are different, you would just store them in a column in the suppliers table.

#21

OK, for completeness, this is the entire (anonymized) script with your emulation additions;

<style>
.error {
  color: red;
  font-family:arial;
  font-weight: bold;
}

.success {
  color: green;
  font-family:arial;
}
</style>

<?php

// Connect to SQL database
        $dbhost = 'localhost';
        $dbuser = 'root';
        $dbpass = 'password';
        $dbname = 'dbname';
        $DB_ENCODING = 'utf8mb4'; // db character encoding. set to match your database table's character set

// this example uses the much simpler and better designed PDO extension

$options =      [
		PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // set the error mode to exceptions
		PDO::ATTR_EMULATE_PREPARES => false, // run real prepared queries
		PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // set default fetch mode to assoc
		PDO::MYSQL_ATTR_LOCAL_INFILE => true // assuming you are using a LOAD LOCAL INFILE ... query to get the data into the database
		];

$pdo = new pdo("mysql:host=$dbhost;dbname=$dbname;charset=$DB_ENCODING",$dbuser,$dbpass,$options);

// with exceptions, your main code will only 'see' error free execution
// if execution continues past a statement that can throw an exception, you know there was no error without needing any conditional logic
echo "<div class='success'><b>Connected successfully to database.<br><br></b></div>";

// query for and fetch the supplier data
$sql = "SELECT `supplier_id`, `supplier`, `db_timestamp`, `filename`  FROM suppliers";
$stmt = $pdo->query($sql);
$suppliers = $stmt->fetchAll();

// prepare the UPDATE query
// $sql = "UPDATE suppliers SET `db_timestamp`=?, `update_needed`=? WHERE id=?";
$sql = "UPDATE suppliers SET `db_timestamp`=? WHERE supplier_id=?";
$stmtu = $pdo->prepare($sql);

// prepare the DELETE query
$sql = "DELETE FROM `distcheck` WHERE `supplier_id` = ?";
$stmtd = $pdo->prepare($sql);

// prepare the IMPORT query
$sql = "LOAD DATA INFILE ? INTO TABLE `distcheck` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (`Sup_Pt_Nr`, `Manufacturer`, `Man_Pt_Nr`, @dummy, `Description`, @dummy, `Qty`, `Cost`) SET `Disti` = ?, `supplier_id` = ?, `shipping` = '10'";
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true); // use emulated prepared queries
$stmtl = $pdo->prepare($sql);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // use real prepared queries

// loop over the supplier definitions
foreach($suppliers as $row)
{
	if(!file_exists($row['filename']))
	{
		echo "<div class='error'>{$row['supplier']} FTP file ({$row['filename']}) does not exist!!!<br><br></div>";
	}
	else
	{
	// file exists, compare timestamps
		$file_timestamp = filemtime($row['filename']);
		$db_timestamp = $row['db_timestamp'];
	
		if ($db_timestamp != $file_timestamp)
		
		{
			echo "<div class='error'>{$row['supplier']} needs updating (file timestamp = $file_timestamp  / database timestamp = {$row['db_timestamp']})<br></div>";

			// if this is part of the update processing, you would simply perform the update here...
			// at the end of successful completion of the update processing
			// update the row in the database table
//			$stmtu->execute([ $file_timestamp, $update_needed, $row['supplier_id'] ]); // update 'db_timestamp' in 'suppliers' table to match newly imported data

			$stmtd->execute([ $row['supplier_id'] ]); // execute script to delete supplier data that needs updating using the supplier id

			$stmtl->execute([
				$row['file'], // the supplier data filename
				$row['supplier_id'], // the supplier id
				$file_timestamp // if this is the current file's timestamp, this would be $file_timestamp
			 ]);

			echo "<div class='success'>{$row['supplier']} data successfully updated.</div class><br>";
		}
		
		else
		
		{
			echo "<div class='success'>{$row['supplier']} is up to date (file timestamp = $file_timestamp / database timestamp = {$row['db_timestamp']})<br><br></div>";
			$update_needed = 0;
			$stmtu->execute([ $file_timestamp, $update_needed, $row['supplier_id'] ]);
		}
	}
}
?>

The error that this throws is;

PHP Warning: Undefined array key “file” in /var/www/clients/client1/web1/web/distcheck/import2.php on line 84
PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘NULL INTO TABLE distcheck FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’ ESCAPED …’ at line 1 in /var/www/clients/client1/web1/web/distcheck/import2.php:86

I tried to see what ‘$stmtl’ was actually presenting by adding;

echo $stmtl."<br>";

But got an error saying the string couldn’t be converted;

PHP Fatal error: Uncaught Error: Object of class PDOStatement could not be converted to string in /var/www/clients/client1/web1/web/distcheck/import2.php:58

So, at this point the script correctly identifies any data in the ‘distcheck’ database which is out of date and deletes the outdated rows from the ‘distcheck’ table… which is amazing!

The only part left is how to import the data into the MySQL ‘distcheck’ table to replace the deleted data!

Thanks

Sponsor our Newsletter | Privacy Policy | Terms of Service