Join query on mysql database

Question

i would like to achieve full list of a table and two different name from another table where first table two id is matched with another two table. here is my query:

$sql = 
      "select 
           ls.id, ls.cat_id, ls.sub_id, rs.cat_name, lh.sub_cat_name, 
           ls.prod_name, ls.price, ls.availability, ls.description, ls.img_1, 
           ls.img_2, ls.img_3,ls.img_4, ls.img_5, ls.img_6, ls.img_7, ls.img_8, 
           ls.img_9, ls.img_10, ls.prod_order, ls.type 
         from product as ls 
         left join category as rs on rs.id = ls.cat_id 
         left join subcategory as lh on lh.id = ls.sub_id 
         where ls.id ='".$pid."'";

So the query is working fine. so my question is can this be normalize to be more short query in length?

If you want to get everything from `ls`, does `ls.*` not work? Would make the query a lot shorter. — Geshode, Jul 23 '18 at 05:13
There is nothing to shorten here (the use of select * is not recommended in production code). The query is too simple for that. — Shadow, Jul 23 '18 at 05:41
@Akshay it's very tiresome to write so much field in a query where actually i want everything from my first field. — mentis, Jul 23 '18 at 06:15

score -1 · Accepted Answer · edited Jul 23 '18 at 07:49

-1

Your query

select
  **
  ls.id,
  ls.cat_id,
  ls.sub_id,
  rs.cat_name,
  lh.sub_cat_name,
  ls.prod_name,
  ls.price,
  ls.availability,
  ls.description,
  ls.img_1,
  ls.img_2,
  ls.img_3,
  ls.img_4,
  ls.img_5,
  ls.img_6,
  ls.img_7,
  ls.img_8,
  ls.img_9,
  ls.img_10,
  ls.prod_order,
  ls.type
  **
from product as ls 
  left join category as rs
    on rs.id = ls.cat_id
  left join subcategory as lh
    on lh.id ls.sub_id
where ls.id = {$pid};

So the bold (enclosed in ** **) characters can be replaced with ls.* indicates all the attributes in Tables have been selected so the query looks like

select
  ls.*
from product as ls 
  left join category as rs
    on rs.id = ls.cat_id 
  left join subcategory as lh
    on lh.id = ls.sub_id 
where ls.id ='".$pid."'";

edited Jul 23 '18 at 07:49

gianebao

17,718
3
31
40

answered Jul 23 '18 at 05:45

Supi

16
1

2

You should not use select * in production code. See https://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select – Shadow Jul 23 '18 at 05:58
@DipayanDas You are thanking me even though in my comment I wrote you should not do this. – Shadow Jul 23 '18 at 06:17

Join query on mysql database

1 Answers1