ID check is present in stored data arrays
This is an extended question THIS
I check that the user entered ID is represented in the saved array using the comma operator.
I have a request 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 stores data in 1,3,5 format. and the entered data i e $ products will have one id.
What is wrong in my request as it returns any data
EDITED
I added another column to my items table named iid, which is also of type Varchar (string). Now I have stored these values in the vendor.products table like this ITM004,ITM003,ITM005
, the query still doesn't show the result :(
source to share
You have an error in your SQL syntax, also a PHP concatenation error:
AND vendor.id=".$vendor.""
You don't need .""
the end, because this is the last part of the instruction. HOWEVER, if $vendor
is a string, you will need to encapsulate it.
AND vendor.id='".$vendor."';
Otherwise, if it's INT, it's just:
AND vendor.id=".$vendor;
source to share
try this, use LEFT
join instead INNER
and make sure the join columns are the same.
you cannot join strings and int like this:
INNER JOIN items ON vendor.products = items.item_id
incorrect because
products:1,2,3 and item_id=1
try using 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;
source to share
From your question, I think you are trying to join an integer column (item_id) with a row column (vendor.products) because you mentioned that the values are stored in separate coma values.
INNER JOIN items ON vendor.products = items.item_id
So the conclusion is that the above query is not working. you need to join an integer column with an integer column and a row column with a row column
source to share