php - 无法在 PHP 中调用 MYSQL 过程

Question

当我连接到我的数据库并创建一个新表时，我有一个过程用数据填充该表。该表创建良好，但未调用该过程，因此未填充该表。当我运行 SQL 查询时，它工作正常，我不知道我做错了什么。

我的 SQL 代码来自:https://gist.github.com/bryhal/4129042

这是我的 PHP 代码:

     mysqli_select_db($con,"db_name");
    mysqli_query($con, "CREATE TABLE test123 (
    id                      INTEGER PRIMARY KEY,
    db_date                 DATE NOT NULL,
    year                    INTEGER NOT NULL,
    month                   INTEGER NOT NULL,
    day                     INTEGER NOT NULL,
    quarter                 INTEGER NOT NULL,
    week                    INTEGER NOT NULL,
    day_name                VARCHAR(9) NOT NULL,
    month_name              VARCHAR(9) NOT NULL,
    day_type                VARCHAR(8) DEFAULT 'In' CHECK (day_type in ('In', 'Off', 'Training', 'Holiday')),
    weekend_flag            CHAR(1) DEFAULT 'f' CHECK (weekday_flag in ('t', 'f')),
    event                   VARCHAR(50), UNIQUE td_ymd_idx (year,month,day), UNIQUE td_dbdate_idx (db_date) )Engine=MyISAM;");

    mysqli_query($con, " TRUNCATE TABLE test123;");



    mysqli_query($con, " CALL fill_date_dimension('2018-01-01','2020-01-01')" ) or die("Query fail: " . mysqli_error() );
    mysqli_query($con, " OPTIMIZE TABLE test123");

    mysqli_close($con);

令人困惑的是我以前可以使用它，但我一定忽略了一些东西。

谢谢

Answer 1

fill_date_dimension(日期1,日期2);没有在任何地方定义。在调用之前先定义它。

mysqli_query($con, "DROP PROCEDURE IF EXISTS fill_date_dimension;
    DELIMITER //
    CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE)
    BEGIN
        DECLARE currentdate DATE;
        SET currentdate = startdate;
        WHILE currentdate < stopdate DO
            INSERT INTO time_dimension VALUES (
                            YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
                            currentdate,
                            YEAR(currentdate),
                            MONTH(currentdate),
                            DAY(currentdate),
                            QUARTER(currentdate),
                            WEEKOFYEAR(currentdate),
                            DATE_FORMAT(currentdate,'%W'),
                            DATE_FORMAT(currentdate,'%M'),
                            'f',
                            CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END,
                            NULL);
            SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
        END WHILE;
    END
    //
    DELIMITER ;");