CODEWITHSUNDEEP
phpmysqltext-files
Ahamed Zulfan
Ahamed Zulfan

Reputation: 135

Create tables in database from queries in a text file

I have the following PHP page to create a table using a text file.

table_create.php

<?php
include $db;
$query_file = "sql.txt";

$fp    = fopen($query_file, 'r');
$sql = fread($fp, filesize($query_file));
fclose($fp); 

$retval = mysql_query($sql);
if(! $retval )
{
  die("Could not create the tables<br>");
}
echo "Table created successfully<br>";
?>

sql.txt

CREATE TABLE ht_account (
  id int(11) NOT NULL AUTO_INCREMENT,
  date date NOT NULL,
  type varchar(50) NOT NULL,
  mode varchar(50) NOT NULL,
  party varchar(50) NOT NULL,
  payee varchar(50) NOT NULL,
  rate decimal(13,2) NOT NULL,
  box int(11) NOT NULL,
  amount decimal(13,2) NOT NULL,
  token varchar(50) NOT NULL,
  remarks varchar(50) NOT NULL,
  user varchar(50) NOT NULL,
  user_confirm varchar(50) NOT NULL,
  status varchar(50) NOT NULL);

CREATE TABLE ht_bank (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50) NOT NULL,
  ac_no varchar(50) NOT NULL,
  address varchar(50) NOT NULL);

CREATE TABLE ht_user_role (
  id int(11) NOT NULL AUTO_INCREMENT,
  value varchar(50) NOT NULL);

When I try to create a single table in the sql.txt file, the code works perfectly. For example:

CREATE TABLE ht_account (
  id int(11) NOT NULL AUTO_INCREMENT,
  date date NOT NULL,
  type varchar(50) NOT NULL,
  mode varchar(50) NOT NULL,
  party varchar(50) NOT NULL,
  payee varchar(50) NOT NULL,
  rate decimal(13,2) NOT NULL,
  box int(11) NOT NULL,
  amount decimal(13,2) NOT NULL,
  token varchar(50) NOT NULL,
  remarks varchar(50) NOT NULL,
  user varchar(50) NOT NULL,
  user_confirm varchar(50) NOT NULL,
  status varchar(50) NOT NULL);

But when I try to create multiple tables, It does not create any table. I doubt that the format in the sql.txt may be incorrect.

Upvotes: 0

Views: 1501

Answers (1)

Serpes
Serpes

Reputation: 672

The format is, almost sure, correct but mysql_query doesn't work with multiple queries:

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

It's better to use mysqli functions because mysql ones are deprecated for PHP 5.5 and mysqli has the function mysqli_multi_query that you need.

If you still want to use mysql functions you could do something like:

$sql_array=explode(';',$sql);
foreach ($sql_array as $s) {
   if(! mysql_query($s)){
      echo mysql_error()."<br>";
   }
}

Upvotes: 2

Related Questions