Php - error in your SQL syntax in prestashop module

Erfanhossinezadeh
May 3, 2023
265 views
0 votes
2 Answers

When I run the sql code in the installation method, it shows me a syntax error.

my sql code :

CREATE TABLE PREFIX_wallet (
    `id` INT NOT NULL,
    `id_customer` INT NOT NULL,
    `cash` INT DEFAULT 0,
    `data_add` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `data_upd` DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`id_customer`) REFERENCES `PREFIX_customer`(`id_customer`) 
);

CREATE TABLE PREFIX_wallet_history (
    `id` INT NOT NULL,
    `id_customer` INT NOT NULL,
    `id_wallet` INT NOT NULL,
    `last_amount` INT NOT NULL,
    `Summary` TEXT(255) NOT NULL,
    `data_add` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `data_upd` DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`id_customer`) REFERENCES `PREFIX_customer`(`id_customer`),
    FOREIGN KEY (`id_wallet`) REFERENCES `PREFIX_wallet`(`id`) 
);

The prefix part is replaced with a DB_PREFIX code.

error :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE ps_wallet_history (
id INT NOT NULL,
id_customer INT NOT NU' at line 11<br /><br /><pre>CREATE TABLE ps_wallet (
id INT NOT NULL,
id_customer INT NOT NULL,
cash INT DEFAULT 0,
data_add DATETIME DEFAULT CURRENT_TIMESTAMP,
data_upd DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY (id_customer) REFERENCES ps_customer(id_customer)
);

CREATE TABLE ps_wallet_history (
id INT NOT NULL,
id_customer INT NOT NULL,
id_wallet INT NOT NULL,
last_amount INT NOT NULL,
Summary TEXT(255) NOT NULL,
data_add DATETIME DEFAULT CURRENT_TIMESTAMP,
data_upd DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY (id_customer) REFERENCES ps_customer(id_customer),
FOREIGN KEY (id_wallet) REFERENCES ps_wallet(id)
);</pre>

I took all ` for the exam, but there was no result.
Thanks for the help.

Answers

Chosen as BEST ANSWER

Thanks for the help It worked correctly when I ran it individually.

        if (!$this->loadSqlFile('install.sql'))
        return false;
    
    if (!$this->loadSqlFile('historyTable.sql'))
        return false;

install.sql file :

CREATE TABLE PREFIX_wallet (
id INT NOT NULL AUTO_INCREMENT,
id_customer INT NOT NULL,
cash INT DEFAULT 0,
data_add DATETIME DEFAULT CURRENT_TIMESTAMP,
data_upd DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)

) ENGINE = INNODB COLLATE utf8_general_ci;

historyTable File :

CREATE TABLE PREFIX_wallet_history (
id INT NOT NULL AUTO_INCREMENT,
id_wallet INT NOT NULL,
last_amount INT NOT NULL,
summary TEXT(255) NOT NULL,
data_add DATETIME DEFAULT CURRENT_TIMESTAMP,
data_upd DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)

) ENGINE = INNODB COLLATE utf8_general_ci;

(Edit)

The ps_customer table must be created first, use this code and see if it solves your problem:

CREATE TABLE ps_customer (
id_customer INT NOT NULL,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
PRIMARY KEY (id_customer)
);

CREATE TABLE ps_wallet (
id INT NOT NULL,
id_customer INT NOT NULL,
cash INT DEFAULT 0,
data_add DATETIME DEFAULT CURRENT_TIMESTAMP,
data_upd DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY (id_customer) REFERENCES ps_customer(id_customer)
);

CREATE TABLE ps_wallet_history (
id INT NOT NULL,
id_customer INT NOT NULL,
id_wallet INT NOT NULL,
last_amount INT NOT NULL,
Summary TEXT(255) NOT NULL,
data_add DATETIME DEFAULT CURRENT_TIMESTAMP,
data_upd DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY (id_customer) REFERENCES ps_customer(id_customer),
FOREIGN KEY (id_wallet) REFERENCES ps_wallet(id)
);

Please signup or login to give your own answer.

Click here to cancel reply.

Php – error in your SQL syntax in prestashop module

Answers