How to do shipping calculations

[buffy]

Hi everyone,

This is my first post here so hopefully I do ok!

I am trying to add shipping to my php/mysql site and was hoping someone could suggest some advice.

Its not an american site so it doesn't need to link into american tracking companies.... which is what most examples I found did.

Now I'm not sure what the best way to do it is, but we were thinking rather then weight we would assign a value for size.

E.g. small items that can fit in a small bag, would have a shipping value of 1.

Then at the end of the cart process, when the customer was ready to check out it would determine the total shipping cost.

For example, the first threshold for a small bag would be <= 5, so if 5 small items had been added to the cart the total shipping would be $6 for a small bag.

Medium items could have a value of 6, with a threshold of <= 13 (allowing two medium items, or 1 medium items and a number of small items to fit).

Now these values can of course change but its currently what we are thinking about using.

Then I need to calculate this. I am thinking of doing it when they view the cart, by adding the shipping values to each other during the while loop to display the items.

I am a little bit unsure of how to calculate a running total. I guess I would have the first items shipping value, assign it as the total, then add the next shipping value to the total?

I have had some tries of:

$tempshipping = $shipping;
$subtotalshipping = $tempshipping + $shipping;

but this of course is not right!.... do I need an array?

Need to somehow have the first value, then add the next values in the while loop...

Please let me know if you want to see any code, at the moment there isn't much besides the while loop to show the product details.

I have the shipping value in the product table, and want to add the total shipping once found to the orderitem table.

Thanks for any advice!

[kows]

you should be storing your "shipping values" (for each item) in the database in the same row as the other information it has (price, name, id, etc), so you should be able to do something like this while showing the user their "cart":

<?php
  $sv = 0;
  while($array = mysql_fetch_array($sql)){
    //display information on this item
    //..
    //..

    //add onto the shipping value
    $sv += $array['shipping_value'];
  }

  //$sv will hold the total shipping value
?>

if you posted some of the code you're already working with, it might be easier to narrow down a solution that might better fit what you're doing.

[buffy]

Thank you kows

My current while loop uses mysql_fetch_object as it needs to display the product picture:

