A complex [for me] if / else statement

[jerryisgood]

Greetings - So I have an issue with my form right now which can be found here: http://youawfulme.com/Form.html

 

If you look, you will see two expanding columns under Providers and References.

 

The HTML code for them to expand looks like this:

<span class="duplicateSpan"><a id="tfa_0583059728034-wfDL" class="duplicateLink" href="#">Next Provider</a></span>

 

and the PHP code that handles their database storage looks 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)

{

 

 

At this moment, when you fill out my form, all of the information saves to three separate tables. The Provider input goes into a temporary providers table, the Referrals to a temporary Referral , and the rest of the data to a temporary information table. Once the user submits the application, the temporary tables are cleared after having been moved to permanent tables (Still three tables)

 

My goal is for all of these values to save to one table, whether it be from three separate temps to one final or one temp to one final. My programmer was having trouble figuring it out and kind of left me on my own.. I really need some help 

 

The Providers and Reference tables are done in such a way that if there are more than one entry, it will create a new row for each entry with a new ID number but the same "tempid" number, the latter matching the ID number in the main information table.

 

Can I have it such that PHP will check to see if the tempIDs match, and if they do, move the information into new columns on the same row? So that way, instead of having say 3 different IDs with one temp ID for three different providers.. instead have that information all placed on one row, with one ID, and new columns?

 

In my head, I figured I would expand my table 5x. If I have five columns now, I will have 25 after. For each "Provider Name", I will now have "Second Provider Name", "Third Provider" name, etc... If the user only adds one provider, these won't be used. If they had multiples, up to five, the info will be placed into these new columns.

 

I just can't get this to work.. When I expand the column and inspect the element in HTML, it says, for example, Provider[1] and Provider[2] .. Incrementing in brackets for each addition. But if I try to, say

 

$SecondProviderName = $_POST['tfa_ProviderName[2]'];

 

.. It simply doesn't work.

 

 

Sorry for all this, I just need some help and so far you guys have been a great resource.

 

Thank you

 

 

[MadTechie]

I really don't feel like digging around your form and getting what i need,

can you provide a short example of what your trying to do!

[jerryisgood]

I really don't feel like digging around your form and getting what i need,

can you provide a short example of what your trying to do!

 

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?

 

[MadTechie]

Okay i think i have a better understanding now

 

I REALLLLLY would recommend NOT taking the "Provider Name", "Second Provider Name", "Third Provider" approach,

I assume the tmp tables is identical to the erm.. live tables

 

If your extracting this data then you could use a SQL JOIN, linking tempId to the table with the remaining details,

 

if you could give a little detail about the database tables (name and fields and what links to what and some samples)

and then let me know what your like to output,

[jerryisgood]

Okay i think i have a better understanding now

 

I REALLLLLY would recommend NOT taking the "Provider Name", "Second Provider Name", "Third Provider" approach,

I assume the tmp tables is identical to the erm.. live tables

 

If your extracting this data then you could use a SQL JOIN, linking tempId to the table with the remaining details,

 

if you could give a little detail about the database tables (name and fields and what links to what and some samples)

and then let me know what your like to output,

 

Thanks for the recommendation.

 

The tables are exactly the same. There is a tmpData, tmpProviders, and tmpReferrals which, upon submission of review, are moved to finalData, finalProviders, and finalReferrals respectively.

 

While it is true that I can SQL JOIN these three tables to create one, my problem still rests on the possibility that there are more than one entry for the same application in the Provider and Referral tables.

 

This is what I came up with for my SQL JOIN which worked to get all the information I'd need for my Mail Merge:

 

SELECT finalData.PracticeName,PracticeHead,MDorPC,CorpOrSolo,TaxIDNum,ProviderType,BillingStreet,BillingCity,BillingState,BillingPostalCode,PracticePhone,PracticeFax,OASalutation, OAFirstName, OALastName, OATelephone, OAEmail, CCSalutation, CCFirstName, CCLastName, CCTelephone, CCEmail, QISalutation, QIFirstName, QILastName, QITelephone, QIEmail, ITSalutation, ITFirstName, ITLastName, ITTelephone, ITEmail, NumberofEmployees,NumberofProviders,NumberofSites,PercentBelowPoverty,PercentUninsured,PercentMedicaid,PercentMedicare,PercentCommercial,EncountersPerYear,UniquePatients, finalProviders.*, finalReferral.*

 

FROM (finalData INNER JOIN finalProviders ON finalData.id = finalProviders.tmpId)

 

INNER JOIN finalReferral ON finalProviders.tmpid = finalReferral.tmpid;

 

So yes, this puts everything in one row.. However, look what happens from my example illustrated above. Since the Referral table has two rows for tmpID 141, my query has created TWO identical rows now sans the referral information.

 

 

Ideally I'd want these to all go to the same row with unique column names so that I can use them in my mail merge.

 

Thank you for the quick response by the way, I appreciate it.

 

 

[MadTechie]

I see your problem,

even using DISTINCT wouldn't help as the end results is for a mail merge,

it would seam that creating a temp table with the extra field would be the best approach, providing the table was only used for the mail merge, and not live data,

 

basically you would loop through the list adding the details from tmpData to the mail merge table,

 

the logic i would take would be as follows:~

 

create a temp table with all fields that are used, in the main table "tmpData"

set a variable ($ProvideCounter) to 0

set a variable ($LastProvideCount) to 0

set a variable ($lastDataID) to 0

set a variable ($CurrentRecord) to 0

loop joined queries results 

compare the $lastDataID to the tmpData.ID field

if it does not match then

  set $lastDataID to tmpData.ID

  set $ProvideCounter = 0;

  Insert the data into MailMerge table

  $CurrentRecord = mysql_insert_id();

else

  $ProvideCounter++;

  if($ProvideCounter > $LastProvideCount){

    create new fields "Provider{$ProvideCounter}" etc etc

  }

  set providers details to "Provider{$ProvideCounter}" fields where ID = $CurrentRecord

}

