Newbie needs help please

smoked1 · August 16, 2007

I am trying to update the prices on one shopping cart using the prices from another shopping carts database on another server. I am still a newbie at PHP and have been working on this all day. If anyone could help me out I would really appreciate it.

<?php 
// CSCart DB Info
$hostcs = "localhost";
$usercs = "user";
$passcs = "pass";
$dbnamecs = "cscart0";
// OSC DB Info
$hostosc = "192.168.6.2";
$userosc = "user";
$passosc = "pass";
$dbnameosc = "shop0";

// Connect to cscart
$handlecscart = mysql_connect($hostcs, $usercs, $passcs);

// Connect to osc
$handleosc = mysql_connect($hostosc, $userosc, $passosc);

// Select the cscart database
mysql_select_db($dbnamecs,$handlecscart)
or die("Couldn't connect to DB");

// Select the osc database
mysql_select_db($dbnameosc,$handleosc)
or die("Couldn't connect to DB");

// Query CSCART
$selectcs = "SELECT product_code, price FROM cscart_products, cscart_product_prices WHERE cscart_products.product_id = cscart_product_prices.product_id";
if (!$selectcs) {
   die('<p>Error performing query: ' . mysql_error() . '</p>');
}

// Query OSC
$selectosc = "SELECT products_model, products_price FROM products WHERE products_id = products_id";
if (!$selectosc) {
   die('<p>Error performing query: ' . mysql_error() . '</p>');
}

// CSCART results
$resultcs = mysql_query($selectcs,$handlecscart); 
if (!$resultcs) {
   die('<p>Error performing query: ' . mysql_error() . '</p>');
}

// OSC Results
$resultosc = mysql_query($selectosc,$handleosc);
if (!$resultosc) {
   die('<p>Error performing query: ' . mysql_error() . '</p>');
}
while ( $rowcs = mysql_fetch_array($resultcs) AND $rowosc = mysql_fetch_array($resultosc) ) {
$cspart = $rowcs['product_code'] ;
$csprice = $rowcs['price'];
$oscpart = $rowosc['products_model'];
$oscprice = $rowosc['products_price'];
echo "<p>Part $cspart is being updated to $oscprice" . "<br />";
$result = mysql_query("UPDATE cscart_product_prices SET price='12.00' WHERE cscart_products.product_code = $cspart AND cscart_products.product_id = cscart_product_prices.product_id",$handlecscart) 
or die(mysql_error());


}
?>

MadTechie · August 16, 2007

would help if you knew that the "problem" is ?

smoked1 · August 17, 2007

Sorry about that.

It stops right at the update query. It prints the first echo and then stops. It does not give me any errors and I have my php.ini file set to show errors. Here is the URL:

http://72.36.197.204/migrate.php

MadTechie · August 17, 2007

seams ok

add

echo "Done";

to the end of the code.. doe it get their ?

smoked1 · August 17, 2007

I added echo "Done"; but it does show Done in the browser.

Fadion · August 17, 2007

At the update query try adding single quotes to $cspart

WHERE cscart_products.product_code = '$cspart'

MadTechie · August 17, 2007

OK.. so whats the problem ?

what are you expecting to happen ?

smoked1 · August 17, 2007

I am trying to take the data from the price field in one data base and put it in the other database for the same product.

elkidogz · August 17, 2007

look up joins for sql statements. please ensure that you have some of the same fields

ie product number (sku) or something to relate table 1 to table 2

MadTechie · August 17, 2007

i think the problem is the logic..

do you have a UniqueID for each record ?

i'll take a guess and assume its product_code

ok the logic should be something like this.

First a word of warning it 4am here and i am about to go to sleep so please backup your database first and read the comments and make sense of the logic

<?php 
// CSCart DB Info
$hostcs = "localhost";
$usercs = "user";
$passcs = "pass";
$dbnamecs = "cscart0";
// OSC DB Info
$hostosc = "192.168.6.2";
$userosc = "user";
$passosc = "pass";
$dbnameosc = "shop0";

// Connect to cscart
$handlecscart = mysql_connect($hostcs, $usercs, $passcs);

// Connect to osc
$handleosc = mysql_connect($hostosc, $userosc, $passosc);

// Select the cscart database
mysql_select_db($dbnamecs,$handlecscart)
or die("Couldn't connect to DB");

// Select the osc database
mysql_select_db($dbnameosc,$handleosc)
or die("Couldn't connect to DB");


// Query OSC
$selectosc = "SELECT products_model, products_price FROM products ";
if (!$selectosc) {
   die('<p>Error performing query: ' . mysql_error() . '</p>');
}

