Sql To Pdo Code

[Blesta Addons]

how i can write this in Blesta fiendly code

 

$table='users';

$sql="SELECT email FROM $table WHERE email in (
SELECT email FROM $table GROUP BY email
HAVING count(email) > 1)";
$result=mysql_query($sql);
$n=1;
while($row = mysql_fetch_array($result)){
echo '<br />('.$n.')';
echo $row['email'];
$n++;
}

i have tried but not working

$match = $this->Record->select("email")->from("users")->group("email");
$this->Record->select("email")->from("users")->where("email", "in", $match)->having(count(email) ,">", "1");

[Greg-Mega]

I would just do something like:

$sql = "SELECT email FROM users WHERE email in (
SELECT email FROM users GROUP BY email
HAVING count(email) > ?)";

$results = $this->Record->query($sql, array(1)); 

print_r($results);

Would be more efficient than running multiple queries.

[Blesta Addons]

I would just do something like:

$sql = "SELECT email FROM users WHERE email in (
SELECT email FROM users GROUP BY email
HAVING count(email) > ?)";

$results = $this->Record->query($sql, array(1)); 

print_r($results);

Would be more efficient than running multiple queries.

 

i get printed the query it self

 

PDOStatement Object ( [queryString] => SELECT username FROM users WHERE username in ( SELECT username FROM users GROUP BY username HAVING count(username) > ?) )

the full code is :

$sub_query = new Record();
$sql = "SELECT username FROM users WHERE username in (
SELECT username FROM users GROUP BY username
HAVING count(username) > ?)";

$results = $sub_query->query($sql, array(1));

print_r($results);

just a note, my code was for check duplicate 'username' in users table .

[Blesta Addons]

i Got it with other way ,wirth the "str_word_count" , the full code is .

    public function GetDuplicates($sort_by="email", $order="asc") {    
        $fields = array(
            "id", "client_id", "contact_type", "first_name","last_name", "email");        
        
        $result = $this->Record->select($fields)->from("contacts")->
            where("contact_type", "=", "primary")->
            order(array($sort_by=>$order))->fetchAll();


        // Add emails to a long string
        $phrases = "" ;
        for ($i=0; $i<count($result); $i++) {
            $phrases .= $result[$i]->email ." " ;
        }        
            
        $counts = array_count_values(str_word_count($result, 1, '0123456789àáãçéèñ.-_@:?!%'));
        arsort($counts);
        return $counts ;    
        
    }