checking a id is present in array stored data

Question

This is the extended question of THIS

I am checking an user entered id is presented in a stored array with comma operator.

i have the query like this

$sql="SELECT 
  vendor.id, 
  vendor.vendor_id AS VID, 
  vendor.name AS VNAME, 
  vendor.category, 
  vendor.website, 
  vendor.email, 
  vendor.phone, 
  vendor.locations, 
  vendor.products, 
  vendor.vat, 
  vendor.pan, 
  location.loc_id, 
  location.name AS locname, 
  items.iid, 
  items.name 
  FROM vendor 
  INNER JOIN location ON vendor.locations = location.loc_id,items
  WHERE 
  items.iid IN (vendor.products) AND 
  items.iid='".$product."' AND 
  vendor.id=".$vendor;

Here vendor.products stored data in 1,3,5 format. and entered data i e $products will have single id.

Whats wrong in my query as it doenst returns any data

EDITED I have added one more column to my items table called iid which is also of type Varchar(string), Now i stored these values in vendor.products table like this ITM004,ITM003,ITM005, still the query doesn't show any result :(

plz echo this query in php page and paste it here.. . Did you get any errors when executing the above? — Anish, Dec 18 '14 at 05:29
`SELECT vendor.id, vendor.vendor_id AS VID, vendor.name AS VNAME, vendor.category, vendor.website, vendor.email, vendor.phone, vendor.locations, vendor.products, vendor.vat, vendor.pan, location.loc_id, location.name AS locname, items.item_id, items.name FROM vendor LEFT JOIN items ON vendor.products = items.item_id INNER JOIN location ON vendor.locations = location.loc_id WHERE items.item_id IN (vendor.products) AND items.item_id=5 AND vendor.id=2` — user2823107, Dec 18 '14 at 05:30
Please! Stop immediately and read this: [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/q/3653462/1446005). — RandomSeed, Dec 18 '14 at 17:25

score 0 · Answer 1 · answered Dec 18 '14 at 05:15

0

You have an error in your SQL syntax, also a PHP concatentation error:

 AND vendor.id=".$vendor.""

You don't need ."" at the end because it's the last part of the statement. HOWEVER, if $vendor is a string, you will need to string encapsulate it.

AND vendor.id='".$vendor."';

Otherwise, if it's an INT, it's just:

AND vendor.id=".$vendor;

answered Dec 18 '14 at 05:15

Ohgodwhy

49,779
11
80
110

` AND vendor.id=".$vendor.""` here last `"` is for closing of my `SELECT` statement. If i remove it, it throws error – user2823107 Dec 18 '14 at 05:21
@user2823107 No. You closed your `select STRING` before that when you broke the interpreter in order to concatenate. Remove the `.""` at the end. – Ohgodwhy Dec 18 '14 at 05:22

Suchit kumar · Answer 2 · 2014-12-18T06:05:34.480

give a try to this: use LEFT join instead of INNER and make sure that on join columns are matching.

you can not join with string and int like this:

INNER JOIN items ON vendor.products = items.item_id is wrong because

products:1,2,3 and item_id=1

so try to use this:

$sql="SELECT 
      vendor.id, 
      vendor.vendor_id AS VID, 
      vendor.name AS VNAME, 
      vendor.category, 
      vendor.website, 
      vendor.email, 
      vendor.phone, 
      vendor.locations, 
      vendor.products, 
      vendor.vat, 
      vendor.pan, 
      location.loc_id, 
      location.name AS locname, 
      items.item_id, 
      items.name 
      FROM vendor 
      INNER JOIN location ON vendor.locations = location.loc_id,items
      WHERE 
      items.item_id IN (vendor.products) AND 
      items.item_id=".$product." AND 
      vendor.id=".$vendor;

because products and item_id are of different type,and they will never equal. — Suchit kumar, Dec 18 '14 at 05:49

score 0 · Answer 3 · answered Dec 18 '14 at 05:43

0

From you question I think you are trying to join an integer column (item_id) with a string column (vendor.products) because you mentioned that the values are stored in coma seperated values.

INNER JOIN items ON vendor.products = items.item_id

So the conclusion is the above query wont work . you have to join an integer column with integer column and string column with a string column

answered Dec 18 '14 at 05:43

Anish

4,262
6
36
58

Oh, i cannot make my item_id column as integer As it is a primary key and also if i make products column as integer , i cannot store array data :(. What can i do then – user2823107 Dec 18 '14 at 05:50
@user2823107 you can join two string columns also, no issues with that . Only thing it should be single item in each fields – Anish Dec 18 '14 at 05:56
For example table.a = table.b where a has value 'test' and b has to contain the value 'test' . then both table will join ie test = test . this test=test,test1 will not work – Anish Dec 18 '14 at 05:58
So, i have to make both data type same right? there is no other way :( – user2823107 Dec 18 '14 at 06:08
yes. you have to . You can try with left join but that will not give you proper result . please paste some values from the both table(which you are trying to join ) ie vendor.products and items.item_id – Anish Dec 18 '14 at 06:16
items table `item_id name
1 Black Board
2 Laptop
3 Pen Stand` vendor id products
1 1,2
2 1,2,3
– user2823107 Dec 18 '14 at 06:41
I have edited query and db table and code is pasted above. can u plz suggest me whether i am right or not – user2823107 Dec 18 '14 at 07:05
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/67196/discussion-between-anish-and-user2823107). – Anish Dec 18 '14 at 07:09

checking a id is present in array stored data

3 Answers3