Query failed issue with php script but works fine in mssql manager!

Posted on 16th Feb 2014 07:03 pm by admin

hi i have the script below which copies data from one table to another but will only insert new data update current data or delete old data from tempproducts to products then it will delete the tempproducts from the db however i keep getting this error:

Warning: mssql_query() [function.mssql-query]: Query failed in E:UpdateProducts.php on line 33

updateproducts.php
Code: <?php
include('../../otherscripts/functions.php');
$log = new Logging();

// create DB connection
$host = "localhost";
$user = "user";
$pass = "pass";
$mydb = "db";

$db = mssql_connect($host,$user,$pass);

//Select Database
mssql_select_db($mydb);


// delete all old data
$sql0 = "SELECT * FROM tempproduct";
$sql1 = "INSERT INTO products SELECT * FROM tempproduct WHERE manf_part_no NOT IN (SELECT manf_part_no FROM products) AND supp_id NOT IN (SELECT supp_id FROM products)";
$sql2 = "DELETE FROM products WHERE manf_part_no NOT IN (SELECT manf_part_no FROM tempproduct) AND supp_id NOT IN (SELECT supp_id FROM tempproduct)";
$sql3 = "UPDATE p1 SET p1.avail_qty = t1.avail_qty, p1.cost_price = t1.cost_price, p1.rrp = t1.rrp, p1.date_added = t1.date_added, p1.description = t1.description FROM Products p1 INNER JOIN tempproduct t1 ON (p1.manf_part_no = t1.manf_part_no AND p1.supp_id = t1.supp_id)";
$sql4 = "TRUNCATE TABLE tempproduct";

//If tempproduct is empty done Execute Commands if it is full then execute commands

$query = mssql_query($sql0) or die($log->lwrite('Failed to select for count from db'));
$rowcount = mssql_num_rows($query);

if($rowcount == 0){
$log->lwrite('Teh tempproduct am emptyish');
}
else{
mssql_query($sql1) or die($log->lwrite('Failed to insert to db'.$sql1));
mssql_query($sql2) or die($log->lwrite('Failed to Delete from db'));
mssql_query($sql3) or die($log->lwrite('Failed to Update db'));
mssql_query($sql4) or die ($log->lwrite('Failed to TRUNCATE db'));
}
?>

if i run $sql1 command in the sql manager it runs fine and no errors occur?

Your Answer:

Login to answer

Other forums

Socket Server Response Headers
Earlier I had a post about my Socket Server, I wasn't able to get it to connect, now I can

I

How to display random record from table?
I have the following code:

Code: <?php
$display_block .= "<input type=

PHP submit form script causing blank page
Im trying to submit a form using this script, however nothing shows up on the page, does anyone know

Sequre login with cookies.
I want to build secure login with cookies. I just want your ideas about this, your suggestions. What

How to limit the calls to an API
Hello, in my simple script I call an api which effectively involves me getting an xml file.

H

Beginner PHP code help
Hi I'm new to php but if someone could please read the question below and help it would be great.

Print out contents of to Excel
I have got this script that gathers all the data that I need but I need it to send it to excel inste

Multiple while loops
I have several DB queries that I know should be returning results and aren't. I have a feeling it ha

mysql timestamp manipulation
How could I use a timestamp (e.g 2009-10-30 13:20:35 ), and with php find out if it is:

from

How to pass parameter to tag query?
Hi,
I want to get the value of a tag at a specific time (like 10/27/09 15:29:59), I need to giv