In this session we go through the process of obtaining and formatting the tax rates and discuss the need to be in a specific format in order to work in PHP. We show how to create a variable after a return statement and a string of cities and values.
That one little part of it is working. Now we do the fun part which is getting all the tax rates. We do that by going to… let’s see, California sales tax rates. Okay so we got these tax rates effective July 1st, 2002. Here’s a list of 2000 places. What we want to do is download it as a CSV file and I’m missing where that is here.
Okay so here is the Excel format which will download that city rates. Open it up in Microsoft and what we’re going to do is format the data so that we can use it because right now, it’s not particularly useful. We want to be able to cut it and paste it easily.
What we have here… let’s see, let’s add a new… enable editing. Okay so we’re going to add a new sheet and this sheet is going to… let’s see, pardon me. We are going to concatenate… let’s see, formula, text, concatenate. This needs to be in a very specific format in order to work in PHP. If we look at our thing right here, this is what the format that we’re going to take the data and turn into. So it’s going to be a double quote. Actually, we could do it as a single quote. We’ll probably do that. Single quote, name of the city, single quote space equals greater than sign, space and then the number.
Okay and so in order to put text in, you have to wrap it in double quotes. So it’s a double quote and a single quote and then a double quote. Then the actual field that we want which is Acampo and then a double quote and a single quote and a space and then equal sign and a greater than sign and a space and then close that up in double quotes. And then the other field except obviously that other field is in the wrong format. Oh actually, it’s just fine like that. Perfect, so we say okay to that.
So now you can see we have our thing set up, Acampo in single quotes, greater than whatever. Now what we have to do is just go all the way down for a couple thousand. That’s going to copy this formatting and… pardon me, I don’t have to go that far. Okay, let’s see where we are now.
Okay, so there we go. We have our list of city names and tax rates formatted and we can just about, I think, copy that, open up a text file and paste it. No, I guess we can’t do it that way. I guess what we have to do is this. Actually, I think we have to move its values. That’s what we have to do. Okay, control copy and then we’re just going to paste its value. Now we can copy it, come back over to this and paste it, I think. Okay, perfect.
Now we have a list that’s formatted properly as a text file that has all of the cities in this like it should. So now we’re going to copy it and copy that and then come on over to our function. You know, I think what I want to do is I don’t want to load this into memory unless it’s absolutely necessary. So what I’m going to do then is I’m going to create my variable right here after the return statement.
So if it’s not California, it’s not going to load the rest of the stuff into the memory. It’ll just go away. If it is California then it’ll move on and we’ll say local rates equals array. Then we’re going to paste that array there. Oh no, I screwed that up. We’re not finished with this formatting yet because in fact, we need a comma after each of these things.
Actually, we’re just going to have to do the concatenation thing one more time. We’re going to concatenate this with comma. Say okay. Now it’s got a comma… oh, that’s a period. I want a comma. Okay so now we’re going to just run that all the way down to 1800, 1779. Copy that, paste the values, copy it again. Don’t save it. Okay, there we go. Now it’s ready to go. Bring it over properly formatted now and the last one does not need a comma after it.
Obviously, we’ve got a little syntax error here some place. Oh yeah, look at that. There was a comma in the middle of… let’s see. There is another one of those so I think I need to keep that one so I’m going to do an escape character there. That’s not the escape character. It’s the other way, isn’t it? There we go. O’Brien and then O’Neal’s so let that be a lesson to you. You need to make sure that you escape those apostrophes in the names. Okay, why does that have a… oh, it needs a semi-colon closing it out.
Let’s okay. What we’ve done here is we’ve created an array of values of city and rate values. Local rates equals array… oh, it’s a dollar sign. It’s a variable, $. Local rates equals array and let’s just jump that over one more so they all line up. The way we’re writing this, this is only going to get loaded when the resident’s actually in California. Otherwise, it’s not going to bother with it. Okay, local rates equals array and then we have a string of cities and values.