Jump to content
dev osCommerce
Forum
  • Checkout
  • Login
  • Contact Us
  • Get in touch

osCommerce

The e-commerce.

New Demo
Our Partners

error in SQL syntax -- HELP! read error message...

Guest

By Guest
in General Support

Recommended Posts

Guest

Guest

Posted
Posted

I was wondering if anyone can make sense of this error. I got it after I installed my fedex shipping module....

 

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{1}19' at line 1

 

select products_files_req from products where products_id = 31{1}19

 

[TEP STOP]

 

any ideas???

Jan Zonjee

Jan Zonjee

Posted
Posted
I was wondering if anyone can make sense of this error. I got it after I installed my fedex shipping module....

 

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{1}19' at line 1

 

select products_files_req from products where products_id = 31{1}19

 

[TEP STOP]

 

any ideas???

The code that uses that select (probably the fedex shipping module) did not anticipate using products with attributes.

 

You will have to change that select query to either cast it into an integer (add (int) before the id after the =) or use the function tep_get_prid to extract the integer products_id out of a products_id with attributes.

Guest

Guest

Posted
Posted
The code that uses that select (probably the fedex shipping module) did not anticipate using products with attributes.

 

You will have to change that select query to either cast it into an integer (add (int) before the id after the =) or use the function tep_get_prid to extract the integer products_id out of a products_id with attributes.

 

 

My co-worker was adding t-shirt size attributes before this error occured. I think you are right.

 

I am very unfamiliar with editing the database for oscommerce. I know how to start up phpmyadmin but getting further than that I don't know what I'm doing yet. I have ran some queries to install contributions but that is it.

 

How might I carry out your instructions. Where to I find the sql query to edit it? Thank you so much.

Guest

Guest

Posted
Posted

The following is the buildfedexdata.php file that was ran as the first step in the fedex shipping module installation:

 

---------------------------------------------------------------------------------------------------

<?php

/*

This program is released under the GPL, version 2.0 or greater

Copyright Tim Wasson 2002

 

*/

 

require('includes/application_top.php');

 

 

// Clean out database of all existing fedex zone table info

 

echo "Dropping and recreating fedex tables...<br>";

 

// zone_id int NOT NULL,

tep_db_query("DROP TABLE IF EXISTS fedex_pcode_to_zone_xref");

tep_db_query("CREATE TABLE fedex_pcode_to_zone_xref (

xref_id int NOT NULL auto_increment,

pcode_from int NOT NULL,

pcode_to int NOT NULL,

zone_id varchar(50) NOT NULL,

zone_id_ground varchar(50) NOT NULL,

PRIMARY KEY (xref_id),

INDEX pcodes (pcode_from, pcode_to),

INDEX pcode_from (pcode_from),

INDEX pcode_to (pcode_to)

)");

 

 

tep_db_query("DROP TABLE IF EXISTS fedex_zones");

tep_db_query("CREATE TABLE fedex_zones (

zone_id int NOT NULL auto_increment,

zone_name varchar(50) NOT NULL,

PRIMARY KEY (zone_id)

)");

 

tep_db_query("DROP TABLE IF EXISTS fedex_shiptype");

tep_db_query("CREATE TABLE fedex_shiptype (

shiptype_id int NOT NULL auto_increment,

shiptype_name varchar(50) NOT NULL,

PRIMARY KEY (shiptype_id)

)");

 

tep_db_query("DROP TABLE IF EXISTS fedex_zone_rates");

tep_db_query("CREATE TABLE fedex_zone_rates (

zonerate_id int NOT NULL auto_increment,

shiptype_id int NOT NULL,

weight int NOT NULL,

zone_id int NOT NULL,

zone_cost decimal(8,2),

PRIMARY KEY (zonerate_id),

INDEX shiptype (shiptype_id),

INDEX weight (weight),

INDEX zone_id (zone_id)

)");

 

//**********

//* Main *

//**********

 

//Import Zone File

ImportZoneFile("fedex_local/Zone_Locator.txt");

 

//Comment out the services that you do not want to offer accordingly

// Import all the FedEx Rates by Service, filename, numberofzonesinfile

ImportRateFile("fedex_local/2008_FedEx_RatesBySvc_2Day.csv",15);

ImportRateFile("fedex_local/2008_FedEx_RatesBySvc_ES.csv",7);

