making PHP psychic

[StevenOliver]

This will be my worst-ever question posted on this forum 😀

But here goes....

I have a mySQL script which retrieves data.... then my PHP script creates a text document out of it, and ends with:

$output = ob_get_contents();
ob_end_clean();
file_put_contents("document.txt", $output);

The resultant thousand-item list will have some of the items duplicated, with one or more numerals appended, like this:

banana 1
apple 1
apple 1 and 2
lemon 1
blueberry 1
strawberry 1
strawberry 1 and 2
strawberry 1 and 2 and 3
melon 1

When an item is duplicated and suffixed with more than one numeral, I want only the latest duplicated item, like this:

banana
apple 1 and 2
lemon 1
blueberry 1
strawberry 1 and 2 and 3
melon 1

Is there a way to make "file_put_contents" go like "oh, an item was listed more than once, let's only list the latest ("modified") item"?

 

 

[requinix]

No.

If you're not getting the output you want, and your script is responsible for creating that output, then you should be modifying your script to produce what you want. Not trying to clean up after the fact.

[Barand]

If I read it correctly, you start with a table like this

            test_fruit
            +----+------------+
            | id | name       |
            +----+------------+
            |  1 | banana     |
            |  2 | apple      |
            |  3 | strawberry |
            |  4 | lemon      |
            |  5 | blueberry  |
            |  6 | strawberry |
            |  7 | melon      |
            |  8 | apple      |
            |  9 | strawberry |
            +----+------------+

and want to end up with "document.txt" like this

apple 1 and 2
banana 1
blueberry 1
lemon 1
melon 1
strawberry 1 and 2 and 3

Code

$res = $db->query("SELECT name
                        , COUNT(*) as tot
                   FROM test_fruit
                   GROUP BY name     
                  ");
$output = '';

foreach ($res as $r) {
    $output .= $r['name'] . ' ' . join(' and ', range(1, $r['tot'])) . "\n";
}

file_put_contents("document.txt", $output);              

 

[StevenOliver]

My fruit table example was too simplistic (oops), may I please ask from a different angle:

I have a mySQL table with items, descriptions, and sku numbers:

Hammer, red, 5555
Pliers, blue, 2222
Pliers, blue, 1111
Pliers, blue, 8888
Goggles, yellow, 6666

I want similar items (in this case, the blue colored pliers) grouped like this:

Hammer, red, 5555
Pliers, blue, 2222, 1111, 8888
Goggles, yellow, 6666

After several 12 hour days, I cannot figure out how to do this.....

[SaranacLake]

Why not step back and explain what you want in English...

What are you trying to accomplish from a business standpoint?

Figure out the real-life problem you want to solve, and then use PHP/mySQL to try and help automate things.

[StevenOliver]

3 minutes ago, SaranacLake said:

Why not step back and explain what you want in English...

SaranacLake, thank you, but I just did.

Here is the real-life problem I want to solve (and I went ahead and used plain English):

I have a mySQL table with items, descriptions, and sku numbers:

Hammer, red, 5555
Pliers, blue, 2222
Pliers, blue, 1111
Pliers, blue, 8888
Goggles, yellow, 6666

I want the result to look like this, with similar items (in this case, the blue colored pliers) grouped as follows:

Hammer, red, 5555
Pliers, blue, 2222, 1111, 8888
Goggles, yellow, 6666

Edited October 6, 2019 by StevenOliver

[SaranacLake]

I wasn't trying to sound sarcastic, but I still don't follow what you are trying to accomplish in real life.

Why do you want a product name, color and every SKU associated with it on one row?

What happens if you have 25 SKUs of Blue Pliers?

If this is a report, I think there is a better way to communicate things.

Follow me?

[StevenOliver]

SaranacLake, actually, I am trying to accomplish this in real life. You also ask "what happens" if I have blue pliers with 25 sku numbers? All the more reason to have it all on one line:

Pliers, blue, 2222, 1111, 8888, 9999, 3292, 2992

And, you're right, usually there is a better way to communicate things.

Edited October 6, 2019 by StevenOliver

[SaranacLake]

Did you try @Barand's approach?  How did it work out?

 

[Barand]

try

/*
INPUT
mysql> select * from oliver;
+---------+--------+------+
| item    | colour | num  |
+---------+--------+------+
| Hammer  | red    | 5555 |
| Pliers  | blue   | 2222 |
| Pliers  | blue   | 1111 |
| Pliers  | blue   | 8888 |
| Goggles | yellow | 6666 |
+---------+--------+------+
*/

$res = $db->query("SELECT item
                        , colour
                        , GROUP_CONCAT(num SEPARATOR ', ') as nums
                   FROM oliver
                   GROUP BY item, colour     
                  ");
$output = '';

foreach ($res as $r) {
    $output .= "{$r['item']}, {$r['colour']}, {$r['nums']}\n";
}

file_put_contents("document.txt", $output); 

output

Goggles, yellow, 6666
Hammer, red, 5555
Pliers, blue, 2222, 1111, 8888

 

[StevenOliver]

SaranacLake, I'll have to get back to you. I am reading and appreciating Barand's most recent answer. Thank you.

 

Edited October 6, 2019 by StevenOliver

[StevenOliver]

OMG! Barand, you nailed it! (pardon the pun). That works "right out of the box!" (pardon yet another pun)

What worries me is that I am realizing there was (and is) NO WAY I could ever have figured this out on my own. There's nothing on the 'net like this. I don't know how you know this stuff....  but you have my undying respect!

THANK YOU!!

[Barand]

2 minutes ago, StevenOliver said:

I don't know how you know this stuff.

It's all in the manual

[SaranacLake]

6 minutes ago, StevenOliver said:

I don't know how you know this stuff.... 

Many many years of experience...  😉

 

[Barand]

6 minutes ago, StevenOliver said:

There's nothing on the 'net like this

There is an example of GROUP BY and GROUP_CONCAT in the SQL tutorial in my sig