0

Store db as comma separated string and i want to retrieve data from a string separated by commas.

https://code.sololearn.com/wKW8F3caReUY/?ref=app

is this code work properly or not bcz it is not showing me the required results

//$userData['city'] = 27 //cities in db are saved as 12,33,9,2,1,27,31

    //$userData['hobbies'] = 3,24
    //hobbies in db have values as 3,24,17,31

    // how to i match these records/arrays by my sql db records

    $SQL = "SELECT * FROM users_stats WHERE 
    type = '1'
    AND
    (
        ".in_array( $userData['city'] ,  explode(",", "'cities'") )."
        OR
        cities IN('all','pak')
    )
    AND
    (
        time = ".$currentTime." OR time = 'all'
    )
    AND
    (
        ".((array_intersect( explode(",",$userData['hobbies']) , explode(",", "'hobbies'")) != null)?1:0)."
    )
    ";
Sameer Rajpoot
  • 1
  • 1
  • I don't see a prepared statement being used. https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – mickmackusa Jul 30 '21 at 03:20

1 Answers1

0

you had confused the seperate environment.
PHP has it's own environment.
Database has it's own environment.

the basic process to get data from sql:
build sql query text -> send to database server -> parse the sql text,execute sql query -> return data back to php

in your php code

".in_array( $userData['city'] ,  explode(",", "'cities'") )."

the sql you build,you want explode the column cities in database,but at that moment the sql not execute yet,it seems you want to execute php in database environment.
explode is a php function,I guess you know that,and at this moment cities is just a string,not a database column which contain data in it.
after php execute the string will parse to (which is false).

for the solution,there is no easy way to operation array in mysql(other database may operation array),so the simple way to do is get the cities column back to php,and foreach to filter the data you want

nay
  • 1,725
  • 1
  • 11
  • 11