Desperatley Need Help with PHP / MYSQL code

[DrByte]

All your product_codes inside the quotes have a space on the end. Is that how they're actually stored in the database?
Maybe you need to clean the data you're getting from your xml source before processing it?

[philip937]

All your product_codes inside the quotes have a space on the end. Is that how they're actually stored in the database?
Maybe you need to clean the data you're getting from your xml source before processing it?

I also noticed this and thought it was strange. I tested the syntax with a space manually by copying and pasting into myphp admin in the database and it worked, but not from the php page, would something be happening with that space that I don't see that stops the syntax from working from the php page?

If so, any suggestions on how I could remove it? Is it the way my php file is Reading it?

Phil
Posted via Mobile Device

[philip937]

I also noticed this and thought it was strange. I tested the syntax with a space manually by copying and pasting into myphp admin in the database and it worked, but not from the php page, would something be happening with that space that I don't see that stops the syntax from working from the php page?

If so, any suggestions on how I could remove it? Is it the way my php file is Reading it?

Phil
Posted via Mobile Device

As a test I set up the following:

$test = 35848M;

I replaced $productcode with $test and ran again, the echo was " 35848M" with no space at the end.
The queery still didn't update the quantity for 35848M!!

Arghhh why won't this work?
Posted via Mobile Device

[DrByte]

" 35848M" is not the same as "35848M"
"00380 " is not the same as "00380"
"052BK " is not the same as "052BK"

If your incoming data feed is sloppy and contains spaces, then you may need to trim those spaces off before using the data in your queries.
Perhaps like this:

$productcode = trim($productcode);

[DrByte]

Unrelated to your "spaces" issue, I noticed this in your code above:

  $ins_product = addslashes($productcode);
  $ins_qty = addslashes($stock);
  $ins_due = addslashes($duedate); 

You've set values for $ins_product (etc), but you're not actually using those in your queries.

[philip937]

Unrelated to your "spaces" issue, I noticed this in your code above:

You've set values for $ins_product (etc), but you're not actually using those in your queries.

im not sure what the purpose of these were. they were just in the examples pulled from the net so left them in.

I may have made a breakthrough although Im not understanding properly, maybe you would be able to figure out for me?

I contacted the people that provide the data feed for xml for me. on the face of it it sounds like your suspition of there being "bad" data could be right, though im not sure how to progress. They sent me the following:

**************
The problem is that in the XML file in the <Product_Code> tag there is 11 blank spaces.
In the PHP you need to eliminate all the blank spaces with a

$new_string = str_replace(' ','',$each_product_code); <<<the first '' has 11 spaces before i pasted into here...

for each product code.


And use:
$sql = 'UPDATE products_with_attributes_stock SET quantity = '.$stock.' WHERE product_code = \''.$productcode.'\' ');

For the MySQL query.
************

Im not sure what I am supposed to do with this... I pasted it in but im sure thats not what they mean to do at all?? can you advise.. does this make sense to you?? are you able to advise how I should use this in my php file?

Kind Regards,

Phil

[DrByte]

Their response does confirm that they include spaces in their data. Not sure exactly why they do that, but I'm sure they have their reasons.
Their suggestion using str_replace will remove all spaces from the $each_product_code variable value. But using str_replace takes more CPU processing than trim() does. The down-side is that trim() only deals with leading and trailing spaces, and not with spaces inside the string, ie: "123 456".

My suggestion would be to change this part of the code you posted earlier from this:

  $ins_product = addslashes($productcode);
  $ins_qty = addslashes($stock);
  $ins_due = addslashes($duedate);  


$sql = ("UPDATE products_with_attributes_stock SET quantity = $stock WHERE product_code = \"$productcode\"");

echo "UPDATE products_with_attributes_stock SET quantity = $stock WHERE product_code = \"$productcode\"<br>";

to this:

  $ins_product = addslashes(trim($productcode));
  $ins_qty = addslashes($stock);

$sql = "UPDATE products_with_attributes_stock SET quantity = " . (int)$stock . " WHERE product_code = '" . $ins_product . "'";

[philip937]

Fantastic! I finally have it working!!!! All Product codes now potulating with new quantities!!!!!!!! Whoohoooo


NEW PROBLEM NOW

Now I have poulated my products_with_attributes_stock table, all the values are ther perfect, and when I look at Catalogue/Product with Atributes Stock they are all populated.

However All products are showing as out of stock?????

is it something to do with the syncing of quantitys?? as the top of each product it says Quantity for all variants, all are 0. I guessing to get it qorking all need to be synced???

Problem is I have over 3000 products, is there a way to sync these using a query in the database!... oooo im almost there! thanks for all your help so far!

Regards,
Phil

[DrByte]

Sorry, I'm not versed in the products_with_attributes_stock addon's architecture. It's different than the core Zen Cart use. You'll have to study the addon to understand how it works and fudge your data accordingly.

[philip937]

Ok, thanks for all your help, I think i need to work out what the button does to the db and try and run a queey that aplies it to all..

THANKS