PHP PDO can't run query INSERT INTO with SELECT

Question

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.

could you please share output of echo $sql also? That could help us to understand your situation. — tanaydin
– tanaydin, Commented Oct 30, 2016 at 21:14
Yes, you can run insert ... select ... in pdo. You run it the same way as any other sql statement. — Shadow
– Shadow, Commented Oct 30, 2016 at 21:14
also I see that you are using special Turkish characters. Did you try to add encoding to DSN like (if your tables are in UTF-8)? ("mysql:host=$servername;dbname=marketlist;charset=utf8mb4" — tanaydin
– tanaydin, Commented Oct 30, 2016 at 21:16
@tanaydin, yes i have no problem with Turkish char in mysql. — Kuvalya
– Kuvalya, Commented Oct 30, 2016 at 21:20

tanaydin · Accepted Answer · 2016-10-30 21:46:30Z

1

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)

answered Oct 30, 2016 at 21:46

tanaydin

5,32632 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kuvalya Over a year ago

Thx. You save my hours :)

ScaisEdge · Accepted Answer · 2016-10-30 21:25:27Z

0

the var should be inside the select

This way

"INSERT INTO items (user_id, market_table_id, price, info )"
." SELECT '$id', table_id , $price, '$info' 
         FROM markets WHERE city= '$city' AND market='$market' ;";

edited Oct 30, 2016 at 21:25

answered Oct 30, 2016 at 21:15

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

2 Comments

Kuvalya Over a year ago

I want to get only market_table_id with (SELECT...) statement, not price, info, user.

ScaisEdge Over a year ago

Why not .... if you use this select you populate the insert column directly ... in the select you select the content of the var $id , $price, $info .. this value are not selected from the table .. but passed ..(injected ) inside the sql statement ..

Collectives™ on Stack Overflow

PHP PDO can't run query INSERT INTO with SELECT

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related