Update variable in array with result from foreach loop

General PHP Help
#22

I have added an ‘import_string’ field in the ‘suppliers’ table and stored the entire (working) import string in this field - is there a way to execute this variable (using mysqli ??) in the update part of the loop using the ‘supplier_id’ to identify which suppliers import script to run?

#23

Trying to read up on a way of doing this… can it be done using PDO::exec ?

#24

That’s because the database column being SELECTed is filename. You would need to change $row['file'] to $row['filename'] in line 84.

You should be able to use print_r() or var_dump().

Yes. Just use $row[‘import_string’] where $sql is being used in the ->prepare($row['import_string']) call. However, because each sql query statement is now only known after $row has been assigned values, the line setting emulated prepared queries to true, the ->prepare() call, and the line setting emulated prepared queries to false will need to be moved inside the looping to right before the ->execute() call for this query.

#25

I think this will be the easiest solution, so have included import_string on line 39, changed the prepare to read $stmtl = $pdo->prepare($row[import_string]); on line 55 and then included $stmtl->execute([ $row['supplier_id'] on line 84 within the loop.

My understanding is that this should call the MySQL script from the ‘import_script’ field in the ‘suppliers’ table.

But I still get this error…

PHP Warning: Module “ssh2” is already loaded in Unknown on line 0

.error { color: red; font-family:arial; font-weight: bold; } .success { color: green; font-family:arial; }
Connected successfully to database.

sh: 1: import_string: not found PHP Warning: Undefined variable $row in /var/www/clients/client1/web1/web/distcheck/import2.php on line 55 PHP Warning: Trying to access array offset on value of type null in /var/www/clients/client1/web1/web/distcheck/import2.php on line 55 PHP Fatal error: Uncaught ValueError: PDO::prepare(): Argument #1 ($query) cannot be empty in /var/www/clients/client1/web1/web/distcheck/import2.php:55

The current script being run is;

<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`, `import_string` 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'";
$stmtl = $pdo->prepare($row[`import_string`]);


// 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

			$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true); // use emulated prepared queries
			$stmtl->execute([ $row['supplier_id'] ]); // updates the suppliers data using SQL Script in 'suppliers' import_string field
			$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // use real prepared queries

/*
			$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'] ]);
		}
	}
}
?>

I did move the

> PDO::ATTR_EMULATE_PREPARES

options either side of the ->execute() call as you said.

#26

As already posted above -

#27

Ahhhh - I didn’t register what you meant there - so will have a play over the weekend again. Thanks

#28

When I try this I get another error…

sh: 1: import_string: not found
PHP Warning: Undefined array key “” in /var/www/clients/client1/web1/web/distcheck/import2.php on line 87
PHP Fatal error: Uncaught ValueError: PDO::prepare(): Argument #1 ($query) cannot be empty in /var/www/clients/client1/web1/web/distcheck/import2.php:87

#29

The syntax you are using for $row['import_string'] is incorrect. You are putting back-ticks ` around the associative array index name. They need to be either single-quotes or double-quotes.

#30

Sorry for the delay in replying, but have been very short on time to look at this recently.

So, inside the loop I now have;

// prepare the IMPORT query
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true); // use emulated prepared queries
$stmtl = $pdo->prepare($row['import_string']);
$stmtl->execute([ $row['supplier_id'] ]); // updates the suppliers data using SQL Script in 'suppliers' import_string field
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // use real prepared queries

I think I have implemented everything you said, including…

The syntax you are using for $row['import_string'] is incorrect. You are putting back-ticks ` around the associative array index name. They need to be either single-quotes or double-quotes.

But this still errors with…

PHP Warning: Module “ssh2” is already loaded in Unknown on line 0

.error { color: red; font-family:arial; font-weight: bold; } .success { color: green; font-family:arial; }
Connected successfully to database.

The Change needs updating (file timestamp = 1726203480 / database timestamp = 1724907480)
PHP Warning: Undefined array key "import_string" in /var/www/clients/client1/web1/web/distcheck/import2.php on line 87 PHP Fatal error: Uncaught ValueError: PDO::prepare(): Argument #1 ($query) cannot be empty in /var/www/clients/client1/web1/web/distcheck/import2.php:87 Stack trace: #0 /var/www/clients/client1/web1/web/distcheck/import2.php(87): PDO->prepare() #1 {main} thrown in /var/www/clients/client1/web1/web/distcheck/import2.php on line 87

Line 87 is;

$stmtl = $pdo->prepare($row['import_string']);

So, it seems it still cant read the ‘import_string’ from the ‘suppliers’ table which contains the LOAD DATA INFILE script for each supplier)

#31

You need to determine what $row contains. What does adding the following debugging statement show -

echo '<pre>'; var_dump($row); echo '</pre>';

Beyond that, you would need to post the current code if you cannot determine what the problem is. A snippet of where the code is breaking is the tail end of the problem. The cause of the problem is somewhere in the code up to that point.

#32

OK, when I run this I get the following output…

Connected successfully to database.

Supplier1 needs updating (file timestamp = 1726462684 / database timestamp = 1724907480)
array(4) {
  ["supplier_id"]=>
  int(2)
  ["supplier"]=>
  string(10) "Supplier1"
  ["db_timestamp"]=>
  int(1724907480)
  ["filename"]=>
  string(72) "/var/www/clients/client1/web1/web/distcheck/datafiles/FTP/Supplier1/ftp.csv"
}
PHP Warning: Undefined array key "import_string" in /var/www/clients/client1/web1/web/distcheck/import2.php on line 88 PHP Fatal error: Uncaught ValueError: PDO::prepare(): Argument #1 ($query) cannot be empty in /var/www/clients/client1/web1/web/distcheck/import2.php:88 Stack trace: #0 /var/www/clients/client1/web1/web/distcheck/import2.php(88): PDO->prepare() #1 {main} thrown in /var/www/clients/client1/web1/web/distcheck/import2.php on line 88

The complete current code (including the debug line) is;

<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'";
$stmtl = $pdo->prepare($sql);
*/

// 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

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

			$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true); // use emulated prepared queries
			
			// 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'";
			$stmtl = $pdo->prepare($row['import_string']);

			$stmtl->execute([ $row['supplier_id'] ]); // updates the suppliers data using SQL Script in 'suppliers' import_string field
			$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // use real prepared queries


/*
			$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'] ]);
		}
	}
}
?>

‘distcheck’ is the table containing the main database, which I am trying to import the refreshed data into.

‘suppliers’ is the table containing the following fields;

  • id - auto increment unique field
  • supplier - Suppliers Name
  • supplier_id - Unique id number for each supplier
  • db_timestamp - UNIX timestamp of data in main (‘distcheck’) database for each supplier
  • filename - complete path + filename of suppliers datafile
  • import_string - the complete SQL import script… e.g:

LOAD DATA INFILE '/var/www/clients/client1/web1/web/distcheck/datafiles/FTP/Supplier1/ftp.csv' INTO TABLE distcheck FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (Manufacturer, Man_Pt_Nr, Description, Qty, Cost, @dummy, @dummy, Weight) SET Disti= 'Supplier1'shipping = '10';

#33

I just re-read the error with your debug line and noticed there was no ‘import_string’ variable listed and realised that I hadnt defined this in the array on line 39… so changed line 39 from;

$sql = “SELECT supplier_id, supplier, db_timestamp, filename FROM suppliers”;

to;

$sql = “SELECT supplier_id, supplier, db_timestamp, filename, import_string FROM suppliers”;

There is now a different error;

Connected successfully to database.

Supplier1 needs updating (file timestamp = 1726462684 / database timestamp = 1724907480)
array(5) {
  ["supplier_id"]=>
  int(2)
  ["supplier"]=>
  string(10) "Supplier1"
  ["db_timestamp"]=>
  int(1724907480)
  ["filename"]=>
  string(72) "/var/www/clients/client1/web1/web/distcheck/datafiles/FTP/Supplier1/ftp.csv"
  ["import_string"]=>
  string(347) "LOAD DATA INFILE '/var/www/clients/client1/web1/web/distcheck/datafiles/FTP/Supplier1/ftp.csv' INTO TABLE `distcheck` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (`Manufacturer`, `Man_Pt_Nr`, `Description`, `Qty`, `Cost`, @dummy, @dummy, `Weight`) SET `Disti` = 'Supplier1', `shipping` = '10';"
}
PHP Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in /var/www/clients/client1/web1/web/distcheck/import2.php:90 Stack trace: #0 /var/www/clients/client1/web1/web/distcheck/import2.php(90): PDOStatement->execute() #1 {main} thrown in /var/www/clients/client1/web1/web/distcheck/import2.php on line 90

If I copy the LOAD DATA INFILE script from the above error message and paste it straight into phpmyadmin it runs perfectly and imports the data, so I am confident that the syntax is correct and all the variables are valid.

#34

Something I have just found out… if I run the PHP script from a browser window it does update the database!!! However if I run the PHP script from command line - as it will when fully functional because it will run as a cron job… it fails with the error above!

#35

OK, I have battled on with this and am really pleased with the progress, but at the moment everything runs perfectly if I manually call the PHP script from a web browser. However I am now at the point where if I run from the commandline in Ubuntu, it runs successfully if no datafiles need to be updated, however if one supplier, or mores, data needs updating I get this error - in this example Supplier1 & Supplier2 are up to date but Supplier3 needs updating… it breaks and doesn’t get beyond Supplier3;

PHP Warning: Module “ssh2” is already loaded in Unknown on line 0

.error { color: red; font-family:arial; font-weight: bold; } .success { color: green; font-family:arial; }
Connected successfully to database.

Supplier1 is up to date (file timestamp = 1726635481 / database timestamp = 1726635481)

Supplier2 is up to date (file timestamp = 1726643652 / database timestamp = 1726643652)

Supplier3 needs updating (file timestamp = 1725455573 / database timestamp = 0)
array(5) {
  ["supplier_id"]=>
  int(4)
  ["supplier"]=>
  string(9) "Supplier3"
  ["db_timestamp"]=>
  int(0)
  ["filename"]=>
  string(81) "/var/www/clients/client1/web1/web/distcheck/datafiles/FTP/Supplier3/supplier3.csv"
  ["import_string"]=>
  string(435) "LOAD DATA INFILE '/var/www/clients/client1/web1/web/distcheck/datafiles/FTP/Supplier3/supplier3.csv' INTO TABLE `distcheck` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES (@dummy, `Description`, @dummy, `Cost`, @dummy, @dummy, `Qty`, `Manufacturer`, `Man_Pt_Nr`, @dummy, `Weight`, @dummy, @dummy, @dummy, @dummy, @dummy) SET `Disti` = 'Supplier3', `supplier_id` = 4, `shipping` = '6';"
}
PHP Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in /var/www/clients/client1/web1/web/distcheck/import2.php:84 Stack trace: #0 /var/www/clients/client1/web1/web/distcheck/import2.php(84): PDOStatement->execute() #1 {main} thrown in /var/www/clients/client1/web1/web/distcheck/import2.php on line 84

The full script is below;

<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`, `import_string`  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);