//retrieve cart items and details
$sqlcart = ("SELECT * FROM orders, orderitem, product
        WHERE .....";

$resultcart=@mysql_query($sqlcart);

//show cart items
while ($row = mysql_fetch_object($resultcart)) {
        $quantity = $row->OrderItemQuantity;
                .......

} 

Now because of this I couldn't use your array one instead, so I used it inside the loop with some debugging code to see if it was calculating it correctly which it was so that was cool! The total was fine but it stuffed up the object loop and stopped it from display all records.

So I tried using the array code AFTER the object loop but for some reason it didn't go through the cart items and show the debugging code in the loop - shipping stayed at 0.

Its using the same $resultcart so I thought it would be doing the query ok.

//sql query as above
//then object while loop

//set shipping value as zero  
 $shipping = 0;
 
   while($array = mysql_fetch_array($resultcart)){
    //do not display as already have
    //just using loop for shipping value purposes

    //add onto the shipping value
    $shipping += $array['ProductShippingValue'];
     
    //debugging
    echo "shipping value = " . $shipping;
  
  } //end while here

//show subtotal
//show shipping
//show total 

I'm pretty sure I'm doing something stupid, please let me know if you are after different code examples.

[kows]

well, first of all -- shouldn't you be able to build $shipping even with mysql_fetch_object()? The following should work, no?

$shipping += $row->ProductShippingValue; 

second, are you storing the product image in the database? if so, might it be easier to store all of the images on the server instead and link to them (have a directory full of images named "product-1190213.gif", referenced with the ID)? this would cut down on database size and possibly even query time (though I'm not sure about the latter).

also, just a side note: since you've already used mysql_fetch_object() to loop through your mysql_query(), I believe you would have to query $sqlcart again so that it started from the beginning of the table. I'm not 100% sure if this is different when dealing with two different functions (mysql_fetch_object and mysql_fetch_array), but I remember having issues once while trying to use mysql_fetch_array() on the same query twice.

let me know if that works or not.

[buffy]

ah! Well that worked great! THANK YOU!

The counter did the trick, though I still need to work on my shipping elseif statements, but thats a minor worry:

//counter for shipping
$shipping += $row['ProductShippingValue'];    

//determine shipping based on shipping value total
if ($shipping <= '5') {
//show small courier price
$shippingtotal = "6.00";
                                        
} elseif ($shipping <= '13') {
//show medium courier price        
$shippingtotal = "8.00";    

} elseif ($shipping > '14' && $island == 'North Island') {
//show big courier price for north island
$shippingtotal = "8.00";
                                        
} else {
//show big courier price for south island
$shippingtotal = "12.00";
}

echo sprintf("%8.2f", $shippingtotal); 

And thanks to you, it also helped me work out how to get the the sub total and total of the products using the += so that was cool!

As for your other notes.... the only experience I have had with pointers is when I want to see whether a row has any data, I then reset the pointer using:

//reset pointer to 0 to display all records
 mysql_data_seek($result,0); 

But I used your advice so didn't really play around with two functions.

Lastly, the images... yes I think its a very good idea to NOT store them in the database.

What I would like to do is modify my current upload script so that I can use ftp and first of all put my image onto the server in the img folder, then using php:

- upload a new product, then for the image, select a image off my harddrive, which is the same (same name) as what I just uploaded on the server
- the php then gets the filename, and amends it to the img dir (usr/../php/img/) and just contains the path in the database
- then I can display the image in a while loop somehow by displaying its relevant path

Thats how I envision it working, is that correct? For me this would be great... so I could do all the images on my computer, copy them to the server, then upload them (well their path and filename) into the db.

Is that somewhat logical?

My current script to upload a image is (i upload a thumb and large image):

//form with "file" input type

//code in image path
        $filepath1='/usr/../php/img/products/';
    $filepath1 .=$_REQUEST['picture1'];
    //handle code for first image
    $isize=filesize($filepath1);
    $hndl=fopen($filepath1,"rb");
    //Display error message if no file is uploaded
    if (!$hndl) {
    echo "Cannot open file"; }
    //Add image data to variable for inserting
    $imgdata="";
    while(!feof($hndl)){
    $imgdata.=fread($hndl,$isize);
    }; 
    $imgdata=addslashes($imgdata);


   //insert new product details into the product table
   $sql = "INSERT INTO product SET 
    ProductName = ...
    ProductImageSmall = '". $imgdata ."'; 

[kows]

It's very simple to upload a file to your server and store it on the server rather than storing it in a database. You can look here on PHP.net for a quick example of how to handle uploading the file using POST and store it wherever. I would suggest storing the file using the ID of the product, rather than a custom name (or the same name as it was on your computer). This way, you don't need to store any extra information in your database, and it will be easier (at least in my eyes) to reference things quickly. This would let you display images in your while() loop simply by doing:

while($blah = true){
  $img_path = "./files/images/products/" . $blah['id'] . ".gif";
  //now just create an image tag with that path: <img src="$img_path" />
} 

Lastly, the only problem I can see in your elseif statements is that if you're selecting "North Island" and have a shipping total of 14, you'll get the "South Island" big courier price (which is $4 more than the North Island big courier price). I'm not sure if that's intended, or if it was just a bug. You can fix it by changing "$shipping > 14" to "$shipping >= 14". I don't see anything else wrong with the statement -- would you care to explain the problem you're experiencing?

[buffy]

Ok then, I'll name them as you sugguest, sounds good.

I have tried those examples before, basically keeping in the exact same sample code (minus the variable name) and I always get the else error message

Its prob why I have sticked with the blob in the db for so long - cause it worked

Code:

//form to get image upload
<form name='orderForm' method="post" action="<?PHP echo $_SERVER['PHP_SELF']. '?UploadProduct=yes';?>" encytype="multipart/form-data">

 <tr>
    <td width="30%">Thumbnail
     </td>
       <td>
 <!-- MAX_FILE_SIZE must precede the file input field -->
 <input type="hidden" name="MAX_FILE_SIZE" value="30000" />
 <input name="picture1" type="file" id="picture1"  size="60">
    </td>
 </tr>

//other fields

</form>

<?php
if ($_REQUEST['UploadProduct']=='yes')
{    
       //examples I tried are below
     
}
?>

//example 1
// Where the file is going to be placed 
$target_path = "img/products/";

/* Add the original filename to our target path.  
Result is "uploads/filename.extension" */
$target_path = $target_path . basename( $_FILES['picture1']['name']); 
 
 if(move_uploaded_file($_FILES['picture1']['tmp_name'], $target_path)) {
    echo "The file ".  basename( $_FILES['picture1']['name']). 
    " has been uploaded";
} else{
    echo "There was an error uploading the file, please try again!";
} 

//example 2
    $uploaddir = '/usr/..../img/products/';
    $uploadfile = $uploaddir . basename($_FILES['picture1']['name']);
 
    echo '<pre>';
    if (move_uploaded_file($_FILES['picture1']['tmp_name'], $uploadfile)) {
    echo "File is valid, and was successfully uploaded.\n";
    } else {
        echo "Possible file upload attack!\n";
    }

    echo 'Here is some more debugging info:';
    print_r($_FILES);
    
    print "</pre>"; 

Both resulted in the else error messages displaying. They are pretty much cut and paste... on the second one it outputs else statement then the word ARRAY for the debugging info.

Oh I also get: Notice: Undefined index: picture1 in /usr/.../pagename.php on line 291 (as the error reporting is on)

Re the shipping price - will come back once I've tested it a bit better... not thinking the best at 2am.

[kows]

make sure you have write privileges for whatever folder you're saving to. if it's returning an error, chances are that it's a permission error.

[buffy]

Just double checked, products folder is still rwx rwx rwx - which I changed it to awhile ago for my current upload method.

[kows]

I didn't see the error you talked about since you edited your post -after- I had posted a reply.

if the picture1 index isn't being created on $_FILES, then your form probably isn't being submitted correctly? also, since you're only checking if that environment variable has been set, maybe do this instead:

if($_SERVER['REQUEST_METHOD'] == "POST" && $_GET['UploadProduct']=='yes'){
       //examples I tried are below
}