[Resolved] Two tables unassociated need to be associated

[BLottman]

I'm having trouble conceptually understanding how to associate two tables that are currently unassociated.

Table: PROFILE (Number of rows = 0)

ID Primary Key
USERID
USERPW
DATECREATED

Table 2: BUSINESS (Number of rows = 2700)

ID Primary Key
BUSINESSNAME
ADDRESS
CITY
STATE
ZIP
ACCT = ID from table Profile

Task:

Loop through each row of the BUSINESS table. While on a specific row, generate a random USERID and USERPW. Insert them into the PROFILE table. Somehow, while still in the same row of the BUSINESS table, extract the row ID from the row just inserted in the PROFILE table and write it to the ACCT field in the BUSINESS table.

I'm unsure how to get started with this task. I'm not looking for code or scripts, just a smart way of accomplishing the task. Is my logic in the ballpark?

Bill

[devinemke]

Originally posted by BLottman
...While on a specific row, generate a random USERID and USERPW.

are there any contraints to what the random USERID and USERPW can be? how many characters? what characters are allowed/not allowed?

[BLottman]

devinemke:

I finally figured it out.

Since each row of table 2 had unique data, eventhough it's the Many table to the one primary table.

I used a for loop to populate the PROFILE Table:

<?php

include("common.php");
include("db.php");

dbConnect('mySQL_data');

// There are 2730 rows in the BUSINESS table

for ($i=0; $i <= 2730; $i++) {

mt_srand((double)microtime() * 1000000);
$myUserPW = substr(md5(mt_rand()),0,6);

mt_srand((double)microtime() * 1000000);
$myUserID = substr(md5(mt_rand()),0,6);

$sql = mysql_query("INSERT INTO PROFILE SET USERID = '$myUserID', USERPW='$myUserPW'") or die ("cant open/get database");

}

?>

Also since I needed to see the randomly generated passwords, so that I could test the login, I didn't insert the USERPW as PASSWORD($myUserPW).

Once the Profile Table was populated and a row ID was created automatically, I then wrote another script to associate the two tables by id.profile.

Looks like this:

<?php

include("common.php");
include("db.php");

dbConnect('mySQL_data');

// Upperbound for i = 12959 thus 12959-10229 = 2730 rows in the business table

$i = 10229;

// $j refers to the ID number of each row in the profile table that was previously populated

for ($j=1; $j <= 2731; $j++) {

$sql = mysql_query("UPDATE BUSINESS SET ACCT='$j' WHERE ID='$i'") or die ("cant open/get database");
$i++;

}

?>

So the rows in the Business table are now associated with the rows in the Profile table through the ACCT column in the Business table.

Now exported all of the data to a spreadsheet and I can now go back and apply PASSWORD($myUserPw) to the PROFILE table USERPW column so that it's fully encrypted.

I'm sure there's a much more eloquent and smarter way to do it - but it worked for me.

Thanks for responding.