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

Parse error: syntax error, unexpected T_ELSEIF in /home/......html/item.php on l
I do not know what is wrong with this. Hope some one can help. I do nto want to post the entire site

RTF fomatting to email content
Im trying to sen an email with content is picked up from a rtf-file (file_get_contents('*.rtf'). Mai

php/mysql problem
Hey all,

Problem: im trying to setup a shop where people can use a drop-down list to select t

Points for Wiki contribution
Hi everyone,

I want to know how long does it take for the moderator to award points for a

Comment Mod System Effects all rows...
Sorry if its confusing but here is whats going on: I have a table in a database called comments and

$_FILES array
Ok,

I just started experimenting with PHP today. My goal right now is to have people be able

ctype() validation - allowing illegal characters
Hello,
I use ctype() to filter and validate a user form. However, I am trying to allow certain c

Setting condidtions for an input box
Hi all,

I would like to set a condition in a txt box that says the number a user enters has t

mySQL and PHP search
Hello,
I am trying to code a project and ran into a brick wall with one of my pages. I am pretty

DBCA Templates
Hi all,

I'm working on creating a template for DBCA, but can't find any documentation on the