• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 792
  • Last Modified:

PHP Mysql query looping, output multiple rows of data need help!

I have some code below that outputs a row with like 11 columns of data.  I need to change the code so that it checks the first table for multiple records based on a contact_ID # and outputs them into additional rows.

For example John Doe has a 2 or 3 loans with bank A. I need to output each loan data record in each row, however many John Doe may have in the database.

Here's my code. Perhaps the query can be written as one entire query then use a while loop??? I didnt architect the database, it's XRMS and I have to work with it.  

<?PHP

// #### -------- get opportunies ids and info ------- ####

$sql = "SELECT
opportunity_id, opportunity_type_id, opportunity_status_id, lender_id, campaign_id, company_id , division_id, user_id, entered_by
FROM opportunities
WHERE contact_id = '" . $contact_id . "'";

$rst = $con->execute($sql);
extract($rst->fields);

// #### -------- get company_name ------- ####

$sql = "SELECT
company_name
FROM companies
WHERE company_id = '" . $company_id . "'";

$rst = $con->execute($sql);
extract($rst->fields);


// #### -------- get loan requested amount ------- ####

$sql = "SELECT
amount AS loan_requested FROM opportunity_amounts
WHERE opportunity_amount_type_id = 1
AND opportunity_id = '" . $opportunity_id . "'";

$rst = $con->execute($sql);
extract($rst->fields);

// #### -------- get credit line amount ------- ####

$sql = "SELECT
amount AS credit_line FROM opportunity_amounts
WHERE opportunity_amount_type_id = 2
AND opportunity_id = '" . $opportunity_id . "'";

$rst = $con->execute($sql);
extract($rst->fields);

// #### -------- get financed amount ------- ####

$sql = "SELECT
amount AS financed_amount FROM opportunity_amounts
WHERE opportunity_amount_type_id = 3
AND opportunity_id = '" . $opportunity_id . "'";

$rst = $con->execute($sql);
extract($rst->fields);

// #### -------- get charge auth amount ------- ####

$sql = "SELECT
amount AS charge_auth_amount FROM opportunity_amounts
WHERE opportunity_amount_type_id = 4
AND opportunity_id = '" . $opportunity_id . "'";

$rst = $con->execute($sql);
extract($rst->fields);

// #### -------- get lender short name ------- ####

$sql = "SELECT
lender_short_name FROM lenders
WHERE id = '" . $lender_id . "'";

$rst = $con->execute($sql);
extract($rst->fields);

// #### -------- get the owner of the record ------- ####

$sql = "SELECT
last_name FROM users
WHERE user_id = '" . $entered_by . "'";

$rst = $con->execute($sql);
extract($rst->fields);

// #### -------- get the CF instance_id ------- ####

$sql = "SELECT
instance_id FROM cf_instances
WHERE key_id = '" . $contact_id . "'";

$rst = $con->execute($sql);
extract($rst->fields);


// #### -------- get the CF loan rate data ------- ####

$sql = "SELECT
value as loan_rate FROM cf_data
WHERE instance_id = '" . $instance_id . "'
AND field_id = 25";

$rst = $con->execute($sql);
extract($rst->fields);

// #### -------- get the CF account number data ------- ####

$sql = "SELECT
value as account_number FROM cf_data
WHERE instance_id = '" . $instance_id . "'
AND field_id = 24";

$rst = $con->execute($sql);
extract($rst->fields);

}


// THE HTML OUTPUT OF THE ROWS

<tr bgcolor="#FFFFFF">
<td align="center"><?php echo render_edit_button("Edit", 'button', "javascript: location.href='worksheets/worksheet.php?opportunity_id=$opportunity_id';") ?></td>
<td align="center" style="line-height: 25px; vertical-align: middle"><?=$opportunity_status_short_name?></td>
<td align="center" style="line-height: 25px; vertical-align: middle"><a href="/xrms/companies/one.php?company_id=<?=$company_id?>"><?=$company_name?></a></td>
<td align="center" style="line-height: 25px; vertical-align: middle"><?=$loan_requested?></td>
<td align="center" style="line-height: 25px; vertical-align: middle"><?=$credit_line?></td>
<td align="center" style="line-height: 25px; vertical-align: middle"><?=$financed_amount?></td>
<td align="center" style="line-height: 25px; vertical-align: middle"><?=$charge_auth_amount?></td>
<td align="center" style="line-height: 25px; vertical-align: middle"><? if ($lender == 0) { echo "ADS"; } else { echo "$lender_short_name"; } ?></td>
<td align="center" style="line-height: 25px; vertical-align: middle"><? if ($loan_rate == "") { echo "Not Set"; } else { echo "$loan_rate"; } ?></td>
<td align="center" style="line-height: 25px; vertical-align: middle"><? if ($account_number == "") { echo "Not Set"; } else { echo "$account_number"; } ?></td>
<td align="center" style="line-height: 25px; vertical-align: middle"><?=$last_name?></td>
</tr>

0
pdheady
Asked:
pdheady
  • 2
2 Solutions
 
trickyidiotCommented:
Some things I'm not seeing in any of your SQL queries that you may want to do some research on if you're not already familiar:
ORDER BY
LIMIT
and joins

Learning about thtese would probably open up your eyes to an easy answer
0
 
pdheadyAuthor Commented:
how do u perform a query and loop through to output multiple rows?
0
 
nitinsawhneyCommented:
Looping a recordset is simple:

In general:
while ($row = mysql_fetch_array($result)) {

//Do something here

}

In your case:
while ($row = extract($rst->fields)) {

//Do something here

}

Hope this helps.
But definitely your Queries are not optimized aa mentioned by trickyidiot. You could achieve what you want using joins.
0
 
pdheadyAuthor Commented:
I was able to do the looping part last night and actually got everything working looping through the opportunity keys. Thanks.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now