dongzhouzhang8696 2016-10-30 21:10
浏览 83
已采纳

PHP PDO无法使用SELECT运行查询INSERT INTO

I can't run INSERT INTO and SELECT queries in one statement.

Have problem with this php code:

$db = connect_db_marketlist();
if($db != null) {
    $sql = "INSERT INTO items (user_id, market_table_id, price, info )"
        ." VALUES ('$id', (SELECT table_id FROM markets WHERE city='$city' AND market='$market'), $price, '$info')";
     echo $sql; // !!! DEBUG !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    try {
        $db->query($sql);
        echo "OKAY: ".$db->lastInsertId();
    } catch (Exception $e) {
        echo "ERROR: ".$e->getMessage();
    }
}

And I got error:

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'market_table_id' cannot be null

Error says SELECT query returns null but when I run $sql statement directly in phpmyadmin, it is working.
This is echo $sql output:

INSERT INTO items (user_id, market_table_id, price, info ) VALUES ('12345678', (SELECT table_id FROM markets WHERE city='ANKARA' AND market='MİGROS'), 22.33, 'TEST_INFO_MİGROS')

What's wrong with me? Maybe it's my db connection:

function connect_db_marketlist() {
    $servername = "localhost";
    $username = "marketuserdb";
    $password = "pass1234";
    $conn = null;
    try {
        $conn = new PDO("mysql:host=$servername;dbname=marketlist", $username, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
    catch(PDOException $e)
        {
        echo "Connection failed: " . $e->getMessage();
        }   
    return $conn;
}

Is it possible to run "INSERT INTO...SELECT..." query with PDO? If yes how, if no why?

P.S: It's working when I enter any integer instead of (SELECT....) query. So no problem with DB connection.

  • 写回答

2条回答 默认 最新

  • dputlf5431 2016-10-30 21:46
    关注

    You should set connections charset to proper one in DSN like

    "mysql:host=$servername;dbname=marketlist;charset=utf8mb4"
    

    (This is for utf-8, you should set it for your tables encoding)

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥20 射频功率问题,解答者有酬谢!
  • ¥80 构建降雨和积水的预测模型
  • ¥15 #Qt Transform setTransform()在鼠标拖动移动视角是一致在原地不动,无法变换视角(细微观察似乎视图有在原地抖动),无法变换视角(细微观察似乎视图有在原地抖动)
  • ¥50 如何利用无人机拍摄的数码照片测量鸟卵的长短径
  • ¥100 github贡献者给与奖励
  • ¥15 使用DS18B20+ESP8266获取温度数据返回-127.00
  • ¥15 odbc代码新增sqlserver数据源
  • ¥15 求一个USB指纹识别的驱动
  • ¥15 wps中工作表与工作表之间怎么汇总信息?
  • ¥50 请教Windows server 2012 R2的DCOM配置问题