how to update a database by csv file ?

pmaonline

Hi, i'm trying to update my database for registered users, i have the code for inserting users into the database but i need to update that database everyday, the code for inserting is this :

<body>
<?php
$sql_hostname = 'localhost';

$sql_user = 'lr';

$sql_password = '';

$mambo_database_name = 'lr';

$path_to_csv = 'test_userlist.csv';

$csv_delimiter = ";";

$testing_mode = 0; // testing = 0 (zero) -> WRITE to the database
// testing = 1 -> do NOT WRITE to the database

$mambodb = mysql_connect($sql_hostname, $sql_user, $sql_password)
or die ('Não é possivel a conecção : ' . mysql_error());
print("Conectado à base de dados em : $sql_hostname<br>");

mysql_select_db($mambo_database_name, $mambodb) or die ('Não é possivel encontrar a base de dados ' .$mambo_database_name . ': ' . mysql_error());

$current_id = mysql_query('SELECT MAX(id) FROM mos_users');
while ($row = mysql_fetch_array($current_id)) {
$old_id = $row[0];
$new_id = $old_id + 1;

print ("<p>O ID mais alto é : " . $row[0]);
print ("<br>Os Novos IDs Começam em : " . $new_id) . "<br>";
}

$handle = fopen ($path_to_csv,"r");

while ($data = fgetcsv ($handle, 1000, $csv_delimiter)) {

$username = mysql_escape_string($data[0]);
$name = mysql_escape_string($data[1]);
$email = $data[2];
$registerdate = date("Y-m-d H:i:s");
$password = md5($data[3]);

$user=$username;

$result=mysql_query("SELECT username FROM mos_users order by username",$mambodb);
while ($myrow = mysql_fetch_array($result))
{
ECHO"<table>";
echo"<tr>";
echo $myrow["username"];
echo"</tr>";
ECHO"</table>";

if ( $myrow["username"]===$user)
{
echo "ERRO - ";
echo "O Nº de Conselheiro $user já existe.";

///////////////////////////////////

//mysql_query("UPDATE mos_users SET id='$new_id', name='$name', username='$username', email='$email', password='password' , usertype='registered', block='0', sendEmail='0', gid='1', registerDate='$registerdate', lastvisitDate='0000-00-00 00:00:00'";

/////////////////////////////////

}
}

if ($testing_mode === 0) { 


		mysql_query("INSERT INTO mos_users (id, name, username, email, password , usertype, block, sendEmail, gid, registerDate, lastvisitDate)
		VALUES ('$new_id', '$name', '$username', '$email', '$password', 'registered', '0', '0', '1', '$registerdate', '0000-00-00 00:00:00')") 
		or die("<br> Tabela não actualizada. Erro : " . mysql_error());

		mysql_query("INSERT INTO mos_core_acl_aro (aro_id , section_value , value , order_value , name , hidden) 
		VALUES ('$new_id', 'users', '$new_id', '0', '$name', '0')") 
		or die("<br> Mos_core_acl_aro not updated. Error is: " . mysql_error());

		mysql_query("INSERT INTO mos_core_acl_groups_aro_map (group_id , section_value , aro_id) 
		VALUES ('18', '', '$new_id')") or die("<br> Mos_core_acl_groups_aro_map not updated. Error is: " . mysql_error());



} 

switch ($testing_mode) { 
	case 0: 
		print ("<br> Utilizador " . $name . " Adicionado");
		break;
	case 1: 
		print ("<br> MODO DE TESTE : Utilizador " . $name . " Teria sido inserido na base de dados.");
		break;
	default: 
		print ("<br> \$testing_mode variável incorrecta : Utilizador " . $name . " teria sido inserido na base de dados.");
} 

$new_id++;

}

fclose ($handle);

mysql_close($mambodb);

$num_records = $new_id - $old_id - 1;
print ( "<br><br>" . $num_records . " utilizadores adicionados.");
print("<br> Processamento completo em : " . $registerdate);

</body>
</html>

Roger_Ramjet

And your question is??

pmaonline

my question is, how to insert in that code a query to update the users that already exist and just those who were changed and insert the new ones ???

Roger_Ramjet

Try the INSERT ... ON DUPLICATE KEY UPDATE syntax for your query.

pmaonline

i don't know how to do that, can you help me ?