phpmyadmin 选择多个表时对数据库执行查询时出错

k4aesqcs  于 2022-11-09  发布在  PHP
关注(0)|答案(1)|浏览(251)

我使用的是最新版本的PHPMYADMIN和XAMPP，出现以下错误：
致命错误：未捕获的类型错误：数据库接口：：getColumns（）：参数#2（$table）必须是字符串类型，给定空值，在第187行的D：\Uni\SDS\XAMPP\phpMyAdmin\libraries\classes\Sql.php中调用，并在D：\Uni\SDS\XAMPP\phpMyAdmin\libraries\classes\DatabaseInterface中定义。php：893堆栈跟踪：（187）您可以在这里找到您想要的数据库。（1536）：在数据库界面中创建一个新的数据库，并将其添加到数据库中，然后在数据库中创建一个新的数据库。（1856年）：我的数据库中有一个名为“数据库模式”的数据库，这个数据库中有一个名为“数据库模式”的数据库，这个数据库中有一个名为“数据库模式，”（1703）：返回的数据类型为：数据类型为：数据类型为：数据类型为：数据类型为：数据类型为：数据类型为：数据类型为：数据类型为：如果您有任何问题，请联系我们。如果您有任何问题，请联系我们。如果您有任何问题，请联系我们。（187）：如果您有任何问题，请联系我们，我们将为您解答。如果您有任何问题，请联系我们。（18）在数据库中查找数据库中的数据，并将其存储在数据库中，然后将数据库中的数据写入数据库中。在第893行的D：\Uni\SDS\XAMPP\phpMyAdmin\libraries\classes\DatabaseInterface.php中抛出了一个错误，该错误可能是由于以下原因造成的：
我的SQL代码：


# Creating the database

DROP DATABASE IF EXISTS bankSchema;
CREATE DATABASE bankSchema;

USE bankSchema;

# Setting up the tables

CREATE TABLE phone_numbers(
    number_ID INT NOT NULL AUTO_INCREMENT,
    phone_number VARCHAR(12) NOT NULL UNIQUE,

    PRIMARY KEY (number_ID)
);

CREATE TABLE addresses(
    address_ID INT NOT NULL AUTO_INCREMENT,
    county VARCHAR(100) NOT NULL,
    city VARCHAR(100) NOT NULL,
    street_name VARCHAR(100) NOT NULL,
    house_number VARCHAR(10) NOT NULL,
    postcode VARCHAR(8) NOT NULL,

    PRIMARY KEY (address_ID)
);

CREATE TABLE accounts(
    account_ID INT NOT NULL AUTO_INCREMENT,
    account_number INT(8) NOT NULL UNIQUE,
    sort_code INT(6) NOT NULL,
    opening_balance DECIMAL(12,2) NOT NULL, 
    CHECK(opening_balance > 50),

    PRIMARY KEY (account_ID)
);

CREATE TABLE customers(
    customer_ID INT NOT NULL AUTO_INCREMENT,
    address_ID INT NOT NULL,
    number_ID INT NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    date_of_birth DATE NOT NULL,

    PRIMARY KEY (customer_ID),
    FOREIGN KEY (address_ID) REFERENCES addresses(address_ID),
    FOREIGN KEY (number_ID) REFERENCES phone_numbers(number_ID)
);

CREATE TABLE customer_accounts(
    account_ID INT NOT NULL,
    customer_ID INT NOT NULL,

    CONSTRAINT PrimaryKey_customer_accounts PRIMARY KEY (account_ID, customer_ID),
    FOREIGN KEY (account_ID) REFERENCES accounts(account_ID),
    FOREIGN KEY (customer_ID) REFERENCES customers(customer_ID)
);

CREATE TABLE transactions(
    transaction_ID INT NOT NULL AUTO_INCREMENT,
    account_ID INT NOT NULL,
    transaction_value DECIMAL(12,2) NOT NULL,
    transaction_type ENUM("incoming", "outgoing") NOT NULL,
    transaction_date DATETIME NOT NULL,

    PRIMARY KEY (transaction_ID),
    FOREIGN KEY (account_ID) REFERENCES accounts(account_ID)
);

