Solved

preg_match() which pattern to find ?

Posted on 2014-07-29
16
180 Views
Last Modified: 2014-07-31
Hi all,

Currently, I want to parse the file containing the SQL queries.
Using preg_match(), I want to extract  the values 21943703 and 22138129 from the excerpt below.
This extract is the part of SQL file.

What pattern should I use to achieve my request?

INSERT INTO [TABLE] ([FIELD1],[FIELD2],[FIELD3],[FIELD4],[FIELD5],[FIELD6],[FIELD7])VALUES ('3595674892',1,'21943703',218.75,0,LabelUK,'EUR')GO 
INSERT INTO [TABLE] ([FIELD1],[FIELD2],[FIELD3],[FIELD4],[FIELD5],[FIELD6],[FIELD7])VALUES ('3595676383',1,'22138129',300.00,0,LabelUK,'EUR')GO 

Open in new window

0
Comment
Question by:Richard Coffre
  • 6
  • 6
  • 4
16 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
What is the output you want to achieve after matching?  REGEX has "capture groups" that can help with this, once we understand the output you want.
0
 
LVL 1

Author Comment

by:Richard Coffre
Comment Utility
Ray,

I want to get the values to look for it in a table, so I will use the output directly or set it to a "simple" variable.
Thinking about your question, preg_match() may not be the best solution.

Thanks in advance for your proposal.
0
 
LVL 8

Expert Comment

by:Surrano
Comment Utility
So it's the third field after VALUES, right?

preg_match('/VALUES \([^,]*,[^,]*,([^,]*)/', $myline, $matches);
echo $matches[1]."\n";

Open in new window

0
 
LVL 1

Author Comment

by:Richard Coffre
Comment Utility
Hi Surrano,

This is strange because when I test your solution I retrieve nothing.
Using print_r() to display $matches as follows:
print_r($matches);

Open in new window

I got an empty array:
Array ( )

Open in new window

0
 
LVL 8

Expert Comment

by:Surrano
Comment Utility
Can you please print the second parameter as well? (I wrote $myline, assume it contains "INSERT ... VALUES (...)"
0
 
LVL 1

Author Comment

by:Richard Coffre
Comment Utility
Surrano,

In the attached file you can see the display and the source code of the HTML page.
preg-match-ko-screenshot.png
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
I think if you can take a moment to please read this article you will understand a little more about why I am asking you to prepare a "mockup" of the correct inputs and desired outputs.   This is called the SSCCE, and it guides us in application development.  There is no amount of verbal explanation, no amount of looking at unworkable code, that is worth as much as a few accurate, unambiguous examples.  As soon as you can give us those, it will be very easy to show you how to get the results you want.  You're correct in your understanding that REGEX may not always be the best approach.  We will know the best approach as soon as we see the test data and the desired output.
0
 
LVL 1

Author Comment

by:Richard Coffre
Comment Utility
Ray,

I will prepare this stuff later and you will have a SSCCE.

Put on hold my question.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
I'll be here and as soon as I see your post, I'll show you how I would try to get the right output.  Best regards, ~Ray
0
 
LVL 1

Author Comment

by:Richard Coffre
Comment Utility
Hi,

Thanks to Ray's advice, you can find all the stuff to test my case.

First, the file "extractReference.php" is the PHP script to use.
Second, the file "datafile.csv" contains the data to process.
The screenshot "what-i-have.png" shows the current result.
The screenshot "what-i-want.png" tries to show you what I want.

My purpose is to extract the reference corresponding to "FIELD3" and to display it.

Thanks in advance,
Richard
extractReference.php
datafile.csv
what-i-have.png
what-i-want.png
0
 
LVL 8

Expert Comment

by:Surrano
Comment Utility
So painful to run php code in my brain cells ;) Will setup php in my vm and get back to you.
0
 
LVL 8

Assisted Solution

by:Surrano
Surrano earned 250 total points
Comment Utility
Wow. You put an extra space in the pattern:

# mine
            preg_match('/VALUES \([^,]*,[^,]*,([^,]*)/', $myline, $matches);
# yours
            preg_match('/VALUES  \([^,]*,[^,]*,([^,]*)/', $myline, $matches);

Open in new window


Try with a single space, and if you want to remove the single quotes, use this version:
            preg_match('/VALUES \([^,]*,[^,]*,\'([^,]*)\'/', $myline, $matches);

Open in new window


