1

I don't know if this is possible as I'm unable to find how I can do this and I'm very new to PHP but here's an overview of my issue:

I have a script which reads a CSV file. One of the columns contains cells which contain HTML tables. At varying positions within all of the tables there exists a table row which contains <td>Retail</td> and then the price such as <td>$300</td> for example. An example is below which I have formatted so that it's easier for you to read, but this is returned as a continuous string from the CSV file normally:

<table>
    <tr>
        <td>Designer</td>
        <td>Hermes</td>
    </tr>
    <tr>
        <td>Size inch</td>
        <td>5.9 x 4.3 x 2.4</td>
    </tr>
    <tr>
        <td>Material</td>
        <td>Cotton</td>
    </tr>
    <tr>
        <td>Retail</td>
        <td>$300.00</td>
    </tr>
    <tr>
        <td>Made in</td>
        <td>France</td>
    </tr>
</table>

These tables are then required to have the CAD [Canadian Dollars] retail price added to them. Example below of the desired end result:

<table>
    <tr>
        <td>Designer</td>
        <td>Hermes</td>
    </tr>
    <tr>
        <td>Size inch</td>
        <td>5.9 x 4.3 x 2.4</td>
    </tr>
    <tr>
        <td>Material</td>
        <td>Cotton</td>
    </tr>
    <tr>
        <td>Retail USD</td>
        <td>$300.00</td>
    </tr>
    <tr>
        <td>Retail CAD</td>
        <td>$410.00</td>
    </tr>
    <tr>
        <td>Made in</td>
        <td>France</td>
    </tr>
</table>

I have looked at using substr() but it looks as though you need to specify the length of characters that will be ignored from the start of the string which isn't possible for me here as the data varies.

So therefore my question is whether it's at all possible to specifically split the price out from the string and then append it back in after the </tr> so that the result is as above. If you could point me in the right direction of the functions that I would need to use to achieve this then I would really appreciate it. Please bear in mind I am already using str_replace() to rename Retail to Retail USD and I already have a variable created ready to convert USD price to a CAD price which uses a finance API.

Thank you in advance for any insight you can offer me here.

Martin
  • 22,212
  • 11
  • 70
  • 132
  • 1
    Wait, that's not a CSV, are you making that table in a website with PHP from the CSV file then? – JSelser Dec 11 '16 at 20:48
  • 1
    I self-formatted this table from the CSV so that my issue could be understood easier, the actual result is like so: `
    DesignerHermes
    Made inFrance
    Size inch5.9 x 4.3 x 2.4
    MaterialCotton
    ColorWhite
    Retail$300.00
    Dust bagNot included
    BoxNot included
    ` and is returned as a string from the CSV. Thank you.     –  Dec 11 '16 at 20:49
  • 1
    Ok, so you actually have a `` as a value in the CSV
     – JSelser Dec 11 '16 at 20:57
  • 2
    your going-to easily get in trouble trying to manipulate html with regular expressions or str_replace, you should be using a DOM parser: http://stackoverflow.com/questions/3577641/how-do-you-parse-and-process-html-xml-in-php –  Dec 11 '16 at 21:03
  • 1
    What generates your CSV file? It would be better to edit the CSV generator rather than trying to mutate your CSV. It would also be better your CSV contained ***only data and not HTML*** – Martin Dec 11 '16 at 21:54
  • Very true but unfortunately it's an external source which I'm unable to modify –  Dec 11 '16 at 21:55

3 Answers3

1

I have looked at using substr() but it looks as though you need to specify the length of characters that will be ignored from the start of the string which isn't possible for me here as the data varies.

So use stripos to find the start of the string you want to replace.

However the more I dig into this, it because a mess very quickly. It would be better to edit the CSV generator rather than trying to mutate your CSV. It would also in an ideal world be better your CSV contained only data and not HTML.

Apologies the following became a large and probably unwieldy answer:

