0

I am writing a few scripts for an internal CMS for my employer it is only basis but gets rid of loads of nasty excel spreadsheets and it gives more flexibility & options.

The problem I have today is that I have a script that writes to the database based upon a userform input.

I have done this successfully for a table called customers but I am trying to create one called ncr but for some reason the script runs but no information gets input into the database.

 NCR: input type="text" name="NCR"
 Project: input type="text" name = "Project"
 Raised By: input type="text" name = "Raised"
 Date: input type="text" name = "Date"
 Pasts Affected: input type="text" name = "Parts"
 Description: input type="text" name = "Description"
 Corrective Action Taken: input type="text" name = "Action
 Components Returned to Spec: input type="text" name = "Rtn_Spec"
 Concession Applied For: input type="text" name = "Concession"
 Corrective Action Approved By: input type="text" name = "CR_By"
 Reviewed For Preventative Actions By: <input type="text" name = "Review_By"
 Preventative Actions Taken: input type="text" name = "Actions_tk"
 Preventative Actions Approved By: <input type="text" name = "Actions_Apr"
 NCR Closed Out?: input type="text" name = "Closed"
 Date Closed: input type="text" name = "Date_Clsd"
 Closed Out By: input type="text" name = "Clsd_By"

This is my script

$NCR=$_POST['NCR'];
$Project=$_POST['Project'];
$Raised=$_POST['Raised'];
$Date=$_POST['Date'];
$Parts=$_POST['Parts'];
$Descr=$_POST['Descr'];
$Action=$_POST['Action'];
$Rtn_Spec=$_POST['Rtn_Spec'];
$Concession=$_POST['Concession'];
$CR_By=$_POST['CR_By'];
$Review_By=$_POST['Review_By'];
$Actions_tk=$_POST['Actions_tk'];
$Actions_Apr=$_POST['Actions_Apr'];
$Closed=$_POST['Closed'];
$Date_Clsd=$_POST['Date_Clsd'];
$Clsd_By=$_POST['Clsd_By'];

// (database connection details go here)

mysql_query(
    "INSERT INTO 'ncr' 
     VALUES ('$NCR', '$Project', '$Raised',
             '$Date','$Parts', '$Descr', 
             '$Action', '$Rtn_Spec', '$Concession',
             '$CR_By', '$Review_By', '$Actions_tk',
             '$Actions_Apr', '$Closed', '$Date_Clsd',
             '$Clsd_By')
    ");

Print "Your information has been successfully added to the database.";

Any help would be appreciated

Improve this question
2
  • 4
    You are vulnerable to sql injection attacks and are simply ASSUMING nothing could ever go wrong with your query call. at least have $result = mysql_query(...) or die(mysql_error()). Commented Oct 16, 2014 at 19:35
  • Hi Marc B, yes I have this query call - I deleted it from my code when asking the question Commented Oct 16, 2014 at 20:05

2 Answers 2

Reset to default
2 
mysql_query("INSERT INTO 'ncr' VALUES ('$NCR', [..snip..]
                         ^^^^^

You have quoted your table name, which turns it into a string - no longer a table name. Either use backticks, or no quotes at all - ncr is not a reserved word in mysql, so there is absolutely no point in quoting it at all:

mysql_query("INSERT INTO `ncr` VALUES ('$NCR', [..snip..]
mysql_query("INSERT INTO ncr VALUES ('$NCR', [..snip..]

Both of these would be acceptable. And take note of my comment above. This should fix you immediate problem, but still leave you with a massive pile of other bigger problems.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Hi Marc, I am new to PHP so I'm picking up things as I go... I think I understand your comments, basically I've used the NCR twice. Once which is the table itself and then a column within the table is also called NCR, so if I revise the column name it should work correctly? thanks for your comments and help
no. ncr is a table/field name as far as sql is concerned. $NCR is a PHP variable, and that variable name will NEVER be seen by the database, since php will replace the variable with its value when the sql string is built and passed into the underlying sql mechanisms.
Hi Marc, thanks again for your help - I can see where I have gone wrong. This crosses one of my million problems scripting from scratch! :-) you will no doubt see loads more questions from me on this site
Hi Marc, I've checked the code and it appears to be displayed correctly. mysql_query("INSERT INTO ncr VALUES ('$NCR', '$Project', - still not working correctly?
0

First, we need to use a library other than mysql_ such as mysqli_ or pdo.

For this example, we'll use mysqli_ as it's probably a smoother transition between mysql_ to mysqli_.

$mysql = new mysqli('host', 'username', 'password', 'database');

This should look pretty familiar to you, give, that it's all the same information as your current connection, with the exception of the database, which is passed in as an argument.

Next, you have an error in your query against the 'ncr' table, which you've turned into a string. Given the fact that this is a table name, the ncr should appear with out ' encapsulation. Furthermore, the only time you should use backtick encapsulation, is it the word its self is reserved, if there are special characters, or if there are spaces.

Now let's move onto the query:

$stmt = $mysqli->prepare("INSERT INTO ncr VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

Each ? indicates the position of a variable that we must later bind against. please observe:

$stmt->bind_param('ssssssssssssssss', $NCR, $Project, $Raised,$Date,$Parts, $Descr, $Action, $Rtn_Spec, $Concession, $CR_By, $Review_By, $Actions_tk, $Actions_Apr, $Closed, $Date_Clsd, $Clsd_By);

In the above, we've identified 16 variables we wish to bind. The s stands for string, so that the engine knows how to treat the data. This must be repeated for each variable we bind, if they were integers, we would use i instead.

Now that we've got our parameters bound, we can execute our query:

$stmt->execute();

Now the data has been stored in the database.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.