Page 1 of 1

PHP&MySQL -> separating field values by comma

Posted: Fri Jul 28, 2006 12:45 am
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?

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

Posted: Fri Jul 28, 2006 1:30 am
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.

Posted: Fri Jul 28, 2006 1:32 am
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.

Posted: Fri Jul 28, 2006 4:23 am
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

Posted: Fri Jul 28, 2006 5:02 am
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.

Posted: Fri Jul 28, 2006 6:55 am
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

Posted: Sat Jul 29, 2006 4:07 am
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.

Posted: Sat Jul 29, 2006 12:22 pm
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.

Posted: Sun Jul 30, 2006 12:37 pm
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 :)