This query returns 2 of the same result... ?

[TeddyKiller]

$result = $db->execute("SELECT u.avatar, u.displayname, p.about_me FROM users as u, profiles as p WHERE u.email IN ('$email') AND p.user_id = u.id LIMIT $offset, $rowsperpage");

 

I ran the query through phpmyadmin, and it does return 2 rows with the same email, when there is only one row with the email, why? o.O

 

Also doesn't quite work when I have the $email containing.. '[email protected], [email protected]' and so on. It's treating it as one string, and not .. like an IN.

[miancu]

try to use an INNER JOIN instead of this select from 2 tables. Also manipulate the $email var before adding it to the query so it is in the needed format 'email1@..,email2@..' and add the var  without the ''. try it like this:

 

SELECT u.avatar, u.displayname, p.about_me

      FROM users  u INNER JOIN profiles p ON p.user_id = u.id

    WHERE u.email IN ($email) AND  LIMIT $offset, $rowsperpage

 

hope this helps!

[Barand]

You need the quotes around each email address

IN ('email1', 'email2', 'email3')

 

With a JOIN, if there are 2 matching rows in profile table you will get 2 rows returned, each with matching data from the user table

[TeddyKiller]

Ah thanks. There was two matching rows in profile table. - Is there anything that can be changed to limit that, if it was duplicated?

 

Also, IN ('email1', 'email2', 'email3')

If $email contained.. email1, email2, email3

How ... could that be changed to what you stated, without having to do..

$email = explode(', ', $email);
foreach($email as $value)
{
    $email[] = " ' " . $value . " ' "; // Spaced it out so it would be clearer
}
$email = implode(', ', $email);

...

[TeddyKiller]

With the above.. it brings up

MySQL Error on Query 'SELECT count(*) FROM users as u, profiles as p WHERE u.email IN (email1, email2, 'email1', 'email2') AND p.user_id = u.id':

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 'email1, email2, 'email1' at line 1

 

Itr's basically.. duplicating the emails, yet the query is..

$email = explode(',', $email);
foreach($email as $value) $email[] = "'" . $value . "'";
$email = implode(', ', $email);

$result = $db->execute("SELECT count(*) FROM users as u, profiles as p WHERE u.email IN ($email) AND p.user_id = u.id");

[Barand]

$emails = array ($e1, $e2, $e3);

$elist = join ("','", $emails);

 

In the query,

... IN ('$elist') ...

 

Also, Try using "SELECT DISTINCT ..." to remove duplicate rows.