// 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, $row['supplier_id'] ]); // update 'db_timestamp' in 'suppliers' table to match newly imported data

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

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

                        // prepare the IMPORT query
                        $stmtl = $pdo->prepare($row['import_string']);
                        $stmtl->execute([ $row['supplier_id'] ]); // updates the suppliers data using SQL Script from 'import_string' in 'suppliers' table
                        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // use real prepared queries

                        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>";
//                      echo '<pre>'; var_dump($row); echo '</pre>';
                        $stmtu->execute([ $file_timestamp, $row['supplier_id'] ]);
                }
        }
}
?>

This is so close now… it would be great if anyone can spot the error and help?

Thanks in advance

#36

The error is because the number of prepared query place-holders (the - ?) in the sql query statement doesn’t match the number of values being supplied in the ->execute([…]) call.

Why have you now changed the sql query statement so that it has hard-coded values in it? One of the points of good programming is - Don’t Repeat Yourself (DRY.) You must have the filename as a separate value so that you can get the filemtime() for it. Since you must do this, supply that same value to the LOAD … query, instead of repeating it in the saved sql query statement. By only having the filename in one place, if you need to change it or it is the only thing that is different about the sql query statement for a set of suppliers, you only have to change the value in one place. Likewise for the supplier name and supplier_id values (of which you should only have the supplier_id in this query. the supplier name is repeating data that’s defined in the suppliers table.) By hard-coding these values in multiple places, you are making more work for yourself. The point here is using the computer as a tool so that it does this work for you.

