在PHP中使用if语句的备用查询

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:

我正在尝试运行一个备用查询,如果初始查询失败(它确实因为我在这个实例中搜索的id只存在于一个正在连接的数据库中)使用if语句而我构建它就像这样:

<?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.

基本上它默认为ID存在的单个表,并且只提取几个字段而不是上面的大语句。我唯一的问题是我的代码不起作用。当我回应它时,我的JSON只会说错。显然不应该。

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.

:编辑:我应该注意,当我输入所有加入的表中存在的ID时,页面上会显示正确的结果(json)。

1 个解决方案

#1

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.

我相信问题是,即使第一个查询中不存在ID,$ query变量仍然有一个正确的查询,并且没有任何错误。如果测试,那不是你应该做的。我认为你应该测试$ 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.

但是正如您所看到的,在if语句中重复了几行代码,这些代码与if语句之前的代码非常相似。也许有一个循环循环两次,但如果$ results不是false则会爆发。

<?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.

世界是你的牡蛎。

#1