?
Solved

preg_match() which pattern to find ?

Posted on 2014-07-29
16
Medium Priority
?
189 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 111

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
7 Extremely Useful Linux Commands for Beginners

Just getting started with Linux? Here's a quick start guide that has 7 commands that we believe will come in handy.

 
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 111

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 111

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 1000 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 111

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 111

Accepted Solution

by:
Ray Paseur earned 1000 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 111

Expert Comment

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

765 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