| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
08-05-11, 00:10
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 19
|
|
|
populating more than one table with a user ID
|
|
Hi, I wonder if you can help me with an issue I don't seem able to resolve. If I've posted into the wrong area, my apologies.
I'm running a Joomla based recruitment website and have been trying to find a way of doing a bulk insert of users and employers via SQL.
I've identified the areas needed are to
- Add User
- populate the additional tables with the new user id
- Create new employer record in employer table using the new user id
So far I have been able to add the user and populate the other core user tables as follows.
Code:
INSERT INTO jos_users( `Name`, `Username`, `Password`, `Email`, `Usertype`, `Gid` )
VALUES( "Ivan Ivanov", 'Ivanov', Md5('12345'), 'Ivanov@********', 'Registered', 18 );
INSERT INTO jos_core_acl_aro( `Section_value`, `Value` )
VALUES ( 'Users', LAST_INSERT_ID() );
INSERT INTO jos_core_acl_groups_aro_map( `Group_id`, `Aro_id` )
VALUES ( 18, LAST_INSERT_ID() );
This works fine but I am lost when it comes to creating the new entry in the employer table.
The normal INSERT INTO would look like
Quote:
INSERT INTO jos_tpjobs_employer (id, user_id, firstname, lastname, id_salutation, other_title, comp_name, primary_phone, fax_number, website, street_addr, id_country, provenceid, regionid, state, city, zip, employ_desc, id_comp_type, id_industry, show_name, show_location, show_phone, show_email, show_addr, show_fax, bill_addr, bill_addr_cont, bill_id_country, bill_provenceid, bill_region, bill_state, bill_city, `int`, bill_zip, bill_phone)
VALUES ('','', '--', '--', 6, '--', '', '0000 000 0000', '', '', '', 2, 0, 0, 0, 0, '0', '', 1, 1, 'n', 'n', 'n', 'n', 'n', 'n', '', '', 2, 0, 0, 0, 0, 0, ' ', '0000 000 0000');
|
I understand that normally you would use the "LAST_INSERT_id" to pull the id into the table, but the problem is this works for the last id generated, which is not the users table. It seems a bit of a catch 22 situation.
Joomla requires these three tables to be updated when a new user is created else that new user doesn't actually show up.
So the question is how can i grab the id and username from the `user` table and insert them into a new record in the `employer` table.
Thanks in advance
|
|
08-05-11, 00:20
|
|
Registered User
|
|
Join Date: May 2011
Posts: 24
|
|
you can assign the last insert ID to a php variable right after the insert on table 'user'.
|
|
08-05-11, 00:26
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 19
|
|
|
|
Thanks Reeson,
Though am still a little confused (bit of a noob), apologies if i haven't understood.
As I see it, if i dont populate the three user tables in the order shown the user wont be registered properly, therefore to put the insert to the employer table directly after the initial user table would mean that the last_inserted_id would be incorrect for the remaining user tables.
please correct me if i am misunderstanding, and thanks for the repsonse
|
|
08-05-11, 00:33
|
|
Registered User
|
|
Join Date: May 2011
Posts: 24
|
|
no it wont because last_insert_id() in those two queries are mysql variable/function.
you are assigning last insert ID of table 'user' into a php variable.
it wont mess with each other.
|
Last edited by reeson; 08-05-11 at 00:38.
|
08-05-11, 02:26
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 19
|
|
Still no joy. There is nothing being populated to the `employer` table field user_id. it simply displays '0' in the database.
Im competely stumped.
|
|
08-05-11, 02:30
|
|
Registered User
|
|
Join Date: May 2011
Posts: 24
|
|
mind showing how you assign the last insert id into a php variable?
|
|
08-05-11, 04:12
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 19
|
|
well here's the php code minus the adding the new user to the employer table.
I've taken that out because it had been changed so much it is proper messed up.
If anyone could suggest how to populate the employer table simultaniously with each new user (and the correct id from the users table), i'd be eternally grateful.
PHP Code:
<?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 ther 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);
echo 'done.';
flush();
}
echo '<br /><br /><strong>Done</strong>';
} else {
// show upload form
?>
<html><head><title>Bulk import users into Joomla 1.5</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);
}
|
|
08-05-11, 04:22
|
|
Registered User
|
|
Join Date: May 2011
Posts: 24
|
|
instead of
PHP Code:
list($user_id) = mysql_fetch_row(mysql_query('SELECT LAST_INSERT_ID()'));
try
PHP Code:
$user_id = mysql_insert_id();
btw, 'users' table does have auto_increment column right?
|
Last edited by reeson; 08-05-11 at 04:25.
|
08-05-11, 04:36
|
|
Registered User
|
|
Join Date: Sep 2009
Location: San Sebastian, Spain
Posts: 620
|
|
In your PHP code I do not see where you are inserting into the employer table?
|
|
08-05-11, 04:49
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 19
|
|
Thanks it-iss.com, I did say in my last post, that i took it out completely cos i'd hackedit about so much, it was totally messed up and wasn't sure it was in the right place anyway.
What i ended up with was
PHP Code:
INSERT INTO jos_tpjobs_employer (id, user_id, firstname, lastname, id_salutation, other_title, comp_name, primary_phone, fax_number, website, street_addr, id_country, provenceid, regionid, state, city, zip, employ_desc, id_comp_type, id_industry, show_name, show_location, show_phone, show_email, show_addr, show_fax, bill_addr, bill_addr_cont, bill_id_country, bill_provenceid, bill_region, bill_state, bill_city, `int`, bill_zip, bill_phone)
VALUES ('','LAST_INSERT_ID()', '--', '--', 6, '--', 'Sedgwick Museum of Earth Sciences', '0000 000 0000', '', '', '', 2, 0, 0, 0, 0, '0', '', 1, 1, 'n', 'n', 'n', 'n', 'n', 'n', '', '', 2, 0, 0, 0, 0, 0, ' CW11 1HZ ', '0000 000 0000');
and we can all see thats it's not really much use 
|
|
08-05-11, 05:28
|
|
Registered User
|
|
Join Date: Sep 2009
Location: San Sebastian, Spain
Posts: 620
|
|
Actually it looks like you were very close. If you add the final lines of PHP
Code:
// Get back ther 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 jos_tpjobs_employer (id, user_id, firstname, lastname, id_salutation, other_title, comp_name, primary_phone, fax_number, website, street_addr, id_country, provenceid, regionid, state, city, zip, employ_desc, id_comp_type, id_industry, show_name, show_location, show_phone, show_email, show_addr, show_fax, bill_addr, bill_addr_cont, bill_id_country, bill_provenceid, bill_region, bill_state, bill_city, `int`, bill_zip, bill_phone)
VALUES ('',%d, '--', '--', 6, '--', 'Sedgwick Museum of Earth Sciences', '0000 000 0000', '', '', '', 2, 0, 0, 0, 0, '0', '', 1, 1, 'n', 'n', 'n', 'n', 'n', 'n', '', '', 2, 0, 0, 0, 0, 0, ' CW11 1HZ ', '0000 000 0000')", $user_id);
mysql_query($sql);
|
|
08-05-11, 07:15
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 19
|
|
Thanks for that..
so close, yet still feeling so far away 
When i look at the tables. It's only inserting to the user table.
In the Joomla backend the user isn't visible, which leads me to think the other tables, no employer record, just the user table gets an insert.
The code as it stands...
PHP Code:
<?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 ther 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);
// Now add in the code for employer using $user_id
$sql = sprintf("
INSERT INTO jos_tpjobs_employer (id, user_id, firstname, lastname, id_salutation, other_title, comp_name, primary_phone, fax_number, website, street_addr, id_country, provenceid, regionid, state, city, zip, employ_desc, id_comp_type, id_industry, show_name, show_location, show_phone, show_email, show_addr, show_fax, bill_addr, bill_addr_cont, bill_id_country, bill_provenceid, bill_region, bill_state, bill_city, `int`, bill_zip, bill_phone)
VALUES ('',$user_id, '--', '--', 6, '--', '', '0000 000 0000', '', '', '', 2, 0, 0, 0, 0, '0', '', 1, 1, 'n', 'n', 'n', 'n', 'n', 'n', '', '', 2, 0, 0, 0, 0, 0, ' ', '0000 000 0000')");
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);
echo 'done.';
flush();
}
echo '<br /><br /><strong>Done</strong>';
} else {
// show upload form
?>
<html><head><title>Bulk import users into Joomla 1.5</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>
hat
</form>
</body></html>
<?php
}
function sql_prep($var) {
return mysql_real_escape_string($var);
}
|
Last edited by tsp003; 08-05-11 at 07:17.
Reason: forgot current code snippet
|
08-05-11, 07:51
|
|
Registered User
|
|
Join Date: Sep 2009
Location: San Sebastian, Spain
Posts: 620
|
|
Everything appears correct but it does suggest like there is an error occurring. What you could do is the following:
mysql_query($sql) or die("ERROR: query failure: $sql: " . mysql_error());
I think there is something wrong in the insert statement which is causing the issue. Are you running this from the command line?
|
|
08-05-11, 08:08
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 19
|
|
I'm running it directly from a php file in the browser, with the error checking i get
Code:
ERROR: query failure: INSERT INTO `jos_core_acl_groups_aro_map` SET `group_id` = 18, `aro_id` = LAST_INSERT_ID() : Duplicate entry '18--3213' for key 1
Unfortunately I dont know what this means 
|
|
08-05-11, 08:36
|
|
Registered User
|
|
Join Date: Sep 2009
Location: San Sebastian, Spain
Posts: 620
|
|
Now are getting somewhere. This error means that an entry exists in this table already for the primary key or unique key defined for that table. What you need to do is from the MySQL prompt, issue the following command:
SHOW CREATE TABLE jos_core_acl_groups_aro_map;
This will return the table definition along with primary key and/or unique keys defined for it. If you can post that we can then determine which part of the above is causing the issue.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
populating more than one table with a user ID
