Jump to content

viviosoft

Members
 View Profile  See their activity

  • Posts

    85

  • Joined

  • Last visited

 Content Type 

Posts posted by viviosoft

    Formatting Array Output

    in PHP Coding Help

    Posted · Edited by viviosoft

    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!

    Split() perserving the values split with

    in Javascript Help

    Posted · Edited by viviosoft

    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"}]

    Split() perserving the values split with

    in Javascript Help

    Posted · Edited by viviosoft

    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"}]

    Split() perserving the values split with

    in Javascript Help

    Posted

    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.

    Loop through array appending data to each iteration

    in Javascript Help

    Posted · Edited by viviosoft

    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 
 		 } 
	 };

    Sum() In Multiple Tables

    in MySQL Help

    Posted

    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!

    Sum() In Multiple Tables

    in MySQL Help

    Posted · Edited by viviosoft

    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

    Sum() In Multiple Tables

    in MySQL Help

    Posted · Edited by viviosoft

    Since you are doing a conversion (STR_TO_DATE) on the date column, I'm pretty sure the index would be useless. You need to store the dates as a DATE datatype. Then an index could be used for the BETWEEN. As it is, the server is going to do a table scan (read through every row in the table) for both of the derived tables (the LEFT JOIN sub-queries), and convert every invoice date in the database (twice since you have two joins).

     

    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!

    Sum() In Multiple Tables

    in MySQL Help

    Posted

    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!

    Sum() In Multiple Tables

    in MySQL Help

    Posted

    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

    Sum() In Multiple Tables

    in MySQL Help

    Posted

    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

    Sum() In Multiple Tables

    in MySQL Help

    Posted

    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}'
		  ";

    Sum() In Multiple Tables

    in MySQL Help

    Posted

    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.

    Str_To_Date Unknown Column Issue

    in MySQL Help

    Posted · Edited by viviosoft

    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'

    Str_To_Date Unknown Column Issue

    in MySQL Help

    Posted · Edited by viviosoft

    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.

    returning a value

    in Javascript Help

    Posted

    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);
        });

     

    returning a value

    in Javascript Help

    Posted

    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;

}

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.