MySql Query

PHP
1

Dear friends

I am really very thankful to all of you for helping me in my first project.
I have successfully developed few scripts for my project. Now i am trying to code a script to pull out results from MySql, but i can’t figure out how to do it. Database structure is

Table ‘ledger’ has two fields named ‘insurers’ & ‘issuing_office’. ‘Insurers’ & ‘Issuing_office’ values are populated from Database to Dropdown & then the values are inserted in ledger table.

Now I am trying to code a loop to select values from ledger table for each ‘insurer’ & ‘issuing_office’ where ‘received_fees’ = 0. The Query is like this.

SELECT bill_no, bill_date, insured_name FROM ledger WHERE insurers=" $insurers " AND issuing_office=" $issuing_office " AND received_fees="0" ;

Insurers table has 12 rows & Issuing_office has 38 rows.
I wana loop through all insurers & all issuing offices where received_fees is Zero.

2

Hi friends
i have written the code successfully. But I need ur suggestions how can i improve this code.


$query = "SELECT insurers FROM insurers"; 
		$result = mysql_query($query); 
	
		if (!$result)
		{
		die ("Could not query the database: <br />". mysql_error( ));
		}	
			while ($insurers = mysql_fetch_assoc($result)) 
			{
				$queryI = "SELECT issuing_office FROM issuing_office"; 
				$resultI = mysql_query($queryI); 
				if (!$resultI)
					{
					die ("Could not query the database: <br />". mysql_error( ));
					}	
					while ($issuing_office = mysql_fetch_assoc($resultI)) 
					{												
						$queryF = 'SELECT bill_no, bill_date, ref_no, insured_name, vehicle, vehicle_no, 
								   date_of_loss, date_of_survey, claim_no, policy_no, survey_fees, remarks
								   FROM ledger WHERE insurers="' .$insurers['insurers']. '" 
								   AND issuing_office="' .$issuing_office['issuing_office'].'" AND received_fees=0';

								   
									$resultF = mysql_query($queryF);
									if (!$resultF)
									{
									die ("Could not query the database: <br />". mysql_error());
									}	
									 $rows = mysql_num_rows($resultF);
									 if ($rows > 0)
										{
										echo '<h3> Pending Fees : ' . $insurers['insurers'] . ' ' . $issuing_office['issuing_office'] . '</h3>';
												echo '<table border="1"><tr>
												<td><b>Sr No</b></td>
												<td><b>Bill No</b></td>
												<td><b>Bill Date</b></td>
												<td><b>Ref No</b></td>
												<td><b>Insured Name</b></td>
												<td><b>Claim of</b></td>
												<td><b>Vehicle No</b></td>
												<td><b>Date of Loss</b></td>
												<td><b>Date of Survey</b></td>
												<td><b>Claim No</b></td>
												<td><b>Policy No</b></td>
												<td><b>Survey Fees</b></td>												
												<td><b>Remarks</b></td></tr>';
												$i = 0;

												
												
												while ($row = mysql_fetch_array($resultF))
												{
												$i++;
												echo '<tr><td>' . $i . 
												     '</td><td>' . $row['bill_no'] .
													 '</td><td>' . $row['bill_date'] .
													 '</td><td>' . $row['ref_no'] . 
													 '</td><td>' . $row['insured_name'] . 
													 '</td><td>' . $row['vehicle'] .
													 '</td><td>' . $row['vehicle_no'] . 
													 '</td><td>' . $row['date_of_loss'] . 
													 '</td><td>' . $row['date_of_survey'] . 
													 '</td><td>' . $row['claim_no'] . 
													 '</td><td>' . $row['policy_no'] . 
													 '</td><td>' . $row['survey_fees'] .													 
													 '</td><td>' . $row['remarks'] .
												     '</td></tr>';
												}
												echo '</table>';

										}
					}
			}
3

First I might move the database calls out to individual functions.


function get_insurers()
{
    $data = array();
    $query = "SELECT insurers FROM insurers"; 
    $result = mysql_query($query); 
    if (!$result) { 
        die ("Could not query the database: <br />". mysql_error( )); 
    }
    while ($row = mysql_fetch_assoc($result)) { 
        $data[] = $row;
    }
    return $data;
}

function get_issuing_offices()
{
    $data = array();
    $query = "SELECT issuing_office FROM issuing_office";
    $result = mysql_query($query); 
    if (!$result) { 
        die ("Could not query the database: <br />". mysql_error( )); 
    }     
    while ($row = mysql_fetch_assoc($result)) {                
        $data[] = $row;
    }
    return $data;
}

