PHP&MySQL -> separating field values by comma

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Locust
Forum Commoner
Posts: 31
Joined: Sat Jul 22, 2006 10:26 am

PHP&MySQL -> separating field values by comma

Post by Locust »

In my database I have a table with a number of fields. One of these fields is to contain digits such as

Code: Select all

Table1: 1,2,3,4
Table2: 1,5,7,8
Table3: 1,3,6
In another table (lets say members table) it has username and their number. Let's say, for example member X is number 3.

I want PHP to sort through the fields to find the match 1,2,3,4 and 1,3,6 but obviously not 1,5,7,8.

This is what I have so far but it's not working so well =\

Code: Select all

$query = "SELECT * FROM table";
 $result = mysql_query($query);

while ($row = mysql_fetch_assoc($result)) {
$list = $row['field'];
$array = explode(',', $list);
}

foreach ($array as $new => $value) {

$records[] = "<a href=\"".$row['field1']."\">".$row['field2']."</a>";
}

echo implode(' | ', $records_menu);
Any ideas on a different approach?
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Re: PHP&MySQL -> separating field values by comma

Post by bdlang »

Locust wrote: Any ideas on a different approach?
Yes, it's called the relational database model, essentially the entire reason to use MySQL in the first place. Take a look at the 'external links' section at the bottom of that link.
Locust
Forum Commoner
Posts: 31
Joined: Sat Jul 22, 2006 10:26 am

Post by Locust »

I read over a lot of the instroductions but it said mainly things I already know and nothing helped me with my problem. I've looked over many boards with problems/solutions and I know hoe to deal with commas in arrays but this problem is a bit different to what I'm used to so I'm a bit lost.

I continue to search.
Locust
Forum Commoner
Posts: 31
Joined: Sat Jul 22, 2006 10:26 am

Post by Locust »

The tables are as follows:

Code: Select all

Table1: 1,2,3,4,5
Table2: 1,5
Table3: 2,4
My current code is

Code: Select all

while ($row_menu = mysql_fetch_assoc($result_menu)) {
$list = $row_menu['men_rank'];
$array = explode(',',$list);
foreach ($array as $test => $value) {
if ($value = $row['mem_rank']) {echo "$test ";}
}
}
and the result is

Code: Select all

0 1 2 3 4 0 1 0 1
What is should echo is

Code: Select all

1 1
Because as you can see from the first box

Code: Select all

Table1: 1,2,3,4,5
        ^------------------
Table2: 1,5
        ^------------------
Table3: 2.4
choppsta
Forum Contributor
Posts: 114
Joined: Thu Jul 03, 2003 11:11 am

Post by choppsta »

I can't be sure without knowing what the tables are or what context they are being used in, but it sounds like you have a many to many relationship between your members table and something else. Try looking this up. It involves having a third table with which to join the other two, but i'm probably not the best person to explain it.

Stop what you are doing with commas, you'll only end up in whole world of pain later on, even if it seems simpler at this stage.
Locust
Forum Commoner
Posts: 31
Joined: Sat Jul 22, 2006 10:26 am

Post by Locust »

Well there is no other way I can think of doing this. I'm pretty close, I just need some assistance trying to do this last part.

Assuming I have 1 table as follows

Code: Select all

1,2,3,4,5
with my code

Code: Select all

while ($row_menu = mysql_fetch_assoc($result_menu)) {
$list = $row_menu['men_rank'];
$array = explode(',',$list);
I can then

Code: Select all

echo $array[1];
Which results in "2" because:

Code: Select all

1,2,3,4,5
^------------Array[0]
1,2,3,4,5
    ^--------Array[2]
1,2,3,4,5
        ^----Array[4]
I understand what I've done so far but what I know need to do is:
Psuedo Code:

Code: Select all

member = Locust
mysql_getfield('member_access') = 1 <-------------Access
mysql_getfield('access_menu_item') = 1,2,3,4,5 <--Access
if (member has access to 'access_menu_item') return true

member = Locust
mysql_getfield('member_access') = 1 <-------------No access
mysql_getfield('access_menu_item') = 3,6,7,13 <---No access
if (member does not have access to 'access_menu_item') return false
Locust
Forum Commoner
Posts: 31
Joined: Sat Jul 22, 2006 10:26 am

Post by Locust »

I hate to try to revive the post but I'm sure this can be done. Just one final attempt at an answer before I try elsewhere. Thanks for the help though :)

I think my above post is pretty clear on what I need to do and what I've got so far. I just need to be able to use that array to get a value (eg '1') and locate that specific value ('1') in another field in the MySQL database.

Let me know if something is unclear.
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post by bdlang »

Back to my original statement, and that which chopper mentioned, I honestly think the best approach is to redesign your database so you have an extra table to relate keys rather than trying to store a comma delimited list of keys.

I'm not certain what your application requires, but if you can explain the relationship between tables and why you're trying to search for keys within a list, I'm sure we can find a much better method. It would seem that currently one or more of your tables is missing a PK.

Rather than pulling down all the data and trying to have PHP sort through it, you'd have a single SQL query that matches records based on the key value and pulls down exactly the resultset you want.
Locust
Forum Commoner
Posts: 31
Joined: Sat Jul 22, 2006 10:26 am

Post by Locust »

I basically want to have a menu with links to various pages but I want the website admin to be able to modify this menu (add/edit/delete) and state what sort of access is required for a menu link to be displayed.

Let's say for simplicity that there are 5 access IDs available: 1, 2, 3, 4, 5.

Let's say, for example, the website admin will create a new menu item and call it "members area" and the only members with access of 1, 2 and 5 are displayed this menu item.

I want PHP to get the access required from 1 table in the databse (eg a field containing 1,2,5) and then compare it to a user whom is already logged in (i.e get a field value of access from another table) who has been assigned an access number (let's say access of 2).

So if the menu item has the same access required as the access of the member logged in, display the menu item, otherwise don't display it.

I figured a field with commas would do this but if you have another suggestion, please let me know. Hope you understand all that.

Thanks :)
Post Reply