current community

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

I have the following multidimensional javascript array.

var accounts =
    [
        { name: '069070474', crd_no:
        [
            {name: '0215020174357351', ssn_no: ['582238797'] }
        ]},
        { name: '089255135', crd_no:
        [
            {name: '0215020178346863', ssn_no: ['583872782','874514213']}
        ]},
        { name: '123456789', crd_no:
        [
            {name: '8888888888888888', ssn_no: ['122121212']}
        ]},
        { name: '131274740', crd_no:
        [
            {name: '0215020178888432', ssn_no: ['478498545','584586942']}
        ]},
        { name: '454296191', crd_no:
        [
            {name: '0215020178896484', ssn_no: ['582214564']}
        ]},
        { name: '987654321', crd_no:
        [
            {name: '8888888888888888', ssn_no: ['122121212']}
        ]}
    ];

And I also have the above data in a mysql table with the following schema:

TABLE `profile_id`
(
`acct_no` varchar(19) NOT NULL COMMENT 'Customer account no',
`crd_no` varchar(19) NOT NULL COMMENT 'Customer card no',
`ssn_no` varchar(9) NOT NULL COMMENT 'Customer social security number',
PRIMARY KEY  (`acct_no`,`crd_no`,`ssn_no`)
)

What I'm trying to do is using php retrieve the data from the mysql table, and create either a multidimensional array or a json encoded string that matches my existing javascript array. I want to have the array built in a loop so as new records are added to the table, the array will constantly be updated.

Exact code is not a necessity (but it is welcome), I am merely looking for suggestions on how to approach this. I can get the array built and filtered on unique combinations of the acct_no and crd_no or all three, but I have yet to get the array built in php looking exactly like how I have it in javascript. The ultimate goal is to feed the js array to a set of three drop down boxes. Each box's selected value will determine the next box's data selection list. Now I know I can set this up by using ajax to query the db when a selection is made from each box, but I am avoiding having to hit the server on multiple occasions. Any other recommendations on getting this done is also a plus.

The following is the javascript code that manipulates the data.

    $(document).ready(function()
    {
        document.soapFormSetup.reset();

        $(function()
        {
            var start = '';
            var options = '<option selected value="'+ start +'">-----------------------' + '</option>' ;

            for (var i = 0; i < accounts.length; i++)
            {
                var opt = accounts[i].name ;

                options += '<option value="' + opt + '">xxxxxxxxxxxxxx' + opt.substring(5) + '</option>';
            }

            $("#sms_acct_no").html(options);

            start = '';
            options = '<option selected value="'+ start +'">-----------------------' + '</option>' ;

            for (var i=0; i < accounts[0].crd_no.length; i++)
            {
                var opt = accounts[0].crd_no[0].name ;

                options += '<option value="' + opt + '">xxxxxxxxxxxxxx' + opt.substring(12) + '</option>';
            }

            $("#sms_crd_no").html(options);

            start = '';
            options = '<option selected value="'+ start +'">--------------' + '</option>' ;

            for (var i=0; i < accounts[0].crd_no[0].ssn_no.length; i++)
            {
                var opt = accounts[0].crd_no[0].ssn_no[i] ;

                options += '<option value="' + opt + '">xxx-xx-' + opt.substring(5) + '</option>';
            }

            $("#sms_ssn_no").html(options);

            document.soapFormSetup.sms_ssn_no.disabled=true;
            document.soapFormSetup.sms_crd_no.disabled=true;

            $("#sms_acct_no").bind("change",
            function()
            {
                if ( $(this).children(":selected").val() !== "" )
                    document.soapFormSetup.sms_crd_no.disabled=false;
                else
                {
                    document.soapFormSetup.sms_crd_no.value="";
                    document.soapFormSetup.sms_ssn_no.value="";

                    document.soapFormSetup.sms_crd_no.disabled=true;
                    document.soapFormSetup.sms_ssn_no.disabled=true;
                }

                for(var i=0; i<accounts.length; i++)
                {
                    if (accounts[i].name == this.value)
                    {
                        start = '';
                        var crd_nos = '<option selected value="'+ start +'">-----------------------' + '</option>' ;

                        for (var j=0; j < accounts[i].crd_no.length; j++)
                        {
                            var opt= accounts[i].crd_no[j].name ;

                            crd_nos += '<option value="' + opt + '">xxxxxxxxxxxxxx' + opt.substring(12) + '</option>';
                        }

                        break;
                    }
                }

                $("#sms_crd_no").html(crd_nos);

                for(var i=0; i<accounts.length; i++)
                {
                    for(var j=0; j<accounts[i].crd_no.length; j++)
                    {
                        if(accounts[i].crd_no[j].name == $("#sms_crd_no").val())
                        {
                            start = '';
                            var crd_ssn_nos = '<option selected value="'+ start +'">--------------' + '</option>' ;

                            for (var k=0; k < accounts[i].crd_no[j].ssn_no.length; k++)
                            {
                                var opt = accounts[i].crd_no[j].ssn_no[k] ;

                                crd_ssn_nos += '<option value="' + opt + '">xxx-xx-' + opt.substring(5) + '</option>';
                            }

                            break;
                        }
                    }
                }

                $("#sms_ssn_no").html(crd_ssn_nos);

                document.soapFormSetup.sms_ssn_no.disabled=true;
                document.soapFormSetup.sms_ssn_no.value="";
            });

            $("#sms_crd_no").bind("change",
                function()
                {
                    if ( $(this).children(":selected").val() !== "" )
                        document.soapFormSetup.sms_ssn_no.disabled=false;
                    else
                    {
                        document.soapFormSetup.sms_ssn_no.value="";

                        document.soapFormSetup.sms_ssn_no.disabled=true;
                    }

                    for(var i=0; i<accounts.length; i++)
                    {
                        for(var j=0; j<accounts[i].crd_no.length; j++)
                        {
                            if(accounts[i].crd_no[j].name == this.value)
                            {
                                start = '';
                                var ssn_nos = '<option selected value="'+ start +'">--------------' + '</option>' ;

                                for (var k=0; k < accounts[i].crd_no[j].ssn_no.length; k++)
                                {
                                    var opt = accounts[i].crd_no[j].ssn_no[k] ;

                                    ssn_nos += '<option value="' + opt  + '">xxx-xx-' + opt.substring(5)  + '</option>';
                                }

                                break;
                            }
                        }
                    }

                    $("#sms_ssn_no").html(ssn_nos);
                });
            });
    });
