• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

echo sql create statements separated by line breaks

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
rgb192
Asked:
rgb192
  • 8
  • 6
  • 4
  • +1
1 Solution
 
GaryCommented:
Is this just for output to the browser?
echo nl2br ($new);

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

but I am desiring two lines and

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

double spaces every line
0
 
GaryCommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Ray PaseurCommented:
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
 
NerdsOfTechTechnology ScientistCommented:
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
 
rgb192Author Commented:
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
 
GaryCommented:
Can you attach the sql file - you're not really making sense.
0
 
NerdsOfTechTechnology ScientistCommented:
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
 
rgb192Author Commented:
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
 
GaryCommented:
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
 
NerdsOfTechTechnology ScientistCommented:
//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
 
Ray PaseurCommented:
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
 
rgb192Author Commented:
<?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
 
NerdsOfTechTechnology ScientistCommented:
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
 
Ray PaseurCommented:
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
 
rgb192Author Commented:
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
 
Ray PaseurCommented:
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
 
rgb192Author Commented:
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
 
Ray PaseurCommented:
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
 
rgb192Author Commented:
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
 
Ray PaseurCommented:
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
 
rgb192Author Commented:
thanks.  Now I can get creates from .sql backup
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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