CODEWITHSUNDEEP
phpmysqlmysqliwhile-loop
jmiller
jmiller

Reputation: 588

While query from two SQL statements?

I have two SQL queries: the first query pulls all criteria from the database and the second query looks for any criteria completed:

SQL Query 1

//get all criteria for section
$getcrit = $mysqli->prepare("SELECT id, category_id, section_name, criteria  
FROM prepare_criteria
WHERE category_id=? AND section_name=? ");
$getcrit->bind_param('is', $cat_id, $sectname);
$getcrit->bind_result($criteria_id, $category_id, $section_name, $criteria);
$getcrit->execute();
$getcrit->store_result();
$row_cnt = $getcrit->num_rows();
$getcrit->fetch();

SQL Query 2

//get any checked criteria from db
$getcompcrit = $mysqli->prepare("SELECT prepare_criteria_complete.criteria_id, prepare_criteria.id, prepare_criteria.criteria, prepare_criteria_complete.business_id, prepare_criteria_complete.category_id 
FROM prepare_criteria_complete, prepare_criteria
WHERE business_id=?
AND prepare_criteria.id=prepare_criteria_complete.criteria_id");
$getcompcrit->bind_param('i', $business_id);
$getcompcrit->bind_result($cc_criteria_id, $criteria_completed_id, $comp_criteria, $business_id_db, $category_db);
$getcompcrit->execute();
$getcompcrit->store_result();
$gc_row_cnt = $getcompcrit->num_rows();

I'm then writing a while loop to check for any criteria completed:

if ($gc_row_cnt >=1)
{
    while ($getcompcrit->fetch() )
    {
        if ($cc_criteria_id==$criteria_completed_id)
        {
            echo $criteria_completed_id." ";    
        }
        if ($criteria_id!=$criteria_completed_id) 
        {
        echo $criteria_id." ";  
        }
    }
}

The second if doesn't function as expected. I know there must be a cleaner way of doing this! Maybe a JOIN statement?

Upvotes: 0

Views: 81

Answers (2)

jmiller
jmiller

Reputation: 588

THE SQL QUERY

$qry = $mysqli->prepare("
SELECT 
    pc.id, pc.criteria, pc.category_id,
    coalesce(pcc.business_id, 'N') business_id
FROM prepare_criteria pc
LEFT JOIN prepare_criteria_complete pcc 
    ON pc.id=pcc.criteria_id 
    WHERE pc.category_id=? ");
$qry->bind_param('i', $cat_id);
$qry->bind_result($qry_id, $qry_criteria, $qry_cat_id, $qry_bus_id );
$qry->execute();
$qry->store_result();
$qry_row_cnt = $qry->num_rows();

PHP

if ($qry_row_cnt >=1)
                            {
                                while ($qry->fetch() )
                                {               
                                    if( ($qry_bus_id==$business_id) )
                                    {
                                    //criteria is checked
                                    }
                                    else
                                    {
                                    //criteria not checked
                                    }   
                                }
                            }

Upvotes: -1

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

Sure you can use next single query:

SELECT 
    pc.id, pc.criteria,
    coalesce(pcc.business_id, 'not complete') business_id,
    coalesce(pcc.category_id, 'not complete') category_id
FROM prepare_criteria pc
LEFT JOIN prepare_criteria_complete pcc 
    ON pc.id=pcc.criteria_id AND pcc.business_id=?;

The query use coalesce operator for return 'not complete' string in case prepare_criteria record have not matched record in prepare_criteria_complete table.

Upvotes: 2

Related Questions