viviosoft

Jessica is correct. The technical term is called "Clean URL's" and you can find many examples out there such as this one to do what you are after. Good luck! http://pixelcode.co.uk/tutorials/Clean+URLs+with+mod_rewrite.html

Hello all! I want to format my post array so that I can insert the data into the database in a particular way such as this: Also, I'm making this dynamic in that I'm not always going to know the column names. So hardcoding the keys is not an option for this solution. INSERT INTO invoice_items (itemCode, itemDesc, itemQty, itemPrice, itemLineTotal) VALUES (1000', Widget0', 10', '25.00', '900.54), (1001', Widget1', 11', '25.01', '900.54), (1002', Widget2', 12', '25.02', '900.54) BUT my current output looks like this. Which is not right of course: INSERT INTO invoice_items (itemCode, itemDesc, itemQty, itemPrice, itemLineTotal) VALUES (1000', '1001', '1003), (Widget', 'Red Hat', 'ioPad with Cover), (1', '2', '3), (100.5', '25.02', '300.18), (25.02', '600.36', '900.54); I don't think I'm that far from having a solution but I can't wrap my head around how to get the output the way I need it. The original $_POST Array: Array ( [itemCode] => Array ( [0] => 1000 [1] => 1001 [2] => 1003 ) [itemDesc] => Array ( [0] => Widget [1] => Red Hat [2] => ioPad with Cover ) [itemQty] => Array ( [0] => 1 [1] => 2 [2] => 3 ) [itemPrice] => Array ( [0] => 100.5 [1] => 25.02 [2] => 300.18 ) [itemLineTotal] => Array ( [0] => 100.50 [1] => 50.04 [2] => 900.54 ) ) Here's the loop that creates the following arrays: $fields = $values = array(); foreach ($post as $column => $value) { $fields[] = $column; $value = implode("', '", $value); $values[$column] = $value; } $columns Array: Array ( [0] => itemCode [1] => itemDesc [2] => itemQty [3] => itemPrice [4] => itemLineTotal ) $values Array: Array ( [itemCode] => 1000', '1001', '1003 [itemDesc] => Widget', 'Red Hat', 'ioPad with Cover [itemQty] => 1', '2', '3 [itemPrice] => 100.5', '25.02', '300.18 [itemLineTotal] => 25.02', '600.36', '900.54 ) Create the sql statement: $query = "INSERT INTO " . $this->table ; $query .= " (" . implode(", ", $fields) . ") "; $query .= "VALUES (" . implode("), (", $values) . "); "; Which outputs this: INSERT INTO invoice_items (itemCode, itemDesc, itemQty, itemPrice, itemLineTotal) VALUES (1000', '1001', '1003), (Widget', 'Red Hat', 'ioPad with Cover), (1', '2', '3), (100.5', '25.02', '300.18), (25.02', '600.36', '900.54); THANK YOU for any help you can provide me!

