0 registered (),
131
Guests and
3
Spiders online. |
Key:
Admin,
Global Mod,
Mod
|
|
|
#442549 - 08/07/06 05:54 PM
php Code problem... SOLVED, Thanx jorge
|
Member
Registered: 08/08/00
Posts: 7759
Loc: Arizona
|
why is this mysql update not working? <?php require_once('Connections/data.php');
function file_open($filename)
{
if($fp = @fopen($filename, "r"))
{
$fp = @fopen($filename, "r");
$contents = fread($fp, filesize($filename));
fclose($fp);
return $contents;
}else{
return false;
}
}
$text = file_open('accpac_feed/shopcart.CSV');
$data = array("\n", ".00", ",".chr(34), chr(34));
$product = array("\naaa", "", "bbb", "ccc");
$serial = "aaa".str_replace($data, $product, $text);
$serial2 = nl2br(rtrim($serial, "aaa"));
$string = array("aaa", "\n", "bbb", "ccc");
$change = array(chr(34)."UPDATE products_attributes SET options_quantity = '", "sssssss", "' WHERE option_Sku = '", "'".chr(34));
$test = explode('sssssss', rtrim(str_replace($string, $change, $serial2), "sssssss"));
foreach ($test as $v) {
mysql_query($v) or die(mysql_error());
echo $v."
";
}
//mysql_query($massupdate) or die('Error, update failed');
//$change = array(chr(34)."UPDATE products_attributes SET options_quantity = '", "sssssss", "' WHERE option_Sku = '", "'".chr(34));
//"UPDATE products_attributes SET options_quantity = '' WHERE option_Sku = ''"
?> im getting a "You have an error in your SQL syntax near '"UPDATE products_attributes SET options_quantity = '14' WHERE option_Sku = '6125' at line 1" error and have been going nuts trying to find the problem... my brain hurts and i'm sure im just missing something obvious
|
Top
|
|
|
|
#442550 - 08/07/06 06:33 PM
Re: php Code problem... SOLVED, Thanx jorge
|
Member
Registered: 10/04/01
Posts: 4114
Loc: Pittsburgh, PA. USA
|
Dude,
I'm looking at it and I don't see a problem. You know when that's the case it's something really stupid. Fun problems eh?
I'll be curious to see what the issue is. Is the record/column names etc.. match what's in the code as far as capitolization/spacing etc?
Had a problem on Friday where in the DB I had Confroom and in the code I had confroom (sql 2000) and it was blowing up on me.
_________________________
Must stay away from political/religious debates. Must stay away........
|
Top
|
|
|
|
#442552 - 08/07/06 06:38 PM
Re: php Code problem... SOLVED, Thanx jorge
|
Member
Registered: 27/11/00
Posts: 1147
Loc: Montclair, NJ
|
Originally posted by Samueul: I'll be curious to see what the issue is. Is the record/column names etc.. match what's in the code as far as capitolization/spacing etc?. If this was the issue he's get something like:Table 'blah' doesn't exist. Also spacing in SQL doesn't matter for anything, at least with MySQL (which he is using).
|
Top
|
|
|
|
#442553 - 08/07/06 10:22 PM
Re: php Code problem... SOLVED, Thanx jorge
|
Member
Registered: 08/08/00
Posts: 7759
Loc: Arizona
|
Originally posted by jorge: This is your problem.
Remove the chr(34)
chr(34) is a double quote.
So you're sending this: "UPDATE...
You can't have a " at the starting of a statement.
Edit: To get in more depth the double quote in mysql is used for a literal table name. So if for some reason you want to call a table a reserved word, like SELECT/UPDATE/REPLACE, etc you can do something like:
SELECT * FROM "SELECT"; HaHaHa nice try... now i get this error: "You have an error in your SQL syntax near '>' at line 1" which to me is far worse than: "You have an error in your SQL syntax near '"UPDATE products_attributes SET options_quantity = '14' WHERE option_Sku = '6125' at line 1" considering ">" is not in that code anywhere BTW... i have no idea why i went through the extra effort of putting chr(34) in there... it seemed right at the time.
|
Top
|
|
|
|
#442555 - 09/07/06 10:47 AM
Re: php Code problem... SOLVED, Thanx jorge
|
Member
Registered: 08/08/00
Posts: 7759
Loc: Arizona
|
i know the code is odd... heres the deal... my client runs oscommerce they run accpac in office for invenory management... they uplaod a csv file spit out by accpac to the server where this script will read it and update quantity several times a day...
it basicly updates the quantity where the sku's match as not all skus will be updated.
the othe rthing to note is that not every field in the DB is updated, only quantity...
the biggest issue was dealing with the ".00" and constructing the query around the entries... str_replace worked fine, but having no leading character before the quantity made that problematic... but you can look through the code and see how i did it.
also running the str_replace to switch things to aaa, bbb, ccc.... then another to switch to xxx,yyy,zzz was a way orund anothe rproblem...
this code is probably more bloated than it needs to be... but it is so close to working that i really do not care.
2 lines from CSV:
14.00,"612558106839" 7.00,"612558106846"
soory, i forgot to put up the echoed query... which i have had all along. error reads:
UPDATE products_attributes SET options_quantity = '14' WHERE option_Sku = '612558106839' :You have an error in your SQL syntax near '>' at line 1
could the'>' in the error simply mean somehting else? because there is no > in the code... i mean is it a code for another error? i am fairly knowledgable about php and mysql but this is just not making sense...
i know htis is gonna be somehting really silly.
infact... it looks like there is an \n in there somehwere? dropping the error to a new line form the querry... but i don't see the code generating it... maybe run a str_replace again.
|
Top
|
|
|
|
#442556 - 09/07/06 11:06 AM
Re: php Code problem... SOLVED, Thanx jorge
|
Member
Registered: 08/08/00
Posts: 7759
Loc: Arizona
|
lol solved it. changed: $serial = "aaa".str_replace($data, $product, $text);
$serial2 = nl2br(rtrim($serial, "aaa")); to $serial = "aaa".str_replace($data, $product, $text);
$serial2 = rtrim($serial, "aaa"); That nl2br() wa sin there from early on when i was just trying to format the query correctly... thansk everyoen for the help.... XOC comes through again.
|
Top
|
|
|
|
#442557 - 09/07/06 11:32 AM
Re: php Code problem... SOLVED, Thanx jorge
|
Member
Registered: 27/11/00
Posts: 1147
Loc: Montclair, NJ
|
You should look at making it work something like this: <?php require_once('Connections/data.php');
function file_open($filename)
{
if($fp = @fopen($filename, "r"))
{
$fp = @fopen($filename, "r");
$contents = fread($fp, filesize($filename));
fclose($fp);
return $contents;
}else{
return false;
}
}
$sText = file_open('accpac_feed/shopcart.CSV');
$aText = explode("\n", $sText);
$sPat = '^([0-9]+)(\.[0-9]{2})?,\"([0-9]+)\"$';
foreach ($aText as $sLine)
if (ereg($sPat, $sLine, $aMatch)) {
$dbSku = '\'' . addslashes($aMatch[3]) . '\'';
$dbQuant = '\'' . addslashes($aMatch[1]) . '\'';
$sQuery = "UPDATE
products_attributes
SET
options_quantity = ${dbQuant}
WHERE
option_Sku = ${dbSku}";
mysql_query($sQuery)
OR die("Error with: ${sQuery} :" . mysql_error());
}//if
?> Much easier to read and debug. Edit: Just noticed the title of this thread. No problem!
|
Top
|
|
|
|
#442558 - 09/07/06 01:58 PM
Re: php Code problem... SOLVED, Thanx jorge
|
Member
Registered: 08/08/00
Posts: 7759
Loc: Arizona
|
jesus man.... you lost me at $sPat = '^([0-9]+)(\.[0-9]{2})?,"([0-9]+)"$'; i know i took a few extra steps but that's just sick. not sure i'm gonna mess with it, after all it works.
|
Top
|
|
|
|
|