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

[marksie1988]

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

<?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?

[redarrow]

Have you got the latest version of php over 5, to do this?

[marksie1988]

Hi I have PHP Version 5.2.11

[redarrow]

how can the current code connect to all them query look for the correct syntax to use multiple query at once?

 

 

 

or are all the query names suppose to be the same.

[marksie1988]

this is MSSQL not MYSQL it works differently and allows you to do it like this but php doesnt seem to like this?

[marksie1988]

i am fairly sure that the query is wrong:

 

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)

 

i think that it needs to be something along the lines of:

 

INSERT INTO products SELECT * FROM tempproduct WHERE manf_part_no NOT IN (SELECT manf_part_no FROM products) AND supp_id = (SELECT supp_id FROM products)

 

unfortunatly im not sure how to do a command like this could someone help and i think that may fix the error i get with the script

[ourfriendbernard]

Hi,

 

I have had similar problems in the past and solved them by using mssql_free_result() on previous queries in the script.

I have also used unset() on the query variables themselves.

 

Hope this helps someone