Query if / then confusion

[jerryisgood]

Greetings! So I've been able to create new tables from existing columns but now I want to push my queries further and I'm not sure how complex I can make a query. Can I do something like this...?

 

Here is an example

 

Say my first table collects all my information about a particular doctor's office.. However, this office can have multiple practices which are stored in my second table.. Now, if Doctor Jame's ID number is 345 in Table 1, his tempID will be 345 in Table 2. So if Doctor James lists two practices in my form, the second table will have auto incrementing IDs but will store the new information in a new row with the same columns and tempID as above... so really, the tempID stays the same for each entry, and only the input data changes and the ID incremented by one.

 

SO...

 

Can I write something that would check the column tempID for duplicates, such as 345 for both entries, and write a NEW column for each field in that record [in the new queried table] .. If not that, can I write each row to a new temporary table that can be merged with whatever else I need from the query?

 

Sorry if I lost you guys.. does this make sense?

[abdfahim]

have to admit, I am really lost

 

I think it'll be better if you just give a small practical example of what you tables' structure and what you want (not description, example by pasting similar table structure and output structure)

[jerryisgood]

have to admit, I am really lost

 

I think it'll be better if you just give a small practical example of what you tables' structure and what you want (not description, example by pasting similar table structure and output structure)

 

Perhaps this would be a better question, haha.

 

My programmer kind of left me high and dry and I'm a bit confused. I needed help with two sections on my web form because I couldn't figure out how to store them in my database properly. There are two sections on my web form, which you can see here (http://youawfulme.com/Form.html) that are expandable.. Providers and references.. The code to handle these two sections look like this:

 

// Insert Providers..

if(count($_POST['tfa_FirstName1']) > 1)

{

 

for($i = 0; $i < count($_POST['tfa_FirstName1']); $i++)

{

 

$tmpsql = "INSERT INTO tmpProviders (tmpId,FirstName,LastName,LicenseType,PrimarySpecialty,SecondarySpecialty,NYStateLicenseNum,DEANumber,NPINumber,EmailAddress,DaysPerWeek,PrimaryCareProv) VALUES ($oldid,'" . $_POST['tfa_FirstName1'][$i] . "'";

 

$tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_LastName1'][$i]) . "'";

$tmpsql = $tmpsql . ",'" . preg_replace("/tfa_([a-zA-Z]+)\d*/","\\1",$_POST['tfa_LicenseType1'][$i]) . "'";

$tmpsql = $tmpsql . ",'" . preg_replace("/tfa_([a-zA-Z]+)\d*/","\\1",$_POST['tfa_PrimarySpecialty1'][$i]) . "'";

$tmpsql = $tmpsql . ",'" . preg_replace("/tfa_([a-zA-Z]+)\d*/","\\1",$_POST['tfa_SecondarySpecial'][$i]) . "'";

$tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_NYStateLicenseNu1'][$i]) . "'";

$tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_DEANumber1'][$i]) . "'";

$tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_NPINumber1'][$i]) . "'";

$tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_EmailAddress1'][$i]) . "'";

$tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_DaysPerWeek1'][$i]) . "'";

$tmpsql = $tmpsql . ",'" . preg_replace("/tfa_([a-zA-Z]+)\d+/","\\1",$_POST['tfa_Ifthisindividual'][$i]) . "');";

 

      mysql_query($tmpsql);

  }

 

}

else

{

  $tmpsql = "INSERT INTO tmpProviders (tmpId,FirstName,LastName,LicenseType,PrimarySpecialty,SecondarySpecialty,NYStateLicenseNum,DEANumber,NPINumber,EmailAddress,DaysPerWeek,PrimaryCareProv) VALUES ($oldid,'" . $_POST['tfa_FirstName1'] . "'";

      $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_LastName1']) . "'";

      $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_([a-zA-Z]+)\d*/","\\1",$_POST['tfa_LicenseType1']) . "'";

      $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_([a-zA-Z]+)\d*/","\\1",$_POST['tfa_PrimarySpecialty1']) . "'";

      $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_([a-zA-Z]+)\d*/","\\1",$_POST['tfa_SecondarySpecial']) . "'";

      $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_NYStateLicenseNu1']) . "'";

      $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_DEANumber1']) . "'";

      $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_NPINumber1']) . "'";

      $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_EmailAddress1']) . "'";

      $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_DaysPerWeek1']) . "'";

      $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_([a-zA-Z]+)\d+/","\\1",$_POST['tfa_Ifthisindividual']) . "');";

 

  mysql_query($tmpsql);

 

}

 

if(count($_POST['tfa_PracticeName']) > 1)

{

 

I can't honestly say I understand all that what's going on here.

 

What I know is this: My data is being saved into three separate "tmp" tables that autoclear after being moved to three master tables. The first table collects all of the data from that form minus the two sections I had mentioned, which go to the other two respective tables.

 

NOW... This is problematic for me because if the user enters more than one provider or referral, the records may look something like this...

 

Table 1 - Final Data

ID  Practice    Name ... etc

001  Sample    Joe S  ...

 

Table 2 - Final Provider

ID    TMP ID    Provider Name

234  001        John Doe

235  001        Mary Jane

 

So the tmpID matches the ID in the main file so we know they are connected.. but if I go to do a mail merge down the line, I can't do this because they share the same column name

 

My goal is this..

 

I want ONE master table that will consider the tmpID in the temporary Provider and Refferal Tables.. It can look.. If there is a matching tmpID, it will fill out new columns (with names like, say Second Provider, Third Provider, .. Second Provider Type, Third Provider Type .. etc.)

 

If it's a new tmp ID, meaning a new record all together.. Those fields would just be left blank.

 

Does this help any? Does anyone know if this is possible? I need all the help I can get because I'd like to wrap this up this evening 

 

Thanks for the help guys!

 

 

 

 

[fenway]

Sorry, why can't you store these data as-is?

[jerryisgood]

Sorry, why can't you store these data as-is?

 

My apologies, I hope this helps:

 

 

This is one of the two expandable fields in question. So for this particular form, I am entering two referrals, hitting the next button once. So I will have referral 0 and referral 1.

 

In my Referrals table, this is my entry. The ID increments but the tempID remains the same showing that we are dealing with the same form/application. In the table including all of the other post data, minus providers, the ID would match the tempID here.

 

This format does not work for my mail merge operation down the line because that draws from column names. So say I have two fields in my contract fro Provider Name under referrals, I can't use <<Provider Name>> because they both share that column. I would instead want every additional entry to be placed in the same row with a unique column name.

 

Does this make more sense?

[fenway]

Not to me, sorry -- maybe to someone else.