Jump to content
StevenOliver

making PHP psychic

Recommended Posts

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"?

 

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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.....

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
Posted (edited)
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 by StevenOliver

Share this post


Link to post
Share on other sites

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?

  • Like 1

Share this post


Link to post
Share on other sites
Posted (edited)

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 by StevenOliver

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites
Posted (edited)

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

 

Edited by StevenOliver

Share this post


Link to post
Share on other sites

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!!

Share this post


Link to post
Share on other sites
6 minutes ago, StevenOliver said:

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

Many many years of experience...  😉

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • 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.