CODEWITHSUNDEEP
phpmysqlsql
Justin
Justin

Reputation: 131

SQL - Insert INTO results in nothing

I've been trying to get this INSERT to work correctly, so I worked through the undefined variable and index problems and now I think I am nearly there.

Below is the code:

<?php
session_start();
require "../dbconn.php";

$username = $_SESSION['username'];

$query1 = "SELECT user_table.user_id FROM user_table  WHERE user_table.username ='".$username."'";    

$query2 = "SELECT department.department_id FROM department, user_table, inventory
       WHERE user_table.user_id = department.user_id
       AND department.department_id = inventory.department_id";

//Copy the variables that the form placed in the URL
//into these three variables
$item_id = NULL;
$category = $_GET['category'];
$item_name = $_GET['item_name'];
$item_description = $_GET['item_description'];
$item_quantity = $_GET['quantity'];
$item_quality = $_GET['quality'];
$item_status = NULL;
$order_date = $_GET['order_date'];
$invoice_attachment = NULL;
$edit_url = 'Edit'; 
$ordered_by = $username;
$user_id = mysql_query($query1) or die(mysql_error());
$department_id = mysql_query($query2) or die(mysql_error());
$price = $_GET['price'];
$vat = $_GET['vat%'];
$vat_amount = $_GET['vat_amount'];
$create_date = date("D M d, Y G:i");
$change_date = NULL;

//set up the query using the values that were passed via the URL from the form
$query2 = mysql_query("INSERT INTO inventory (item_id, category, item_name, item_description, item_quantity, item_quality, item_status, order_date,
invoice_attachment, edit_url, ordered_by, user_id, department_id, price,     vat, vat_amount, create_date, change_date VALUES(
'".$item_id."',
'".$category."',
'".$item_name."',
'".$item_description."',
'".$item_quantity."',
'".$item_quality."',
'".$item_status."',
'".$order_date."',
'".$invoice_attachment."',
'".$edit_url."',
'".$ordered_by."',
'".$user_id."',
'".$department_id."',
'".$price."',
'".$vat."',
'".$vat_amount."',
'".$create_date."',
'".$change_date."')")
or die("Error: ".mysql_error());
header( 'Location:../myorders.php');
?>

Error: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES( '', 'adasd', 'dsadsa', 'dsad', 'sadsad', '' at line 2

Could anyone please let me know where I am going wrong? :(

Been staring at this for 3-5 hours already :(

Upvotes: 0

Views: 681

Answers (3)

inquam
inquam

Reputation: 12942

You are not actually trying to insert any data into your table. You only craft and assign the query in string form to a variable. You need to use the function mysql_query to actually run the code.

As pointed out you will also have to specify the columns you are inserting data into in the MySQL query if you don't supply data for every column (in the correct order). Here you can look at the MySQL insert syntax.

I would also urge you to look into using the MySQLi or the MySQL PDO extensions for communicating with your MySQL database since the MySQL extension is deprecated. Look here for additional information and comparisons.

Upvotes: 3

Cornel Raiu
Cornel Raiu

Reputation: 3005

Here, you only assign the values to the $query var:

$query = "INSERT INTO inventory VALUES (
'".$item_id."',
'".$category."',
'".$item_name."',
'".$item_description."',
'".$quantity."',
'".$quality."',
'".$item_status."',
'".$order_date."',
'".$invoice_attachment."',
'".$edit_url."',
'".$ordered_by."',
'".$price."',
'".$vat."',
'".$vat_amount."',
'".$create_date."',
'".$change_date."')"
or die("Error: ".mysql_error());

You do not actually run the query.

try:

$query = mysql_query("INSERT INTO inventory (column_name1, column_name 2, column_name3 ... the column name for each field you insert) VALUES (
    '".$item_id."',
    '".$category."',
    '".$item_name."',
    '".$item_description."',
    '".$quantity."',
    '".$quality."',
    '".$item_status."',
    '".$order_date."',
    '".$invoice_attachment."',
    '".$edit_url."',
    '".$ordered_by."',
    '".$price."',
    '".$vat."',
    '".$vat_amount."',
    '".$create_date."',
    '".$change_date."')")
    or die("Error: ".mysql_error());

Also, you should use mysqli_* or any other PDO as the mysql_* functions are deprecated

Upvotes: 1

Juan
Juan

Reputation: 3705

If you are not inserting in all columns you need to specify the columns you are going to insert. Like this:

INSERT INTO Table(Column1, Column6) VALUES (Value1, Value6)

You are missing the column names in your INSERT

Upvotes: 0

Related Questions