How to create a database in php?

A

Andrey Dyrkov2015-04-20 20:59:25

Database

Andrey Dyrkov, 2015-04-20 20:59:25

There is such a file that creates a database

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Document</title>
</head>
<body>

<?
  if ($_SERVER["REQUEST_METHOD"] == "POST") { 
    if (isset($_POST["host"])) { $host = $_POST["host"]; }
    if (isset($_POST["db"])) { $db = $_POST["db"]; }
    if (isset($_POST["login"])) { $login = $_POST["login"]; }
    if (isset($_POST["pwd"])) { $password = $_POST["pwd"]; }

    mysql_connect($host, $login, $password) or die('БД не создана. '.mysql_error());



    $sql ='
      SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
    ';

    mysql_query($sql) or die(mysql_error());
    $sql ='
      SET time_zone = "+00:00";
    ';

    mysql_query($sql) or die(mysql_error());



    $sql = 'CREATE DATABASE IF NOT EXISTS '.$db;
    mysql_query($sql) or die(mysql_error());
    mysql_select_db($db) or die(mysql_error());

    $sql = "
    CREATE TABLE IF NOT EXISTS `users` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) CHARACTER SET utf8mb4 NOT NULL,
      `login` varchar(50) NOT NULL,
      `password` varchar(50) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `id` (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=18 ;";

    mysql_query($sql) or die("Не была создана таблица tovar. ".mysql_error());



    $sql = "
    CREATE TABLE IF NOT EXISTS `tests` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(200) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `id` (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;";

    mysql_query($sql) or die("Не была создана таблица test. ".mysql_error());



    $sql = "
    CREATE TABLE IF NOT EXISTS questions (
      id int(11) NOT NULL auto_increment COLLATE utf8_general_ci,
      idTest int(11) NOT NULL COLLATE utf8_general_ci,
      description varchar(900) COLLATE utf8_general_ci,
      PRIMARY KEY (id)
    )";

    mysql_query($sql) or die("Не была создана таблица questions. ".mysql_error());




    $sql = "
    CREATE TABLE IF NOT EXISTS `answers` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `idQuestions` int(11) NOT NULL,
      `description` varchar(900) NOT NULL,
      `flag` tinyint(1) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `id` (`id`),
      KEY `idQuestions` (`idQuestions`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;";

    mysql_query($sql) or die("Не была создана таблица answers. ".mysql_error());

/*
    $sql ="
      ALTER TABLE `answers`
      ADD CONSTRAINT `answers_ibfk_1` FOREIGN KEY (`idQuestions`) REFERENCES `questions` (`id`);
    ";

    mysql_query($sql) or die("Проблема с связями 1. ".mysql_error());



    $sql ="
      ALTER TABLE `questions`
      ADD CONSTRAINT `questions_ibfk_1` FOREIGN KEY (`idTest`) REFERENCES `tests` (`id`);
    ";

    mysql_query($sql) or die("Проблема с связями 2. ".mysql_error());*/



    /*
    ============
      DATA
    ============
    */

    /*$sql ="
      INSERT INTO `tests` (`id`, `name`) VALUES
                (1,    'New test'),
                (3,    'My two test'),
                (4,    'Three test');

    ";

    mysql_query($sql) or die("Проблема с связями. ".mysql_error());




    $sql ="
      INSERT INTO `questions` (`id`, `idTest`, `Description`) VALUES
                  (1,       1,        'How many money needed you?'),
                  (2,       1,        'How are you?'),
                  (3,       3,        'Hello, how is you name?'),
                  (4,       3,        'Why?'),
                  (5,       3,        'what it?'),
                  (6,       3,        'What be tummmrow?'),
                  (7,       3,        'Как дела?'),
                  (8,       3,        'Ну что будешь делать,');
    ";

    mysql_query($sql) or die("ПРоблема с связями. ".mysql_error());

    $sql ="
      INSERT INTO `answers` (`id`, `idQuestions`, `description`,             `flag`) VALUES
                (1,     1,             'i need 20 money',0),
                (2,     1,             'i need 10 money',1),
                (5,     1,             'i need 100 money',0),
                (7,     2,             'i am ok',1),
                (8,     2,             'i am bad',0),
                (9,     3,             'пока',0),
                (10     4,             'както фигово',0);
    ";

    mysql_query($sql) or die("ПРоблема с связями. ".mysql_error());

*/

    echo "<br />";
    echo "База данных создана!<br />";
    echo "<br />";

    mysql_close();
  }

?>


  <form action="createdatabase.php" method="post">
    <table border="0">
      <tr><td><label>Имя хоста:</label></td>
        <td><input type="text" name="host" value="localhost" /></td>
      </tr>
      <tr><td><label>Имя базы данных:</label></td>
        <td><input type="text" name="db" value="SISTESTDATABASETEST" /></td>
      </tr>
      <tr><td><label>Пользователь:</label></td>
        <td><input type="text" name="login" value="root" /></td>
      </tr>
      <tr><td><label>Пароль:</label></td>
        <td><input type="password" name="pwd" value="" /></td>
      </tr>
      <tr><td></td>
        <td><input type="submit" value="Создать БД" /></td>
      </tr>
    </table>
  </form>
</body>
</html>

Writes an error when I try to install the keys

Reply

Answer the question

In order to leave comments, you need to log in

1 answer(s)

A

Andrey Dyrkov, 2015-04-20
@VIKINGVyksa

I found the problem. When creating the Constraint, I already had its name, so when creating a copy of the database, the constraints already exist and I need to rename them every time. Another point when entering data into the database, you must use the statment REPLACE INTO and not INSERT INTO. repetition of primary keys is possible, at change of records they simply change and at insert into the error will jump out.