I'm sure someone with more knowledge than me can help...
I had a query that was comparing my products on the shop sql database to the website mysql database and updating it.
It had to be converted from php mysql to sqlsrv as my host stopped supporting it.
I tried to convert but unfortunately I have broken the query and it has stopped updating the website mysql database.
Can anyone help me troubleshoot or see any glaring mistakes? I would ideally like to see where it is failing as the data is correct on the sql database but not being updated on the mysql database.
Any help appreciated :-)
The original code using php mysql and mysql
<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');
$Server = "XXXXXX";
$User = "XXXXXX";
$Pass = "XXXXXX";
$DB = "XXXXXX";
//connection to the database
$dbhandle = mssql_connect($Server, $User, $Pass)
or die("Couldn't connect to SQL Server on $Server. Error: " . mssql_get_last_message());
//select a database to work with
$selected = mssql_select_db($DB, $dbhandle)
or die("Couldn't connect to SQL Server on $Server. Error: " . mssql_get_last_message());
//declare the SQL statement that will query the database
$query = "select I.ID, I.Code Code, H.OnHand / coalesce(StU.UnitSize, 1) OnHand
";
$query .= "from Products I
";
$query .= "left join ProductStockLocations St on (St.ProductID = I.ID)
";
$query .= "left join ProductPOSLocations L on (L.ProductID = I.ID)
";
$query .= "left join ProductUnits StU on (StU.ID = St.StockUnitID)
";
$query .= "left join ProductStockOnHandItems H on (H.ProductID = I.ID and H.StockLocationID = St.StockLocationID)
";
$query .= "where I.ID > 0
";
$query .= "and St.StockLocationID = 1
";
$query .= "and L.POSLocationID = 1
";
$query .= "and L.SoldAtLocation = 1
";
// execute the SQL query and return a result set
// mssql_query() actually returns a resource
// that you must iterate with (e.g.) mssql_fetch_array()
$mssqlResult = mssql_query($query);
if($mssqlResult === FALSE) {
die(mysql_error()); // TODO: better error handling
}
// connect to the MySQL database
mysql_connect("XXXXXX", "XXXXXX", "XXXXXX") or die(mysql_error());
mysql_select_db("XXXXXX") or die(mysql_error());
while ( $mssqlRow = mssql_fetch_array($mssqlResult) ) {
$mssqlCode = $mssqlRow['Code'];
$mssqlOnHand = $mssqlRow['OnHand'];
mysql_query(
"UPDATE product_option_relation SET stock = $mssqlOnHand WHERE sku = '$mssqlCode'"
// extra quotes may be required around $mssqlCode depending on the column type
);
mysql_query(
"UPDATE product SET quantity = $mssqlOnHand WHERE sku = '$mssqlCode' AND has_option = '0' ");
}
//close the connection
mssql_close($dbhandle);
// Update product total with the total quantities of the product options
$result = mysql_query("UPDATE product a INNER JOIN (SELECT product_id, SUM(stock) AS summedqty FROM product_option_relation GROUP BY product_id) b ON b.product_id = a.product_ID SET a.quantity = summedqty")
or die(mysql_error());
?>
The new code using sqlsrv and mysqli
<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');
$Server = "XXXXXX";
$User = "XXXXXX";
$Pass = "XXXXXX";
$DB = "XXXXXX";
//Connection to the database
$connectionInfo = array( "Database"=>$DB, "UID"=>$User, "PWD"=>$Pass);
@$conn = sqlsrv_connect( $Server, $connectionInfo);
if( $conn === false) {
echo "Connection could not be established.<br />";
die( print_r( sqlsrv_errors(), true));
}
//declare the SQL statement that will query the database
$query = "select I.ID, I.Code Code, H.OnHand / coalesce(StU.UnitSize, 1) OnHand
";
$query .= "from Products I
";
$query .= "left join ProductStockLocations St on (St.ProductID = I.ID)
";
$query .= "left join ProductPOSLocations L on (L.ProductID = I.ID)
";
$query .= "left join ProductUnits StU on (StU.ID = St.StockUnitID)
";
$query .= "left join ProductStockOnHandItems H on (H.ProductID = I.ID and H.StockLocationID = St.StockLocationID)
";
$query .= "where I.ID > 0
";
$query .= "and St.StockLocationID = 1
";
$query .= "and L.POSLocationID = 1
";
$query .= "and L.SoldAtLocation = 1
";
// execute the SQL query and return a result set
// sqlsrv_query() actually returns a resource
// that you must iterate with (e.g.) sqlsrv_fetch_array()
@$mssqlResult = sqlsrv_query( $conn, $sql);
if( $mssqlResult === false) {
echo "No result resource after MSSQL query.<br />";
die( print_r( sqlsrv_errors(), true) );
}
// connect to the MySQL database
@$db = mysqli_connect("XXXXXX", "XXXXXX", "XXXXXX", "XXXXXX");
if (mysqli_connect_error()) {
echo "Error: Unable to connect to MySQL.<br />";
exit;
}
while( $mssqlRow = sqlsrv_fetch_array( $mssqlResult, SQLSRV_FETCH_ASSOC) ) {
$mssqlCode = $mssqlRow['Code'];
$mssqlOnHand = $mssqlRow['OnHand'];
$mysqlQuery = "UPDATE product_option_relation SET stock = ".$mssqlOnHand." WHERE sku = '".$mssqlCode."'";
mysqli_query($db, $mysqlQuery);
$mysqlQuery = "UPDATE product SET quantity = ".$mssqlOnHand." WHERE sku = '".$mssqlCode."' AND has_option = '0' ";
mysqli_query($db, $mysqlQuery);
}
//close the MSSRV connections
sqlsrv_free_stmt($mssqlResult);
sqlsrv_close($conn);
// Update product total with the total quantities of the product options
$mysqlQuery = "UPDATE product a INNER JOIN (SELECT product_id, SUM(stock) AS summedqty FROM product_option_relation GROUP BY product_id) b ON b.product_id = a.product_ID SET a.quantity = summedqty";
@$result = mysqli_query($db, $mysqlQuery);
if(!$result){
echo "No result resource after MySQL query.<br />";
}
//Close MySQL connection
//mysqli_free_result($result);
mysqli_close($db);
?>
