Insert Data into MSSQL DB using PHP

Question

Hello there am trying to insert data into MSSQL using PHP. I have tried many times to figure out what the problem might be but i seem not to find it. Is there something am not getting right or missing?

 <?php
//pull form fields into php variables
$no = $_POST['no'];
$name= $_POST['name'];
$date = $_POST['date'];
$leave = $_POST['leave'];
$days= $_POST['days'];
$empno= $_POST['empno'];

//connect to sql
$dbc = mssql_connect('Server-PC','user','password','database')or die('Error connecting to
      the   SQL Server database.');

 // Input into staff database
  $query = "INSERT INTO dbo.[CAGD$Leave Plan] ([No_],[Employee No_],[Employee Name],
 [Leave Name],   [Start Date],[Leave Days],Satus) VALUES   
('$no','$name','$leave','$date','days','empno')";
$r esult = mssql_query($query,$dbc)or die('Error querying MSSQL database');

//close to sql
mssql_close($dbc);

echo $name . 'Your submission has been received<br />';
echo 'If you need change this request please contact your HR Manager<br />';
echo 'Thank you <br />';
echo 'HR Manager';
?>

I get this error message: Warning: mssql_query() [function.mssql-query]: message: Invalid object name 'dbo.CAGD Plan'.
(severity 16) in C:\xampp\htdocs\CAGD\leave_request.php on line 110

Warning: mssql_query() [function.mssql-query]: Query failed in C:\xampp\htdocs  
\CAGD\leave_request.php on line 110
Error querying MSSQL database

YES Warning: mssql_query() [function.mssql-query]: message: Invalid object name 'dbo.CAGD Plan'. (severity 16) in C:\xampp\htdocs\CAGD\leave_request.php on line 110 Warning: mssql_query() [function.mssql-query]: Query failed in C:\xampp\htdocs\CAGD\leave_request.php on line 110 Error querying MSSQL database — user3315848
– user3315848, Commented Sep 3, 2014 at 19:04
Your variable is $leave, not $Leave, so if you're going for something like CAGD4 Plan, change your variable to lowercase.PHP is case sensitive. — tbddeveloper
– tbddeveloper, Commented Sep 3, 2014 at 19:06
If you can avoid putting spaces in fieldnames, you really should. Yes, you can get around it, but its so ugly. — developerwjk
– developerwjk, Commented Sep 3, 2014 at 19:10

Machavity · Accepted Answer · 2016-04-27 19:56:19Z

3

You can use SQLSRV Driver instead of MSSQL Driver and then try this

<?php 
$serverName = "serverName";
$options = array(  "UID" => "sa",  "PWD" => "Password",  "Database" => "DBname");
$conn = sqlsrv_connect($serverName, $options);

if( $conn === false )
     {
     echo "Could not connect.\n";
     die( print_r( sqlsrv_errors(), true));
     }

$no = $_POST['no'];
$name= $_POST['name'];

$query = "INSERT INTO dbo.Test
        (No_,FirstName)
        VALUES(?, ?)";
$params1 = array($no,$name);                       
$result = sqlsrv_query($conn,$query,$params1);

sqlsrv_close($conn);
?>

This is more useful, and you can learn more here:

https://msdn.microsoft.com/en-us/library/cc626305(v=sql.105).aspx

edited Apr 27, 2016 at 19:56

Machavity♦

31.8k27 gold badges97 silver badges108 bronze badges

answered Apr 27, 2016 at 19:37

Ali

4693 silver badges16 bronze badges

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

Comments

Jayakarthik Appasamy · Accepted Answer · 2014-09-03 19:16:05Z

2

First Specify your database Connection...

mssql_connect('Server-PC','user','password','database')
like -> "localhost","root","XXXX", "DBNAME"

then query like

 $query = "INSERT INTO TABLENAME  (id,name) VALUES   
('$id','$name')";
$result = mssql_query($query,$dbc)

answered Sep 3, 2014 at 19:16

Jayakarthik Appasamy

1295 silver badges15 bronze badges

Comments

Cosmin Hutanu · Accepted Answer · 2014-09-03 19:17:17Z

0

Hmm, it seems to me that you have 7 fields in the table but only 6 values submitted - you are missing the value for the first column, [No_]. Besides, the last column satus (i suppose it should be 'status') does not have de [] delimiters. The error returned tells you that the name of the table is wrong. And yes variable names are case sensitive in PHP, it should be $leave - best to exit the string and concatenate - something like "bla bla".$leave."anything here with spaces or not" .

edited Sep 3, 2014 at 19:17

answered Sep 3, 2014 at 19:12

Cosmin Hutanu

11 bronze badge

Comments

Marc B · Accepted Answer · 2014-09-03 19:19:11Z

0

Is this supposed to be a variable?

$query = "INSERT INTO dbo.[CAGD$Leave Plan] ([No_],[Employee No
                               ^^^^^^

If so, then it's apparently undefined in your code, and the generated query string will contain dbo.[CAGD Plan], and not whatever value was supposed to be in that variable. If the $ is literally in your table name, then it should be CAGD\$Leave, so that $Leave isn't treated as a variable.

answered Sep 3, 2014 at 19:19

Marc B

362k44 gold badges433 silver badges508 bronze badges

2 Comments

user3315848 Over a year ago

dbo.[CAGD$Leave Plan] is the table name

Marc B Over a year ago

then you'll have to escape the $ so that php doesn't treat $Leave as a variable.

Collectives™ on Stack Overflow

Insert Data into MSSQL DB using PHP

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related