xoops forums

wcrwcr

Home away from home
Posted on: 2009/4/9 18:55
wcrwcr
wcrwcr (Show more)
Home away from home
Posts: 1114
Since: 2003/12/12
#1

Is there a query to add users in a batch?

Hi all

I remember of reading that already somewhere but after some search I could not find any input, so:

Can I add a bunch of users in a batch running any kind of sql query?

If so, can you guys point me the right direction or the query itself?
(BTW > i´m not a coder )

Thnak you all


Burning

Theme Designer
Posted on: 2009/4/9 19:14
Burning
Burning (Show more)
Theme Designer
Posts: 1163
Since: 2006/8/22
#2

Re: Is there a query to add users in a batch?

hi'

You should test this script

<?php
// christian@frxoops.org - http://www.frxoops.org
// Import users from a csv or text file into XOOPS 2.3.2
// variables
$bdd"bdd-name";
$host"localhost";
$user"root";
$pass"";
$prefix"xc2234"// tables prefix, without _
$separator ";";//separator used in original file 
$regdate=time();
$fichier "file-name.csv"// file name to read


// IMPORTANT : DO NOT LEAVE AN EMPTY LINE IN YOUR ORIGINAL DOCUMENT (file-name.csv)

mysql_connect($host,$user,$pass) or die("Unable to connect Database");
mysql_select_db($bdd);

//Open file for read 
if (file_exists($fichier))
$fp fopen("$fichier""r");
else{ 
// unknown file 
echo "File not found !<br />Import stopped.";
exit();
}
echo 
'Begin file import : <b>'.$fichier.'</b><br /><br />';
echo 
'<table border="1" cellspacing="3">';
echo 
'<tr><td>UID</td><td>Pseudo</td>';
// import line by line 
while (!feof($fp)){
$ligne fgets($fp,4096);
$liste explode($separator,$ligne); //create array 



// Add real name
$uname $liste[0];
// End

// Add website
$url $liste[1];
// End

// Add avatar
$user_avatar $liste[2];
// End

// Add "From" info
$user_from "xoops.org";
// End

// Add Sign
$user_sig $liste[3];
// End

// Add Password
$passmd5(trim($liste[4])); 
// End

$rank "1"// Choose rank

// Add occupation
$user_occ "Xoops Designer";
// End



$user_mailok "1"// Choose 1 if you allow administrator to send email


// Add user in xoops_users table
$query "insert into ".$prefix."_users (uname, url, user_avatar, user_regdate, user_from, user_sig, pass, rank, user_occ, user_mailok) VALUES ('".$uname."', '".$url."', '".$user_avatar."', '".$regdate."', '".$user_from."', '".$user_sig."', '".$pass."', '".$rank."', '".$user_occ."', ".$user_mailok.")";

$resultmysql_query($query);
$uid mysql_insert_id();

//Add user in group : registered users per default
$numgroup ='2';// choose Group number
$query "insert into ".$prefix."_groups_users_link (groupid, uid) VALUES('".$numgroup."', '".$uid."')";
$resultmysql_query($query);

if (
mysql_error()){
echo 
"Error in database : ".mysql_error();
echo 
"</table><br /><br /><b>Importation stoppée.</b><br /><br />";
fclose($fp);
exit();
}else{
echo 
"<tr><td> ".$uid." </td><td> ".$uname." </td></tr> ";
}
}

echo 
"</table><br />Import finished successfully.";// That's ok !

fclose($fp);
?>