CREATE TABLE loans(
    loan_ID INT NOT NULL AUTO_INCREMENT,
    account_ID INT NOT NULL,
    payment_rate DECIMAL(12,2) NOT NULL,
    loan_length INT NOT NULL,
    first_payment_date DATE NOT NULL,
    monthly_due_date INT NOT NULL,

    PRIMARY KEY (loan_ID),
    FOREIGN KEY (account_ID) REFERENCES accounts(account_ID)
);

CREATE TABLE loan_transactions(
    payment_ID INT NOT NULL AUTO_INCREMENT,
    loan_ID INT NOT NULL,
    transaction_ID INT NOT NULL,

    PRIMARY KEY (payment_ID),
    FOREIGN KEY (loan_ID) REFERENCES loans(loan_ID),
    FOREIGN KEY (transaction_ID) REFERENCES transactions(transaction_ID)
);

DELIMITER $$
CREATE PROCEDURE GetcustomerBalance(
    IN first_name varchar(64),
    IN last_name varchar(64),
    IN date_of_birth date
)
BEGIN
    SELECT accounts.accountnumber, accounts.sort_code, (accounts.opening_balance + transactions_group.net_change) as Balance
    FROM customeraccounts
    INNER JOIN customers ON customers.first_name = first_name AND customers.last_name = last_name AND customers.DOB = date_of_birth
    INNER JOIN accounts ON accounts.account_ID = customeraccounts.account_ID
    RIGHT JOIN (
        SELECT account_ID, SUM(transactions.transactions_value) as net_change
        FROM transactions
        group BY transactions.account_ID
        ) transactions_group ON transactions_group.account_ID = accounts.account_ID
    LIMIT 1;
END $$

CREATE PROCEDURE GetTotalOutstandings()
BEGIN
    SELECT SUM(accounts.opening_balance + transactions_group.net_change) AS TotalOutstandings
    FROM accounts
    RIGHT JOIN (
        SELECT account_ID, SUM(transactions.transaction_value) as net_change
        FROM transactions
        GROUP BY transactions.account_ID
        ) transactions_group ON transactions_group.account_ID = accounts.account_ID
    LIMIT 1;
END $$

DELIMITER ;

# Adding all of the data to the tables

# Adding all the phone numbers

INSERT INTO phone_numbers (phone_number) VALUES ("07726711546");
INSERT INTO phone_numbers (phone_number) VALUES ("07927715584");
INSERT INTO phone_numbers (phone_number) VALUES ("07131015372");
INSERT INTO phone_numbers (phone_number) VALUES ("07918095513");
INSERT INTO phone_numbers (phone_number) VALUES ("07847774296");
INSERT INTO phone_numbers (phone_number) VALUES ("07732710928");
INSERT INTO phone_numbers (phone_number) VALUES ("07043641210");
INSERT INTO phone_numbers (phone_number) VALUES ("07072150657");
INSERT INTO phone_numbers (phone_number) VALUES ("07781737664");
INSERT INTO phone_numbers (phone_number) VALUES ("07789935038");

# Adding all the addresses

INSERT INTO addresses (county, city, street_name, house_number, postcode) VALUES ("Kent", "Tunbridge Wells", "New Street", "92", "TN40 8BF");
INSERT INTO addresses (county, city, street_name, house_number, postcode) VALUES ("Greater London", "London", "Park Avenue", "73", "N62 3GB");
INSERT INTO addresses (county, city, street_name, house_number, postcode) VALUES ("Berkshire", "Reading", "South Street", "92", "RG79 7FN");
INSERT INTO addresses (county, city, street_name, house_number, postcode) VALUES ("Denbighshire", "Llandudno", "The Grove", "63", "LL94 1NM");
INSERT INTO addresses (county, city, street_name, house_number, postcode) VALUES ("Wiltshire", "Swindon", "South Street", "80", "SN49 7PW");
INSERT INTO addresses (county, city, street_name, house_number, postcode) VALUES ("Greater London", "Enfield", "South Street", "46", "EN69 0JM");
INSERT INTO addresses (county, city, street_name, house_number, postcode) VALUES ("Midlothian", "Edinburgh", "South Street", "92", "EH85 0FE");
INSERT INTO addresses (county, city, street_name, house_number, postcode) VALUES ("Greater London", "Romford", "South Street", "48", "RM73 4MK");
INSERT INTO addresses (county, city, street_name, house_number, postcode) VALUES ("Greater London", "London", "South Street", "15", "E13 8XR");
INSERT INTO addresses (county, city, street_name, house_number, postcode) VALUES ("Lancashire", "Preston", "South Street", "64", "PR93 2YH");

