Kuvalya Kuvalya - 1 month ago 8
MySQL Question

PHP PDO can't run query INSERT INTO with SELECT

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 marketler 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.

Answer

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)