1

In my table restaurant there is one column rest_admin. This contains the id of the admin who's logged in. This ID is stored in $_SESSION['adminSession'].

To select all restaurants for this admin I use this query:

SELECT * FROM restaurant INNER JOIN city ON restaurant.rest_city=city.city_id WHERE rest_admin=:rest_admin_id

:rest_admin_id=$_SESSION['adminSession']

So far so good, no problem.

Now I want multiple admins for one restaurant, still in the same column but the ID's separated by comma's like:

1, 6, 9, 24

Now my question is, in this case, how can I select only the restaurants where the logged in admin is one of the admins?

piet.t
  • 11,718
  • 21
  • 43
  • 52
Tim Biesmans
  • 73
  • 1
  • 11
  • Why are you not using relational model? – Aleksandar Pavić Mar 08 '17 at 12:30
  • 1
    Basicly you can do `WHERE rest_admin IN(1, 6, 9, 24)` but how you will do that with a prepared query, is on you. – JustOnUnderMillions Mar 08 '17 at 12:31
  • Don't do that. Instead normalize your database: Add a separate table to link admins to restaurants. – jeroen Mar 08 '17 at 12:31
  • @OP: You can make use of [`find_in_set`](https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set). But, you better normalize your data. – Ravinder Reddy Mar 08 '17 at 12:32
  • this is not the way you should be using a RDBMS. There are thousands of questions here from people who took this path and are now stuggling Don't save CSV in a column http://stackoverflow.com/questions/41304945/best-type-of-indexing-when-there-is-like-clause/41305027#41305027 http://stackoverflow.com/questions/41215624/sql-table-with-list-entry-vs-sql-table-with-a-row-for-each-entry/41215681#41215681 – e4c5 Mar 08 '17 at 12:32
  • Edit your question and provide sample data and desired results. If you want to store *numeric*, *foreign key* references in a comma-delimited string, then you should learn how to properly store such things in SQL. That is not the right way. – Gordon Linoff Mar 08 '17 at 12:33
  • http://stackoverflow.com/questions/8837584/mysql-in-statement use IN () in your query – Shafiqul Islam Mar 08 '17 at 12:46
  • find_in_set is working now. Thanks. @jeroen, can you explain the separate table? – Tim Biesmans Mar 08 '17 at 12:46
  • 1
    Add a table with just 2 columns, the restaurant id and the admin id, the combination of the two unique. Then you can add as many restaurant-admin combinations as you like; multiple admins for one restaurant, multiple restaurants for one admin, etc. and with a simple join you can get the relevant information. – jeroen Mar 08 '17 at 13:16

0 Answers0