echo sql create statements separated by line breaks

Is this just for output to the browser?
echo nl2br ($new);

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

echo nl2br ($new);
new lines

but I am desiring two lines and

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

double spaces every line

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

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.

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

Select allOpen in new window

https://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>';

Can you attach the sql file - you're not really making sense.

Sorry about the replace function typo; try this corrected code:

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

Select allOpen in new window

The output should look like

CREATE1
(blank) Line
CREATE2

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;

Select allOpen in new window

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

//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>';

Select allOpen in new window

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;

Select allOpen in new window

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

Select allOpen 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;

Select allOpen in new window

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;
                                            

Select allOpen in new window

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.

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;

Select allOpen in new window

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

storage-creates2.sql

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]);

Select allOpen in new window

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;

Select allOpen 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;
}

Select allOpen 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.

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?

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

thanks.  Now I can get creates from .sql backup