Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Aug 2011
    Posts
    19

    Unanswered: 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

    1. Add User
    2. populate the additional tables with the new user id
    3. 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

    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

  2. #2
    Join Date
    May 2011
    Posts
    24
    you can assign the last insert ID to a php variable right after the insert on table 'user'.

  3. #3
    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

  4. #4
    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 01:38.

  5. #5
    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.

  6. #6
    Join Date
    May 2011
    Posts
    24
    mind showing how you assign the last insert id into a php variable?

  7. #7
    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);
        }

  8. #8
    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 05:25.

  9. #9
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    In your PHP code I do not see where you are inserting into the employer table?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  10. #10
    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 (iduser_idfirstnamelastnameid_salutationother_titlecomp_nameprimary_phonefax_numberwebsitestreet_addrid_countryprovenceidregionidstatecityzipemploy_descid_comp_typeid_industryshow_nameshow_locationshow_phoneshow_emailshow_addrshow_faxbill_addrbill_addr_contbill_id_countrybill_provenceidbill_regionbill_statebill_city, `int`, bill_zipbill_phone)
    VALUES ('','LAST_INSERT_ID()''--''--'6'--''Sedgwick Museum of Earth Sciences''0000 000 0000'''''''20000'0'''11'n''n''n''n''n''n'''''200000' CW11 1HZ ''0000 000 0000'); 
    and we can all see thats it's not really much use

  11. #11
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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);
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  12. #12
    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 08:17. Reason: forgot current code snippet

  13. #13
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  14. #14
    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

  15. #15
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •