[RESOLVED] function not working

[vbbit]

I need some help, I tried to develop this function. Say I have a database that has 2 colums for now.

row1
column1(id) - 1
column2 - test

row2
colum1(id)-2
column2 - test, testing, tester

I coded up this function to retrieve that information seperately. For example, if a user has a URL query string with user=test, then when the query run, the user should get outputs of the id's "1 and 2", but if the user=testing, then the output should be only "1"... but my logic is not working.

$userid = $_GET['user'];

   $sql = "SELECT * FROM interest";
   $return = mysql_query($sql) or die ("Unable to query" . mysql_error());
   while ($col = mysql_fetch_assoc($return))
   {
         $values = "{$col['Interested_ID']}";
            $test = explode(",", $values);
            for ($i=0; $i<=3; $i++)
            {
                //echo $test[$i];
                //echo "<br>";
                if ($test[$i] = $userid)

                //{
                // find out who is interested in that user
                $qry = "SELECT UserID FROM interest WHERE Interested_ID = '$test[$i]'";
                $result = mysql_query($qry) or die ("Unable to query" . mysql_error());
                while ($row = mysql_fetch_assoc($result))
                {
                      $value = "{$row['UserID']}";
                      echo $value;
                      echo "<br>";
                      exit;
                      //exit;
                }
                //}

            } 

[visualAd]

$userid = $_GET['user'];

   $sql = "SELECT * FROM interest";
   $return = mysql_query($sql) or die ("Unable to query" . mysql_error());
   while ($col = mysql_fetch_assoc($return))
   {
         /* why not use ? $values = $col['Interested_ID']; */
         $values = "{$col['Interested_ID']}";
            $test = explode(",", $values);
            for ($i=0; $i<=3; $i++)
            {
                //echo $test[$i];
                //echo "<br>";
		
		/* the statement below assigns the value of $userId to $test[$i]
                      this will always evalute to the boolean value of the expression on 
                      the left of the assignment operator. == is equality

                      Anyhow, that line will not even parse, it is missing a semicolon
                    */
                if ($test[$i] = $userid)

                //{
                // find out who is interested in that user
                $qry = "SELECT UserID FROM interest WHERE Interested_ID = '$test[$i]'";
                $result = mysql_query($qry) or die ("Unable to query" . mysql_error());
                while ($row = mysql_fetch_assoc($result))
                {
                      /* why not use ? $values = $col['UserID']; */
                      $value = "{$row['UserID']}";
                      echo $value;
                      echo "<br>";
                      exit;
                      //exit;
                }
                //}

            }

Aside from the above issues, can you explain to me the purpose of this table and why you should need to traverse the entire table each time you search for one user ID? I think your schema may need some revision.

[vbbit]

hm... I am not sure, in fact besides the above issues, I still did not get what I want.

[visualAd]

Why have you created the table? What are the meaning of the columns? What exactly does the function do?

[vbbit]

row1
column1(id) - 1
column2 - test

row2
colum1(id)-2
column2 - test, testing, tester

This is what I want to accomplish. I coded up this function to retrieve that information seperately. For example, if a user has a URL query string with user=test, then when the query run, the user should get outputs of the id's "1 and 2", but if the user=testing, then the output should be only "1"... but my logic is not working. I can't get this to work. I might have too many loops but please help me.

[penagate]

Using a single field to hold multiple values like that is making life hard for yourself. It's not what databases are designed to do.

I suggest making a second table that contains the "test, testing, tester" values in individual rows linked to to your original ("interest"?) table. Tables should contain only contain data relevant to themselves; doing it all using one table does not work.

I also suggest you invest some time in studying relational database theory. It is a valuable skill to have.

[visualAd]

You need to create another table called user_interest:

+--------+        +---------------+         
|  user  |        | user_interest |         +---------------+
+--------+        +---------------+         |   interest    |
| id     |-+-----<| user_id       |         +---------------+ 
+--------+        | interest_id   |>------+-| id            |
                  +---------------+         | name (UNIQUE) |
                                            +---------------+

You can then just use a single query to pull out all the user ID's for a specfiic interest:

SELECT user_id FROM user_interest,interest 
    WHERE interest.name=##INTEREST NAME## AND
    interest.id=user_interest.interest_id;

[vbbit]

But the thing is if I have test, testing in different rows, that would be so hard to capture, and then my rows in the table is going to go forever. Above 3 examples just for examples, I allow user to put in the input box.

Here is what it is. If User1 browses User2's profile, he clicked on "show interest", I wanna keep track that. So that if User2 logs in, he/she can see who is interested in him/her. And if User3 browses the profile, again interested in User2, then i wanna keep track that too. Which means User2 has 2 ppl or maybe more users that is interested in him/her.

[visualAd]

But the thing is if I have test, testing in different rows, that would be so hard to capture, and then my rows in the table is going to go forever. Above 3 examples just for examples, I allow user to put in the input box.

You use the interest_id (a number to keep track of it). Firstly where does test and testing come into it? What exactly do you mean by interest (is this just a list of use ID's)?

And what is a problem of keeping track of these in a separate table?

[vbbit]

Yes, visual aid. I keep track the Interested_ID. the user ID will be the ID for the users that browse the profile and click on the Interest link. The interested_ID will be the User's ID who has been interested by another UserID. Forget about the testing, I was doing for example to see if it makes more sense with that function, apparently NOT.

See I have a table called Interest
Column1(UserID) - PK only one value
colum2(Interested_ID) - I separate those UserID with commas for this filed by using implode.

[vbbit]

grrrrr.. i must be thinking out of the box. It was very simple but I made it too complicated and hard on myself. grrrr...

[visualAd]

Like penagate said, combing these is not a good idea. Instead separate each interest into a row.

user_id,interested_user_id

Make both these columns a composite primary key and you can extract the data you need a lot easier.