//****DON'T NEED TO PULL DATE FROM HERE YET*****
// Query CSCART
//$selectcs = "SELECT product_code, price, product_id FROM cscart_products, cscart_product_prices";
// if (!$selectcs) {
//   die('<p>Error performing query: ' . mysql_error() . '</p>');
// }
// CSCART results
//$resultcs = mysql_query($selectcs,$handlecscart); 
// if (!$resultcs) {
//   die('<p>Error performing query: ' . mysql_error() . '</p>');
// }
//********************************

// OSC Results
$resultosc = mysql_query($selectosc,$handleosc);
if (!$resultosc) {
   die('<p>Error performing query: ' . mysql_error() . '</p>');
}
//*******ONLY LOOP ONE
//while ( $rowcs = mysql_fetch_array($resultcs) AND $rowosc = mysql_fetch_array($resultosc) )
while ( $rowcs = mysql_fetch_array($resultcs) )
{
$cspart = $rowcs['product_code'] ;
$csprice = $rowcs['price'];


$csPID = $rowcs['product_id'] ;
//Find the New Product price etc
$selectosc = "SELECT products_model, products_price FROM products WHERE products_id = $csPID";
$resultcs = mysql_query($selectcs,$handlecscart); 
$rowosc = mysql_fetch_array($resultosc)
$oscpart = $rowosc['products_model'];
$oscprice = $rowosc['products_price'];


echo "<p>Part $cspart is being updated to $oscprice" . "<br />";
$result = mysql_query("UPDATE cscart_product_prices SET price='$oscprice'
	WHERE cscart_products.product_id = $csPID",$handlecscart) 
	or die(mysql_error());
}
?>

smoked1 · August 17, 2007

Thanks man. There was one ";" missing and I added that in but now I am getting:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/vhosts/coastalmicrosupply.com/httpdocs/migrate_new.php on line 62

I went ahead and added the code with the additional ";" here for reference.

<?php 
// CSCart DB Info
$hostcs = "localhost";
$usercs = "user";
$passcs = "pass";
$dbnamecs = "cscart0";
// OSC DB Info
$hostosc = "192.168.6.2";
$userosc = "user";
$passosc = "pass";
$dbnameosc = "shop0";

// Connect to cscart
$handlecscart = mysql_connect($hostcs, $usercs, $passcs);

// Connect to osc
$handleosc = mysql_connect($hostosc, $userosc, $passosc);

// Select the cscart database
mysql_select_db($dbnamecs,$handlecscart)
or die("Couldn't connect to DB");

// Select the osc database
mysql_select_db($dbnameosc,$handleosc)
or die("Couldn't connect to DB");


// Query OSC
$selectosc = "SELECT products_model, products_price FROM products ";
if (!$selectosc) {
   die('<p>Error performing query: ' . mysql_error() . '</p>');
}

//****DON'T NEED TO PULL DATE FROM HERE YET*****
// Query CSCART
//$selectcs = "SELECT product_code, price, product_id FROM cscart_products, cscart_product_prices";
// if (!$selectcs) {
//   die('<p>Error performing query: ' . mysql_error() . '</p>');
// }
// CSCART results
//$resultcs = mysql_query($selectcs,$handlecscart); 
// if (!$resultcs) {
//   die('<p>Error performing query: ' . mysql_error() . '</p>');
// }
//********************************

// OSC Results
$resultosc = mysql_query($selectosc,$handleosc);
if (!$resultosc) {
   die('<p>Error performing query: ' . mysql_error() . '</p>');
}
//*******ONLY LOOP ONE
//while ( $rowcs = mysql_fetch_array($resultcs) AND $rowosc = mysql_fetch_array($resultosc) )
while ( $rowcs = mysql_fetch_array($resultcs) )
{
$cspart = $rowcs['product_code'] ;
$csprice = $rowcs['price'];


$csPID = $rowcs['product_id'] ;
//Find the New Product price etc
$selectosc = "SELECT products_model, products_price FROM products WHERE products_id = $csPID";
$resultcs = mysql_query($selectcs,$handlecscart); 
$rowosc = mysql_fetch_array($resultosc);
$oscpart = $rowosc['products_model'];
$oscprice = $rowosc['products_price'];


echo "<p>Part $cspart is being updated to $oscprice" . "<br />";
$result = mysql_query("UPDATE cscart_product_prices SET price='$oscprice'
	WHERE cscart_products.product_id = $csPID",$handlecscart) 
	or die(mysql_error());
}
?>

MadTechie · August 17, 2007

after a quick review

change

while ( $rowcs = mysql_fetch_array($resultcs) )

to

while ( $rowcs = mysql_fetch_array($resultosc) )

l3asturd · August 17, 2007

$rowcs is looking for $resultcs, but $resultcs is commented out. That's why the fetch_array error arguement error.

MadTechie · August 17, 2007

full update

<?php 
// CSCart DB Info
$hostcs = "localhost";
$usercs = "user";
$passcs = "pass";
$dbnamecs = "cscart0";
// OSC DB Info
$hostosc = "192.168.6.2";
$userosc = "user";
$passosc = "pass";
$dbnameosc = "shop0";

// Connect to cscart
$handlecscart = mysql_connect($hostcs, $usercs, $passcs);

// Connect to osc
$handleosc = mysql_connect($hostosc, $userosc, $passosc);

// Select the cscart database
mysql_select_db($dbnamecs,$handlecscart)
or die("Couldn't connect to DB");

// Select the osc database
mysql_select_db($dbnameosc,$handleosc)
or die("Couldn't connect to DB");

// Query OSC
$selectosc = "SELECT products_model, products_price FROM products ";
if (!$selectosc) {
   die('<p>Error performing query: ' . mysql_error() . '</p>');
}

// OSC Results
$resultosc = mysql_query($selectosc,$handleosc);
if (!$resultosc) {
   die('<p>Error performing query: ' . mysql_error() . '</p>');
}
//*******ONLY LOOP ONE
//while ( $rowcs = mysql_fetch_array($resultcs) AND $rowosc = mysql_fetch_array($resultosc) )
while ( $rowosc = mysql_fetch_array($resultosc) )
{
$oscpart = $rowosc['product_code'] ;
$oscprice = $rowosc['price'];

$oscPID = $rowosc['product_id'] ;
//Find the New Product price etc
$selectosc = "SELECT products_model, products_price FROM products WHERE products_id = $oscPID";
$resultcs = mysql_query($selectcs,$handlecscart); 
$rowcs = mysql_fetch_array($resultcs);
$cspart = $rowcs['products_model'];
$csprice = $rowcs['products_price'];


echo "<p>Part $cspart is being updated to $oscprice" . "<br />";
$result = mysql_query("UPDATE cscart_product_prices SET price='$csprice'
	WHERE cscart_products.product_id = $oscPID",$handlecscart) 
	or die(mysql_error());
}
?>

smoked1 · August 17, 2007

I was still getting an error and I noticed a few things so I made some changes. Here is the location: http://coastalmicrosupply.com/migrate_new.php

I am still getting the same original issue. Here are the changes.

<?php
// CSCart DB Info
$hostcs = "localhost";
$usercs = "user";
$passcs = "pass";
$dbnamecs = "cscart0";
// OSC DB Info
$hostosc = "192.168.6.2";
$userosc = "user";
$passosc = "pass";
$dbnameosc = "shop0";

// Connect to cscart
$handlecscart = mysql_connect($hostcs, $usercs, $passcs);

// Connect to osc
$handleosc = mysql_connect($hostosc, $userosc, $passosc);

// Select the cscart database
mysql_select_db($dbnamecs,$handlecscart)
or die("Couldn't connect to DB");

// Select the osc database
mysql_select_db($dbnameosc,$handleosc)
or die("Couldn't connect to DB");

// Query OSC
$selectosc = "SELECT products_model, products_price FROM products ";
if (!$selectosc) {
   die('<p>Error performing query: ' . mysql_error() . '</p>');
}

// OSC Results
$resultosc = mysql_query($selectosc,$handleosc);
if (!$resultosc) {
   die('<p>Error performing query: ' . mysql_error() . '</p>');
}
//*******ONLY LOOP ONE
//while ( $rowcs = mysql_fetch_array($resultcs) AND $rowosc = mysql_fetch_array($resultosc) )
while ( $rowosc = mysql_fetch_array($resultosc) )
{
// $oscpart = $rowosc['product_code'] ;
$oscpart = $rowosc['products_model'];
// $oscprice = $rowosc['price'];
$oscprice = $rowosc['products_price'];

// $oscPID = $rowosc['product_id'] ;
// Updates should be based on the product model

//Find the New Product price etc
$selectosc = "SELECT products_model, products_price FROM products WHERE products_model = '$oscpart'";
$resultcs = mysql_query($selectosc,$handleosc); 
$rowcs = mysql_fetch_array($resultcs);
$cspart = $rowcs['products_model'];
$csprice = $rowcs['products_price'];


echo "<p>Part $cspart is being updated to $csprice" . "<br />";
// Made some changes so that new price is updated based on $oscpart
$result = mysql_query("UPDATE cscart_product_prices SET price='$csprice'
	WHERE cscart_products.product_id = cscart_products_price.product_id AND cscart_products.product_code = '$oscpart'",$handlecscart) 
	or die(mysql_error());
}
?>

MadTechie · August 17, 2007

well this is my update i changed a few thing so it makes more sense to me but i don't know thw databse setup etc, the logic is the main thing

<?php
// From DB
// CSCart DB Info
$hostF = "localhost";
$userF = "user";
$passF = "pass";
$dbnameF = "cscart0";

//To DB
// OSC DB Info
$hostT = "192.168.6.2";
$userT = "user";
$passt = "pass";
$dbnameT = "shop0";

// Connect to cscart
$handleFrom = mysql_connect($hostF, $userF, $passF);

// Connect to osc
$handleTo = mysql_connect($hostT, $userT, $passT);

// Select the cscart database
mysql_select_db($dbnameF,$handleFrom)
or die("Couldn't connect to DB");

// Select the osc database
mysql_select_db($dbnameT,$handleTo)
or die("Couldn't connect to DB");

// Query OSC
$SqlFrom = "SELECT products_model, products_price FROM products ";

// OSC Results
$resultFrom = mysql_query($SqlFrom, $handleF);
if (!$resultFrom) {
   die('<p>Error performing query: ' . mysql_error() . '</p>');
}


while ( $FromRow = mysql_fetch_array($resultFrom) )
{
$FromID = $FromRow['product_id'];
$FromPart = $FromRow['products_model'];
$FromPrice = $FromRow['products_price'];

$SqlTo = "SELECT products_model, products_price 
	FROM products 
	WHERE product_id = '$FromID' 
	AND product_code = '$FromPart'";

$resultTo = mysql_query($SqlTo,$handleT); 
$ToRow = mysql_fetch_array($resultTo);
$ToPart = $ToRow['products_model'];
$ToPrice = $ToRow['products_price'];

echo "<p>Part $FromPart is being updated from $FromPrice to $ToPrice" . "<br />";

$SqlUpdate = "UPDATE cscart_product_prices 
	SET price='$ToPrice'
	WHERE product_id = '$FromID'  
	AND product_code = '$FromPart'"
$result = mysql_query($SqlUpdate, $handlecscart) 
	or die(mysql_error());
}
?>

smoked1 · August 17, 2007

Based on that I made some changes for my database and now it tells me that a table does not exist when it does. As far as I can tell the query looks fine.

Part TH8BG-YF is being updated from 665.0000 to

Unknown table 'cscart_products' in where clause

<?php
// From DB
// OSC DB Info
$hostF = "192.168.6.2";
$userF = "user";
$passF = "pass";
$dbnameF = "shop0";

//To DB
// CSCART DB Info
$hostT = "localhost";
$userT = "user";
$passT = "pass";
$dbnameT = "cscart0";

// Connect to osc
$handleFrom = mysql_connect($hostF, $userF, $passF);

// Connect to cscart
$handleTo = mysql_connect($hostT, $userT, $passT);

// Select the osc database
mysql_select_db($dbnameF,$handleFrom)
or die("Couldn't connect to DB");

// Select the cscart database
mysql_select_db($dbnameT,$handleTo)
or die("Couldn't connect to DB");

// Query OSC
$SqlFrom = "SELECT products_model, products_price FROM products ";

// OSC Results
$resultFrom = mysql_query($SqlFrom, $handleFrom);
if (!$resultFrom) {
   die('<p>Error performing query: ' . mysql_error() . '</p>');
}


while ( $FromRow = mysql_fetch_array($resultFrom) )
{
$FromID = $FromRow['product_id'];
$FromPart = $FromRow['products_model'];
$FromPrice = $FromRow['products_price'];

$SqlTo = "SELECT product_code, price
	FROM cscart_products, cscart_product_prices 
	WHERE product_code = '$FromPart'";

$resultTo = mysql_query($SqlTo,$handleTo)
		or die(mysql_error());
$ToRow = mysql_fetch_array($resultTo);
$ToPart = $ToRow['products_model'];
$ToPrice = $ToRow['products_price'];

echo "<p>Part $FromPart is being updated from $FromPrice to $ToPrice" . "<br />";

$SqlUpdate = 
"UPDATE cscart_product_prices SET price='$ToPrice' 
WHERE cscart_products.product_id = cscart_product_prices.product_id 
AND cscart_products.product_code = '$FromPart'";
$result = mysql_query($SqlUpdate, $handleTo) 
	or die(mysql_error());
}
?>

MadTechie · August 17, 2007

whats

WHERE cscart_products.product_id = cscart_product_prices.product_id

all about ?

why do you keep adding that ?

smoked1 · August 18, 2007

I added that because the price is in one table that does not have the model number in it and the other table is the one that has the model number.

MadTechie · August 18, 2007

sighs.. that why i have this!

$SqlUpdate = "UPDATE cscart_product_prices 
	SET price='$ToPrice'
	WHERE product_id = '$FromID'  
	AND product_code = '$FromPart'"

does the logic the my version make sense to you ?

Sign In

Newbie needs help please

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information