Data will not enter database

Question

For some reason $query3 and $query4 will throw out this error

Couldn't enter data: 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 'WHERE job_id = '35' at line 1

I cannot see why it is doing this the query syntax seems fine.

Table structure: https://i.stack.imgur.com/kEBgO.jpg

Actionpage7:

session_start();
require 'config.php';


$id = $_SESSION['login_user'];

$bidid = $_POST['bid_id'];
$jobid = $_POST['job_id'];
$bidder_id = $_POST['bidder_id'];
$bid_amount = $_POST['bid_amount'];

$query = " UPDATE bid SET status = '1' WHERE  bid_id = '$bidid'";
$success = $conn->query($query);

$query2 = " UPDATE job SET accepted = '1' WHERE  job_id = '$jobid'";
$success = $conn->query($query2);

$query3 = "INSERT into job (accepted_bidder) VALUES('" . $bidder_id . "') WHERE  job_id = '$jobid'";
$success = $conn->query($query3);

$query4 = "INSERT into job (accepted_bid) VALUES('" . $bid_amount . "') WHERE job_id = '$jobid'";
$success = $conn->query($query4);

if(!$success) {
    die("Couldn't enter data: " . $conn->error);
}

echo "Thank You For Contacting Us <br>";
header("location: myjobs.php");
$conn->close();

[Little Bobby](http://bobby-tables.com/) says **[you may be at risk for SQL Injection Attacks](https://stackoverflow.com/q/60174/)**. Learn about [Prepared Statements](https://en.wikipedia.org/wiki/Prepared_statement) with [parameterized queries](https://stackoverflow.com/a/4712113/5827005). I recommend `PDO`, which I [wrote a class for](https://github.com/GrumpyCrouton/GrumpyPDO) to make it extremely easy, clean, and more secure than using non-parameterized queries. Also, [This article](https://phpdelusions.net/pdo/mysqli_comparison) may help you choose between `MySQLi` and `PDO` — GrumpyCrouton, Apr 18 '18 at 20:44
You see an error, it is __obvious__ that syntax __is not fine__. — u_mulder, Apr 18 '18 at 20:44
If it was fine, it wouldn't be throwing an error. Hint: `WHERE` clauses aren't valid on `INSERT` (https://dev.mysql.com/doc/refman/5.7/en/insert.html) — jmoerdyk, Apr 18 '18 at 20:45
But it is though that's the problem I have used queries exactly like this and they work this is why I am here asking. — James Wood, Apr 18 '18 at 20:45

score 3 · Accepted Answer · answered Apr 18 '18 at 20:45

3

You can do it in one query:

UPDATE job SET 
    accepted = '1',
    accepted_bidder = 'value',
    accepted_bid = 'value'
WHERE job_id = '$jobid'

As stated in comments - your code is vulnerable to SQL injections. Refer to this topic to know more.

answered Apr 18 '18 at 20:45

u_mulder

54,101
5
48
64

1

@JamesWood Because `UPDATE` is for an existing record and `INSERT` is to create a new record. – chris85 Apr 18 '18 at 20:53
If answer helps - feel free to accept it, it also raises your rep and helps other people in future) – u_mulder Apr 18 '18 at 20:55

score 0 · Answer 2 · answered Apr 18 '18 at 21:34

You have two types of queries here.

Query 1 and 2 are updates

$query = " UPDATE bid SET status = '1' WHERE  bid_id = '$bidid'";
$query2 = " UPDATE job SET accepted = '1' WHERE  job_id = '$jobid'";

They say UPDATE table and SET column = value WHERE condition is true. As the name implies this updates existing rows. The condition is used to limit the rows that the update is applied to. Without it every bid would have its status set to 1 and every job would be accepted. Which is probably not good.

Query 3 and 4 are inserts

$query3 = "INSERT into job (accepted_bidder) VALUES('" . $bidder_id . "') WHERE  job_id = '$jobid'";
$query4 = "INSERT into job (accepted_bid) VALUES('" . $bid_amount . "') WHERE job_id = '$jobid'";

They say INSERT into table using (columns...) having VALUES(values...) WHERE condition. Again the name says it all, INSERT inserts new rows into the table. Now the question is what is the WHERE clause supposed to do?

Are you trying to limit the inserted rows to only those that match your condition? Well you are the one saying what rows to insert so you don't really need to do that. Are you trying to set values on the rows to be inserted? Well you can do that by adding more columns to the column list and their respective values to the value list. So it turns out there isn't really much point to a WHERE clause on an INSERT statement like that and in fact it's not allowed. That's what the error is trying to tell you.

As the other answer says you probably want to update an existing job and not insert a new one anyways.

Data will not enter database

2 Answers2