CODEWITHSUNDEEP
phptransformation
user1634439
user1634439

Reputation: 11

php Entry transformation

I have a database with more than 20000 rows.

A column has this expression:

<td align="left" valign="top"><input name="mail" type="hidden" id="mail" value="[email protected]," /></td>

And I want to retrieve only the email address; how can I do that using php?

Upvotes: 1

Views: 59

Answers (4)

Grzegorz
Grzegorz

Reputation: 3335

Do as follows, assuming $column contains your column value as shown below:

$column='<td align="left" valign="top"><input name="mail" type="hidden" id="mail" value="[email protected]," /></td>' ;
$xml = simplexml_load_string( $column );
echo $xml->input[0]['value'] . "\n" ;

Example of the otput:

[email protected],

It will display your email. Of course this good when you have already $column. In case you want to process whole query like that, please do as others suggest, processing it while querying for it will be much faster.

In case you like it here a function you could use:

function getmail( $column )
{
    return simplexml_load_string( $column )->input[0]['value'] ;
}

and usage:

echo getmail( '<td align="left" valign="top"><input name="mail" type="hidden" id="mail" value="[email protected]," /></td>' ) ;

Have fun.

Upvotes: 0

Jocelyn
Jocelyn

Reputation: 11393

I suggest using TRIM to remove the extra text before and after the email value:

SELECT TRIM(TRAILING  '," /></td>'
            FROM TRIM(LEADING '<td align="left" valign="top"><input name="mail" type="hidden" id="mail" value="'
            FROM your_field)) AS email
FROM your_table;

It will work because you confirmed that in a comment this is the exacte expression in each column only the email adress which is different.

Using a MySQL-only solution will be faster than using MySQL to retrieve the data, and PHP to extract the emails. It also enables you to write a query to easily and quickly save the emails in another column or table.

Upvotes: 2

Frank Farmer
Frank Farmer

Reputation: 39356

As long as this format is completely static, you can get away with a pretty sloppy regex, as demonstrated below:

$ cat emailreg.php
<?php
$html = '<td align="left" valign="top"><input name="mail" type="hidden" id="mail" value="[email protected]," /></td>';
if(preg_match('@value="(?P<email>[^",]+),"@', $html, $matches)) {
    var_dump($matches['email']);
}
$ php emailreg.php
string(15) "[email protected]"
$

Upvotes: 0

Mike Brant
Mike Brant

Reputation: 71384

If the HTML is exactly the same in all rows, you can actually do this in SQL like this:

SELECT REPLACE(
    REPLACE(
        field_name,
        '<td align="left" valign="top"><input name="mail" type="hidden" id="mail" value="',\
        ''
    ),
    '" /></td>',
    ''
) as email
FROM table;

Upvotes: 1

Related Questions