Jump to content
Terminaxx

How to only get one object for each row

Recommended Posts

Hey guys,

 

I got a table something like this:

 

ID            name           price      quantity

1             name1           40            5

2             name1           50            6

3             name2           30            7

4             name2           40            9

5             name1           60           11

 

 

I want to only get the objects with the lowest price from everywhere where the name is the same.

So for example, if we look at the table above i want to get    name1 (price 40 - quantity 5) and name2 (price 30 - quantity 7)

 

How can I manage to do this?

 

I tried this, but this could be somewhere belong wrong:

$res = mysqli_query($con, "SELECT * FROM shop WHERE MIN(price / name)");

Thanks for any help

Edited by Terminaxx

Share this post


Link to post
Share on other sites

Use a table subquery which has the min price and name of each item then join to this matching on name and price.

SELECT s.name
   , s.price
   , s.quantity
FROM 
   shop s
   JOIN
   (  SELECT name
          , MIN(price) as price
      FROM shop
      GROUP BY name
   ) mins
   USING (name, price)

Share this post


Link to post
Share on other sites

@Barand,

 

is there any problem doing it like this?

SELECT 
   id, `name`, quantity, MIN(price) price
FROM
    shop
GROUP BY name;

OR

SELECT
a.id,
a.`name`,
a.price,
a.quantity
FROM
shop a
WHERE
a.price = (
        SELECT 
            MIN(b.price)
        FROM
           shop b WHERE a.`name` = b.`name`)
Edited by benanamen

Share this post


Link to post
Share on other sites

Yes.

 

For every row you are running a (dependent) subquery to fetch the min price for that name to see if the price matches.

 

Run EXPLAIN on the query. If you see the phrase DEPENDENT SUBQUERY there is generally a better option.

Share this post


Link to post
Share on other sites

Ok, I see that on the second query. Good to know.

 

What about the first query? I do see that it will only work if min(price) is last though.

Share this post


Link to post
Share on other sites

Thanks for the answers but I kinda dont understand the code (im quite new to this and just try to learn)

 

Like I do not understand what you mean by s.price etc. - what is the s for? Why select something from shop s?

 

I understood it like this:

$res = mysqli_query($con, "SELECT * FROM shop");

                            while($row1 = mysqli_fetch_object($res)) {
                                $name1 = $row1->name;
                                $price1 = $row1->price;
                                
                                
                            $res2 = mysqli_query($con, "SELECT * FROM shop JOIN ( SELECT name, MIN(price) as price FROM shop GROUP BY name ) mins USING ($name1, $price1)");
                                while($row = mysqli_fetch_object($res2)) {
                                
                                $id = $row->id;
                                $img = $row->img;
                                $owner = $row->number;
                                $name = $row->name;
                                $price = $row->price;
                                $quantity = $row->quantity;
                                }                            

But I am pretty sure I did it wrong. 
Excuse me for my stupid question - it might sound clear to someone who isnt new to this.

 

Thanks in advance

Edited by Terminaxx

Share this post


Link to post
Share on other sites

The s is just an alias for the table. Nobody said to write two queries. @Barand gave you the query you asked for which you didn't even copy correctly? Why did you change it and throw variables into it? All you had to do was copy/paste the query.

 

Do not SELECT *, specify the column names like you were shown. Do not create variables for nothing.

Edited by benanamen

Share this post


Link to post
Share on other sites

@benanamen - in your first query there is no guarantee that the quantity value returned will belong to the record with the min price. It will be an arbitrary value from any member of the group.

 

@terminaxx - all you need is the single query that I gave you, although you may want add to the SELECT the extra columns that you didn't mention.

 

The "S" is an alias for the table "shop". Because the shop table and the subquery both have column "name" then we specify s.name to remove ambiguity about which one we want. The short aliases make it more readable then prefixing column names with table names.

Share this post


Link to post
Share on other sites

Thanks, I understood it now. But I gotta annoy you one last time:

<?php
$res = mysqli_query($con, "SELECT s.name
   , s.price
   , s.quantity
   , s.img
   , s.id
   , s.owner
FROM 
   shop s
   JOIN
   (  SELECT name
          , MIN(price) as price
      FROM shop
      GROUP BY name
   ) mins
   USING (name, price)");
                        
                            $furnis = array();
                            $names = array();
                            while($row = mysqli_fetch_object($res)) {
                                $id = $row->id;
                                $img = $row->img;
                                $owner = $row->number;
                                $name = $row->name;
                                $price = $row->price;
                                $quantity = $row->quantity;
                                
                                if(!in_array($name, $names)) {
                                    $f = new furnipack();
                                    $f->img = $img;
                                    $f->quantity = $quantity;
                                    $f->name = $name;
                                    $f->price = $price;
                                    $f->id = $id;

                                    array_push($furnis, $f); 
                                    array_push($names, $name); 
                                }
                                }
                            }

                            foreach ($furnis as $f) {
                                    echo "<h3>$f->name</h3>";
                                    echo "<h5>".number_format($f->price, 0, ",", ".")."</h5>";
                                    echo "$f->quantity x";
                                        echo "<input name='quantity[$f->id]' type='number' value='1' min='1' max='$f->quantity'>";
                                        echo "<button type='submit' name='buy' value='$f->id'>Buy</button>";
                                }
                            }
                        ?> 

I got the total code now something similar like this.

I mean, I still gotta put the results in variables right? How else would i echo them

 

What did I do wrong - is it because of the "if(!in_array($name, $names))" code? or did i mistake something once again with your code?

Edited by Terminaxx

Share this post


Link to post
Share on other sites

You could try this, to save most of the coding

while ($f = mysql_fetch_object($res, "furnipack") {
    $furnis[] = $f;
}
You will only get duplicate names if there are more than one record with the same lowest price. How do you decide which you want in this event?

Share this post


Link to post
Share on other sites

It still isn't working. Not sure what I am doing wrong. Could you add the code up, as i should have put it in to check  if I got it correctly?

Would be awesome!

 

I got the above part from my latest version, where I didnt want to have them seperated.

 

 

I am kinda confused about all the code here...

Share this post


Link to post
Share on other sites

I've given you the query. I've given you the processing of the query. What more do you want?

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.