function get_pending_fees($insurer, $issuingOffice)
{
    $data = array();
    $query = 'SELECT bill_no, bill_date, ref_no, insured_name, vehicle, vehicle_no,
               date_of_loss, date_of_survey, claim_no, policy_no, survey_fees, remarks 
               FROM ledger WHERE insurers="' . $insurer . '"
               AND issuing_office="' . $issuingOffice . '" AND received_fees=0'; 
                
    $result = mysql_query($query); 
    if (!$result) { 
        die ("Could not query the database: <br />". mysql_error()); 
    }     
    while ($row = mysql_fetch_assoc($result) {
        $data[] = $row;
    }
    return $data;    
}

We can also shift the presentation of pending fees to a separate function.


function show_pending_fees($pendingFees) {
    if (count($pendingFees) === 0) {
        return;
    }
    echo '<h3> Pending Fees : ' . $insurer['insurers'] . ' ' . $issuingOffice['issuing_office'] . '</h3>'; 
    echo '<table border="1"><tr> 
        <td><b>Sr No</b></td> 
        <td><b>Bill No</b></td> 
        <td><b>Bill Date</b></td> 
        <td><b>Ref No</b></td> 
        <td><b>Insured Name</b></td> 
        <td><b>Claim of</b></td> 
        <td><b>Vehicle No</b></td> 
        <td><b>Date of Loss</b></td> 
        <td><b>Date of Survey</b></td> 
        <td><b>Claim No</b></td> 
        <td><b>Policy No</b></td> 
        <td><b>Survey Fees</b></td>
        <td><b>Remarks</b></td></tr>'; 
    foreach ($pendingFees as $i => $pendingFee) {
        echo '<tr><td>' . $i + 1 .
            '</td><td>' . $row['bill_no'] . 
            '</td><td>' . $row['bill_date'] . 
            '</td><td>' . $row['ref_no'] .
            '</td><td>' . $row['insured_name'] .
            '</td><td>' . $row['vehicle'] . 
            '</td><td>' . $row['vehicle_no'] .
            '</td><td>' . $row['date_of_loss'] .
            '</td><td>' . $row['date_of_survey'] .
            '</td><td>' . $row['claim_no'] .
            '</td><td>' . $row['policy_no'] .
            '</td><td>' . $row['survey_fees'] . 
            '</td><td>' . $row['remarks'] . 
            '</td></tr>'; 
    } 
    echo '</table>'; 
}

Thanks to the above functions neatly encapsulating the work that needs to be done, the remaining code structure defines how the work gets done.


$insurers = get_insurers();
$issuingOffices = get_issuing_offices();
foreach ($insurers as $insurer) {
    foreach ($issuingOffices as $issuingOffice) {
        $pendingFees = get_pending_fees($insurer['insurers'], $issuingOffice['issuing_office']);
        show_pending_fees($pendingFees);
    } 
}
4

thanks a lot Mr pmw57 :slight_smile:

Your code suggestions were realy very helpful to me.
Thank you so much again.

rgrds

5

When you run the query in phpMyAdmin or via the MySQL console, are all the rows that you are expecting returned?

6

Let me explain my query again.

I am using phpmyadmin to query database.

Insurers table has 12 company names like

  1. NIC
  2. NIAC
  3. OIC
    …
    …
  4. BAL

& Issuing table has 32 city names like

  1. City1
  2. City2
    …
    …
    32 City32

Now the company NIC1 has 5 office in five different cities (City1, City2 etc)
Company NIAC has 10 office (City1, City2, City30 etc). Insurers & issuing_office fields has repeating company name & office.

A web form is created. In this form there are two dropdowns. Both dropdowns are populated from database table Insurers (with 12options) & issuing_office (with 32 options) & then the values are inserted into ‘ledger’ table in relevant fields (insurer & issuing_office).

First i wana generate list of company NIC & city1 where received_fees=0
& then company NIC & city2 & so on for each company & respective office.

at this point have stored Insurers & Issuing_Office values into two different arrays like this


$query = "SELECT insurers FROM insurers"; 
		$result = mysql_query($query); 
	
		if (!$result)
		{
		die ("Could not query the database: <br />". mysql_error( ));
		}	
			while ($row = mysql_fetch_assoc($result)) 
			{ 
			
			$insurers[] = $row['insurers'] ;
			}

print_r($insurers);	

echo '<br /><br /><br /><br />';		

$queryI = "SELECT issuing_office FROM issuing_office"; 
		$resultI = mysql_query($queryI); 
	
		if (!$resultI)
		{
		die ("Could not query the database: <br />". mysql_error( ));
		}	
			while ($row = mysql_fetch_assoc($resultI)) 
			{ 
			
			$issuing_office[] = $row['issuing_office'] ;
			}

print_r($issuing_office);

First I wana select NIC & City1 & then show fields where received_fees_=0 & then

NIC & City2 & the show fields.

I can’t figure out how to loop this query. May be it is possible with loop inside loop.

Please friends help me. I am working on this script from the last 2 days.