However to do it, you need to isolate this CSV column, into a variable $csvData. Then work with it directly:

$csvData = "<table data from your question>";
$csvData = str_replace("</td>","*!*</td>",$csvData);

//remove all the HTML junk
$csvDataClean = strip_tags($csvData);

// Form an array.
$csvDataArray = explode("*!*",$csvDataClean);

// trim contents of the array.
$csvDataArray = array_map('trim', $csvDataArray);

// remove empty array values.
$csvDataArray = array_filter($csvDataArray);

// build new contents array.
foreach($csvDataArray as $key=>$value){
    if($key%2 == 0){
        //odd number. Is a content header.
        $value = str_replace(" ","_",$value);
        $lastHeader = preg_replace("/[^a-z0-9-_]/i","",$value);
    } 
    else {
        //even number, it's a value 
        $csvArray[$lastHeader] = $value;
    }
}
//tidy up. 
unset($key,$value,$lastHeader,$csvDataArray,$csvDataClean);

print_r($csvArray);

This will now output for you an array of headers and values from your HTML table. You can then easily reference values from this array and then recompile them into an HTML table as nessecary.

Using phpsandbox I can output: 

Array
(
    [Designer] => Hermes
    [Size_inch] => 5.9 x 4.3 x 2.4
    [Material] => Cotton
    [Retail] => $300.00
    [Made_in] => France
)

So you can then take $csvArray['Retail'] and process this value to get the other currency values, and add them to this array. Then you can run this array through another process to rebuild a table, to save into the CSV (although this doesn't come recommended, it's better to save the arraty as a CSV itself, but I don't know your requirements).

So:

//whatever system you currently use to get conversion.
$csvArray['Retail_CAD'] = convert_currency($csvArray['Retail']);
$csvArray['Retail_USD'] = convert_currency($csvArray['Retail']);

And now rebuild the HTML form:

foreach($csvArray as $key=>$value){
    $csvOutput .= "<tr><td>".str_replace("_"," ",$key)."</td><td>".$value."</td></tr>\n";
}
unset($key,$value);
$csvOutput = "<table>".$csvOutput."</table>";

print_r($csvOutput);

You can also manually delete and readd the Made_in array key if you want to maintain this as the final array value:

 //whatever system you currently use to get conversion.
$csvArray['Retail_CAD'] = convert_currency($csvArray['Retail']);
$csvArray['Retail_USD'] = convert_currency($csvArray['Retail']);
....
$value = $csvArray['Made_in'];
unset($csvArray['Made_in']);
$csvArray['Made_in'] = $value;

This is a hacky but quick way of keeping the "made in" column after the new Retail columns added above.

Martin
  • 22,212
  • 11
  • 70
  • 132
  • Thank you very much for the time and effort you put into this answer. I'll take a look at it :) –  Dec 11 '16 at 22:31
0

What you pasted here is a html table, not csv.

Anyway, there are several ways to manipulate strings. str_replace() is one of the most basic ones, so you got that already. In your case, you're probably best off using regular expressions. It's like str_replace but much more powerful. There are plenty of tutorials out there.

If you want to do a lot and more complex manipulation of html or xml data, you may want to have a look at XSLT.

LorenzSchaef
  • 1,523
  • 10
  • 16
0

I had to deal with a similar scenario once, what I would do is:

1.-Form first your desired output block in a variable $output_block i.e :

<td>Retail USD</td><td>$300.00</td></tr><tr><td>Retail CAD</td><td>$410.00</td>

note: you dont need the firs opening tr tag neither the last closing one cause you already have those on your original output.

2.-find the position of <td>Retail</td> (use strpos)

3.-Save the substring you have before in a a variable i.e: $first_part

4.-find the position of <td>Made in</td>

5.-Save the substring you have after this in a variable : $last_part

6.- Your final output: $final_output = $firstpart . $output_block . $last_part;

easy cake... ;)

Pedro Emilio Borrego Rached
  • 316
  • 3
  • 10