As to why this works differently between requesting the page through the web server and running it via a cron job, you likely have more than one database, one with the supplier_id hard-coded in the saved sql query statement, and one with a prepared query place-holder for it.

#38

I only have one database, so what you are saying about there being one database for the page request through the web server and one for the cron job isnt correct. I have created a field called ‘import string’ which contains the whole SQL ‘LOAD DATA INFILE’ line as if I was to just have a field for filename, I would also need separate fields to define the ‘FIELDS TERMINATED BY’, ‘ENCLOSED BY’, ‘ESCAPED BY’, ‘LINES TERMINATED BY’ characters, as well as the number of lines to ignore, and the field import mapping as every suppliers datafile is very different… so rather than defining about 10+ variables for each import script, to me, it seems much less work to just have a field containing the script which works for importing that particular suppliers datafile. The filename, as well as being incorporated in the import string, is held separately in the ‘filename’ field so the file timestamp can be obtained to compare to the database timestamp - this part works flawlessly thanks to your earlier advice.

For clarity… the supplier table contains;

• id = unique AI field
• supplier = text name of supplier
• supplier_id = unique id number for supplier (I plan to remove this and use id as it’s a waste :blush:)
• filename = full path and filename of the suppliers datafile (used to obtain the datafile timestamp for comparison to dB timestamp)
• import_string = full SQL string to import suppliers data

