Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

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

0
Richard Coffre
Asked:
Richard Coffre
  • 6
  • 6
  • 4
2 Solutions
 
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
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.

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

Featured Post

Industry Leaders: 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!

  • 6
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now