[php/mysql] script geeft op andere server opeens error

[TromboneFreakus]

Na maanden proefdraaien met Joomla op een andere server met een nieuwe site was het vandaag tijd om de hele boel over te zetten naar de eigenlijke server.

Onderdeel van de website was een koppeling van de bestaande ledenadministratie aan de gebruikersadministratie van Joomla door middel van een PHP-script. Dit had ik uitvoerig getest op de andere server en het verliep steeds voorbeeldig.

Op de eigenlijke server krijg ik echter de volgende cryptische foutmelding:

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

De query waar deze foutmelding bij komt luidt als volgt:

PHP:

$query = "
  SELECT 
    LidID, Emailadres, Postcode, Huisnummer 
  FROM 
    $database_ledenadmin.adresgegevens 
  LEFT JOIN 
      $database_ledenadmin.postcodes 
    ON 
      $database_ledenadmin.adresgegevens.PostcodeID = $database_ledenadmin.postcodes.PostcodeID
  WHERE 
      Emailadres NOT IN ( SELECT email FROM $database_joomla.jos_users ) 
  AND JoomlaID NOT IN (SELECT id FROM $database_joomla.jos_users) 
  AND LidID IN ( SELECT LidID FROM $database_ledenadmin.lidmaatschapsduur WHERE `Einde` IS NULL OR  `Einde` > NOW( ));";

Het vreemde nu is: beide database zijn met PhpMyAdmin volledig ingesteld op utf8_general_ci.

Wanneer ik zoek op deze foutmelding kom ik op allerlei pagina's met evenzoveel oplossingen. Het zou mooi zijn dat door discussie op dit forum mogelijk eerder *de* tip komt bovendrijven.

We maken gebruik van PHP versie 5.2.9 en MySQL versie 5.0.67-community.

Voor wie denkt dat het in gebruikersrechten zit nog het volgende. Zoals te zien worden gegeven uit twee databases gecombineerd. De MySQL-user die hiervoor gebruikt wordt heeft leesrechten op de tabel $database_ledenadmin en alle rechten op de tabel $database_joomla.

Voor de volledigheid, en voor wie zelf eens gegevens van Joomla wil koppelen aan een bestaande database, hierbij het volledige script.

Wie heeft er mogelijk ideeen? Het is om gek van te worden.

PHP:

<?php
// Report all PHP errors
error_reporting(E_ALL);

/*
Koppeling tussen database Jubal en Joomla

Dit script doet niets meer of minder dan het toevoegen van gebruikers 
uit de database van Jubal aan de gebruikerslijst van Joomla en het verwijderen
van gebruikers uit de gebruikerslijst van Joomla, die volgens de database
van Jubal geen lid meer zijn.

Gegevens van de database van Jubal zijn bepalend. Dat betekent dat het
de bezoeker van de website onmogelijk moet worden gemaakt zijn/haar gegevens
via Joomla aan te passen. Het is nu immers eenrichtingsverkeer: 
    database Jubal ==> database Joomla

Achterliggende idee is dat dit script als cron-job bijvoorbeeld iedere nacht 
draait. Het effect is dan dat altijd binnen 24 uur een lid gebruik kan maken van 
de website.

Gebruikersnaam van het lid is zijn/haar mailadres. 

*/

$dbhost = "localhost";
$dbuser = "*****_joomla1";
$dbpassword = "**************";
$database_joomla = "******_joomla15";
$database_ledenadmin = "******_ledenadmin";



/* 
BEGIN koppeling ledendatabase en gebruikersadministratie Joomla!
*/
function createRandomPassword($length = 31) {
    $chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
    srand((double)microtime()*1000000);

    $i = 0;

    $pass = '' ;

    while ($i <= $length) {
        $num = rand() % 61;
        $tmp = substr($chars, $num, 1);
        $pass = $pass . $tmp;
        $i++;
    }
    return $pass;
}


function createJoomlaUser($username, $password = null, $mailadres, $naam) {
    global $dbhost, $database_joomla, $dbuser, $dbpassword;
    
    if (strlen($username)<2) {
        return false;
        exit;
    }
    if (strlen($mailadres)<2) {
        return false;
        exit;
    }
    if (strlen($naam)<2) {
    //if (!isset($naam)) {
        return false;
        exit;
    }
    
    if ($password == null) {
        //genereer random password met lengte 9 
        $password = createRandomPassword(9);
    }
    
    //naam veilig stellen voor MySQL
    $naam =  mysql_real_escape_string($naam);
    

    $dblink=mysql_connect ($dbhost, $dbuser, $dbpassword, true);
    
    if (!$dblink) {
    die('Could not connect: ' . mysql_error());
    }

    if (!mysql_select_db($database_joomla,$dblink)) {
        echo "<p>fout! $database_joomla";
    }
    
    //genereer salt met lengte 32 karakters
    $salt = createRandomPassword();
    
    //md5 wachtwoord maken met salt
    //salt staat in tekst na :
    $joomlapassword = md5($password.$salt).":$salt";

    //toevoegen aan tabel jos_users
    $query = "INSERT INTO `$database_joomla`.`jos_users` (`name` , `username` ,`email` ,`password` ,`usertype` ,`gid`, `registerDate`) VALUES ('$naam', '$username', '$mailadres', '$joomlapassword', 'Registered', '18', NOW());";
    $result = mysql_query($query)or die(mysql_error());
    
    //nu toevoegen aan tabel jos_core_acl_aro
    $userid = mysql_insert_id();
    $query = "INSERT INTO `jos_core_acl_aro` (`section_value` ,`value` ,`order_value` ,`name` ) VALUES ( 'users', '$userid', '0', '$naam');";
    $result = mysql_query($query)or die(mysql_error());
     
    //nu toevoegen aan tabel jos_core_acl_groups_aro_map
     $aroid = mysql_insert_id();
     $query = "INSERT INTO `jos_core_acl_groups_aro_map` (`group_id` ,`aro_id`
) VALUES ( '18', '$aroid');";
    $result = mysql_query($query)or die(mysql_error());
    
    mysql_close($dblink);

    return array($userid, $password);
}


