How to use str_split ()?

Question

I am currently working in my project and my problem starts here : I am inserting in a table and it insert correctly but the problem is it look so messy because in one id theres a lot of reference numbers they are just separated by commas , and what I want to do is use str_split to separate it...

Here is my code:

$sql = "INSERT INTO transaction_detail (`transaction_id`,`ref_number`)
          VALUES ('$transaction_id','$ref_number') ";

    $query = $conn->query($sql);

How can I do it? I also want to use while loop because I don't know the maximum number of ref_number? Can somebody help me? Thanks :)

UPDATE

id  transaction_id ref_number
1           12      12411235435
2           12      214354657
3           12      2153564657

I want that my output will look something like this because my current output look something like this.

id    transaction_id    ref_number
1     12                12411235435,214354657,2153564657

UPDATE

$ref_array = explode(',' , $ref_number);
$po_array = explode(',' , $po_number);
$inv_array = explode(',' , $inv_number);
$asn_array = explode(',' , $asn_number);
$adj_array = explode(',' , $adj_number);
$amount_array = explode(',' , $amount);

// count the number of po,invoice,asn and adj
if(count($po_array) != count($ref_array) || count($inv_array) != count($ref_array) || count($asn_array) != count($ref_array) || count($adj_array) != count($ref_array) || count($ref_array) != count($amount_array)){

    foreach ($ref_array as $i => $ref_num){
        $po_num = isset($po_array[$i]) ? $po_array[$i] : '' ; //leave blank there is no $po_array[$i]
        $inv_num = isset($inv_array[$i]) ? $invoice_array[$i] : '';
        $asn_num = isset($asn_array[$i]) ? $asn_array[$i] : '' ;
        $adj_num = isset($adj_array[$i]) ? $adj_array[$i] : '' ;
        $amount_num = isset($amount_array[$i])? $amount_array[$i] : '';



        if(intval($ref_num) != 0 ){

            $conn->query ("INSERT INTO transaction_detail (`transaction_id`,`ref_number`,`po_number`,`inv_number`,`asn_number`,`adj_number`,`amount`)
                    VALUES ('$transaction_id','$ref_num','$po_num','$inv_num','$asn_num','$adj_num','$amount_num') " );
        }
    }  
}

Here are the errors :

[29-Jun-2015 04:20:59 Europe/Berlin] PHP Notice: Undefined offset: 1 in C:\xampp\htdocs\WebService\webservice_revised.php on line 83

[29-Jun-2015 04:20:59 Europe/Berlin] PHP Notice: Undefined offset: 2 in C:\xampp\htdocs\WebService\webservice_revised.php on line 83

[29-Jun-2015 04:21:11 Europe/Berlin] PHP Notice: Undefined offset: 1 in C:\xampp\htdocs\WebService\webservice_revised.php on line 83

[29-Jun-2015 04:21:11 Europe/Berlin] PHP Notice: Undefined offset: 2 in C:\xampp\htdocs\WebService\webservice_revised.php on line 83

[29-Jun-2015 04:21:11 Europe/Berlin] PHP Notice: Undefined offset: 3 in C:\xampp\htdocs\WebService\webservice_revised.php on line 83

[29-Jun-2015 04:21:11 Europe/Berlin] PHP Notice: Undefined offset: 4 in C:\xampp\htdocs\WebService\webservice_revised.php on line 83

[29-Jun-2015 04:21:11 Europe/Berlin] PHP Notice: Undefined offset: 5 in C:\xampp\htdocs\WebService\webservice_revised.php on line 83

[29-Jun-2015 04:21:11 Europe/Berlin] PHP Notice: Undefined offset: 6 in C:\xampp\htdocs\WebService\webservice_revised.php on line 83

My guess is that you have to work on the input,why do you have comma delimited values in the first place — Mihai, Jun 26 '15 at 06:48
Depending on where you get $transaction_id and $ref_number from you should be careful not to expose yourself to sql injection. — Marged, Jun 26 '15 at 06:49
Use explode instead of str_split, you can define your delimiter in explode. Find detail here http://php.net/manual/en/function.explode.php. It returns an array , which you can iterate. — Ramesh Dahiya, Jun 26 '15 at 06:51
[Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — Barmar, Jun 26 '15 at 07:08
Your question isn't clear. The database has commas and you want to show them on separate lines, or the database has them in separate rows and you want to show them on one line with commas? — Barmar, Jun 26 '15 at 07:09
In my database it is showing in one line separated by commas and I wnat it to be inserted like the example above in my questions — Errorhere, Jun 26 '15 at 07:14

Barmar · Accepted Answer · 2015-07-17T03:13:31.930

3

Use explode() to split up the comma-separated reference numbers, and loop over them with a foreach.

foreach (explode(',', $ref_number) as $refnum) {
    $conn->query("INSERT INTO transaction_detail (`transaction_id`,`ref_number`)
                  VALUES ('$transaction_id','$refnum') ";
}

To include the PO number, explode that variable into another array, and use the index in the foreach loop to access it:

$transaction_id = '123456';
$po_number = '1,2';
$ref_number = '11,22,33,44,55,66';

$po_array = explode(',', $po_number);
$ref_array = explode(',', $ref_number);

foreach ($ref_array as $i => $refnum) {
    $ponum = isset($po_array[$i]) ? $po_array[$i] : '';
    if (intval($refnum) != 0) {
        $conn->query("INSERT INTO transaction_detail (`transaction_id`,`ref_number`, `po_number`)
                      VALUES ('$transaction_id','$refnum', '$ponum') ");
    }
}

I tested the above code, it performs the following queries, with no errors or notices from PHP:

INSERT INTO transaction_detail (`transaction_id`,`ref_number`, `po_number`) VALUES ('123456','11', '1') 
INSERT INTO transaction_detail (`transaction_id`,`ref_number`, `po_number`) VALUES ('123456','22', '2') 
INSERT INTO transaction_detail (`transaction_id`,`ref_number`, `po_number`) VALUES ('123456','33', '') 
INSERT INTO transaction_detail (`transaction_id`,`ref_number`, `po_number`) VALUES ('123456','44', '') 
INSERT INTO transaction_detail (`transaction_id`,`ref_number`, `po_number`) VALUES ('123456','55', '') 
INSERT INTO transaction_detail (`transaction_id`,`ref_number`, `po_number`) VALUES ('123456','66', '')

If you could also have fewer ref numbers than po numbers, you could use this code to use the longer array for the loop:

$po_array = explode(',', $po_number);
$ref_array = explode(',', $ref_number);
$limit = max(count($po_array), count($ref_array));

for ($i = 0; $i < $limit; $i++) {
    $ponum = isset($po_array[$i]) ? $po_array[$i] : '';
    $refnum = isset($ref_array[$i]) ? $ref_array[$i] : '';
    $conn->query("INSERT INTO transaction_detail (`transaction_id`,`ref_number`, `po_number`)
                    VALUES ('$transaction_id','$refnum', '$ponum') ");
}

edited Jul 17 '15 at 03:13

answered Jun 26 '15 at 07:14

Barmar

741,623
53
500
612

hey,,, as what stated in the example code it is just the ref_number that you foreach, means this only base on ref_number ,, how about if I only got 1 ref_number and many po_number how can I possibly do it? – Errorhere Jul 17 '15 at 03:04
Change the `foreach` to user `$po_array` instead of `$ref_array`. – Barmar Jul 17 '15 at 03:07
If you only have 1 ref_number and many po_number, what should be put into the `ref_number` column for the missing numbers? – Barmar Jul 17 '15 at 03:10
I added more code showing how to loop over the number of elements in the longer list. – Barmar Jul 17 '15 at 03:14
You're never going to be a successful programmer if you need someone to spoon feed you every little change to code. You need to be able to figure this kind of thing out on your own or you're doomed. – Barmar Jul 17 '15 at 03:15
yeah you're right, but what i am thinking is just to compare my code with you,,and it looks like is really different..... thanks a lot ,, especially for your advice ,, – Errorhere Jul 17 '15 at 03:48
just want to ask this,,, why is that after being inserted all the possible values in ref_number and po_number ,, there is blank, before it start to insert again for another ref_number and po_number – Errorhere Jul 17 '15 at 08:07
It's not doing all the combinations. It's just pairing them up sequentially: ref 1 with po 1, ref 2 with po 2, ref 3 with po 3, etc. If there are fewer of one that the other, it uses blanks for them. – Barmar Jul 17 '15 at 08:13
Your English is so bad I can't tell what you mean. Please update the question with what you want to be inserted. – Barmar Jul 17 '15 at 09:17

How to use str_split ()?

1 Answers1