-4

I am trying to run a basic SQL statement, but I can't figure out what I am doing wrong. Here is the context:
I have a database named: my_database
a table named: users
Two columns: service_id which type is BIGINT
and video_id which type is varchar(20)

Here is my code: 

$video_id = "chartext";
$service_id = 12345678910;
$bdd = new PDO("mysql:host=127.0.0.1;dbname=moods_db", "user", "pass");
$query = $bdd->prepare("INSERT INTO users(video_id) VALUES (?) WHERE service_id = $service_id");
$query->execute([$service_id]);
var_dump($req->execute([$video_id]));
// which gives me false

Some info: My PHP_INT_MAX gives me 9223372036854775807

I am aware of SQL Injection and just removed it to be clearer

I var_dumped each steps and the execute one is the only one that is not working

I ran the statement in phpmyadmin console and it told me: "Syntax error near 'WHERE (service_id = '12345678910')' line 1

I also searched the usage of WHERE Clause but I did not understand if I could put it in an INSERT statement, I think I already did that and it worked, I am lost

Thanks in advance

Jaquarh
  • 6,493
  • 7
  • 34
  • 86
Narek
  • 20
  • 6
  • 3
    There is no such syntax. What is this code **supposed** to do? – Mureinik Oct 21 '18 at 16:55
  • I think you answer by your own "I also searched the usage of WHERE Clause but I did not understand if I could put it in an INSERT statement". – A l w a y s S u n n y Oct 21 '18 at 16:57
  • Did not see `$req->` when answering question, where does `$req` come from? It should be `UPDATE SET` – Jaquarh Oct 21 '18 at 16:59
  • 1
    There is no WHERE clause on an INSERT. Insert adds a new row to a table, unless it breaks some other reference rule – RiggsFolly Oct 21 '18 at 16:59
  • _Big Note_ If you are going to use a bound parameter for `video_id` why not also do that for `service_id` – RiggsFolly Oct 21 '18 at 17:02
  • @Mureinik is it supposed to insert a video id in a row that matches the service id given – Narek Oct 21 '18 at 17:10
  • As already mentioned, INSERT has no WHERE clause. What you are trying to do can only be accomplished with REPLACE INTO. This will insert a new record, if it doesn't exist, or replace the record if it exists. I suggest you try learning more about SQL before trying to do anything of importance with it, as this is very basic knowledge that you are required to have in order to not destroy any data. For starters, your WHERE clause should return 1 or 0 rows, otherwise you run the risk of overwriting all your data (i.e., service_id should be a PRIMARY KEY). – Yoshimitsu Oct 21 '18 at 17:10
  • @Jaquarh req was the french for 'requete' which means query, yes I found it after thanks – Narek Oct 21 '18 at 17:12

4 Answers4

0

PDOStatement::execute — Executes a prepared statement. It returns TRUE on success or FALSE on failure.

So for your case it is returning FALSE because In an INSERT statement you wouldn't have an existing row to do a WHERE clause.

You need to try like this way to insert one row with two values or use UPDATE query to update one row with one value based on another value on the existing row. Hope it clears your doubts now :)

$video_id = "chartext";
$service_id = 12345678910;
$bdd = new PDO("mysql:host=127.0.0.1;dbname=moods_db", "user", "pass");
$sql = "INSERT INTO users(video_id, service_id) VALUES (?,?)";
$array = array($video_id , $service_id );
$sth = $bdd->prepare($sql);
$sth->execute($array);
var_dump($sth);
A l w a y s S u n n y
  • 36,497
  • 8
  • 60
  • 103
  • Hi, thanks for answering, Im not sure I understood this example, it looks like it Inserts 2 values instead of one and not where it should be, sorry for the mess I found the answer just then, thanks ! – Narek Oct 21 '18 at 17:09
  • @Narek glad it helps you somehow. Best of luck :) – A l w a y s S u n n y Oct 21 '18 at 17:10
0

Actually you can't use WHERE Clause with INSERT TO.
Here are some answers, https://stackoverflow.com/a/11913305/7183227 https://stackoverflow.com/a/485057/7183227 from MySQL Insert Where query

Narek
  • 20
  • 6
0

The problem is with the way you have written INSERT query. INSERT query does not contain where clause.

You have to update it your query as follows:

INSERT INTO users(video_id) VALUES (?)

Ajay Tambe
  • 11
  • 3
-1

You shouldn't use WHERE clause in INSERT query. so, either perform INSERT (it does not have WHERE) or try UPDATE where you can use WHERE clause. just visit here https://phpdelusions.net/pdo_examples/insert if you want to insert the record or here https://phpdelusions.net/pdo_examples/update if you want to update and check the syntax and perform it properly. :)

Yash
  • 141
  • 1
  • 5