How to only get one object for each row

[Terminaxx]

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 November 6, 2017 by Terminaxx

[Barand]

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)

[benanamen]

@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 November 6, 2017 by benanamen

[Barand]

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.

[benanamen]

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.

[Terminaxx]

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 November 6, 2017 by Terminaxx

[benanamen]

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 November 6, 2017 by benanamen

[Barand]

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

[Terminaxx]

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 November 6, 2017 by Terminaxx

[Barand]

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?

[Terminaxx]

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

[Barand]

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

[Terminaxx]

Got it now. Thanks for the explanations and sorry for annoying you.