//ImportRateFile("fedex_local/2008_FedEx_RatesBySvc_FO.csv",11);

//ImportRateFile("fedex_local/2008_FedEx_RatesBySvc_PO.csv",15);

ImportRateFile("fedex_local/2008_FedEx_RatesBySvc_SO.csv",13);

ImportRateFile("fedex_local/2008_FedEx_US-Ground.csv",18);

ImportRateFile("fedex_local/2008_FedEx_US-HomeDelivery.csv",15);

 

function ImportZoneFile($textfilename){

/*

This will import a "zone" file that describes how to convert from your post code

to a zone number which can be used with the weight to look up the shipping costs.

 

*/

$pcode_length = 5; // the number of digits in your postal codes

$thefile = file($textfilename);

echo "Reading Zone Chart File <b>$textfilename</b><br>";

$rownum = 0; // counter for the display

 

foreach( $thefile as $therow ){

$rownum++;

echo "Reading row # $rownum from the file...<br>";

$rowparts = explode( " ",$therow);

$thecount = 0;

$newrow = array();

foreach ($rowparts as $part){

if ( rtrim(ltrim($part)) != "" ){

// we only keep the non-space parts!

$newrow[] = $part;

}

}

$newdoc[] = $newrow;

}

 

foreach ( $newdoc as $row ){

if ( ltrim(rtrim($row[1])) != 'NA'){

// we only want to process rows that mean something, not NA's

$range = split( "-",$row[0]); // break up the string

$from = rtrim(ltrim($range[0]));

if ( isset( $range[1]) ){

// this is a range type number

$to = rtrim(ltrim($range[1]));

} else {

// It's a single number, use it for both start and finish

$to = $from;

}

$from = $from . "0000000";

$from = substr($from,0,$pcode_length);

$to = $to . "999999999";

$to = substr($to,0,$pcode_length);

echo "From $from to $to is zone " . $row[1] . " ". $row[2] . "<br>";

$thiszone = array( $from, $to, $row[1], $row[2]);

$zonemap[] = $thiszone;

}

}

 

// Now let's pump it into the db!!!!

foreach ( $zonemap as $zone ){

$zonename = (int) $zone[2];

$zonename2 = '';

if (tep_not_null($zone[3])) {$zonename2 = (int) $zone[3];}

$from = $zone[0];

$to = $zone[1];

 

$sql = "SELECT * FROM fedex_zones WHERE zone_name='$zonename'";

$results = QueryResult($sql);

if ( count( $results ) > 0 ){

// existing zone, use it.

} else {

echo "Adding new zone name: " . $zonename . "<br>";

// new zone, add it and use it.

$sql = "INSERT INTO fedex_zones( zone_name ) VALUES ('$zonename')";

tep_db_query($sql);

 

$sql = "SELECT zone_id FROM fedex_zones WHERE zone_name='$zonename'";

$results = QueryResult($sql);

 

}

$result = $results[0];

$second = $result['zone_id'];

$zone_id = $second;

 

//cater for multiple FedEx Zone for Hawaii, PR and Alaska

$zone_id2 = '';

if (tep_not_null($zonename2)){

$sql = "SELECT * FROM fedex_zones WHERE zone_name='$zonename2'";

$results = QueryResult($sql);

$first = $results[0];

if ( count( $results ) > 0 ){

// existing zone, use it.

} else {

echo "Adding new zone name2: " . $zonename2 . "<br>";

// new zone, add it and use it.

$sql = "INSERT INTO fedex_zones( zone_name ) VALUES ('$zonename2')";

tep_db_query($sql);

 

$sql = "SELECT zone_id FROM fedex_zones WHERE zone_name='$zonename2'";

$results = QueryResult($sql);

 

}

$result = $results[0];

$zone_id2 = $result['zone_id'];

}

 

$sql = "INSERT INTO

fedex_pcode_to_zone_xref (pcode_from, pcode_to, zone_id, zone_id_ground)

VALUES ('$from', '$to', '$zone_id', '$zone_id2')";

tep_db_query($sql);

}

}//end ImportZoneFile

 

