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

So I'm working through my problem. If I comment out the third JOIN and the SELECT SUMs I get results as expected. But, if I include the third JOIN it doesn't produce results? You'll notice the "invoice_items AS lastYearItems" JOIN has a different date. I would expect to see NULL in the LastYearPurchases and LastYearRetail IF the dates didn't exist but they do? What could I be doing wrong? SELECT stores.sName, stores.BillToId, stores.sCustNum, SUM(invoice_items.merchantPrice) AS currYearPurchases, SUM(invoice_items.retailPrice) AS currYearRetail, SUM(lastYearItems.merchantPrice) AS LastYearPurchases, SUM(lastYearItems.retailPrice) AS LastYearRetail FROM invoice_details JOIN stores ON (invoice_details.sCustNum = stores.sCustNum) JOIN invoice_items ON (invoice_details.invoiceNumber = invoice_items.invNumber && STR_TO_DATE(invoice_details.invoiceDate,'%m/%d/%y') BETWEEN '2012-01-01' AND '2012-12-31' ) 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' ) WHERE stores.BillToId = 7161 AND stores.sSalesman = 508 GROUP BY stores.sCustNum

Okay - I have to following queries I'm using to get the data I want form the database. I do get what I'm after but don't think it's the most practical approach and it takes some time to query the results. I would be very grateful if someone could help me combine these into one statement. Maybe it can't be done and I'm going about it the right way? Query One: $query = "SELECT sCustNum, BillToId, sNumber, sSalesman, sName, sAddress FROM stores AS store WHERE store.sSalesman = 508 AND store.BillToId = 7161 GROUP BY store.sCustNum "; Query Two: $query = "SELECT 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.sCustNum = '" . $store->sCustNum . "' AND STR_TO_DATE(invDetails.invoiceDate,'%m/%d/%y') BETWEEN '{$fromDate}' AND '{$toDate}' "; Query Three - Same as Query Two but with different dates: $query = "SELECT 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.sCustNum = '" . $store->sCustNum . "' AND STR_TO_DATE(invDetails.invoiceDate,'%m/%d/%y') BETWEEN '{$fromDateLastYear}' AND '{$toDateLastYear}' ";

Barand, fare enough ... I will work to come up with a good query to explain my problem.

Hello All - I'm new to creating somewhat complicated queries (at least for me they are). I have several tables that have data in them and criteria that needs met in order to complete the query. I was going about this looping through the first Query: SELECT * FROM stores AS store INNER JOIN invoice_details AS invDetails ON store.sCustNum = invDetails.sCustNum WHERE store.sSalesman = 508 AND store.BillToId = 7161 AND STR_TO_DATE(invDetails.invoiceDate,'%m/%d/%y') BETWEEN '2012-01-01' AND '2012-12-31' GROUP BY store.sCustNum Then using the following query to get the purchases from each customer based on the output of the above query. So I was looping through this query to get the SUMS of purchases for each customer. This wasn't working as it was just "stuck" with no output. Damn, I'm really envious of you pros. SELECT SUM(invItems.merchantPrice) AS purchases FROM invoice_items AS invItems INNER JOIN invoice_details AS invDetails ON invDetails.invoiceNumber = invItems.invNumber WHERE invDetails.sCustNum = '{$store->sCustNum}' GROUP BY invDetails.sCustNum It then dawned on me that I may be able to accomplish this using one query. I don't like others to do my work for me but I'm not sure how to approach this? I was looking into multiple SELECTs but again I'm not sure if that's the right direction. Really, thanks for any help on this matter.

Haha, I'm sorry.... duh. Thank you.

Correction... I can use GROUP BY, however I don't think this is the best approach as the Query takes almost 1 minute to complete. Any help would be great. SELECT *, STR_TO_DATE(invDetails.invoiceDate,'%m/%d/%y') AS newDate FROM stores AS store INNER JOIN invoice_details AS invDetails ON store.sCustNum = invDetails.sCustNum GROUP BY store.sCustNum HAVING store.sSalesman = 508 AND store.BillToId = 7161 AND newDate BETWEEN '2011-01-01' AND '2011-12-31'

Thank you Pikachu2000... so I'm wondering what the alternative might be? I can't use HAVING because GROUP BY won't work. Do I need a nested SELECT? If so, what would the syntax look like? Thank you.

Hello - I'm trying to format a date (which works) howerver, I'm getting "Unknown column 'newDate' in 'where clause'" Here's my sql statement: SELECT *, STR_TO_DATE(invDetails.invoiceDate,'%m/%d/%y') AS newDate FROM stores AS store INNER JOIN invoice_details AS invDetails ON store.sCustNum = invDetails.sCustNum WHERE store.sSalesman = 508 AND store.BillToId = 7161 AND newDate BETWEEN '2012-01-01' AND '2012-12-31' GROUP BY store.sCustNum Here's the output WITHOUT the BETWEEN logic ( [id] => 1216 [billToId] => 7161 [sCustNum] => VS7121 [sNumber] => 5263 [sSalesman] => 508 [sName] => CIRCLE K PICKERINGTON [sAddress] => 29 S HILL RD [sCity] => PICKERINGTON [sState] => OH [sZip] => 43147-1218 [sPhone] => 6148379337 [sFax] => [sContact] => [cId] => VS7121 [invoiceNumber] => 097319 [repNumber] => 512 [invoiceDate] => 1/10/11 [payMethod] => 2 [invoiceType] => [invoiceHistory] => 1 [newDate] => 2011-01-10 ) Can't think of whatI might be doing wrong... thanks for any help you can provide me.

Well - I guess I should have done a bit more research. It turns out that you have return values on an asynchronous functions. You have to create a callback function. I've done that and this is what I've come up with which works. If there's a better approach please, by all means, reply. The new callback function: function getSaleman(callback) { var sId; sId = urlParam('sId', window.location.href); var salesmanID = AVDB.readTransaction(function (tx) { tx.executeSql('SELECT * FROM stores WHERE id = ? ', [sId], function (tx, results) { var len = results.rows.length; if(len > 0) { var row = results.rows.item(0); var temp = row.sSalesman; callback(salesmanID); } }); }); } How to call this function and return it's value: getSaleman(function(value){ alert(value); });

Hi guys! Passing values within a function or outside a function has always been a problem for me (to understand). Here's what I need help with. I have the following function that should return a value. Just as an fyi... I'm creating a jQuery mobile application (not that it matters for this problem). So I would like to use the value that is returned in another function but can't seem to get at it. If I alert the value inside the for loop it's fine and I get a value. So I know the value does exist. So somewhere else on the page I'm calling alert(getSaleman()); ... the alert returns undefined? Thanks for any help you can provide me. function getSalesman() { var sId; var salesman; sId = urlParam('sId', window.location.href); AVDB.readTransaction(function (tx) { tx.executeSql("SELECT * FROM stores WHERE id = ?;", [ sId ], function (tx, results) { for (var i = 0; i < results.rows.length; i++) { var row = results.rows.item(i); salesman = row.sSalesman } // for i }) }); return salesman; }