Solved

echo sql create statements separated by line breaks

Posted on 2014-01-15
22
391 Views
Last Modified: 2014-02-24
This question is related to:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28339083.html

<?php
ini_set('display_errors', TRUE);
$sql = file_get_contents("C:/Users/Acer/Documents/dumps/storage-creates2.sql");

// ISOLATE THE CREATE TABLE STATEMENTS
preg_match_all("/CREATE TABLE [^;]+;/", $sql, $mat);
$new = implode(PHP_EOL, $mat[0]);

// REMOVE THE AUTO_INCREMENT
$new = preg_replace('/AUTO_INCREMENT=\d+?\s/', NULL, $new);
var_dump($new);
echo $new;

Open in new window



current output
string(810) "CREATE TABLE `a_images` ( `a_images_id` int(11) NOT NULL auto_increment, `profile_id` varchar(20) default NULL, `image_name` varchar(100) default NULL, `image_url` varchar(200) default NULL, PRIMARY KEY (`a_images_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE `a_messages` ( `a_messages_id` int(11) NOT NULL auto_increment, `conversation_id` bigint(20) default NULL, `profile_id` varchar(20) default NULL, `sender` varchar(20) default NULL, `message_id` bigint(20) default NULL, `message_text` varchar(1000) default NULL, `dateAgo` datetime default NULL, `message_read` tinyint(4) default NULL, `this_user` varchar(20) default NULL, PRIMARY KEY (`a_messages_id`), UNIQUE KEY `unique_message_id` (`message_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;" CREATE TABLE `a_images` ( `a_images_id` int(11) NOT NULL auto_increment, `profile_id` varchar(20) default NULL, `image_name` varchar(100) default NULL, `image_url` varchar(200) default NULL, PRIMARY KEY (`a_images_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE `a_messages` ( `a_messages_id` int(11) NOT NULL auto_increment, `conversation_id` bigint(20) default NULL, `profile_id` varchar(20) default NULL, `sender` varchar(20) default NULL, `message_id` bigint(20) default NULL, `message_text` varchar(1000) default NULL, `dateAgo` datetime default NULL, `message_read` tinyint(4) default NULL, `this_user` varchar(20) default NULL, PRIMARY KEY (`a_messages_id`), UNIQUE KEY `unique_message_id` (`message_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Open in new window




want this output for echo $new
CREATE TABLE `a_images` ( `a_images_id` int(11) NOT NULL auto_increment, `profile_id` varchar(20) default NULL, `image_name` varchar(100) default NULL, `image_url` varchar(200) default NULL, PRIMARY KEY (`a_images_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

CREATE TABLE `a_messages` ( `a_messages_id` int(11) NOT NULL auto_increment, `conversation_id` bigint(20) default NULL, `profile_id` varchar(20) default NULL, `sender` varchar(20) default NULL, `message_id` bigint(20) default NULL, `message_text` varchar(1000) default NULL, `dateAgo` datetime default NULL, `message_read` tinyint(4) default NULL, `this_user` varchar(20) default NULL, PRIMARY KEY (`a_messages_id`), UNIQUE KEY `unique_message_id` (`message_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Open in new window



note: I have never seen linebreaks using \n\n on a browser
So I always use <br> but the experts write \n\n.
0
Comment
Question by:rgb192
  • 8
  • 6
  • 4
  • +1
22 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 39784234
Is this just for output to the browser?
echo nl2br ($new);

If you want two lines
echo str_replace("\r",'<br><br>',$new);
0
 

Author Comment

by:rgb192
ID: 39784286
echo nl2br ($new);
new lines

but I am desiring two lines and

echo str_replace("\r",'<br><br>',$new);

double spaces every line
0
 
LVL 58

Expert Comment

by:Gary
ID: 39784299
Uh?
nl2br moves to a new line

The str_replace moves down 2 lines (i.e. one full blank line between).

If you want two blank lines then
echo str_replace("\r","\n<br><br><br>",$new);
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39784308
When I'm debugging code, I like to throw echo '<pre>'; into the top of the script.  Either that or use view source in the browser.  If you're using PHP to create the data string, you can use this to make end-of-lines into breaks:

$datastring = str_replace(PHP_EOL, '<br>' . PHP_EOL, $datastring);

Or you can use nl2br() - same sort of effect.

If you combine echo '<pre>'; and nl2br() you'll get double line spacing.
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 39784784
Since $new already has linebreaks (PHP_EOL's), just double up the PHP_EOL's via replace() and simply output the string inside of a PRE tag:

echo '<pre>' . replace(PHP_EOL, PHP_EOL . PHP_EOL, $new) . '</pre>';

Open in new window

0
 

Author Comment

by:rgb192
ID: 39785965
http://www.experts-exchange.com/viewCodeSnippet.jsp?refID=28339852&rtid=10&icsi=3

want first create to be on new line
blank line
second create on a new line


echo str_replace("\r","\n<br><br><br>",$new);
double line spacing on every line




$datastring = str_replace(PHP_EOL, '<br>' . PHP_EOL, $new);
echo $datastring;

new line for second query (not first query)







Fatal error: Call to undefined function replace() in C:\wamp\www\test\readingsql10.php on line 13
echo '<pre>' . replace(PHP_EOL, PHP_EOL . PHP_EOL, $new) . '</pre>';
0
 
LVL 58

Expert Comment

by:Gary
ID: 39786022
Can you attach the sql file - you're not really making sense.
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 39786077
Sorry about the replace function typo; try this corrected code:

echo '<pre>' . str_replace(PHP_EOL, PHP_EOL . PHP_EOL, $new) . '</pre>'; 

Open in new window


The output should look like

CREATE1
(blank) Line
CREATE2
0
 

Author Comment

by:rgb192
ID: 39786924
echo '<pre>' . str_replace(PHP_EOL, PHP_EOL . PHP_EOL, $new) . '</pre>';

CREATE TABLE `a_images` (

  `a_images_id` int(11) NOT NULL auto_increment,

  `profile_id` varchar(20) default NULL,

  `image_name` varchar(100) default NULL,

  `image_url` varchar(200) default NULL,

  PRIMARY KEY  (`a_images_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `a_messages` (

  `a_messages_id` int(11) NOT NULL auto_increment,

  `conversation_id` bigint(20) default NULL,

  `profile_id` varchar(20) default NULL,

  `sender` varchar(20) default NULL,

  `message_id` bigint(20) default NULL,

  `message_text` varchar(1000) default NULL,

  `dateAgo` datetime default NULL,

  `message_read` tinyint(4) default NULL,

  `this_user` varchar(20) default NULL,

  PRIMARY KEY  (`a_messages_id`),

  UNIQUE KEY `unique_message_id` (`message_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Open in new window

0
 
LVL 58

Expert Comment

by:Gary
ID: 39786948
You need to attach the file so we can see the makeup of the file, it make more sense now.
Else try this

$new=str_replace("\r","",$new);
echo str_replace("CHARSET=utf8;","CHARSET=utf8;<br><br>",$new);
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 39786988
//replace all linebreaks with NULL; then replace all semi-colons with semi-colon AND two linebreaks
echo '<pre>' . str_replace(';', ';' . PHP_EOL . PHP_EOL, str_replace(PHP_EOL, '', $new)) . '</pre>';

Open in new window

0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39787136
This seems to test out OK.  http://www.laprbass.com/RAY_temp_rgb192.php

Browser output collapses multiple whitespace characters (blank, tab, end-of-line) into a single blank character, so that is why the <br> tag exists - to cause line breaks in browser output.  But if you preformat the output with the <pre> tag, the whitespace is preserved.  Bonus: You get a unispace font that makes for good character alignment from one line to the next.

You can also get this effect with the browser "view source."

<?php // RAY_temp_rgb192.php
error_reporting(E_ALL);

$string = <<<EOD
CREATE TABLE `a_images` ( `a_images_id` int(11) NOT NULL auto_increment, `profile_id` varchar(20) default NULL, `image_name` varchar(100) default NULL, `image_url` varchar(200) default NULL, PRIMARY KEY (`a_images_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE `a_messages` ( `a_messages_id` int(11) NOT NULL auto_increment, `conversation_id` bigint(20) default NULL, `profile_id` varchar(20) default NULL, `sender` varchar(20) default NULL, `message_id` bigint(20) default NULL, `message_text` varchar(1000) default NULL, `dateAgo` datetime default NULL, `message_read` tinyint(4) default NULL, `this_user` varchar(20) default NULL, PRIMARY KEY (`a_messages_id`), UNIQUE KEY `unique_message_id` (`message_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;" CREATE TABLE `a_images` ( `a_images_id` int(11) NOT NULL auto_increment, `profile_id` varchar(20) default NULL, `image_name` varchar(100) default NULL, `image_url` varchar(200) default NULL, PRIMARY KEY (`a_images_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE `a_messages` ( `a_messages_id` int(11) NOT NULL auto_increment, `conversation_id` bigint(20) default NULL, `profile_id` varchar(20) default NULL, `sender` varchar(20) default NULL, `message_id` bigint(20) default NULL, `message_text` varchar(1000) default NULL, `dateAgo` datetime default NULL, `message_read` tinyint(4) default NULL, `this_user` varchar(20) default NULL, PRIMARY KEY (`a_messages_id`), UNIQUE KEY `unique_message_id` (`message_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;";
EOD;

// MAKE AN ARRAY FROM THE LONG STRING OF CREATE STATEMENTS
$array = explode("CREATE TABLE", $string);

// RECONSTRUCT THE ARRAY INTO SEPARATE LINES
$new =  implode(PHP_EOL . PHP_EOL . "CREATE TABLE", $array);

// ENSURE PREFORMATTING WILL CAUSE NEW LINES IN THE BROWSER OUTPUT
echo '<pre>';

// SHOW THE WORK PRODUCT
echo $new;

Open in new window

0
 

Author Comment

by:rgb192
ID: 39818617
<?php
ini_set('display_errors', TRUE);
$sql = file_get_contents("C:/Users/Acer/Documents/dumps/storage-creates2.sql");


// MAKE AN ARRAY FROM THE LONG STRING OF CREATE STATEMENTS
$array = explode("CREATE TABLE", $sql);

// RECONSTRUCT THE ARRAY INTO SEPARATE LINES
$new =  implode(PHP_EOL . PHP_EOL . "CREATE TABLE", $array);

// ENSURE PREFORMATTING WILL CAUSE NEW LINES IN THE BROWSER OUTPUT
echo '<pre>';

// SHOW THE WORK PRODUCT
echo $new;

Open in new window


I saw all the .sql file
Please only show create


I do not understand the changes I should make from
NerdsOfTech and Cathal


but i have code of storage-creates2.sql

CREATE DATABASE  IF NOT EXISTS `dbname` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `dbname`;
-- MySQL dump 10.13  Distrib 5.6.13, for Win32 (x86)
--
-- Host: name.dbname.839127.hostedresource.com    Database: dbname
-- ------------------------------------------------------
-- Server version	5.0.96-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Not dumping tablespaces as no INFORMATION_SCHEMA.FILES table on this server
--

--
-- Table structure for table `a_images`
--

DROP TABLE IF EXISTS `a_images`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `a_images` (
  `a_images_id` int(11) NOT NULL auto_increment,
  `profile_id` varchar(20) default NULL,
  `image_name` varchar(100) default NULL,
  `image_url` varchar(200) default NULL,
  PRIMARY KEY  (`a_images_id`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `a_images`
--

LOCK TABLES `a_images` WRITE;
/*!40000 ALTER TABLE `a_images` DISABLE KEYS */;
INSERT INTO `a_images` VALUES ();
/*!40000 ALTER TABLE `a_images` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `a_messages`
--

DROP TABLE IF EXISTS `a_messages`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `a_messages` (
  `a_messages_id` int(11) NOT NULL auto_increment,
  `conversation_id` bigint(20) default NULL,
  `profile_id` varchar(20) default NULL,
  `sender` varchar(20) default NULL,
  `message_id` bigint(20) default NULL,
  `message_text` varchar(1000) default NULL,
  `dateAgo` datetime default NULL,
  `message_read` tinyint(4) default NULL,
  `this_user` varchar(20) default NULL,
  PRIMARY KEY  (`a_messages_id`),
  UNIQUE KEY `unique_message_id` (`message_id`)
) ENGINE=MyISAM AUTO_INCREMENT=254 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `a_messages`
--

LOCK TABLES `a_messages` WRITE;
/*!40000 ALTER TABLE `a_messages` DISABLE KEYS */;
INSERT INTO `a_messages` VALUES ();
/*!40000 ALTER TABLE `a_messages` ENABLE KEYS */;
UNLOCK TABLES;

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 39818691
Is there a script generating this .sql file?

If so, post it and the experts can help flag the area that is generating the "create table" lines to force the line-wrap you desire.


Is this what you are looking for? :
CREATE DATABASE  IF NOT EXISTS `dbname` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `dbname`;
-- MySQL dump 10.13  Distrib 5.6.13, for Win32 (x86)
--
-- Host: name.dbname.839127.hostedresource.com    Database: dbname
-- ------------------------------------------------------
-- Server version	5.0.96-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Not dumping tablespaces as no INFORMATION_SCHEMA.FILES table on this server
--

--
-- Table structure for table `a_images`
--

DROP TABLE IF EXISTS `a_images`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `a_images` (`a_images_id` int(11) NOT NULL auto_increment, `profile_id` varchar(20) default NULL, `image_name` varchar(100) default NULL, `image_url` varchar(200) default NULL, PRIMARY KEY  (`a_images_id`)) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `a_images`
--

LOCK TABLES `a_images` WRITE;
/*!40000 ALTER TABLE `a_images` DISABLE KEYS */;
INSERT INTO `a_images` VALUES ();
/*!40000 ALTER TABLE `a_images` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `a_messages`
--

DROP TABLE IF EXISTS `a_messages`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `a_messages` (`a_messages_id` int(11) NOT NULL auto_increment, `conversation_id` bigint(20) default NULL, `profile_id` varchar(20) default NULL, `sender` varchar(20) default NULL, `message_id` bigint(20) default NULL, `message_text` varchar(1000) default NULL, `dateAgo` datetime default NULL, `message_read` tinyint(4) default NULL, `this_user` varchar(20) default NULL, PRIMARY KEY  (`a_messages_id`), UNIQUE KEY `unique_message_id` (`message_id`)) ENGINE=MyISAM AUTO_INCREMENT=254 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `a_messages`
--

LOCK TABLES `a_messages` WRITE;
/*!40000 ALTER TABLE `a_messages` DISABLE KEYS */;
INSERT INTO `a_messages` VALUES ();
/*!40000 ALTER TABLE `a_messages` ENABLE KEYS */;
UNLOCK TABLES;
                                            

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39819232
I saw all the .sql file
Please only show create
Didn't we have an earlier question showing you how to isolate only the CREATE statements from the dump?

Can you please post the test data you want us to use, here at EE?  We cannot get to the C: drive on your server, so we can't test with your test data set.
0
 

Author Comment

by:rgb192
ID: 39847511
Is there a script generating this .sql file?
I am using mysql workbench query editor to generate storage-creates2.sql

storage-creates2.sql
CREATE DATABASE  IF NOT EXISTS `dbname` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `dbname`;
-- MySQL dump 10.13  Distrib 5.6.13, for Win32 (x86)
--
-- Host: name.dbname.839127.hostedresource.com    Database: dbname
-- ------------------------------------------------------
-- Server version	5.0.96-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Not dumping tablespaces as no INFORMATION_SCHEMA.FILES table on this server
--

--
-- Table structure for table `a_images`
--

DROP TABLE IF EXISTS `a_images`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `a_images` (
  `a_images_id` int(11) NOT NULL auto_increment,
  `profile_id` varchar(20) default NULL,
  `image_name` varchar(100) default NULL,
  `image_url` varchar(200) default NULL,
  PRIMARY KEY  (`a_images_id`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `a_images`
--

LOCK TABLES `a_images` WRITE;
/*!40000 ALTER TABLE `a_images` DISABLE KEYS */;
INSERT INTO `a_images` VALUES ();
/*!40000 ALTER TABLE `a_images` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `a_messages`
--

DROP TABLE IF EXISTS `a_messages`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `a_messages` (
  `a_messages_id` int(11) NOT NULL auto_increment,
  `conversation_id` bigint(20) default NULL,
  `profile_id` varchar(20) default NULL,
  `sender` varchar(20) default NULL,
  `message_id` bigint(20) default NULL,
  `message_text` varchar(1000) default NULL,
  `dateAgo` datetime default NULL,
  `message_read` tinyint(4) default NULL,
  `this_user` varchar(20) default NULL,
  PRIMARY KEY  (`a_messages_id`),
  UNIQUE KEY `unique_message_id` (`message_id`)
) ENGINE=MyISAM AUTO_INCREMENT=254 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `a_messages`
--

LOCK TABLES `a_messages` WRITE;
/*!40000 ALTER TABLE `a_messages` DISABLE KEYS */;
INSERT INTO `a_messages` VALUES ();
/*!40000 ALTER TABLE `a_messages` ENABLE KEYS */;
UNLOCK TABLES;

Open in new window



I also attached file.  Maybe there is a line-wrapping copy paste error.

storage-creates2.sql
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39847735
Please try this

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


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28339852.html#a39847511

//  READ TEST DATA
$url = 'http://filedb.experts-exchange.com/incoming/2014/02_w07/833542/storage-creates2.sql';
$str = file_get_contents($url);

// ACTIVATE THIS TO SHOW WHAT WE READ
// echo htmlentities($str);

// A REGULAR EXPRESSION TO MATCH THE CREATE TABLE STATEMENTS
$rgx
= '#'             // REGEX DELIMITER
. 'CREATE TABLE'  // SIGNAL STRING
. '.*?'           // ANYTHING
. ';'             // THE ENDING SEMICOLON
. '#'             // REGEX DELIMITER
. 'is'            // CASE INSENSITIVE, SINGLE LINE
;

// PERFORM THE MATCH
preg_match_all($rgx, $str, $mat);

// THE QUERIES ARE STORED HERE
print_r($mat[0]);

Open in new window

0
 

Author Comment

by:rgb192
ID: 39857872
Array
(
    [0] => CREATE TABLE `a_images` (
  `a_images_id` int(11) NOT NULL auto_increment,
  `profile_id` varchar(20) default NULL,
  `image_name` varchar(100) default NULL,
  `image_url` varchar(200) default NULL,
  PRIMARY KEY  (`a_images_id`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=utf8;
    [1] => CREATE TABLE `a_messages` (
  `a_messages_id` int(11) NOT NULL auto_increment,
  `conversation_id` bigint(20) default NULL,
  `profile_id` varchar(20) default NULL,
  `sender` varchar(20) default NULL,
  `message_id` bigint(20) default NULL,
  `message_text` varchar(1000) default NULL,
  `dateAgo` datetime default NULL,
  `message_read` tinyint(4) default NULL,
  `this_user` varchar(20) default NULL,
  PRIMARY KEY  (`a_messages_id`),
  UNIQUE KEY `unique_message_id` (`message_id`)
) ENGINE=MyISAM AUTO_INCREMENT=254 DEFAULT CHARSET=utf8;
)
CREATE TABLE `a_images` (
  `a_images_id` int(11) NOT NULL auto_increment,
  `profile_id` varchar(20) default NULL,
  `image_name` varchar(100) default NULL,
  `image_url` varchar(200) default NULL,
  PRIMARY KEY  (`a_images_id`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=utf8;CREATE TABLE `a_messages` (
  `a_messages_id` int(11) NOT NULL auto_increment,
  `conversation_id` bigint(20) default NULL,
  `profile_id` varchar(20) default NULL,
  `sender` varchar(20) default NULL,
  `message_id` bigint(20) default NULL,
  `message_text` varchar(1000) default NULL,
  `dateAgo` datetime default NULL,
  `message_read` tinyint(4) default NULL,
  `this_user` varchar(20) default NULL,
  PRIMARY KEY  (`a_messages_id`),
  UNIQUE KEY `unique_message_id` (`message_id`)
) ENGINE=MyISAM AUTO_INCREMENT=254 DEFAULT CHARSET=utf8;

Open in new window




still has AUTO_INCREMENT=254
ENGINE=MyISAM AUTO_INCREMENT=254 DEFAULT CHARSET=utf8;

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


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28339852.html#a39847511

//  READ TEST DATA
$url = 'http://filedb.experts-exchange.com/incoming/2014/02_w07/833542/storage-creates2.sql';
$str = file_get_contents($url);

// ACTIVATE THIS TO SHOW WHAT WE READ
// echo htmlentities($str);

// A REGULAR EXPRESSION TO MATCH THE CREATE TABLE STATEMENTS
$rgx
= '#'             // REGEX DELIMITER
. 'CREATE TABLE'  // SIGNAL STRING
. '.*?'           // ANYTHING
. ';'             // THE ENDING SEMICOLON
. '#'             // REGEX DELIMITER
. 'is'            // CASE INSENSITIVE, SINGLE LINE
;

// PERFORM THE MATCH
preg_match_all($rgx, $str, $mat);

// THE QUERIES ARE STORED HERE
print_r($mat[0]);

foreach ($mat[0] as $creates){
  echo '<br><br>'.$creates;
}

Open in new window





Thanks for teaching me lines 8-10
//  READ TEST DATA
$url = 'http://filedb.experts-exchange.com/incoming/2014/02_w07/833542/storage-creates2.sql';
$str = file_get_contents($url);

where data is already on experts-exchange.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39859401
OK, you want to remove the auto_increment=xxx from the query string, is that right?  Is there anything else you want to change about the query string?
0
 

Author Comment

by:rgb192
ID: 39872132
OK, you want to remove the auto_increment=xxx from the query string, is that right?
correct.  

Is there anything else you want to change about the query string?
no
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39872176
Please try it like this: http://iconoun.com/demo/temp_rgb192.php

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


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28339852.html#a39847511


//  READ TEST DATA
$url = 'http://filedb.experts-exchange.com/incoming/2014/02_w07/833542/storage-creates2.sql';
$str = file_get_contents($url);

// ACTIVATE THIS TO SHOW WHAT WE READ
// echo htmlentities($str);

// A REGULAR EXPRESSION TO MATCH THE CREATE TABLE STATEMENTS
$rgx
= '#'             // REGEX DELIMITER
. 'CREATE TABLE'  // SIGNAL STRING
. '.*?'           // ANYTHING
. ';'             // THE ENDING SEMICOLON
. '#'             // REGEX DELIMITER
. 'is'            // CASE INSENSITIVE, SINGLE LINE
;

// PERFORM THE MATCH
preg_match_all($rgx, $str, $mat);

// THE QUERIES ARE STORED HERE
// print_r($mat[0]);

// REMOVE THE AUTO_INCREMENT VALUES
$rgx
= '#'                // REGEX DELIMITER
. 'AUTO_INCREMENT='  // SIGNAL STRING
. '\d{1,}'           // DIGIT, ONE OR MORE
. ' {1}'             // BLANK - EXACTLY ONE
. '#'                // REGEX DELIMITER
. 'is'               // CASE-INSENSITIVE, SINGLE LINE
;
foreach ($mat[0] as $qry)
{
    $new = preg_replace($rgx, NULL, $qry);
    echo '<br>' . $new . PHP_EOL;
}

Open in new window

HTH, ~Ray
0
 

Author Closing Comment

by:rgb192
ID: 39884678
thanks.  Now I can get creates from .sql backup
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
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…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

758 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

22 Experts available now in Live!

Get 1:1 Help Now