share|improve this question
 
Why not use all objects instead of mixed objects an arrays? It'll be much closer to the json syntax and IMO more visually appealing and readable. –  elclanrs May 12 '12 at 20:48
 
each account number has 1 or more cc numbers, and each cc number has 1 or more ss numbers? Is the always the correct relationship for your data? –  rambo coder May 12 '12 at 21:03
 
The more I think about this the more appealing it gets....thanks for the suggestion –  user1391491 May 12 '12 at 21:03
 
@chris...the correct relation is that an account can have multiple cc numbers, but each cc number will have a unique ssn no. This is for the case of joint accounts, where you have two individuals sharing the same bank account, and they each have a card number tied to that account, but they have separate ssn no's –  user1391491 May 12 '12 at 21:04
 
ok, it conflicts with the sample data where you showed ssn_no: being an array w/ 2 seperate entries. –  rambo coder May 12 '12 at 21:08
show 4 more comments

2 Answers

up vote 1 down vote

I believe the following code will produce an identical, JSON encoded string:

$accounts = array();
while($row = mysql_fetch_assoc($query)) {
    $accounts[] = array(
        "name"   => $row["acct_no"],
        "crd_no" => array(
            "name"   => $row["crd_no"],
            "ssn_no" => $row["ssn_no"]
        ) 
    );
}
echo json_encode($accounts);
share|improve this answer
 
This is close but still does not show exact relationships...where you can have an account no that has the same ard no's but different ssn nos. Thanks though this is pointing in the right direction. –  user1391491 May 12 '12 at 21:02
 
OK, can you tell me what fields of your db map to which portion of your JSON? –  Salman A May 12 '12 at 21:36
add comment
up vote 0 down vote

Presumably, a single account name may be associated with multiple cards (otherwise, why would crd_no be an array) so try this:

// Create an array keyed by acct_no, containing arrays of crd_no data
$accounts = array();
while ($row = mysql_fetch_assoc($query)) {
    if (!isset($accounts[$row["acct_no"]])) {
       $accounts[$row["acct_no"]] = array();
    }
    $accounts[$row["acct_no"]][] = array(
        "name"   => $row["crd_no"],
        "ssn_no" => $row["ssn_no"]
    );
}

// but it's not the format you want so now...
$accounts_formatted = array();
foreach ($accounts as $name => $account) {
    $accounts_formatted[] = array(
        "name" => $name,
        "crd_no" => $account
    );
}
echo json_encode($accounts_formatted);
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.