Finding comma separated values with a colon delimiter

Question

I am setting values in my database for tourneyID,Selected,Paid,Entered,date then separating each selection with a colon

So I have a string that may look like this

    187,S,,,09-21-2013:141,S,,,06-21-2013:144,S,,,05-24-2013

but it also could look like this

    145,S,,,07-12-2013:142,S,,,05-24-2013:187,S,,,09-21-2013

and some times is looks like this

    87,S,,,07-11-2013:125,S,,,06-14-2013

I am trying to find this sequence: 187,S,,,09-21-2013

I have data stored like that because I paid a programmer to code it for me. Now, as I learn, I see it was not the best solution, but it is what I have till I learn more and it is working.

My problem is when using LIKE it returns both the 187 and 87 values

    $getTeams = mysql_query("SELECT * FROM teams 
    WHERE (team_tourney_vector LIKE '%$tid,S,P,,$tourney_start_date%' 
    OR team_tourney_vector LIKE '%$tid,S,,,$tourney_start_date%') 
    AND division='$division'");

I tried this using FIND_IN_SET() but it would only return the the team id for this string

187,S,,,09-21-2013:141,S,,,06-21-2013:144,S,,,05-24-2013

and does not find the team id for this string

145,S,,,07-12-2013:142,S,,,05-24-2013:187,S,,,09-21-2013

   SELECT * FROM teams WHERE FIND_IN_SET('187',team_tourney_vector) AND (team_tourney_vector LIKE '%S,,,09-21-2013%')

Any thoughts on how to achieve this?

@lc I have data stored like that because I paid a (programer) to code it for me. Now, as I learn, I see it was not the best solution, but it is what I have till I learn more and it is working. I do see the disadvantage like updating has to be done with 2 different screens. I got no help from the guy who helped me other than to say he needed more money to fix his problem. So I rolled up my sleeves and coded a patch myself. Just like the solution I came up with to fix this bug — iconMatrix, Dec 15 '12 at 22:29

score 2 · Answer 1 · edited May 23 '17 at 10:24

2

You are making this task far more difficult by storing the data in the format you're using.

You need to store tourney vectors in a separate table. Each tourney goes in a separate row, and each comma-separated field goes in a separate column.

Then you can use SQL to look up specific matching $tid rows, and you can even use indexes to help speed up those searches.

Also, MySQL dates should be stored in YYYY-MM-DD format.

Also see my answer to Is storing a delimited list in a database column really that bad?

edited May 23 '17 at 10:24

Community

1
1

answered Dec 15 '12 at 21:46

Bill Karwin

538,548
86
673
828

dates are stored in YYYY-MM-DD then flipped to a human version using date('m-d-Y', strtotime($tourney_start_date)); Thanks for the tip on your method of storing a comma separated list :) – iconMatrix Dec 15 '12 at 22:14
Don't know what you mean, because your example clearly shows dates stored in MM-DD-YYYY format. – Bill Karwin Dec 15 '12 at 22:48
Ahhh I see what you mean, I store it YYYY-MM-DD originally then switch to m-d-y and store that. I get it – iconMatrix Dec 15 '12 at 23:05

christopher · Answer 2 · 2012-12-15T21:45:42.590

0

Don't use sql. It is much easier to use PHP strpos

$getTeams = mysql_query("SELECT * FROM teams WHERE division='$division'");

while ( $rows = mysql_fetch_array($getTeams) ) {
  if ( strpos($rows['team_tourney_vector'],'187,S,,,09-21-2013') ) {
    echo $rows['team_tourney_vector'];
  }
}

edited Dec 15 '12 at 21:45

answered Dec 15 '12 at 04:22

christopher

615
5
11

I tried your example as seen below, but for every id in the db it returns Warning: strpos() [function.strpos]: Empty delimiter in /home/premier/public_html/tournament_teams1.php on line 69 not found – iconMatrix Dec 15 '12 at 18:11
$getTeams = mysql_query("SELECT * FROM teams WHERE division='$division'"); while ( $rows = mysql_fetch_array($getTeams) ) { $id = $rows['ID']; $TeamName = $rows['TeamName']; $ManagerFirstName = $rows['ManagerFirstName']; $ManagerLastName = $rows['ManagerLastName']; $team_tourney_vector = $rows['team_tourney_vector']; if ( strpos('187,S,,,09-21-2013',$rows['team_tourney_vector']) ) { echo $team_tourney_vector; } else { echo 'not found';}} – iconMatrix Dec 15 '12 at 18:18
If you do this, you might as well store your data in a text file. – Bill Karwin Dec 15 '12 at 21:35
Sorry, I always do that with strpos. Mix haystack and needle. Updated and should work now – christopher Dec 15 '12 at 21:46

iconMatrix · Accepted Answer · 2012-12-15T23:01:17.410

0

Here is my solution to the problem above using RLIKE

    $tourney_start_date=date('m-d-Y', strtotime($tourney_start_date));
    $vector = '"'.$tid.',S,,,'.$tourney_start_date.'"';
    $getTeams = mysql_query("
    SELECT * FROM `teams` 
    WHERE `team_tourney_vector` 
    RLIKE $vector 
    AND division='$division'");

edited Dec 15 '12 at 23:01

answered Dec 15 '12 at 21:30

iconMatrix

183
1
2
9

Still doesn't prevent false matches. Backslashes before commas are not needed. Nonstandard double-quote characters used for SQL string literals. – Bill Karwin Dec 15 '12 at 22:47
removed the backslashed but don't understand your comment about nonstandard double quotes, when removing them it created an error – iconMatrix Dec 15 '12 at 23:25
SQL uses single-quotes for string literals and date literals. MySQL non-standardly also uses double-quotes in the same way, but the ANSI/ISO SQL standard (and most other brands of RDBMS) use double-quotes to delimite column names and table names. You should get into the habit of using quotes in the standard way, in case you use another brand of database someday. – Bill Karwin Dec 16 '12 at 00:09
Thanks Bill, I do want to do it correctly, I will read up and make the change – iconMatrix Dec 16 '12 at 02:20
I finally figured out how to make this better with your suggestion Bill. Tables just work better, less to go wrong `SELECT * FROM team_tourneys INNER JOIN tourneys ON team_tourneys.tid =tourneys.tid WHERE team_id = '$team_id'` – iconMatrix Jan 19 '14 at 19:51

Finding comma separated values with a colon delimiter

3 Answers3