How to insert data to database with subquery using PDO in PHP?

Question

I'm going to insert into database id_user, date_registration and time_registration, but that not working. A problem involve id_user, but I don't know where. I hope someone help me.

public function insert_statistics()
{
    $login = $this->data['login'];
    $date = date("Y-m-d");
    $time = date('H:i:s');
     
    if(empty($this->validation->getErrors())) {
         $query = $this->database->connect()->prepare("INSERT INTO statystics
                    (`id_user`, `date_registration`, `time_registration`) 
            VALUES((select id from users 
                    where login = :login
                    ),
                :date_registration, :time_registration)");

        $query->bindParam(':login', $login, PDO::PARAM_INT); 
        $query->bindParam(':date_registration', $date, PDO::PARAM_STR); 
        $query->bindParam(':time_registration', $time, PDO::PARAM_STR);  
        $query->execute();
         
    }
 
}

_but that not working_ Is not really helpful. Please show us any error messages — RiggsFolly, Feb 12 '21 at 17:13
The program shows no errors. This function simply does not insert data into the table — Matthew, Feb 12 '21 at 17:19

ScaisEdge · Answer 1 · 2021-02-12T19:12:13.563

0

for an insert select You should use this syntax

"INSERT INTO statystics (`id_user`, `date_registration`, `time_registration`) 
 select id,   :date_registration, :time_registration
 from users 
 where login = :login"

NB don't use

select id from users where login = :login, :date_registration, :time_registration    <---this i wrong

but use

 select id,   :date_registration, :time_registration
 from users 
 where login = :login

as is

if(empty($this->validation->getErrors())) {
     $query = $this->database->connect()->prepare(
           "INSERT INTO statystics (`id_user`, `date_registration`, `time_registration`) 
            select id,   :date_registration, :time_registration
            from users 
         where login = :login");

    $query->bindParam(':login', $login, PDO::PARAM_INT); 
    $query->bindParam(':date_registration', $date, PDO::PARAM_STR); 
    $query->bindParam(':time_registration', $time, PDO::PARAM_STR);  
    $query->execute();

edited Feb 12 '21 at 19:12

answered Feb 12 '21 at 17:13

ScaisEdge

131,976
10
91
107

I don't understand this query – Matthew Feb 12 '21 at 17:32
answer updated .. removing wrong 'elec' ..let me know – ScaisEdge Feb 12 '21 at 17:33
That not working now. – Matthew Feb 12 '21 at 17:43
show me the exact error message – ScaisEdge Feb 12 '21 at 17:45
Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' '2021-02-12', '18:41:05'' at line 1 in C:\xampp\htdocs\messbox\klasy\insert.php:78 Stack trace: #0 C:\xampp\htdocs\messbox\klasy\insert.php(78): PDOStatement->execute() #1 C:\xampp\htdocs\messbox\index.php(101): Insert->insert_statistics() #2 {main} thrown in C:\xampp\htdocs\messbox\klasy\insert.php on line 78 – Matthew Feb 12 '21 at 17:47
show me the exact code you are really using .. – ScaisEdge Feb 12 '21 at 17:50
`$query = $this->database->connect()->prepare("INSERT INTO statystics(`id_user`, `date_registration`, `time_registration`) select id from users where login = :login, :date_registration, :time_registration"); $query->bindParam(':login',$field, PDO::PARAM_INT); $query->bindParam(':date_registration', $field20, PDO::PARAM_STR); $query->bindParam(':time_registration', $field100, PDO::PARAM_STR); $query->execute();` – Matthew Feb 12 '21 at 17:58
yiu are not using my code you are using a wrong code .. try use my code as is .. just change you sql string with mine – ScaisEdge Feb 12 '21 at 17:58
A delete my code above and I add your code `INSERT INTO statystics (`id_user`, `date_registration`, `time_registration`) select id, from users where login = :login :date_registration, :time_registration`. – Matthew Feb 12 '21 at 18:04
.. you don't have to add the param after the where ..is worng .. use my code as is answer clearly updated – ScaisEdge Feb 12 '21 at 18:05
But should I remove bindParam and execute ? – Matthew Feb 12 '21 at 18:09
answer update with full code -- – ScaisEdge Feb 12 '21 at 19:12

How to insert data to database with subquery using PDO in PHP?

1 Answers1