Thanks anyway, I figured it out. Someone else needing this solution can find it here: var string = '[{"require":true,"minLengthInput":"6"},{"require":true,"emailAddress":"email"}]'; console.log(string.split(/(?={)/)); jsFiddle: http://jsfiddle.net/Ez3b3/72/

I know it's JSON. If I convert the string into an JSON array (object) (which is NOT what I want), then it doesn't perserve the output in the format I want. In stead of telling me what I already know, can someone please just provide help with my original question? So, with that out of the way, this: http://jsfiddle.net/Ez3b3/51/ does close to what I'm after. However, it's returning the }, on the second line and it needs to be on the first line as indicated here which is the output I'm after: [0] = [{"require":true,"minLengthInput":"6"}, [1] = {"require":true,"emailAddress":"email"}]

Haha, no, I know I can convert the string into an object but that's not want I want to do. I want to do what I asked above. I need to perserve the string as-is. var[0] = [{"require":true,"minLengthInput":"6"}, var[1] = {"require":true,"emailAddress":"email"}]

Okay - I'm trying to split the following string while perserving the values I used to split the string. I've tried searching on this and can't seem to figure out how to accomplish what I'm after using examples I've found. Here's what I have so far: var string = '[{"require":true,"minLengthInput":"6"},{"require":true,"emailAddress":"email"}]'; console.log(string.split(/},{(?=},{/)); So I want to perserve the },{ in the objects that come back. The above doesn't work at all but the output I want is: var[0] = [{"require":true,"minLengthInput":"6"}, var[1] = {"require":true,"emailAddress":"email"}] Thanks for any help you can provide me.

I'm sorry... I don't undertand... are you saying that this is correct way to build an object? I think it's missing some commas? var rules = { 'username' : { required: true email: true minLength: 6 maxLength: 20 } 'password' : { required: true minLength: 6 maxLength: 20 } };

Hello - I'm creating a source code generator and have the following code that loops over an array that creates an object. I want to add a , (comma) to the end of each iteration. Here's what the final output should look like: I have everything generating successfully but adding the , (comma) at the end of each item in the object. var rules1 = { 'fullName' : { required: true }, 'username' : { required: true, email: true }, 'password' : { required: true, minlength: 6, maxlength: 20 } }; How would I iterate over the object and add that comma? Also notice that the last item doesn't have a comma. Here's the source code I have to build the object: var rulesArray = []; $(".InputRulesSection").each(function () { $('.rulesTable').each(function () { var ruleInputName = $('.fieldNameInput', this).val(); var minLength = $('.minLengthInput', this).val(); var maxLength = $('.maxLengthInput', this).val(); var email = $(".email", this).attr("rel"); rulesArray.push( { ruleInputName:ruleInputName, minLengthInput:minLength, maxLengthInput:maxLength, email:email } ); }); }); return rulesArray; Here's the code I have to iterate over the newly created object: for (var i in rulesArray) { var ruleInputVal = rulesArray[i].ruleInputName; var email = rulesArray[i].email; var minLength = rulesArray[i].minLengthInput; var maxLength = rulesArray[i].maxLengthInput; rulesCode += "\t\t '" + ruleInputVal + "' : { \n "; rulesCode += "\t\t\t required: true" + addComma + "\n "; if (email == "email") { rulesCode += "\t\t\t email: true" + addComma + " \n "; } if (minLength > 0) { rulesCode += "\t\t\t minLength: " + minLength + addComma + " \n "; } if (maxLength > 0) { rulesCode += "\t\t\t maxLength: " + maxLength + addComma + " \n "; } rulesCode += "\t\t } \n"; } And finally, here's what I currently generate using the above code: Notice there aren't any commas after each item and keys? That's what I'm stumped on... how to approach it? Any help would be great. Thank you! var rules = { 'username' : { required: true email: true minLength: 6 maxLength: 20 } 'password' : { required: true minLength: 6 maxLength: 20 } };

Got it, Thanks again, it's all working as expected!

wow... didn't realize Indexes made that impact on querying data? Thank you. I guess this would have worked MUCH better if I have indexed those in the first place. I'll be sure to clean up the table structure. Sorry. It's working as expected and MUCH faster... thanks again!

So I've added a column invoiceDateYMD and I'm GETTING a result BUT it takes 129 secs to complete and I'm only querying a month? Here's what I have: This is Barand indexing the new column name invoiceDateYMD. Is there a way to speed this up? If the client has a range of a year they could be siting there until Christmas. This makes sense why it's taking so long to get results back... The JOINS are querying the entire database and not WHERE store.sSalesman = 508 AND store.BillToId = 7161 Right? SELECT store.sCustNum, BillToId, sNumber, sSalesman, sName, sAddress, totals1.purchases as purchases1, totals1.retail as retail1, totals2.purchases as purchases2, totals2.retail as retail2 FROM stores AS store LEFT JOIN ( SELECT invDetails.sCustNum, SUM(invItems.merchantPrice) AS purchases, SUM(invItems.retailPrice) AS retail FROM invoice_items AS invItems JOIN invoice_details AS invDetails ON invDetails.invoiceNumber = invItems.invNumber WHERE invDetails.invoiceDateYMD BETWEEN '2012-01-01' AND '2012-01-31' GROUP BY invDetails.sCustNum ) as totals1 ON store.sCustnum = totals1.sCustNum LEFT JOIN ( SELECT invDetails.sCustNum, SUM(invItems.merchantPrice) AS purchases, SUM(invItems.retailPrice) AS retail FROM invoice_items AS invItems JOIN invoice_details AS invDetails ON invDetails.invoiceNumber = invItems.invNumber WHERE invDetails.invoiceDateYMD BETWEEN '2011-01-01' AND '2011-01-31' GROUP BY invDetails.sCustNum ) as totals2 ON store.sCustnum = totals2.sCustNum WHERE store.sSalesman = 508 AND store.BillToId = 7161 GROUP BY store.sCustNum

Thanks guys... so I'm wondering if this will solve my initial problem? I think I'm going to try option 3 and update the NEW field column with the new date and see where that gets me.

I understand... I'm using data that is imported from another system (Access) which the dates are stored as m/d/y. I'm wondering if I could write something to convert the date to a datetime format when importing? Currently the data is brought in using a simple CSV import with no "clean-up". The data I'm importing is historical... moving forward the data is saved as DATE datatype. Thank you for your input. I would welcome any suggestions on how I could import the historical data converting the invoiceDate field using MySQL? My guess would be that I'd have to create an import method of sorts? Thanks again!

Hi Barand - Thanks for helping on this issue. I'm learning which is good. I've played around with the solution you included and it seems logical, howerver, when I put actual dates in the BETWEEN clause it just crunches (forever) and doesn't complete. Or I'm not patient enough to wait but should it really take that long? Note: If I leave the date fields blank I get all columns with expected results but NULL for Purchase1, Retail1, Purchase2 and Retail2 I don't know of any other way of testing this issues but sending you the data. I've been using MySQL Workbench to do my testing. If you'd prefer, send me a message with your email address and I'll send you a dump of the database. I would attach the data to this post but the database is very large. 6 MB compressed. Thanks again!

I've changed the query a bit but still not able to get results? SELECT stores.sName, stores.BillToId, stores.sCustNum, invoice_details.invoiceDate, ROUND(SUM(currYearItems.merchantPrice),2) AS currYearPurchases, ROUND(SUM(currYearItems.retailPrice),2) AS currYearRetail, ROUND(SUM(lastYearItems.merchantPrice),2) AS LastYearPurchases, ROUND(SUM(lastYearItems.retailPrice),2) AS LastYearRetail FROM stores LEFT JOIN invoice_details ON (invoice_details.sCustNum = stores.sCustNum) JOIN invoice_items AS lastYearItems ON (invoice_details.invoiceNumber = lastYearItems.invNumber && STR_TO_DATE(invoice_details.invoiceDate,'%m/%d/%y') BETWEEN '2011-01-01' AND '2011-12-31' ) JOIN invoice_items AS currYearItems ON (invoice_details.invoiceNumber = currYearItems.invNumber && STR_TO_DATE(invoice_details.invoiceDate,'%m/%d/%y') BETWEEN '2012-01-01' AND '2012-12-31' ) WHERE stores.BillToId = 7161 AND stores.sSalesman = 508 GROUP BY stores.sCustNum