# Adding all the accounts

INSERT INTO accounts (account_number, sort_code, opening_balance) VALUES (06725303, 774009, 10000.87);
INSERT INTO accounts (account_number, sort_code, opening_balance) VALUES (52214025, 774009, 100.00);
INSERT INTO accounts (account_number, sort_code, opening_balance) VALUES (02654236, 774009, 1054.62);
INSERT INTO accounts (account_number, sort_code, opening_balance) VALUES (32951358, 774009, 5038.21);
INSERT INTO accounts (account_number, sort_code, opening_balance) VALUES (83565092, 774009, 2457.00);
INSERT INTO accounts (account_number, sort_code, opening_balance) VALUES (74914912, 774009, 1040.23);
INSERT INTO accounts (account_number, sort_code, opening_balance) VALUES (24918996, 774009, 3000.85);
INSERT INTO accounts (account_number, sort_code, opening_balance) VALUES (02897170, 774009, 2961.00);
INSERT INTO accounts (account_number, sort_code, opening_balance) VALUES (05914777, 774009, 576.44);
INSERT INTO accounts (account_number, sort_code, opening_balance) VALUES (86755716, 774009, 307.95);

INSERT INTO customers (address_ID, number_ID, first_name, last_name, date_of_birth) VALUES (5, 7, "Josey", "Dunkerly", "1976-07-02");
INSERT INTO customers (address_ID, number_ID, first_name, last_name, date_of_birth) VALUES (3, 2, "Dominic", "Semken", "1988-01-15");
INSERT INTO customers (address_ID, number_ID, first_name, last_name, date_of_birth) VALUES (2, 5, "Almeda", "Laffranconi", "2000-06-13");
INSERT INTO customers (address_ID, number_ID, first_name, last_name, date_of_birth) VALUES (1, 8, "Sondra", "Stockall", "1988-06-23");
INSERT INTO customers (address_ID, number_ID, first_name, last_name, date_of_birth) VALUES (10, 6, "Sayres", "Hought", "1978-09-21");
INSERT INTO customers (address_ID, number_ID, first_name, last_name, date_of_birth) VALUES (7, 3, "Robinet", "Anning", "1995-12-25");
INSERT INTO customers (address_ID, number_ID, first_name, last_name, date_of_birth) VALUES (6, 1, "Sansone", "Halwood", "1993-05-13");
INSERT INTO customers (address_ID, number_ID, first_name, last_name, date_of_birth) VALUES (9, 4, "James", "Nairns", "1969-03-17");
INSERT INTO customers (address_ID, number_ID, first_name, last_name, date_of_birth) VALUES (8, 9, "Lexi", "Bater", "1986-09-19");
INSERT INTO customers (address_ID, number_ID, first_name, last_name, date_of_birth) VALUES (4, 10, "Ulla", "Celiz", "1997-10-21");

INSERT INTO customer_accounts (account_ID, customer_ID) VALUES (1, 4);
INSERT INTO customer_accounts (account_ID, customer_ID) VALUES (2, 9);
INSERT INTO customer_accounts (account_ID, customer_ID) VALUES (3, 6);
INSERT INTO customer_accounts (account_ID, customer_ID) VALUES (4, 3);
INSERT INTO customer_accounts (account_ID, customer_ID) VALUES (5, 8);
INSERT INTO customer_accounts (account_ID, customer_ID) VALUES (6, 7);
INSERT INTO customer_accounts (account_ID, customer_ID) VALUES (7, 10);
INSERT INTO customer_accounts (account_ID, customer_ID) VALUES (8, 2);
INSERT INTO customer_accounts (account_ID, customer_ID) VALUES (9, 5);
INSERT INTO customer_accounts (account_ID, customer_ID) VALUES (10, 1);

