Desperatley Need Help with PHP / MYSQL code

[philip937]

Hi everyone.

Please Please Please can someone help me?

I am trying to set up a php page that updates product qty's in the database. I almost have it working, however seem to have hit a brick wall.

The php simply reads an XML file which contains Product Codes & Qtys, and then $variable echo's are used in the mysql queery to UPDATE the database.

All works perfectly apart from when I have a product code that is alpha numeric.

For Example, My php file reads the XML and will find product codes as they are in the XML: i.e $productcode will echo 28923 or 28374S.

Now, the mysql syntax im using that works fine with numbers is:

UPDATE products_with_attributes_stock SET quantity = 100 WHERE product_code = 28923

This updates the quatity as it should as does the PHP file for any $productcode that echo's as just a number with:

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

What doesnt work is:

UPDATE products_with_attributes_stock SET quantity = 100 WHERE product_code = 28374S

this returns the error -

************************
Error

SQL query:

UPDATE products_with_attributes_stock SET quantity =100 WHERE product_code = 28374S

MySQL said: Documentation
#1054 - Unknown column 'product_code' in 'where clause'
************************
the value 28374S does exist! - I dont understand why mysql is returning this result..

I have noted that if I repeat the syntax with double quotations i.e "28734S" it works! BUT this is no good as my $productcode is not echoing that!???

In addition, I have managed to make my php echo the codes in double quotatons by doing $quote = '"'.$productcode.'"'; and using the following code for the syntax:

mysql_query("UPDATE products_with_attributes_stock SET quantity = $stock WHERE product_code = $quote");

BUT THIS ALSO DOESNT WORK!

Please please please let there be someone out there that can tell me why this isn't working!???

Is it my php code? is it the syntax? is it the way I have my table set up?? IM STUCK..

Kind Regards
Phil

[DrByte]

Wrapping quote-marks around alphanumeric strings is absolutely important. A pair of single-quotes or a pair of double-quotes will suffice. Single is the usual approach when using strings.

Also make sure your custom added "product_code" field exists in the table and that it's not a numeric/integer type. If it needs to handle strings it will need to be a varchar or char type (or text if it's huge data).

[philip937]

Wrapping quote-marks around alphanumeric strings is absolutely important. A pair of single-quotes or a pair of double-quotes will suffice. Single is the usual approach when using strings.

Also make sure your custom added "product_code" field exists in the table and that it's not a numeric/integer type. If it needs to handle strings it will need to be a varchar or char type (or text if it's huge data).

OK - so I definatley need double quotes.
Any ideas as to why when I use the $quote, which echos "24437S " it still doesnt work?

is it my php code that is incorrect in the syntax string part ? How can I write it so it adds quotations to the queery?

The custom field does exist and it is set to:

product_code varchar(32) latin1_swedish_ci No

I am desperate to get the php to run this queery and need a workaround.

Thanks for you quick response, any further suggestions/advice would be greatly appreciated.

Phil

[DrByte]

Now, the mysql syntax im using that works fine with numbers is:

UPDATE products_with_attributes_stock SET quantity = 100 WHERE product_code = 28923

This updates the quatity as it should as does the PHP file for any $productcode that echo's as just a number with:

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

What doesnt work is:

UPDATE products_with_attributes_stock SET quantity = 100 WHERE product_code = 28374S

1. I don't use "mysql_query()" when running inside Zen Cart, because the database abstraction layer inside ZC is smarter and safer because there are numerous data-typing (aka security) protection options available in it, etc.

2. I *always* refrain from embedding variables inside double-quotes. While it's technically accepted, it can make for confusion when troubleshooting something that's going wrong. To keep it clear what's doing what, I always concatenate strings and vars separately instead. You'll see that pattern consistently throughout the Zen Cart code too.

3. Since you want to use mysql_query() directly, this syntax should work just fine:

mysql_query("UPDATE products_with_attributes_stock SET quantity = " . $stock . " WHERE product_code = '" . $productcode . "'");

[philip937]

mysql_query("UPDATE products_with_attributes_stock SET quantity = " . $stock . " WHERE product_code = '" . $productcode . "'");

Thanks again for your quick response. That code didn't seem to work, this time it didnt even UPDATE to Number only codes? Any thing else I can try??

[DrByte]

You didn't mention any error message, so I can only assume it ran without one.
If it's not working with that, then I'd be suspecting that the PHP logic you're using is faulty or your input data is bad ... or there are no relevant records to update.

[DrByte]

What if you replace the "mysql_query" call with just an "echo" call? ie: output the generated SQL to the screen, to see what it's really doing?
Then what happens if you run that SQL in phpMyAdmin? What happens?

Both of those steps can be very revealing of where you've got logic or syntax errors.

[philip937]