Found on frxoops forums for months (I can't find link anymore). It works fine with XOOPS 2.3.2

You have to customize some informations (comments translated by me, sorry )
Still learning CSS and... english

wcrwcr

Home away from home
Posted on: 2009/4/9 19:20
wcrwcr
wcrwcr (Show more)
Home away from home
Posts: 1114
Since: 2003/12/12
#3

Re: Is there a query to add users in a batch?

thanks Burning

Do you have an idea how to run it because it´s not a query but a php file isn´t it?

Did you tested it on a XOOPS 20182 too?

Burning

Theme Designer
Posted on: 2009/4/9 19:29
Burning
Burning (Show more)
Theme Designer
Posts: 1163
Since: 2006/8/22
#4

Re: Is there a query to add users in a batch?

I'm not sure that it works for XOOPS 2.0.18

I will search on frxoops original link (because original script was made for XOOPS 2.0.18 if I can remember).


To use this script it is very simple (I'm not a coder but I have been able to make it work) :

1. You have to create a table in your favorite software like Miscrosoft Excel (.xls file) or Open Office (.odt file). Each column have to exist and listed like this :
uname, url, user_avatar, user_regdate, user_from, user_sig, pass, rank, user_occ, user_mailok
If you want less or more information to be imported, modify import script.

2. export this table with .csv extension

3. Put script above (for example .my-import.php) and your .csv file in root path (www/your-site/)

4. run script : http://localhost/your-site/my-import.php


Be sure to save your original database !! Many attempts could be necessary
Still learning CSS and... english

Burning

Theme Designer
Posted on: 2009/4/9 19:36
Burning
Burning (Show more)
Theme Designer
Posts: 1163
Since: 2006/8/22
#5

Re: Is there a query to add users in a batch?

Same thing for XOOPS 2.0.18, with less information to import.

<?php
// christian@frxoops.org - http://www.frxoops.org
// Import users from a csv or text file into XOOPS 2.0.18
// variables
$bdd"nom de la base";
$host"localhost";
$user"utilisateur de la base";
$pass"mot de passe de la base";
$prefix"préfixe"//préfixe des tables, sans indiquer le _
$separator ";";//séparateur uilisé dans le fichier à importer
$regdate=time();
$fichier "membres.txt"//nom du fichier à parcourir
//file structure : pseudo;email;password en clair

//ne pas laisser de ligne vide à la fin du fichier à importer

mysql_connect($host,$user,$pass) or die("Unable to connect Database");
mysql_select_db($bdd);

//Open file for read - Ouverture du fichier contenant les membres
if (file_exists($fichier))
$fp fopen("$fichier""r");
else{ 
// unknown file - fichier non trouvé
echo "File not found !<br />Import stopped.";
exit();
}
echo 
'Begin file import : <b>'.$fichier.'</b><br /><br />';
echo 
'<table border="1" cellspacing="3">';
echo 
'<tr><td>UID</td><td>Pseudo</td>';
// import line by line - import ligne par ligne
while (!feof($fp)){
$ligne fgets($fp,4096);
$liste explode($separator,$ligne); //create array 
$uname $liste[0];
$email $liste[1];
$passmd5(trim($liste[2])); 
$user_mailok "0"//n'autorise pas l'administrateur à envoyer des mails par défaut - mettre à un (1) dans le cas contraire

// Add user in xoops_users table
$query "insert into ".$prefix."_users (uname, email, pass, user_regdate, user_mailok) VALUES ('".$uname."', '".$email."', '".$pass."', '".$regdate."' , ".$user_mailok.")";
$resultmysql_query($query);
$uid mysql_insert_id();

//Add user in group : registered users / utilisateurs enregistrés
$numgroup ='2';//changer cette valeur pour affecter le membre à un autre groupe
$query "insert into ".$prefix."_groups_users_link (groupid, uid) VALUES('".$numgroup."', '".$uid."')";
$resultmysql_query($query);

if (
mysql_error()){
echo 
"Error in database : ".mysql_error();
echo 
"</table><br /><br /><b>Importation stoppée.</b><br /><br />";
fclose($fp);
exit();
}else{
echo 
"<tr><td> ".$uid." </td><td> ".$uname." </td></tr> ";
}
}

echo 
"</table><br />Import finished successfully.";//Import réalisé avec succès

fclose($fp);
?>

Still learning CSS and... english

wcrwcr

Home away from home
Posted on: 2009/4/9 19:54
wcrwcr
wcrwcr (Show more)
Home away from home
Posts: 1114
Since: 2003/12/12
#6

Re: Is there a query to add users in a batch?

Many thanks Burning!

I´ll try It ASAP

I guess that will be a nice feature to be in hand or even in te core.

seesoe

Just popping in
Posted on: 2009/7/29 16:58
seesoe
seesoe (Show more)
Just popping in
Posts: 33
Since: 2007/1/3 4
#7

Re: Is there a query to add users in a batch?

will this work in 2.3.3?

planetexcel

Just popping in
Posted on: 2009/12/9 12:35
planetexcel
planetexcel (Show more)
Just popping in
Posts: 13
Since: 2008/8/23
#8

Re: Is there a query to add users in a batch?

Can this work for 2.4.1?

Burning

Theme Designer
Posted on: 2009/12/9 13:16
Burning
Burning (Show more)
Theme Designer
Posts: 1163
Since: 2006/8/22
#9

Re: Is there a query to add users in a batch?

hi'

Not tested with XOOPS 2.4.x

You should try first script, witch worked fine with XOOPS 2.3.x

planetexcel

Just popping in
Posted on: 2009/12/10 12:26
planetexcel
planetexcel (Show more)
Just popping in
Posts: 13
Since: 2008/8/23
#10

Re: Is there a query to add users in a batch?

Quote:
<?php
// christian@frxoops.org - http://www.frxoops.org
// Import users from a csv or text file into XOOPS 2.3.2
// variables
$bdd= "----";
$host= "localhost";
$user= "-------";
$pass= "-----";
$prefix= "-----"; // tables prefix, without _
$separator = ";";//separator used in original file
$regdate=time();
$fichier = "myimp.csv"; // file name to read


// IMPORTANT : DO NOT LEAVE AN EMPTY LINE IN YOUR ORIGINAL DOCUMENT (file-name.csv)

mysql_connect($host,$user,$pass) or die("Unable to connect Database");
mysql_select_db($bdd);

//Open file for read
if (file_exists($fichier))
$fp = fopen("$fichier", "r");
else{ // unknown file
echo "File not found !<br />Import stopped.";
exit();
}
echo 'Begin file import : <b>'.$fichier.'</b><br /><br />';
echo '<table border="1" cellspacing="3">';
echo '<tr><td>UID</td><td>Pseudo</td>';
// import line by line
while (!feof($fp)){
$ligne = fgets($fp,4096);
$liste = explode($separator,$ligne); //create array


// Add real name
$name = $liste[0];
// End

// Add real name
$uname = $liste[1];
// End

// Add real name
$email = $liste[2];
// End

// Add Password
$pass= md5(trim($liste[5]));
// End

// Add website
$url = $liste[3];
// End

// Add website
$user_regdate = $liste[4];
// End

// Add avatar
$user_avatar = "blank.gif";
// End

$rank = "1"; // Choose rank

$attachsig = "1";

$user_mailok = "1"; // Choose 1 if you allow administrator to send email


// Add user in xoops_users table
$query = "insert into ".$prefix."_users (name, uname, url, user_avatar, user_regdate, user_from, user_sig, pass, rank, user_occ, user_mailok, email) VALUES ('".$name."','".$uname."', '".$email."','".$url."', '".$user_avatar."', '".$regdate."', '".$user_from."', '".$user_sig."', '".$pass."', '".$rank."', '".$user_occ."', '".$user_mailok."')";

$result= mysql_query($query);
$uid = mysql_insert_id();

//Add user in group : registered users per default
$numgroup ='2';// choose Group number
$query = "insert into ".$prefix."_groups_users_link (groupid, uid) VALUES('".$numgroup."', '".$uid."')";
$result= mysql_query($query);

if (mysql_error()){
echo "Error in database : ".mysql_error();
echo "</table><br /><br /><b>Importation stoppee.</b><br /><br />";
fclose($fp);
exit();
}else{
echo "<tr><td> ".$uid." </td><td> ".$uname." </td></tr> ";
}
}

echo "</table><br />Import finished successfully.";// That's ok !

fclose($fp);
?>


I used this. It succesfully entered the unames BUT without the emails and without the regdate, and without the name. What did i did wrong?