Output for your csv is:
root@ubuntu:~/ee# php preg.php
>>INSERT INTO [TABLE] ([FIELD1],[FIELD2],[FIELD3],[FIELD4],[FIELD5],[FIELD6],[FIELD7])VALUES ('3595674892',1,'21943703',218.75,0,LabelUK,'EUR')GO
<br>2. 21943703<br>
>>INSERT INTO [TABLE] ([FIELD1],[FIELD2],[FIELD3],[FIELD4],[FIELD5],[FIELD6],[FIELD7])VALUES ('3595676383',1,'22138129',300.00,0,LabelUK,'EUR')GO
<br>3. 22138129<br>
>>INSERT INTO [TABLE] ([FIELD1],[FIELD2],[FIELD3],[FIELD4],[FIELD5],[FIELD6],[FIELD7])VALUES ('3595676337',1,'21847438',255.00,0,LabelUK,'EUR')GO
<br>4. 21847438<br>
>>INSERT INTO [TABLE] ([FIELD1],[FIELD2],[FIELD3],[FIELD4],[FIELD5],[FIELD6],[FIELD7])VALUES ('3595676385',1,'21898560',126.25,0,LabelUK,'EUR')GO
<br>5. 21898560<br>
>>INSERT INTO [TABLE] ([FIELD1],[FIELD2],[FIELD3],[FIELD4],[FIELD5],[FIELD6],[FIELD7])VALUES ('3595675139',2,'22097139',16.88,0,LabelUK,'EUR')GO
<br>6. 22097139<br>
>>INSERT INTO [TABLE] ([FIELD1],[FIELD2],[FIELD3],[FIELD4],[FIELD5],[FIELD6],[FIELD7])VALUES ('3595673265',1,'21491051',63.75,0,LabelUK,'EUR')GO
<br>7. 21491051<br>
>>INSERT INTO [TABLE] ([FIELD1],[FIELD2],[FIELD3],[FIELD4],[FIELD5],[FIELD6],[FIELD7])VALUES ('3595679813',1,'20715050',83.33,0,LabelUK,'EUR')GO
<br>8. 20715050<br>
>>INSERT INTO [TABLE] ([FIELD1],[FIELD2],[FIELD3],[FIELD4],[FIELD5],[FIELD6],[FIELD7])VALUES ('3595675139',1,'22097137',16.88,0,LabelUK,'EUR')GO
<br>9. 22097137<br>
>>INSERT INTO [TABLE] ([FIELD1],[FIELD2],[FIELD3],[FIELD4],[FIELD5],[FIELD6],[FIELD7])VALUES ('3595675553',1,'21847417',127.13,0,LabelUK,'EUR')GO
<br>10. 21847417<br>

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Here is how I might do it.  The $sql variable is the input data string.  This might be read via file_get_contents(), or the file might be read into an array (skipping the first explode() step) if you used file().
http://iconoun.com/demo/temp_pyxos.php

<?php // demo/temp_pyxos.php
error_reporting(E_ALL);
echo '<pre>';

// SEE http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28486417.html

$sql = <<<EOD
QUANTITY;SQL
1;INSERT INTO [TABLE] ([FIELD1],[FIELD2],[FIELD3],[FIELD4],[FIELD5],[FIELD6],[FIELD7])VALUES ('3595674892',1,'21943703',218.75,0,LabelUK,'EUR')GO
1;INSERT INTO [TABLE] ([FIELD1],[FIELD2],[FIELD3],[FIELD4],[FIELD5],[FIELD6],[FIELD7])VALUES ('3595676383',1,'22138129',300.00,0,LabelUK,'EUR')GO
1;INSERT INTO [TABLE] ([FIELD1],[FIELD2],[FIELD3],[FIELD4],[FIELD5],[FIELD6],[FIELD7])VALUES ('3595676337',1,'21847438',255.00,0,LabelUK,'EUR')GO
1;INSERT INTO [TABLE] ([FIELD1],[FIELD2],[FIELD3],[FIELD4],[FIELD5],[FIELD6],[FIELD7])VALUES ('3595676385',1,'21898560',126.25,0,LabelUK,'EUR')GO
1;INSERT INTO [TABLE] ([FIELD1],[FIELD2],[FIELD3],[FIELD4],[FIELD5],[FIELD6],[FIELD7])VALUES ('3595675139',2,'22097139',16.88,0,LabelUK,'EUR')GO
1;INSERT INTO [TABLE] ([FIELD1],[FIELD2],[FIELD3],[FIELD4],[FIELD5],[FIELD6],[FIELD7])VALUES ('3595673265',1,'21491051',63.75,0,LabelUK,'EUR')GO
1;INSERT INTO [TABLE] ([FIELD1],[FIELD2],[FIELD3],[FIELD4],[FIELD5],[FIELD6],[FIELD7])VALUES ('3595679813',1,'20715050',83.33,0,LabelUK,'EUR')GO
1;INSERT INTO [TABLE] ([FIELD1],[FIELD2],[FIELD3],[FIELD4],[FIELD5],[FIELD6],[FIELD7])VALUES ('3595675139',1,'22097137',16.88,0,LabelUK,'EUR')GO
1;INSERT INTO [TABLE] ([FIELD1],[FIELD2],[FIELD3],[FIELD4],[FIELD5],[FIELD6],[FIELD7])VALUES ('3595675553',1,'21847417',127.13,0,LabelUK,'EUR')GO
EOD;

// LOAD THE TEST DATA INTO AN ARRAY
$arr = explode(PHP_EOL, $sql);

// FIRST ROW IS NOT NEEDED
unset($arr[0]);

// PROCESS EACH ELEMENT OF THE ARRAY
foreach ($arr as $key => $str)
{
    // WE WANT THE "VALUES" PORTION OF THE QUERY
    $sub = explode('VALUES', $str);
    $sub = explode(',', $sub[1]);

    // WE WANT THE THIRD ELEMENT (NUMBERED FROM ZERO)
    $sub = trim($sub[2], "'");

    // SHOW THE ROW NUMBER AND THE CAPTURED ELEMENT
    echo PHP_EOL . "$key: $sub";
}

Open in new window

0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 250 total points
Comment Utility
Sidebar note... The CSV file attached here seemed to be in two-byte unicode.  I converted it to UTF-8.  This may or may not matter to the solution.  It could be an artifact of copy/paste between different text editors.
0
 
LVL 1

Author Closing Comment

by:Richard Coffre
Comment Utility
Ray,

Your sidebar note was very important: until I convert the datafile in UTF-8, the script failed.

Surrano,

Thank you so much.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Thanks for the points and thanks for using E-E! ~Ray
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now