Hi,
I think I need a fresh pair of eyes on one of my scripts.
I've adapted the following code for my own purposes and it almost works perfectly. When it gets to the last table (line 168: add record to jobs table) it adds the record twice and I can't for the life of me work out why?
Can anyone highlight why it's doing this, I've been scratching my head for days.
Many thanks
<?php
/*
Script to bulk import users into a Joomla installation (v 1.5).
*/
// Hande form upload
if(isset($_POST['import'])) {
$mysql_host = trim($_POST['mysql_host']);
$mysql_user = trim($_POST['mysql_username']);
$mysql_password = trim($_POST['mysql_password']);
$mysql_schema = trim($_POST['mysql_schema']);
$table_prefix = trim($_POST['table_prefix']);
if(!mysql_connect($mysql_host, $mysql_user, $mysql_password) || !mysql_select_db($mysql_schema)) {
echo 'Supplied MySQL details were incorrect - aborting';
return;
}
// Get the joomla groups
$sql = sprintf('
SELECT `id`, `value`
FROM `%score_acl_aro_groups`
',
$table_prefix
);
$rs = mysql_query($sql);
$groups = array();
while($group = mysql_fetch_object($rs)) {
$groups[$group->value] = $group->id;
}
$fp = fopen($_FILES['csv']['tmp_name'], 'r');
while($user = fgetcsv($fp)) {
printf('Importing "%s" ... ', $user[0]);
// Lookup and verify user group
if(!isset($groups[$user[4]])) {
printf('error: Invalid group (%s) for %s. Defaulting to <code>Registered</code><br />%s', $user[4], $user[0], PHP_EOL);
$user[4] = 'Registered';
}
// Insert record into users
$sql = sprintf('
INSERT INTO `%susers`
SET
`name` = "%s",
`username` = "%s",
`email` = "%s",
`password` = "%s",
`usertype` = "%s",
`block` = "%s",
`sendEmail` = "%s",
`gid` = "%s",
`registerDate` = NOW(),
`lastvisitDate` = "0000-00-00 00:00:00",
`activation` = "",
`params` = ""
',
$table_prefix,
sql_prep($user[0]),
sql_prep($user[1]),
sql_prep($user[2]),
isset($_POST['md5_passwords']) ? md5($user[3]) : sql_prep($user[3]),
sql_prep($user[4]),
sql_prep($user[5]),
sql_prep($user[6]),
$groups[$user[4]]
);
mysql_query($sql);
// Get back the user's ID
list($user_id) = mysql_fetch_row(mysql_query('SELECT LAST_INSERT_ID()'));
// Insert record into core_acl_aro
$sql = sprintf('
INSERT INTO `%score_acl_aro`
SET
`section_value` = "users",
`value` = %d,
`name` = "%s"
',
$table_prefix,
$user_id,
sql_prep($user[0])
);
mysql_query($sql);
// Insert record into core_acl_groups_aro_map
$sql = sprintf('
INSERT INTO `%score_acl_groups_aro_map`
SET
`group_id` = %d,
`aro_id` = LAST_INSERT_ID()
',
$table_prefix,
$groups[$user[4]]
);
mysql_query($sql);
// Now add in the code for employer using $user_id
$sql = sprintf('
INSERT INTO %stpjobs_employer
SET
`user_id` = %d,
`firstname` = "%s",
`lastname` = "%s",
`id_salutation` = "%s",
`other_title` = "%s",
`comp_name` = "%s",
`primary_phone` = "00000 000000",
`fax_number` = "00000 000000",
`street_addr` = "%s",
`id_country` = "%s",
`state` = "%s",
`city` = "%s",
`zip` = "%s",
`id_comp_type` = "%s",
`id_industry` = "%s",
`show_name` = "n",
`show_location` = "n",
`show_phone` = "n",
`show_email` = "n",
`show_addr` = "n",
`show_fax` = "n",
`bill_addr` = "%s",
`bill_addr_cont` = "%s",
`bill_id_country` = "%s",
`bill_state` = "%s",
`bill_city` = "%s",
`bill_zip` = "%s",
`bill_phone` = "%s"
',
$table_prefix,
$user_id,
sql_prep($user[7]),
sql_prep($user[8]),
sql_prep($user[9]),
sql_prep($user[10]),
sql_prep($user[11]),
sql_prep($user[12]),
sql_prep($user[15]),
sql_prep($user[16]),
sql_prep($user[17]),
sql_prep($user[18]),
sql_prep($user[19]),
sql_prep($user[20]),
sql_prep($user[21]),
sql_prep($user[28]),
sql_prep($user[29]),
sql_prep($user[30]),
sql_prep($user[31]),
sql_prep($user[32]),
sql_prep($user[33]),
sql_prep($user[34])
);
mysql_query($sql) or die("ERROR: query: $sql: " . mysql_error());
// Now add the job to the job table
$sql = sprintf('
INSERT INTO %stpjobs_job
SET
`employer_id` = %d,
`job_title` = "%s",
`id_degree_level` = "%s",
`id_pos_type` = "%s",
`id_salary_type` = "%s",
`id_job_exp` = "%s",
`id_job_spec` = "%s",
`salary` = "%s",
`currency_salary` = "%s",
`id_country` = "%s",
`state` = "%s",
`city` = "%s",
`short_desc` = "%s",
`long_desc` = "%s",
`publish_date` = NOW(),
`expire_date` = "%s",
`is_active` = "n",
`Organisation` = "%s"
',
$table_prefix,
$user_id,
sql_prep($user[34]),
sql_prep($user[35]),
sql_prep($user[36]),
sql_prep($user[37]),
sql_prep($user[38]),
sql_prep($user[39]),
sql_prep($user[40]),
sql_prep($user[41]),
sql_prep($user[42]),
sql_prep($user[43]),
sql_prep($user[44]),
sql_prep($user[45]),
sql_prep($user[46]),
sql_prep($user[47]),
sql_prep($user[48]),
sql_prep($user[49]),
sql_prep($user[50]),
sql_prep($user[51]),
sql_prep($user[52])
);
mysql_query($sql) or die("ERROR: query: $sql: " . mysql_error());
echo 'done.';
flush();
}
echo '<br /><br /><strong>Done</strong>';
} else {
// show upload form
?>
<html>
<head>
<title>Bulk import Jobs</title>
</head>
<body>
<h1>Import Users to Joomla</h1>
<p> Use this script to do a bulk import of users into Joomla 1.5.<br />
Upload a CSV file with the following format:<br />
<code> name, username, email, password, usertype, block, send_email </code><br />
Wrap details with commas in them in quotes. </p>
<hr />
<form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post" enctype="multipart/form-data">
<input type="hidden" name="import" value="1" />
<table cellpadding="4px">
<tr>
<td>CSV File: </td>
<td><input type="file" name="csv" /></td>
</tr>
<tr>
<td>MD5 Hash Passwords: </td>
<td><input type="checkbox" name="md5_passwords" />
<br />
<small>*Check this option if the passwords in your CSV are in plain text</small></td>
</tr>
<tr>
<td>Joomla Table Prefix: </td>
<td><input type="text" name="table_prefix" value="jos_" /></td>
</tr>
<tr>
<td>Joomla Database Name: </td>
<td><input type="text" name="mysql_schema" value="joomla" /></td>
</tr>
<tr>
<td>MySQL Host: </td>
<td><input type="text" name="mysql_host" value="localhost" /></td>
</tr>
<tr>
<td>MySQL Username: </td>
<td><input type="text" name="mysql_username" value="" /></td>
</tr>
<tr>
<td>MySQL Password: </td>
<td><input type="text" name="mysql_password" value="" /></td>
</tr>
<tr>
<td></td>
<td><input type="submit" name="submit" value=" Import Users! " /></td>
</tr>
</table>
</form>
</body>
</html>
<?php
}
function sql_prep($var) {
return mysql_real_escape_string($var);
}
mysql_query($sql) or die("ERROR: query failure: $sql: " . mysql_error());