//verbinding leggen database
$dblink=mysql_connect ($dbhost, $dbuser, $dbpassword, true);

if (!$dblink) {
  die('Could not connect: ' . mysql_error());
}

if (!mysql_select_db($database_joomla,$dblink)) {
    echo "<p>fout! $database_joomla";
}

//actieve leden opzoeken die nog niet in joomla staan en deze invoegen
$query = "SELECT LidID, Emailadres, Postcode, Huisnummer FROM $database_ledenadmin.adresgegevens LEFT JOIN $database_ledenadmin.postcodes ON $database_ledenadmin.adresgegevens.PostcodeID = $database_ledenadmin.postcodes.PostcodeID WHERE Emailadres NOT IN ( SELECT email FROM $database_joomla.jos_users ) AND JoomlaID NOT IN (SELECT id FROM $database_joomla.jos_users) AND LidID IN ( SELECT LidID FROM $database_ledenadmin.lidmaatschapsduur WHERE `Einde` IS NULL OR `Einde` > NOW( ));";
$result = mysql_query($query)or die(mysql_error());

//loop door deze leden en daarvan gebruikers aanmaken
while($row = mysql_fetch_array($result)) {
    $LidID = $row['LidID'];
    $Emailadres = "".$row['Emailadres'];
    $naam = "".$row['naam'];
    $Postcode = "".$row['Postcode'];
    $Huisnummer = "".$row['Huisnummer'];
    
    //default wachtwoord is postcode en huisnummer zonder spaties
    $wachtwoord = str_replace (" ", "", strtolower($Postcode.$Huisnummer));
    
    //invoegen als nieuwe gebruiker
    $nieuwe_gebruiker = createJoomlaUser($Emailadres, $wachtwoord, $Emailadres, $naam);
    
    //joomla-ID invoegen in database adresgegevens
    $dblink2=mysql_connect ($dbhost, $dbuser, $dbpassword, true);
    $bijwerk_query = "UPDATE `$database_ledenadmin`.`adresgegevens` SET `JoomlaID` = '".$nieuwe_gebruiker[0]."' WHERE `adresgegevens`.`LidID` = $LidID ";
    $bijwerk_result = mysql_query($bijwerk_query)or die(mysql_error());

}

echo "<p>Bijwerken ID in ledenamdin gelukt";
//oud-leden uit Joomla verwijderen
$dblink3=mysql_connect ($dbhost, $dbuser, $dbpassword, true);
if (!mysql_select_db($database_joomla,$dblink3)) {
    echo "<p>fout! $database_joomla";
}
$verwijderquery = "DELETE FROM `$database_joomla`.`jos_users` WHERE `jos_users`.`id` IN (SELECT JoomlaID FROM $database_ledenadmin.adresgegevens WHERE LidID NOT IN ( SELECT LidID FROM $database_ledenadmin.lidmaatschapsduur WHERE `Einde` IS NULL OR `Einde` > NOW( ) ));";
$verwijder_result = mysql_query($verwijderquery)or die(mysql_error());

//koppeling met Community Builder goed maken
$cbbuilder_query = "INSERT INTO jos_comprofiler (id, user_id) SELECT id, id FROM jos_users WHERE id NOT IN (SELECT id FROM jos_comprofiler);";
$cbbuilder_result = mysql_query($cbbuilder_query)or die(mysql_error());

//vervolgens bijwerken gegevens alle leden die nu al in joomla staan
$dblink4=mysql_connect ($dbhost, $dbuser, $dbpassword, true);
if (!mysql_select_db($database_joomla,$dblink4)) {
    echo "<p>fout! $database_joomla";
}
$bijwerkquery = "UPDATE $database_ledenadmin.adresgegevens bron, $database_joomla.jos_users doel SET doel.name = CONCAT(bron.`Voornaam`,' ',bron.`tv`,' ',bron.`ACHTERNAAM`), doel.email = bron.Emailadres WHERE doel.id = bron.JoomlaID;";
$bijwerk_result = mysql_query($bijwerkquery)or die(mysql_error());

/* 
EINDE koppeling ledendatabase en gebruikersadministratie Joomla!
*/

?>

[TromboneFreakus]

Verder Googlen heeft me dit fantastische script opgeleverd: http://bogdan.org.ua/2008...llation-into-another.html

Dit lijkt te werken

[cariolive23]

$database_ledenadmin.adresgegevens.PostcodeID = $database_ledenadmin.postcodes.PostcodeID

Kun je even kijken welke character encoding deze kolommen in genoemde tabellen hebben?

De operator = geeft hierop de genoemde foutmelding, het is de enige = in je query.

Ps. Waarom toch al die backticks ` in je SQL? Die voegen niets toe, hooguit wat dramatische bugs wanneer je ze een keer vergeet en keywords als tabel- en/of kolomnamen hebt gebruikt. Poef! Weg data...