INSERT INTO `transactions` (`account_ID`,`transaction_value`,`transaction_type`,`transaction_date`)
VALUES
  (6,430,"incoming","2022-01-30"),
  (6,811,"incoming","2021-12-21"),
  (9,966,"incoming","2022-02-19"),
  (9,881,"outgoing","2022-03-16"),
  (4,809,"outgoing","2021-12-09"),
  (4,87,"incoming","2021-03-29"),
  (3,793,"incoming","2022-01-25"),
  (1,300,"outgoing","2021-10-10"), #8
  (2,502,"outgoing","2021-01-26"),
  (3,565,"incoming","2021-01-14");
INSERT INTO `transactions` (`account_ID`,`transaction_value`,`transaction_type`,`transaction_date`)
VALUES
  (6,81,"incoming","2021-01-19"),
  (8,119,"outgoing","2021-08-17"),
  (9,737,"incoming","2021-12-29"),
  (1,914,"outgoing","2022-01-13"),
  (2,460,"incoming","2021-11-21"),
  (2,401,"incoming","2021-11-14"),
  (3,981,"outgoing","2021-07-16"),
  (1,305,"incoming","2021-06-27"),
  (4,952,"incoming","2021-03-25"),
  (6,631,"outgoing","2021-07-07");
INSERT INTO `transactions` (`account_ID`,`transaction_value`,`transaction_type`,`transaction_date`)
VALUES
  (5,337,"incoming","2022-02-18"),
  (5,627,"outgoing","2022-01-21"),
  (7,369,"outgoing","2021-08-20"),
  (8,159,"incoming","2021-07-11"),
  (8,828,"outgoing","2021-09-30"),
  (5,792,"outgoing","2022-03-11"),
  (7,908,"incoming","2022-02-27"),
  (4,538,"incoming","2022-03-08"),
  (4,434,"incoming","2021-04-16"),
  (4,597,"outgoing","2022-03-25");
INSERT INTO `transactions` (`account_ID`,`transaction_value`,`transaction_type`,`transaction_date`)
VALUES
  (8,517,"outgoing","2021-01-30"),
  (4,439,"outgoing","2021-09-04"),
  (7,780,"incoming","2021-01-02"),
  (5,90,"incoming","2021-02-21"),
  (7,182,"incoming","2021-11-26"),
  (10,975,"outgoing","2022-01-06"),
  (8,358,"incoming","2021-01-13"),
  (4,91,"incoming","2021-03-04"),
  (6,887,"incoming","2021-11-14"),
  (7,609,"incoming","2021-04-15");
INSERT INTO `transactions` (`account_ID`,`transaction_value`,`transaction_type`,`transaction_date`)
VALUES
  (2,213,"incoming","2021-12-02"),
  (7,280,"incoming","2021-02-17"),
  (9,366,"outgoing","2021-04-08"),
  (3,369,"outgoing","2022-03-14"),
  (5,543,"outgoing","2021-04-06"),
  (8,385,"outgoing","2021-10-26"),
  (2,471,"outgoing","2021-11-02"),
  (3,820,"outgoing","2021-06-17"),
  (8,582,"incoming","2022-01-07"),
  (3,942,"incoming","2022-02-28");
INSERT INTO `transactions` (`account_ID`,`transaction_value`,`transaction_type`,`transaction_date`)
VALUES
  (6,612,"outgoing","2021-11-22"),
  (2,802,"outgoing","2021-04-10"),
  (10,454,"incoming","2021-03-07"),
  (10,959,"outgoing","2021-01-27"),
  (7,961,"outgoing","2021-03-22"),
  (7,408,"outgoing","2021-12-20"),
  (9,150,"outgoing","2022-01-07"), # 57
  (6,378,"outgoing","2021-11-11"),
  (10,902,"outgoing","2021-07-29"),
  (6,402,"incoming","2021-07-03");
INSERT INTO `transactions` (`account_ID`,`transaction_value`,`transaction_type`,`transaction_date`)
VALUES
  (8,99,"incoming","2021-07-26"),
  (2,402,"incoming","2021-12-21"),
  (9,211,"incoming","2021-06-24"),
  (10,561,"outgoing","2022-03-25"),
  (5,779,"incoming","2021-03-30"),
  (7,277,"incoming","2021-07-08"),
  (6,786,"outgoing","2021-05-26"),
  (10,858,"incoming","2021-07-08"),
  (5,530,"incoming","2021-03-16"),
  (6,644,"incoming","2021-06-19");
