无法在php中调用mysql过程

bxjv4tth 于 2021-06-24 发布在 Mysql

关注(0)|答案(1)|浏览(254)

当我连接到我的数据库并创建一个新表时，我有一个用数据填充表的过程。表创建得很好，但是没有调用过程，因此表没有填充。当我运行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);

让人困惑的是，我以前一直在工作，但我一定忽略了什么。
谢谢

mysql php

来源：https://stackoverflow.com/questions/51095817/cannot-invoke-mysql-procedure-within-php

1条答案

按热度按时间

mzsu5hc01#

填写日期维度（日期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 ;");

赞(0）回复(0）举报 2021-06-24

我来回答

无法在php中调用mysql过程

1条答案

相关问题

热门标签

最新问答