Solved

input of many lines.  Only want the 'create table ' lines

Posted on 2013-12-03
9
362 Views
Last Modified: 2013-12-04
BEGIN TRANSACTION;
CREATE TABLE Accounts (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER, status INTEGER, pwdchangestatus INTEGER, logoutreason INTEGER, commitstatus INTEGER, suggested_skypename TEXT, skypeout_balance_currency TEXT, skypeout_balance INTEGER, skypeout_precision INTEGER, skypein_numbers TEXT, subscriptions TEXT, cblsyncstatus INTEGER, offline_callforward TEXT, chat_policy INTEGER, skype_call_policy INTEGER, pstn_call_policy INTEGER, avatar_policy INTEGER, buddycount_policy INTEGER, timezone_policy INTEGER, webpresence_policy INTEGER, phonenumbers_policy INTEGER, voicemail_policy INTEGER, authrequest_policy INTEGER, ad_policy INTEGER, partner_optedout TEXT, service_provider_info TEXT, registration_timestamp INTEGER, nr_of_other_instances INTEGER, partner_channel_status TEXT, owner_under_legal_age INTEGER, type INTEGER, skypename TEXT, pstnnumber TEXT, fullname TEXT, birthday INTEGER, gender INTEGER, languages TEXT, country TEXT, province TEXT, city TEXT, phone_home TEXT, phone_office TEXT, phone_mobile TEXT, emails TEXT, homepage TEXT, about TEXT, profile_timestamp INTEGER, received_authrequest TEXT, displayname TEXT, refreshing INTEGER, given_authlevel INTEGER, aliases TEXT, authreq_timestamp INTEGER, mood_text TEXT, timezone INTEGER, nrof_authed_buddies INTEGER, ipcountry TEXT, given_displayname TEXT, availability INTEGER, lastonline_timestamp INTEGER, capabilities BLOB, avatar_image BLOB, assigned_speeddial TEXT, lastused_timestamp INTEGER, authrequest_count INTEGER, assigned_comment TEXT, alertstring TEXT, avatar_timestamp INTEGER, mood_timestamp INTEGER, rich_mood_text TEXT, synced_email BLOB, set_availability INTEGER, options_change_future BLOB, cbl_profile_blob BLOB, authorized_time INTEGER, sent_authrequest TEXT, sent_authrequest_time INTEGER, sent_authrequest_serial INTEGER, buddyblob BLOB, cbl_future BLOB, node_capabilities INTEGER, node_capabilities_and INTEGER, revoked_auth INTEGER, added_in_shared_group INTEGER, in_shared_group INTEGER, authreq_history BLOB, profile_attachments BLOB, stack_version INTEGER, offline_authreq_id INTEGER, verified_email BLOB, verified_company BLOB, flamingo_xmpp_status INTEGER, federated_presence_policy INTEGER, liveid_membername TEXT, uses_jcs INTEGER, roaming_history_enabled INTEGER, cobrand_id INTEGER);
INSERT INTO Accounts VALUES(...);
CREATE TABLE Alerts (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER, timestamp INTEGER, partner_name TEXT, is_unseen INTEGER, partner_id INTEGER, partner_event TEXT, partner_history TEXT, partner_header TEXT, partner_logo TEXT, meta_expiry INTEGER, message_header_caption TEXT, message_header_title TEXT, message_header_subject TEXT, message_header_cancel TEXT, message_header_later TEXT, message_content TEXT, message_footer TEXT, message_button_caption TEXT, message_button_uri TEXT, message_type INTEGER, window_size INTEGER, chatmsg_guid BLOB, notification_id INTEGER, event_flags INTEGER, extprop_hide_from_history INTEGER);
INSERT INTO Alerts VALUES(...); 

Open in new window



This is a sql text file for skype for windows

I want only the create lines so I can guess the table relationship

So how can I echo only the create lines
0
Comment
Question by:rgb192
  • 3
  • 3
  • 3
9 Comments
 
LVL 51

Accepted Solution

by:
Julian Hansen earned 250 total points
Comment Utility
Something like this ?
$filename = "nameofsqlfile.sql";
$lines = explode("\n", file_get_contents($filename));
foreach($lines as $l) {
   if (substr($l, 0, 6) == 'CREATE') {
      echo $l . PHP_EOL;
   }
}

Open in new window

0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
Comment Utility
@rgb192: As with most questions, the quality of the answer you get will depend greatly on the quality of the test data you provide.  In the case of CREATE TABLE statements, they can be on any number of lines with line breaks in the middle of the statements.  In addition the statements are not case-sensitive and can be bounded with whitespace.  This means you need a regular expression to normalize the incoming data and a regular expression to extract the CREATE TABLE statements.  If you're willing to assume that there are no CREATE statements that are not about TABLE (eg: CREATE DATABASE) this algorithm will probably work well.
http://www.laprbass.com/RAY_temp_rgb192.php

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

// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28309711.html

// A REGEX TO FIND PADDED WHITESPACE
$nws = '#\s\s+#';

// A REGEX TO FIND "CREATE TABLE" STATEMENTS
$rgx
= '#'         // REGEX DELIMITER
. '\b'        // WORD BOUNDARY
. '('         // START CAPTURE GROUP
. 'CREATE'    // STRING LITERAL
. '.*?'       // ANYTHING
. ';'         // STRING LITERAL SEMICOLON
. ')'         // END OF CAPTURE GROUP
. '#'         // REGEX DELIMITER
. 'i'         // CASE INSENSITIVE
;

// WHAT AN INPUT FILE MIGHT LOOK LIKE AFTER file_get_contents()
$str = <<<EOD
BEGIN TRANSACTION;
  Create TABLE Accounts (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER, status INTEGER
  , pwdchangestatus INTEGER, logoutreason INTEGER, commitstatus INTEGER, suggested_skypename TEXT
  , skypeout_balance_currency TEXT, skypeout_balance INTEGER);
INSERT INTO Accounts VALUES(...);

CREATE
TABLE
Alerts (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER, extprop_hide_from_history INTEGER);
INSERT INTO Alerts VALUES(...);
EOD;

// NORMALIZE AND PROCESS THE FILE
$new = preg_replace($nws, ' ', $str);
echo "REGEX: $rgx FINDS:" . PHP_EOL;
if (preg_match_all($rgx, $new, $mat)) print_r($mat[0]);

Open in new window

Best regards, ~Ray
0
 

Author Comment

by:rgb192
Comment Utility
I used this in my ide. I had to change EOL to <br>
and
add more information requiring create table

<?php
$filename = "C:/Users/Acer/AppData/Roaming/Skype/respondto/sql-file";
$lines = explode("\n", file_get_contents($filename));
foreach($lines as $l) {
   if (substr($l, 0, 13) == 'CREATE TABLE ') {
      echo $l . '<br>';
   }
}

Open in new window


Ray's code also worked.  Thanks
0
 

Author Closing Comment

by:rgb192
Comment Utility
I had to write the closing comment with code.
Experts-exchange: This last text area does not allow code blocks.

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28309711.html#a39695419

Thanks
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
To anyone coming upon this question in the future, please be aware of the large memory requirements of PHP arrays.  There are reasons to choose string variables and not choose to process the data by converting it into an array.  These reasons go beyond the risk of variations in the data structure.  The author of this question has another question here at EE showing why sometimes you may not want to use arrays if you can use strings instead.
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
To add to the above - the asker's question seemed to indicate a specific requirement i.e. not a solution to the general case of parsing a file.

If arrays are not your preference then you can simply use plain ol' file processing
<?php
$file = 's.sql';
$fp = fopen($file,'rt');
while($line = fgets($fp)) {
   if (substr($line, 0, 6) == 'CREATE') {
      echo $line . '<br/>';
   }
}
?>

Open in new window

While regular expressions are very powerful and a valid solution here - given the requirements of the askers question a substr comparisson is just as acceptable.

The point being that for small case specific requirements where the general case is not an issue it is acceptable to use simpler / faster methods for processing data.

The preference should be given to what you are most comfortable with.
0
 

Author Comment

by:rgb192
Comment Utility
so regular expression is array and substring is string?
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
A "regular expression" (make a Google search) is a string that is used to parse external input according to the rules of regular expressions.  The output of the parse operation may be an array.  A substring is a string that is a subset (a part) of a string,

You may want to experiment with the code examples shown here in this question and here in this other question to develop your own understanding of the similarities and differences.  If you give it a little bit of test time you will be able to come up with your own teaching examples for your permanent code library.

Good luck with it, ~Ray
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
There are many ways to skin this cat

substr returns a specific part of a string (specific length from a specific offset). It is useful when you know the structure of your input and exactly what you are looking for. In this case you are looking for the word 'CREATE' (or 'CREATE TABLE') and you know it starts at the beginning of the line. This makes substr a simple and quick solution to the problem.

Use
if (substr($source, 0,12) = 'CREATE TABLE') ...

Another solution is strstr. This can be used in this context like this

if (strstr($source, 'CREATE TABLE')) ...

strstr looks for a specific instance of a string within another string and returns the substring from the first occurance of the the required string or FALSE if not found.

This is usefull when you know what you are looking for but you don't know where in the source it might be.

Regular expressions are the most powerful and generally used when searching for a pattern that may occur one or more times in the source. The pattern can include wildcards and / or specific substrings / characters.

This is useful when you want to search for 1 or more occurrances of a specific pattern within an input string.

Regular expressions can be used in any situation you may have in searching for a substring or pattern - whereas the other two are more restrictive. Which one you use depends on your personal preference and the task you are trying to achieve.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

762 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

14 Experts available now in Live!

Get 1:1 Help Now