What if you replace the "mysql_query" call with just an "echo" call? ie: output the generated SQL to the screen, to see what it's really doing?
Then what happens if you run that SQL in phpMyAdmin? What happens?

Both of those steps can be very revealing of where you've got logic or syntax errors.

the following is my php code, granted im a novice and its been peiced together form examples off the net. perhaps you could validate?

<?php

$con = mysql_connect('localhost', '<username>', '<password>);
if (!$con) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_select_db('overplan_mirror');



$rssFeeds = array ('supplier.xml');

//Loop through the array, reading the feeds one by one
foreach ($rssFeeds as $feed) {
  readFeeds($feed);
}
function startElement($xp,$name,$attributes) {  
global $item,$currentElement;  $currentElement = $name; 
//the other functions will always know which element we're parsing  
if ($currentElement == 'PRODUCT') { 
//by default PHP converts everything to uppercase    
$item = true; 
// We're only interested in the contents of the item element. 
////This flag keeps track of where we are  
}}

function endElement($xp,$name) {  
global $item,$currentElement,$productcode,$stock,$duedate;$conn;    
if ($name == 'PRODUCT') { 
// If we're at the end of the item element, display 
// the data, and reset the globals    
echo "<b>Product Code:</b> $productcode<br>";    
echo "<b>Stock:</b> $stock<br>";    
echo "<b>Due Date:</b> $duedate<br><br>";  
  $ins_product = addslashes($productcode);
  $ins_qty = addslashes($stock);
  $ins_due = addslashes($duedate);  



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

$productcode = '';
$stock = '';    
$duedate = '';    
$item = false;  }}



function characterDataHandler($xp,$data) {  
global $item,$currentElement,$productcode,$stock,$duedate;    
if ($item) { 
//Only add to the globals if we're inside an item element.    
switch($currentElement) {      
case "PRODUCT_CODE":        
$productcode .= $data; 
// We use .= because this function may be called multiple 
// times for one element.        
break;      
case "AVAILABLE_STOCK":        
$stock.=$data;        
break;      
case "DUE_DATE":        
$duedate.=$data;        
break;     }  }}



function readFeeds($feed) {
  $fh = fopen($feed,'r'); 
// open file for reading

  $xp = xml_parser_create(); 
// Create an XML parser resource

  xml_set_element_handler($xp, "startElement", "endElement"); 
// defines which functions to call when element started/ended

  xml_set_character_data_handler($xp, "characterDataHandler");

  while ($data = fread($fh, 4096)) {
    if (!xml_parse($xp,$data)) {
      return 'Error in the feed';
    }
  }
}
?>

Also supplier.xml is in the following format:

<StockValues>
−
<Product>
<Product_Code>00380 </Product_Code>
<Available_Stock>3960</Available_Stock>
<Due_Date>N/A</Due_Date>
</Product>
−
<Product>
<Product_Code>00429 </Product_Code>
<Available_Stock>4285</Available_Stock>
<Due_Date>N/A</Due_Date>
</Product>
−
<Product>
<Product_Code>00495 </Product_Code>
<Available_Stock>1035</Available_Stock>
<Due_Date>N/A</Due_Date>
</Product>
−
<Product>
</StockValues>

********The Following is the php output echo from the code above as it is******
mysql_query("UPDATE products_with_attributes_stock SET quantity = $stock WHERE smiffy_id = $productcode");

Product Code: 00380
Stock: 3960
Due Date: N/A

Product Code: 00429
Stock: 4285
Due Date: N/A

Product Code: 00495
Stock: 1035
Due Date: N/A

Product Code: 0122
Stock: 2953
Due Date: N/A

Product Code: 0190
Stock: 3434
Due Date: N/A

Product Code: 052A
Stock: 550
Due Date: N/A

*********************When I echo the queery**************************:

echo = ("UPDATE products_with_attributes_stock SET quantity = $stock WHERE smiffy_id = $productcode");

UPDATE products_with_attributes_stock SET quantity = 789 WHERE product_code = 20320 Product Code: 20389L
Stock: 11
Due Date: N/A

UPDATE products_with_attributes_stock SET quantity = 11 WHERE product_code = 20389L Product Code: 20389M
Stock: 161
Due Date: N/A

UPDATE products_with_attributes_stock SET quantity = 161 WHERE product_code = 20389M Product Code: 20390M
Stock: 87
Due Date: N/A

UPDATE products_with_attributes_stock SET quantity = 87 WHERE product_code = 20390M Product Code: 20390S
Stock: 0
Due Date: N/A

UPDATE products_with_attributes_stock SET quantity = 0 WHERE product_code = 20390S Product Code: 20392L
Stock: 160
Due Date: N/A


Im just about to echo the code you sent to see what that shows..