[RESOLVED] Trying to get all instances of same value from a table

[DRandy]

Hi

In my database table I have a column called ID that has individual numbers for each of my users. The next column is called ReferringuserID. Lets say user 2 has an ID of 2 and has referred user 5, 6 and 7. So in the ReferringuserID column of 5, 6 and 7, it will hold the number 2. Another column of my table is user-email. I want to get the email address of user 5, 6 and 7 because the number 2 is present in the ReferringuserID of user 5, 6 and 7.

This is what I been trying to use but this code only gives me the first email address (user 5). How can I get all the email addresses?

function get_email($id='')
{
if ($id!=null)
{
global $db;
$query = "SELECT * FROM $db->users WHERE ReferringuserID = ".$id."";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
return $row['user-email'];
}
}
}

[laserlight]

You would write something like this:

function get_email($id)
{
    global $db;
    $emails = array();
    $id = (int)$id;
    $query = "SELECT `user-email` FROM $db->users WHERE ReferringuserID = $id";
    $result = mysql_query($query) or die(mysql_error());
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
    {
        $emails[] = $row['user-email'];
    }
    return $emails;
}

The key difference is that instead of returning in the loop body, I collected the data into an array and returned that array after the loop. I also removed the default argument since it does not make sense in this case. You may wish to have $db as a parameter to the function instead of declaring it global.

[DRandy]

What do you mean (You may wish to have $db as a parameter to the function instead of declaring it global)? What are the problamatic issues with the way I did it?

And thanks for the quick reply and your help. I'm still pretty new at this.

[laserlight]

What do you mean (You may wish to have $db as a parameter to the function instead of declaring it global)?

You could write it as:

function get_email($id, $db)
{
    $emails = array();
    $id = (int)$id;
    $query = "SELECT `user-email` FROM $db->users WHERE ReferringuserID = $id";
    $result = mysql_query($query) or die(mysql_error());
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
    {
        $emails[] = $row['user-email'];
    }
    return $emails;
}

What are the problamatic issues with the way I did it?

Your get_email() function is tightly coupled to the name you use for your database object and your function may have unintended hidden side-effects, e.g., if $db is overwritten by accident under maintenance. It would be easier to track such a mistake if the variable was local to the function.

[DRandy]

OK

I understand. That makes sense.

Thanks for your help.

I appreciate it.

[DRandy]

Well ...

It doesn't work. What I get is the email address of the first instance of the user ID and a second email address of - [e-mail]. But I don't know where that came from. It is not the email address of anything.

??

Thanks

[DRandy]

Please anyone.

Laserlight's suggestion was good but I think something is still missing.

[laserlight]

It doesn't work. What I get is the email address of the first instance of the user ID and a second email address of - [e-mail]. But I don't know where that came from. It is not the email address of anything.

That is weird. I might understand an output of "Array" (it means that you are trying to print an array as a scalar variable), but since you do get an email address printed, it seems to be that the data stored in your database is corrupted.

[DRandy]

Currupted? Well I'll check 0on it but I need to log off now. Please watch this thread and I'll try to get back on this tomorrow after 6:00 p.m.

It's 10:53 p.m. where I am right now.

I hope we can get back together tomorrow and get this resolved.

Thanks for your help.

[DRandy]

I'm back now.

Maybe I should back up. I was using some code to email a test message after returning the emails from the function above.

So instead of trying to continue with that route, I echoed your function to see what it gives me on my page.

I get an error message of:

Missing argument 2 for get_email() ...

and

... You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE ReferringuserID = 2' at line 1.

If it helps, I am first identifying the value for $id (theuserID) in my file that calls this function. That's why I used $id='' in my original above. The actual written function is in a different file.
So why use $id = (int)$id; in yours?

Anyways, maybe that has nothing to do with it. What does the erro message mean and where do I go from here?

Thanks

[DRandy]

OK

Now this time I used global $db; and the error message went away but now it prints 'Array'. No email addresses. Just the word 'Array'.

I'm not sure what you mean (it means that you are trying to print an array as a scalar variable).

Please explain.

Thanks

[laserlight]

So instead of trying to continue with that route, I echoed your function to see what it gives me on my page.

Yes, it is wise to concentrate on specific portions on your code separately when you are in doubt.

Anyways, maybe that has nothing to do with it. What does the erro message mean and where do I go from here?

As you have noticed, the problem is that you changed the function signature of get_email(), but forgot to reflect this change in the caller. In other words, you are writing:

get_email($id);

instead of:

get_email($id, $db);

Putting back the use of the global in the definition of get_email "solves" this problem, but then you get back the same old tight coupling problem. The real fix is to change how you call the function.

I'm not sure what you mean (it means that you are trying to print an array as a scalar variable).

For example, you would get that if you tried this:

$array = array('hello', 'world');
echo $array;

instead of:

$array = array('hello', 'world');
foreach ($array as $value)
{
    echo $value . ' ';
}