function ImportRateFile($csvfilename, $numofzones){

 

echo "Importing weight/zone rate file <b>$csvfilename</b><br>";

$thefile = file($csvfilename);

$shippingtype_row = $thefile[0]; // we assume the first row has the Shipping Type name (Express, Ground, whatever)

$zonename_row = split(",",$thefile[1]); // the second row has the headers in it.

 

$shipname = trim($shippingtype_row);

 

// The top row has a single entry, it is the name

$sql = "SELECT * FROM fedex_shiptype WHERE shiptype_name='$shipname'";

$results = QueryResult($sql);

$first = $results[0];

if ( count( $results ) > 0 ){

// existing zone, use it.

echo "Using existing Ship Type name: " . $shipname . "<br>";

} else {

echo "Adding new Ship Type name: " . $shipname . "<br>";

// new zone, add it and use it.

$sql = "INSERT INTO fedex_shiptype( shiptype_name ) VALUES ('$shipname')";

tep_db_query($sql);

 

$sql = "SELECT * FROM fedex_shiptype WHERE shiptype_name='$shipname'";

$results = QueryResult($sql);

}

$first = $results[0];

$second = $first['shiptype_id'];

$shiptype_id = $second;

 

 

// each column must have one zone in it

 

for ( $i=1; $i<$numofzones+1; $i++){

$zonename = $zonename_row[$i]; // get the zone name;

// see if it exists in the table

$sql = "SELECT * FROM fedex_zones WHERE zone_name=$zonename";

$results = QueryResult($sql);

$first = $results[0];

if ( count( $results ) > 0 ){

// existing zone, use it.

} else {

echo "Adding new zone name: " . $zonename . "<br>";

// new zone, add it and use it.

$sql = "INSERT INTO fedex_zones( zone_name ) VALUES ('$zonename')";

tep_db_query($sql);

 

$sql = "SELECT zone_id FROM fedex_zones WHERE zone_name='$zonename'";

$results = QueryResult($sql);

}

$first = $results[0];

$second = $first['zone_id'];

$zone_id[$i] = $second;

}

 

// remove the existing entries for this ship type to prevent dupes in the db

#$sql = "DELETE FROM fedex_zone_rates WHERE shiptype_id=$shiptype_id";

#tep_db_query($sql);

 

foreach ($thefile as $therow){

if ( $therow == '' ){ break; }

$thepieces = split(',',$therow);

 

if ( is_numeric( $thepieces[0] ) ){

echo "Found a numeric item <br>";

$weight = $thepieces[0];

for ( $i=1; $i<$numofzones+1; $i++){

$thispiece = ltrim(rtrim($thepieces[$i]));

if (is_numeric( $thispiece ) ){

$sql = "INSERT INTO

fedex_zone_rates (

shiptype_id,

weight,

zone_id,

zone_cost

)

VALUES (" .

$shiptype_id . ", " .

$weight . ", " .

$zone_id[$i] . ", " .

$thispiece .

" )";

echo "SQL is " . $sql . "<br>";

tep_db_query($sql);

} else {

echo "Piece was non-numeric: " .$thispiece . "<br>";

echo "Piece length was " . strlen($thispiece) . "<br";

}

}

}

}

}// end of import rate file

 

function QueryResult($qStr) {

$retArr = array();

 

$qResult = tep_db_query($qStr);

while($rec = tep_db_fetch_array($qResult)) {

$retArr[] = $rec;

}

return $retArr;

}

 

?>

----------------------------------------------------------------------------------------------

 

is this the file I change. I don't have any .sql files with the module. Does this give more info? Thanks again!

Guest

Guest

Posted
Posted

here is the statement in the shopping_cart.php file and it is below where the image upload code is. I payed smartinfosys from getafreelancer.com for an upload feature. I think in combination with the fedex shipping module it is messed up...

 

 

// code for uplode images for products

 

 

$sql_files = tep_db_query("select products_files_req from products where products_id = ".$products[$i]['id']);

$arres_files = tep_db_fetch_array($sql_files);

$image_code="";

$pID = $products[$i]['id'];

$productsIds .= ",".$pID;

if($arres_files['products_files_req'] > 0)

{

$image_code .= '<INPUT name="image_'.$pID.'_1" size="20" id="image_'.$pID.'_1" type="file"><br>

<div id="attach_parent'.$pID.'">

<script type="text/javascript" language="javascript">

var count'.$pID.' = 1;

var cntadd'.$pID.' = 0;

var total'.$pID.' = 1;

var cn'.$pID.'=0;

var removed'.$pID.'="";

var re'.$pID.'=new Array();

var ren'.$pID.'=0;

function addAttach'.$pID.'() {

 

count'.$pID.'=count'.$pID.'+1; //unique id for each div

total'.$pID.'=total'.$pID.'+1; //total numer of attachments

cntadd'.$pID.'=parseInt(document.getElementById(\'cityzipcnt'.$pID.'\').value);

cntadd'.$pID.'=cntadd'.$pID.'+1;

//alert(cntadd);

var attach_parent'.$pID.' = document.getElementById(\'attach_parent'.$pID.'\');

var attach_child'.$pID.' = document.createElement(\'div\');

attach_child'.$pID.'.setAttribute(\'id\',\'att_'.$pID.'_\'+count'.$pID.');

attach_child'.$pID.'.setAttribute(\'style\',\'margin:2px 0\');

attach_child'.$pID.'.innerHTML = \'<input type="file" name="image_'.$pID.'_\'+total'.$pID.'+\'" size="20"/>  <a href="java script:;" onclick="removeAttach'.$pID.'(\'+count'.$pID.'+\');">Remove</a><input type="hidden" name="total_no'.$pID.'" id="total_no'.$pID.'" value="\'+total'.$pID.'+\'" />\';

document.getElementById(\'cityzipcnt'.$pID.'\').value=cntadd'.$pID.';

attach_parent'.$pID.'.appendChild(attach_child'.$pID.');

 

}

function removeAttach'.$pID.'(object_id) {

total'.$pID.'=total'.$pID.'-1;

var cntre'.$pID.'=parseInt(document.getElementById(\'cityzipcnt'.$pID.'\').value);

cntre'.$pID.'=cntre'.$pID.'-1;

//alert(cntre);

var attach_parent'.$pID.' = document.getElementById(\'attach_parent'.$pID.'\');

var attach_child'.$pID.' = document.getElementById(\'att_'.$pID.'_\'+object_id);

attach_parent'.$pID.'.removeChild(attach_child'.$pID.');

 

document.getElementById(\'cityzipcnt'.$pID.'\').value=cntre'.$pID.';

}

--></script>

<div id="att_'.$pID.'_1">

 

</div>

</div>

<a href="java script:;" onclick="addAttach'.$pID.'();">Add Another</a>

<input type="hidden" name="total_ids'.$pID.'" id="total_ids'.$pID.'" value="" />

<input type="hidden" name="cntupdate'.$pID.'" id="cityids'.$pID.'" value="" />

<input type="hidden" name="cityzipcnt'.$pID.'" id="cityzipcnt'.$pID.'" value="0" />

';

}

else

{

$image_code .="";

}

$info_box_contents[$cur_row][] = array('align' => 'left',

'params' => 'class="productListing-data" style="padding-left:10px;" valign="top" width="35%"',

'text' => '<b>'. $image_code .'</b>');

 

 

 

$sql_image = tep_db_query("select * from products where products_id = ".$products[$i]['id']);

$arr_image = tep_db_fetch_array($sql_image);

$no_of_image = tep_db_num_rows($sql_image);

$image_pro = "";

if($no_of_image > 0)

{

$image_pro = $arr_image['ex_image'];

$ex_imge = explode(",",$image_pro);

$image_pro_data = "";

for($k=0;$k<count($ex_imge)-1;$k++)

{

$image_pro_data .= $ex_imge[$k].'<br>';

}

}

else

{

$image_pro = "";

}

 

$info_box_contents[$cur_row][] = array('params' => 'class="productListing-data" valign="top"',

'text' => $image_pro_data);

}

 

new productListingBox($info_box_contents);

?>

Jan Zonjee

Jan Zonjee

Posted
Posted

Try adding this line:

// code for uplode images for products

$products[$i]['id'] = tep_get_prid($products[$i]['id']); // make sure this is an integer
 $sql_files = tep_db_query("select products_files_req from products where products_id = ".$products[$i]['id']);

Guest

Guest

Posted
Posted
Try adding this line:

// code for uplode images for products

$products[$i]['id'] = tep_get_prid($products[$i]['id']); // make sure this is an integer
 $sql_files = tep_db_query("select products_files_req from products where products_id = ".$products[$i]['id']);

 

 

on it... i'll come back to let you know how it worked.

Guest

Guest

Posted
Posted
on it... i'll come back to let you know how it worked.

 

 

Amazing. It worked. Thank you!

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...