this should build a new table with all the details required,

 

I hope that makes sense!

 

 

EDIT: dame! lucky i can code better than i can explain things!!!

[jerryisgood]

I see your problem,

even using DISTINCT wouldn't help as the end results is for a mail merge,

it would seam that creating a temp table with the extra field would be the best approach, providing the table was only used for the mail merge, and not live data,

 

basically you would loop through the list adding the details from tmpData to the mail merge table,

 

the logic i would take would be as follows:~

 

create a temp table with all fields that are used, in the main table "tmpData"

set a variable ($ProvideCounter) to 0

set a variable ($LastProvideCount) to 0

set a variable ($lastDataID) to 0

set a variable ($CurrentRecord) to 0

loop joined queries results 

compare the $lastDataID to the tmpData.ID field

if it does not match then

  set $lastDataID to tmpData.ID

  set $ProvideCounter = 0;

  Insert the data into MailMerge table

  $CurrentRecord = mysql_insert_id();

else

  $ProvideCounter++;

  if($ProvideCounter > $LastProvideCount){

    create new fields "Provider{$ProvideCounter}" etc etc

  }

  set providers details to "Provider{$ProvideCounter}" fields where ID = $CurrentRecord

}

this should build a new table with all the details required,

 

I hope that makes sense!

 

 

EDIT: dame! lucky i can code better than i can explain things!!!

 

The logic makes sense I'm just having trouble turning it into working code  :-\. I also don't understand why we'd be drawing from the tmpData which doesn't include any of the information from the Referrals or Providers? Do you provide services? I'm trying to wrap this up over night and don't see myself reaching completion.

 

PS - Thank you for the timely and informative response.

[sasa]

you can use GROUP and GROUP_CONCAT in your query

[jerryisgood]

you can use GROUP and GROUP_CONCAT in your query

 

The group_concat method is interesting though it still wouldn't give me unique column names which I'd need for my mail merge operation. Thanks though

[sasa]

SELECT 
group_concat(if((select count(b.id) from table b where b.id <= a.id and a.grup = b.grup)=1, a.name, '') separator '') name_1,
group_concat(if((select count(b.id) from table b where b.id <= a.id and a.grup = b.grup)=2, a.name, '') separator '') name_2,
group_concat(if((select count(b.id) from table b where b.id <= a.id and a.grup = b.grup)=3, a.name, '') separator '') name_3,
group_concat(if((select count(b.id) from table b where b.id <= a.id and a.grup = b.grup)=4, a.name, '') separator '') name_4,
group_concat(if((select count(b.id) from table b where b.id <= a.id and a.grup = b.grup)=5, a.name, '') separator '') name_5

FROM table a 

group by a.grup

ORDER BY a.id