INSERT INTO `transactions` (`account_ID`,`transaction_value`,`transaction_type`,`transaction_date`)
VALUES
  (1,967,"outgoing","2021-10-12"),
  (3,684,"outgoing","2022-01-31"),
  (7,108,"incoming","2021-03-20"),
  (3,133,"outgoing","2021-11-14"),
  (7,200,"outgoing","2021-11-15"), # 75
  (6,109,"outgoing","2021-06-30"),
  (7,554,"incoming","2021-06-13"),
  (2,819,"outgoing","2021-01-16"),
  (4,442,"incoming","2021-03-29"),
  (4,72,"incoming","2021-01-17");
INSERT INTO `transactions` (`account_ID`,`transaction_value`,`transaction_type`,`transaction_date`)
VALUES
  (3,486,"outgoing","2022-01-19"),
  (9,805,"incoming","2021-12-16"),
  (2,159,"incoming","2021-01-15"),
  (8,566,"incoming","2021-03-16"),
  (1,760,"incoming","2021-11-12"),
  (5,470,"outgoing","2021-10-19"), # 86
  (6,824,"incoming","2021-06-08"),
  (3,976,"incoming","2021-04-18"),
  (8,79,"incoming","2021-02-07"),
  (7,675,"outgoing","2021-05-02");
INSERT INTO `transactions` (`account_ID`,`transaction_value`,`transaction_type`,`transaction_date`)
VALUES
  (4,500,"outgoing","2021-03-05"), #91
  (2,542,"outgoing","2021-03-10"),
  (7,384,"incoming","2021-12-31"),
  (2,325,"incoming","2021-10-19"),
  (3,413,"outgoing","2021-08-06"),
  (8,73,"outgoing","2021-04-08"),
  (9,510,"incoming","2022-02-23"),
  (3,950,"outgoing","2021-05-17"),
  (6,503,"incoming","2021-01-26"),
  (9,154,"incoming","2021-08-29");

INSERT INTO loans (account_ID, payment_rate, loan_length, first_payment_date, monthly_due_date) VALUES (1, 300, 21, "2021-02-05", 5);
INSERT INTO loans (account_ID, payment_rate, loan_length, first_payment_date, monthly_due_date) VALUES (4, 500, 12, "2021-03-21", 21);
INSERT INTO loans (account_ID, payment_rate, loan_length, first_payment_date, monthly_due_date) VALUES (5, 470, 15, "2021-09-19", 19);
INSERT INTO loans (account_ID, payment_rate, loan_length, first_payment_date, monthly_due_date) VALUES (7, 200, 25, "2021-10-15", 15);
INSERT INTO loans (account_ID, payment_rate, loan_length, first_payment_date, monthly_due_date) VALUES (9, 150, 29, "2021-11-07", 7);

INSERT INTO loan_transactions (loan_ID, transaction_ID) VALUES (1, 8);
INSERT INTO loan_transactions (loan_ID, transaction_ID) VALUES (1, 57);
INSERT INTO loan_transactions (loan_ID, transaction_ID) VALUES (1, 75);
INSERT INTO loan_transactions (loan_ID, transaction_ID) VALUES (1, 86);
INSERT INTO loan_transactions (loan_ID, transaction_ID) VALUES (1, 91);

我尝试执行的查询：

SELECT accounts.account_number, customers.first_name, customers.last_name, loans.monthly_due_date
FROM customer_accounts
INNER JOIN customers ON customers.customer_ID = customer_accounts.customer_ID
INNER JOIN accounts ON accounts.account_ID = customer_accounts.account_ID
INNER JOIN loans ON loans.account_ID = accounts.account_ID
WHERE loans.monthly_due_date <=7

我试过重新安装，使用不同的版本，我禁用了UAC，我在另一台机器上测试了我的代码，它工作，但在我的PC上不工作，发生在其他数据库，我试图查询多个表。我试过删除该高速缓存和使用不同的浏览器。

phpmyadmin

1条答案

按热度按时间
6tdlim6h

6tdlim6h1#

这看起来像是phpmyadmin中的一个bug：https://github.com/phpmyadmin/phpmyadmin/issues/17001
这个里程碑是5.1.2，它得到纠正，这是11天前发布的（截至本答案）.尝试phpmyadmin 5.1.2，我相信它应该得到纠正.

赞(0）分享  回复(0）举报  2022-11-09
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com