0

The more I think about this the more I think there is a fundamental problem with my design. I have a form. I have a select input option. Users can select up to four items from the list. The options each have a number associated with them. When the form is submitted the number of each option selected is added together, separated by a comma and added to a column in the database. I have no control over the order in which they select the options. There are a total of 30 options they can choose from. So I end up with 4 numbers in the column ranging from 1-30 in not particular order. At some point the user will select an option in another screen and all the information relating to that option should be displayed in tabular form. I am struggling to get just one option number back from the database. I have selected the column with the option numbers in from the database and passed it to explode() using the comma as the delimiter but its not returning anything. If I do a var_dump on the $rows returned by the query on the database column I can see all the information has been retrieved. So Im close. This is how I am exploding the returned data.

    var_dump(explode(',' , $rows['scat']));

The bigger problem may well be, once I get the information from the database is how do I extract a particular number from it given that it could be between 1 and 30 and in no particular order.

Adrian Fischer
  • 119
  • 3
  • 10
  • 1
    your correct, the design is wrong - https://en.wikipedia.org/wiki/Database_normalization –  Jul 06 '16 at 02:31
  • 1
    in short items table, (user|item), one row for each –  Jul 06 '16 at 02:32
  • 2
    yep and if you use a delimiter in a table, id suggest using it at the beginning and end as well, Why? Because I'm crazy like `|4|45|` as in select number LIKE `%|4|%` works much better, when you can grantee its surrounded by a delimiter.... Just my 2$ Materialized path anyone? – ArtisticPhoenix Jul 06 '16 at 02:42
  • The only reason ya ought to be accessing that data is for the last time .... in an ETL attempt to get it normalized. It's time for a [change](http://stackoverflow.com/a/32620163) toward Junctions or Association tables. Intersects, whatever you want to call them. Just because you can *human visualize* the system better your way, doesn't mean you are doing the users, UX, you or the server any favors. Including but not limited to the non-use of indexes, and horrible code needed. – Drew Jul 06 '16 at 02:52
  • Using @ArtisiticPhoenix comment I now have this when I enter the information into the db `$scat = implode("|",$_POST['scat']);' and when I extract the information I use `SELECT * FROM registervendors WHERE state='$state' AND scat LIKE '%$scat%' ORDER BY busname ASC' It selects the correct number every time. – Adrian Fischer Jul 06 '16 at 03:11
  • note that searches on scat wont be indexed in that case, which may or may not be an issue for you. @ArtisiticPhoenix you should not really be suggestion such bad approaches ;) –  Jul 06 '16 at 03:13
  • TL; DR; but yes. You're first sentence says everything you (and we) need to know. – Strawberry Jul 06 '16 at 05:36

1 Answers1

-1

You have to focus step by step:-

while saving data to DB use:-

$qry = "insert into registervendors (scat) values ('".implode(",", $_POST['scat'])."')";

when you want to retrieve data use this query:-

$qry = "SELECT * FROM registervendors WHERE state='".$state."' AND FIND_IN_SET(".$scat.", scat) ORDER BY busname ASC";

After getting result use:-

$catIDS = explode(",", $resultROw[$key]['scat']);

Here $catIds have list of categories between 1 to 30. Now you need to search 10 in it so use:-

if(in_array(10, $catIDS)){
    echo "exists";
}
Rakesh Kumar
  • 4,319
  • 2
  • 17
  • 30