CODEWITHSUNDEEP
phpmysql
Habitat
Habitat

Reputation: 709

Alternate query using if statement in PHP

I'm trying to run an alternate query if the initial query fails (it does because the id I'm searching for in this instance only exists in one of the databases being joined) using an if statement and I've constructed it like so:

<?php
require_once($_SERVER['DOCUMENT_ROOT'].'/php/link_costreport_2013.php');
$id = $_GET['id']; //ID # For page/query
if($query = $link->prepare("SELECT locale.id, locale.provider_num, locale.provider_name, locale.state, locale.city,
                        finstat_ca.coh_and_banks, finstat_ca.temp_investments, finstat_ca.notes_receivable, finstat_ca.accounts_receivable, finstat_ca.other_receivables,
                        finstat_ca.afun_and_ar, finstat_ca.inventory, finstat_ca.prepaid_expenses, (finstat_ca.other_cur_assets + finstat_ca.due_from_other_funds) as other_cur_assets, finstat_ca.total_current_assets,
                        finstat_fa.total_fixed_assets,
                        finstat_olta.investments, (finstat_olta.dep_on_leases + finstat_olta.due_from_owners_officers + finstat_olta.other_assets) as all_olta, finstat_olta.total_other_assets, finstat_olta.end_assets,
                        finstat_cl.accounts_payable, finstat_cl.salaries_wages_fees_payable, finstat_cl.payroll_taxes_payable, finstat_cl.notes_loans_payable, finstat_cl.deferred_income, finstat_cl.total_current_liabilities,
                        (finstat_cl.total_current_liabilities - (finstat_cl.accounts_payable + finstat_cl.salaries_wages_fees_payable + finstat_cl.payroll_taxes_payable + finstat_cl.notes_loans_payable + finstat_cl.deferred_income)) as all_other_cl,
                        finstat_ltl.mortgage_payable, finstat_ltl.notes_payable, finstat_ltl.unsecured_loans, finstat_ltl.other_long_term_liabilities, finstat_ltl.total_long_term_liabilities,
                        finstat_talfb.total_fund_balance, finstat_talfb.total_lia_plus_fb
                        FROM `locale`
                        INNER JOIN `finstat_ca`
                        ON locale.id = finstat_ca.id
                        INNER JOIN `finstat_fa`
                        ON locale.id = finstat_fa.id
                        INNER JOIN `finstat_olta`
                        ON locale.id = finstat_olta.id
                        INNER JOIN `finstat_cl`
                        ON locale.id = finstat_cl.id
                        INNER JOIN `finstat_ltl`
                        ON locale.id = finstat_ltl.id
                        INNER JOIN `finstat_talfb`
                        ON locale.id = finstat_talfb.id
                        WHERE locale.id = :id
                        LIMIT 1")){

} else {
$query = $link->prepare("SELECT id, provider_num, provider_name, state, city
                        FROM `locale`
                        WHERE id = :id
                        LIMIT 1");
}
$query->bindParam(':id', $id);
$query->execute();
$results = $query->fetch(PDO::FETCH_ASSOC);

echo json_encode($results);

Basically it defaults to the single table where the ID does exist and only pulls a couple fields as opposed to the large statement above it. My only issue is that my code here is not working. My JSON only says false when I echo it. It obviously should not.

Is there an error in my code here?

Thanks in advance

:edit: I should note that when I enter an ID that exists in all the tables joined, the correct result (json) is displayed on the page.

Upvotes: 1

Views: 50

Answers (1)

braks
braks

Reputation: 1610

I believe the problem is that even if ID does not exist in the first query, the $query variable still has a proper query in it and there is nothing false about it. That's not what you should be if-testing.
I think you should be testing $results.

This shows you the logic.

<?php
require_once($_SERVER['DOCUMENT_ROOT'] . '/php/link_costreport_2013.php');
//ID # For page/query
$id = $_GET['id'];

$sql_1 = "SQL CODE FOR QUERY 1";
$sql_2 = "SQL CODE FOR QUERY 2";

$query = $link->prepare($sql_1);
$query->bindParam(':id', $id);
$query->execute();
$results = $query->fetch(PDO::FETCH_ASSOC);

if (!$results)
{
    $query = $link->prepare($sql_2);
    $query->bindParam(':id', $id);
    $query->execute();
    $results = $query->fetch(PDO::FETCH_ASSOC);
}

echo json_encode($results);

However as you can see there are a few lines of code that are repeated inside the if-statement that very similar to code that was just before the if-statement. Perhaps with a loop that loops twice but breaks out if $results is not false would be neater.

<?php
require_once($_SERVER['DOCUMENT_ROOT'] . '/php/link_costreport_2013.php');
//ID # For page/query
$id = $_GET['id'];

$sql[] = "SQL CODE FOR QUERY 1";
$sql[] = "SQL CODE FOR QUERY 2";

foreach ($sql as $sql_query)
{
    $query = $link->prepare($sql_query);
    $query->bindParam(':id', $id);
    $query->execute();
    $results = $query->fetch(PDO::FETCH_ASSOC);
    if ($results)
    {
        break;
    }
}

echo json_encode($results);

The world is your oyster.

Upvotes: 2

Related Questions