Solved

preg_match() which pattern to find ?

Posted on 2014-07-29
16
188 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 4
16 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40227893
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
ID: 40228330
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
ID: 40228622
So it's the third field after VALUES, right?

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

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Richard Coffre
ID: 40228640
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
ID: 40228691
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
ID: 40228759
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 110

Expert Comment

by:Ray Paseur
ID: 40228784
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
ID: 40228787
Ray,

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

Put on hold my question.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40228793
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
ID: 40228825
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
ID: 40228855
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
ID: 40228868
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 110

Expert Comment

by:Ray Paseur
ID: 40228930
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 110

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 40228936
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
ID: 40231436
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 110

Expert Comment

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

Featured Post

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
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…

707 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