preg_match() which pattern to find ?

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

LVL 1
Richard Coffree-commerce Product ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
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
Richard Coffree-commerce Product ManagerAuthor Commented:
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
SurranoSystem EngineerCommented:
So it's the third field after VALUES, right?

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

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Richard Coffree-commerce Product ManagerAuthor Commented:
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
SurranoSystem EngineerCommented:
Can you please print the second parameter as well? (I wrote $myline, assume it contains "INSERT ... VALUES (...)"
0
Richard Coffree-commerce Product ManagerAuthor Commented:
Surrano,

In the attached file you can see the display and the source code of the HTML page.
preg-match-ko-screenshot.png
0
Ray PaseurCommented:
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
Richard Coffree-commerce Product ManagerAuthor Commented:
Ray,

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

Put on hold my question.
0
Ray PaseurCommented:
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
Richard Coffree-commerce Product ManagerAuthor Commented:
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
SurranoSystem EngineerCommented:
So painful to run php code in my brain cells ;) Will setup php in my vm and get back to you.
0
SurranoSystem EngineerCommented:
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
Ray PaseurCommented:
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
Ray PaseurCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard Coffree-commerce Product ManagerAuthor Commented:
Ray,

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

Surrano,

Thank you so much.
0
Ray PaseurCommented:
Thanks for the points and thanks for using E-E! ~Ray
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.