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

How to refresh a parent page from a modal popup
Hi,I have a modal popup in which I need to upload a file and store in the database should give a mes

rdns with php
I am trying to build a script that will show all of the different domains that are hosted on the sam

Array becomes unset by itself?
Hi,

I have a bit of experience with PHP but not extensive. It seems I cannot store any items

PL/SQL: ORA-00947: not enough values error message
Hi all i am getting Error(25,63): PL/SQL: ORA-00947: not enough values error message when executing

Custom list order
Hi there,

I have checked this tutorial and it's great till the point where I want to display

Remove letter from numeric textbox
I have a textbox that will search the employee database by entering in the employee ID and it will r

Thought I has this figured out especially after all the help i received?
Cags, Salathe, Daniel helped me with this yesterday, but not sure i took it all in.
I am attempti

Why is my row count 0?
Here's the MySQL query i'm running. It basically pulls data from 2 tables based on some data passed.

generate seo frienly url from a text like wordpress
Convert: "åäö" to "aao", "space" to "-", "!?&q

Help adding strtolower( to my in_array
strtolower i need to add it to this to work, iuno how.

Code: $moderators = array('Admin', 'ba