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

Displaying pictures
i have worked my way through storing images in directory and storing the location in mySQL db.Now i

Code working in IE but not FireFox
I created a dynamic navigation list for my website based off of a table in my database. The code is

form variables from database help.
Hi all,

I would like to have a form that gives you options based on the results of an mysql q

Need help-Error istream header declaration
Hi Everyone ,

I am having issue with istream declaration. I am new to C programming.
can a

 formating when pulling data from a mysql database 
Ok so Im not to sure if this is the right thread to post in but here is my catch 22 issue.

I 
close site for maintenance 
i get a tutorial, saying the following code can put our site offline, and only the developer can vie
MS Access data into a html table 
Hi everyone, I am quite a novice at php but I have created some helpful scripts that fetch data
f
Simple email form - Newbie  
Hello all,

First time poster!

I've been asked to create a simple HTML form that submit
extending tidy 
I have problem with type-hinting and extending tidy. This code creates error:
Code: class cMyTidy
While Problem  
i am having a problem with a while statement here is the code 
Code: [Select]<?php
sess