Solved

echo sql create statements separated by line breaks

Posted on 2014-01-15
22
413 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
[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
  • 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 110

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

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 110

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 110

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 110

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 110

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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

623 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