[SOLVED] Creating a MYSQL database and then uploading a sql file to it

winmastergames · December 29, 2007

Well first off how do I connect to a MYSQL server then make a new database on it IN PHP

Then after thats done how do you think i could then get a SQL file thats in the same folder as the script that i can upload to a MYSQL database??

Thanks for your time

PHP_PhREEEk · December 29, 2007

I assume you know mysql_connect, so there's your connection. You use a CREATE query to make a new database, but without knowing your hosting situation, I cannot guarantee it will work (you need GRANT CREATE permissions to do it).

All of this can be done by installing phpMyAdmin, and this all then becomes extremely simple...

Using PHP to read in SQL statements, then INSERT them is not an easy script to do. You would end up re-writing phpMyAdmin (or at least the Import module within it).

PhREEEk

winmastergames · December 29, 2007

Well i had a look at Mega File hosting script 1.2 installation i just want something like that and they use a SQL file for the tables in the database so how could i have it like that?

nuxy · December 29, 2007

PHP has some nifty functions for mysql.

One of them is mysql_createdb, this should come in handly.

I would however, suggest you use the mysql_query to make a database, it will give you much more controll over the process.

Executing a SQL file to the database should be relativley easy, you just need to make an array of the file and loop trough it.

<?php

$connection = mysql_connect('host','user','passwd');
if (!empty($connection)) {
  mysql_query("CREATE DATABASE `db`");
  mysql_query("USE `db`");
  $file = file_get_contents('db.sql');
  $array = split(';', $file);
  foreach($array as $line) {
     if (!empty(mysql_query("$line"))
       echo 'Executed: ' . $line . '<br>';
     else 
       echo '<b>Failed: ' . $line . '</b><br>';
  } 
}
else 
 echo 'Could not connect to database';
?>

Edit: just make sure you set the right mysql delimiter..

just to give you an idea.

winmastergames · December 29, 2007

Umm i tried the script but it didnt work it came up with this error

Fatal error: Can't use function return value in write context in C:\xampp\htdocs\createdatabase\makedb-insert-data.php on line 10

This is my final script because i might of changed something i shouldnt of

<?php

$connection = mysql_connect('localhost','MYUSERNAME','MYPASSWORD');
if (!empty($connection)) {
   mysql_query("CREATE DATABASE `database`");
   mysql_query("USE `database`");
   $file = file_get_contents('database.sql');
   $array = split(';', $file);
   foreach($array as $line) {
      if (!empty(mysql_query("$line"))
        echo 'Executed: ' . $line . '<br>';
      else 
        echo '<b>Failed: ' . $line . '</b><br>';
   } 
}
else 
  echo 'Could not connect to database';
?>

winmastergames · December 29, 2007

Does anyone know whats wrong because i really need this script to work ASAP

winmastergames · December 29, 2007

Are you allowed to Bump in this forum??

nuxy · December 29, 2007

Try removing the quotes..

<?php

$connection = mysql_connect('localhost','MYUSERNAME','MYPASSWORD');
if (!empty($connection)) {
mysql_query("CREATE DATABASE `database`");
mysql_query("USE `database`");
$file = file_get_contents('database.sql');
$array = split(';', $file);
foreach($array as $line) {
if (!empty(mysql_query($line))
echo 'Executed: ' . $line . '<br>';
else  
echo '<b>Failed: ' . $line . '</b><br>';
}  
}
else  
echo 'Could not connect to database';
?>

winmastergames · December 29, 2007

same error

nuxy · December 29, 2007

Does the file "database.sql" exist in the directory?

IF not, make test file with that name, or change the name of the current file.

winmastergames · December 29, 2007

yes i do have a database,sql script in the directory

this is it

-- phpMyAdmin SQL Dump
-- version 2.11.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 29, 2007 at 10:32 AM
-- Server version: 5.0.45
-- PHP Version: 5.2.4

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `prelink`
--

-- --------------------------------------------------------

--
-- Table structure for table `prelink`
--

CREATE TABLE `prelink` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) collate latin1_general_ci NOT NULL,
  `url` varchar(255) collate latin1_general_ci NOT NULL,
  `description` text collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=39 ;

--
-- Dumping data for table `prelink`
--

winmastergames · December 29, 2007

Wonder whats wrong?

redarrow · December 29, 2007

try this please then add the detection off the file.sql

<?php

// database connection...

// 1. Conection  2. Username 3. Password

$db=mysql_connect("localhost","root","password");

// 1. The $query 2. CREATE DATABASE 3. databse name

$db_name="database_test";

$query='CREATE DATABASE '.$db_name.' ';

$result=mysql_query($query)or die('<center><b>Database exists please delete database or change database name</b></center>');

mysql_select_db('database_test',$db)or die('could not select database');

$result="mysql_query(CREATE TABLE members('id INT',$db)or die('Table error')";

mysql_close($db);

?>

PHP_PhREEEk · December 29, 2007

Using PHP to read in SQL statements, then INSERT them is not an easy script to do.

PhREEEk

If it was, I would have just offered a script. It's not impossible by any means, I've written a few customized ones, but it's what I consider to be beyond a 'Forum Fixer', so good luck with it...

PhREEEk

atlanta · December 29, 2007

Found this function in one of the scripts that i have that auto imports db files. you can try to disect it and see what you get

<?php
function mysql_restore($host, $name, $pass, $database, $file)
{
	mysql_connect ($host, $name, $pass) or die ("Could not connect to the Database");
	mysql_select_db($database)  or die ("Could not choose a Database");
    mysql_query("SET NAMES '".$this->charset."'");


		if(preg_match("/^(.+?)\.sql(\.(bz2|gz))?$/", $file, $matches)) {
			if (isset($matches[3]) && $matches[3] == 'bz2') {
			    $this->SET['comp_method'] = 2;
			}
			elseif (isset($matches[2]) &&$matches[3] == 'gz'){
				$this->SET['comp_method'] = 1;
			}
			else{
				$this->SET['comp_method'] = 0;
			}
			$this->SET['comp_level'] = '';
			if (!file_exists($file))    die("File not found!");
    		}

	$fp = $this->fn_open($file, "r");
	$this->file_cache = $sql = $table = $insert = '';
        $is_skd = $query_len = $execute = $q =$t = $i = $aff_rows = 0;
		$limit = 300;
        $index = 4;
		$tabs = 0;
		$cache = '';
		$info = array();



		while(($str = $this->fn_read_str($fp)) !== false){
			if (empty($str) || preg_match("/^(#|--)/", $str)) {
				if (!$is_skd && preg_match("/^#SKD101\|/", $str)) {
				    $info = explode("|", $str);
					$is_skd = 1;
				}
        	    continue;
        	}
			$query_len += strlen($str);

			if (!$insert && preg_match("/^(INSERT INTO `?([^` ]+)`? .*?VALUES)(.*)$/i", $str, $m)) {
				if ($table != $m[2]) {
				    $table = $m[2];
					$tabs++;
					$i = 0;
				}
        	    $insert = $m[1] . ' ';
				$sql .= $m[3];
				$index++;
				$info[$index] = isset($info[$index]) ? $info[$index] : 0;
				$limit = round($info[$index] / 20);
				$limit = $limit < 300 ? 300 : $limit;
				if ($info[$index] > $limit){
					$cache = '';
				}
        	}
			else{
        		$sql .= $str;
				if ($insert) {
				    $i++;
    				$t++;
				}
        	}

			if (!$insert && preg_match("/^CREATE TABLE (IF NOT EXISTS )?`?([^` ]+)`?/i", $str, $m) && $table != $m[2]){
				$table = $m[2];
				$insert = '';
				$tabs++;
				$cache .= "Table `{$table}`.";
				$i = 0;
			}
			if ($sql) {
			    if (preg_match("/;$/", $str)) {
            		$sql = rtrim($insert . $sql, ";");
            		$insert = '';
            	    $execute = 1;
            	}
            	if ($query_len >= 65536 && preg_match("/,$/", $str)) {
            		$sql = rtrim($insert . $sql, ",");
            	    $execute = 1;
            	}
    			if ($execute) {
            		$q++;
            		mysql_query($sql) or trigger_error ("Wrong Query.<BR>" . mysql_error(), E_USER_ERROR);
					if (preg_match("/^insert/i", $sql)) {
            		    $aff_rows += mysql_affected_rows();
            		}
            		$sql = '';
            		$query_len = 0;
            		$execute = 0;
            	}
			}
		}


    mysql_close();
    return true;
}
?>

redarrow · December 29, 2007

i was just teaching my self some mysql with php

here what i got so far if it helps........

<?php

// database connection
$db=mysql_connect("xxxx","xxx","xxxx");

//create database
$query='CREATE DATABASE database_connection';
$result=mysql_query($query)or die('<center><b>Database exists please delete database or change database name</b></center>');

//select database
mysql_select_db("database_connection",$db);

$sql = 'CREATE TABLE `zones` (
        `zid` TINYINT( 3 ) UNSIGNED NOT NULL AUTO_INCREMENT,
        `zdesc` VARCHAR( 150 ) NOT NULL,
        `zwidth` SMALLINT( 3 ) DEFAULT 0 NOT NULL,
        `zheight` SMALLINT( 3 ) DEFAULT 0 NOT NULL,
        `zmax` MEDIUMINT(7) unsigned NOT NULL default 12288,
        PRIMARY KEY ( `zid` )
       )';

//query database
mysql_query( $sql, $db );


  $result5="SHOW COLUMNS FROM zones";
  
  $result6=mysql_query($result5)or die('error on show columns');
  
  while($row=mysql_fetch_row($result6)){
  	
  	for($i=0; $i<count($row); $i++){
  	
  	echo "<br>$row[$i] <br>";
  }
  }
    
mysql_close($db);
?>

winmastergames · December 30, 2007

try this please then add the detection off the file.sql

<?php

// database connection...

// 1. Conection  2. Username 3. Password

$db=mysql_connect("localhost","root","password");

// 1. The $query 2. CREATE DATABASE 3. databse name

$db_name="database_test";

$query='CREATE DATABASE '.$db_name.' ';

$result=mysql_query($query)or die('<center><b>Database exists please delete database or change database name</b></center>');

mysql_select_db('database_test',$db)or die('could not select database');

$result="mysql_query(CREATE TABLE members('id INT',$db)or die('Table error')";

mysql_close($db);

?>

Im confused at what that has to do with my script?

The script I have now makes the database (whoops should of told you that) but it doesnt insert the SQL file into it?

winmastergames · December 30, 2007

Sorry didnt see second page hold on while i try it

winmastergames · December 30, 2007

Umm that last code worked well kind of it came up error on show columns But i looked at in PHPmyAdmin and it was all there

What does that error mean?

Sign In

[SOLVED] Creating a MYSQL database and then uploading a sql file to it

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information