Link to home
Start Free TrialLog in
Avatar of Ridgejp
RidgejpFlag for United Kingdom of Great Britain and Northern Ireland

asked on

How Do I Escape Strings in the Header Row of an Upload File for either CSV or TXT

Hi,

I'm using the following to import data into MySql Database via PHP, but how do I Escape Strings that are found in the header row of the data itself? In the example below the first header $order-id contains "-" which stops the import rountine.

How the best way to get around this without having to change the header rows?

while(($fileop = fgetcsv($handle,10000,",")) !== false)
        
            {
            
			$order-id			= $mysqli->real_escape_string($fileop[0]);

Open in new window

Avatar of Dmitrii
Dmitrii
Flag of Russian Federation image

PHP manual (http://php.net/manual/en/language.variables.basics.php) states:

Variable names follow the same rules as other labels in PHP. A valid variable name starts with a letter or underscore, followed by any number of letters, numbers, or underscores.
— no any «-»
Avatar of Ridgejp

ASKER

So is there a way around the "-" or not?
This is a data-dependent problem.  Please post a small sample of the test data, so we can run a few tests and show you exactly how to do this.
You often have to skip the first (header) row of a CSV file when you are importing it when some of the columns are numeric types and all you have in the header is text.  It is a data type mismatch.  It is not usually an issue with '-'.
Another thing to try.  Change $order-id to $order_id.  This is what Dmitrii was getting at.

Or you might try something encapsulated in curly braces.
Avatar of Ridgejp

ASKER

Hi,

I tried wrapping the $order-id in '$order-id' and "$order-id" and ($order-id) and {$order-id} to no avail ... I'll take a bash at changing it from $order-ID to $order_ID tomorrow or later if I get chance.

In every case above I get the following error: -

Parse error: syntax error, unexpected '=' in /***/***/***/***.php .... this presumably relates to the "=" in the following: -

$order-id = $mysqli->real_escape_string($fileop[0]);

Remove the "-" in $order-ID and it works perfectly so my current work-around is to wash it through google-sheets (doing a find/replace on the header row) prior to re-outputting a revised downloaded csv.

J
Ridgejp,
how do you construct MySQL query?

I really cannot imagine situation when a php variable's name must be equal to a MySQL table field's name or any "heder row".
Avatar of Ridgejp

ASKER

Hi Dmitrii,

The file I'm importing into my database is from a 3rd party website which contains orders for products we sell through them. The 'header row' of the CSV file that I wish to map into my database is determined by the 3rd party website not ourselves.

A selection of the header rows is as follows:-

order-id
order-item-id
purchase-date
payments-date
buyer-email
buyer-name

Many if not all of the header rows contain "-" ... hope that additional information helps.

J
Let me try this one again...

This is a data-dependent problem.  Please post a small sample of the test data, so we can run a few tests and show you exactly how to do this.  

The basic concept of test-driven development is valuable, and you should take advantage of our offer to show you tested and working code samples.  Free of charge.  All you have to do is show us some of your test data!
Avatar of Ridgejp

ASKER

Apologies Ray must have missed that one - send it up shortly!
Avatar of Ridgejp

ASKER

File enclosed - the data is completely made up for obvious reasons, however the content and file structure is exactly what we'd expect to receive.Download---Test-File.txt
ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ridgejp

ASKER

Hi Ray,

Thanks very much for looking at this - I'll take a look and come back shortly.

J