Jump to content

How to only get one object for each row


Terminaxx

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

Link to comment
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

Link to comment
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.

Link to comment
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.

Link to comment
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?

Link to comment
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...

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

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