MYSQL) CREATE UNIQUE INDEX works in phpmyadmin but not in php script

Question

I have a really simple SQL script

$addQuery = "CREATE TABLE IF NOT EXISTS WORDLIST(
        english VARCHAR(20),
        korean VARCHAR(20),
        swedish VARCHAR(20),
        synonyms TEXT
    );CREATE UNIQUE INDEX englishIndex ON WORDLIST(english);";

This query works in phpmyadmin

but when I do the same thing just in php script

database is empty.

BUT! If I remove CREATE UNIQUE query in php and do just CREATE TABLE like this,

    $addQuery = "CREATE TABLE IF NOT EXISTS WORDLIST(
        english VARCHAR(20),
        korean VARCHAR(20),
        swedish VARCHAR(20),
        synonyms TEXT
    );
    ";

php works, I just can't understand why. What's wrong with my code?

here is my full php code

<?php 
header('Access-Control-Allow-Origin: http://localhost:3000/');
$addValue = $_GET['Add'];
if (!preg_match('/[^A-Za-z]+/', $addValue)&&!preg_match('/\s/', $addValue))
 {
include 'db/db_connection.php';
$conn = mysqli_connect('localhost','root','','worddb');
if(connectDB($conn)){
    $addQuery = "CREATE TABLE IF NOT EXISTS WORDLIST(
        english VARCHAR(20),
        korean VARCHAR(20),
        swedish VARCHAR(20),
        synonyms TEXT
    );CREATE UNIQUE INDEX englishIndex ON WORDLIST(english);
    ";
    if (mysqli_query($conn, $addQuery)) {
        echo "Table WORDLIST created successfully";
      }
      echo $addQuery;
      mysqli_close($conn);
  }
 }else{
 echo "search again";
 }
?>

connectDB function is from db_connection.php

<?php 
function connectDB($conn){
    $connectStatus = null;
    if (!$conn) {
        $connectStatus = false;
        die("Connection failed: " . $conn->connect_error);
    }else{
        echo "Connected successfully";
        $connectStatus = true;
    }
    return $connectStatus;
}
?>

Have you tried to run 2 separate queries? One `CREATE TABLE` and one `CREATE UNIQUE INDEX...` query — B001ᛦ, Jul 09 '20 at 17:09
@B001ᛦ, Wait what the heck? I did exactly as you said, $addQuery = CREATE TABLE .... mysqli_query for $addQuery then $addQuery2=CREATE INDEX bla blah mysqli_query for $addQuery2 ... and worked... I don't know any technical difference.... — game lover, Jul 09 '20 at 17:16
Adding a unique key during create table is simply `, UNIQUE KEY englishIndex (english)` after the other fields. — IncredibleHat, Jul 09 '20 at 17:38
Does this answer your question? [How to execute two mysql queries as one in PHP/MYSQL?](https://stackoverflow.com/questions/802437/how-to-execute-two-mysql-queries-as-one-in-php-mysql) (See the last part of the accepted answer) — user3783243, Jul 13 '20 at 18:31

score -1 · Answer 1 · answered Jul 13 '20 at 18:26

-1

You can't combine multiple statements in a single mysqli query. phpmyadmin is doing the work of parsing them out into separate queries for you. Issue your two statements separately, or embed the index creation in the CREATE TABLE.

answered Jul 13 '20 at 18:26

chaos

122,029
33
303
309

MYSQL) CREATE UNIQUE INDEX works in phpmyadmin but not in php script

1 Answers1