An example import_string is;

"LOAD DATA INFILE '/var/www/clients/client1/web1/web/distcheck/datafiles/FTP/Supplier1/ftp.csv' INTO TABLE 'distcheck' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES ('Manufacturer', 'Man_Pt_Nr', 'Description', 'Qty', 'Cost', @dummy, @dummy, 'Weight') SET 'Disti' = 'Supplier1', 'supplier_id'= 2, 'shipping' = '10';"

I interpreted that from the error when the PHP file is run from the CLI, but couldn’t work this out… please excuse my lack of knowledge, but I am a real novice at this and dont know what a bound variable is… as I said before, my current script which does this is over 2000 lines long as I have done every import, update etc in full for each supplier and it is now so badly constructed its a nightmare to make any updates…

“the number of prepared query place-holder (the -?) in the sql query statement”… I believe this is line 83 in my script which currently reads;

$stmtl = $pdo->prepare($row['import_string']);

and doesn’t have any - ? in it at all, so I changed this to;

$stmtl = $pdo->prepare($row['import_string' WHERE 'supplier_id=?]);

And, I believe, the ->execute([…]) call line which you refer to is line 84;

$stmtl->execute([ $row['supplier_id'] ]); // updates the suppliers data using SQL Script from 'import_string' in 'suppliers' table

Both these only have 1 value, ‘import_string’ so I am not sure why the error says that number of values doesn’t match.

As, you no doubt will already know, the change to the prepare statement above kills the script with an error;

PHP Warning: Module “ssh2” is already loaded in Unknown on line 0
PHP Parse error: syntax error, unexpected token “,”, expecting “]” in /var/www/clients/client1/web1/web/distcheck/import.php on line 83

I do realise that all the help on here is designed to teach, so people asking for help can learn to develop their skills… but I can see that I am missing the fundamental’s to get this working properly - so think I will have to resign myself to manually calling the script a few times during the day, every day, from the web browser to update the database as that seems to work perfectly and if you can recommend any good ‘idiot guides’ websites for me to try and learn the basics about PDO as for years I have been using ‘mysql_query’ in PHP to achieve this and only recently updated the whole script to use ‘mysqli_query’ - but I can see PHP/PDO is the way to do this… but sadly is taking too long for me to learn while trying to develop the new site!

Thanks, Slimat

#39

This example import_string does not have any prepared query place-holders ? in it. Specifically, for the supplier_id value, which is a hard-coded 2. The ->execute() statement for this query is what you posted - $stmtl->execute([ $row['supplier_id'] ]); This is attempting to supply the value in $row[‘supplier_id’] to be used for a prepared query place-holder, of which there is none.

Another possibility for the current error is that you have some stored import_string sql query statements with place-holders and some without, and the code only produces an error when a query without a place-holder gets executed.

Since I/we are not sitting next to you and don’t know everything you have done differently from the example that was provided, IF all the the stored import_string sql query statements do not have any prepared query place-holders ?, simply change execute call to $stmtl->execute();

In fact, since this is no longer a prepared query, and I doubt you will change it to be one, you can change all of this -

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

// prepare the IMPORT query
$stmtl = $pdo->prepare($row['import_string']);
$stmtl->execute([ $row['supplier_id'] ]); // updates the suppliers data using SQL Script from 'import_string' in 'suppliers' table
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // use real prepared queries

To this -

$pdo->query($row['import_string']);
#40

Thanks @phdr - that works perfectly now… I realise that I don’t fully understand PHP-PDO and was resigned to rewriting it in mysqli_query… but I would like to learn more about this so if anyone has any recommendations of good sites to learn from please do let me know.

Thanks a lot for all the help and sorry it was so long winded.

Sponsor our Newsletter | Privacy Policy | Terms of Service