I have some PHP code I'm working on which contains 2 queries and I need to set extra conditions for 2 out of 3 view options and one less query for 1 out of 3 view options.
Basically, I'm rendering a history list of shows with three options ALL | PRO | UPCOMING. The ALL option should include both queries and have no extra conditions. The PRO option should have an extra condition with both queries. The UPCOMING option should have an extra condition but only one query.
I've never built an if else
statement with queries before, but I'm sure there is an easier way to make the code shorter.
This PHP is also within an API that get pulled to the front end. Is there a way to render buttons on the front end to change the query from ALL to PRO or UPCOMING?
function get_shows($playid) {
// There are two tables, so we will need to merge the results
$results = array();
$count = ($this->input->get('count')) ? $this->input->get('count') : $this->max_results;
$offset = ($this->input->get('offset')) ? $this->input->get('offset') : 0;
$view = 'all';
// ALL
if ($view == 'all') {
$extra_condition = ""; //If ALL no extra condition needs to be set
$query = $this->db->query("SELECT distinct customer.customerid as customerid, organization, vcity, vstate, vcountry, firstdate, lastdate
FROM customer, orders, venue
WHERE orders.ordersid=venue.ordersid
AND orders.playid=? AND orders.active=1
AND customer.customerid=orders.customerid
AND shipped != 0
AND defunct != 1
AND orders.lastdate != 0
AND (ordertype != 'misc'
AND ordertype != 'reading')
AND hidepic!=1
AND hideprod!=1
AND num_perf>0
ORDER BY firstdate desc
LIMIT $offset,$count", $playid);
foreach ($query->result() as $row)
{
$results[] = array
(
'id' => $row->customerid,
'src' => 'customerid',
'organization' => $row->organization,
'city' => $row->vcity,
'state' => $row->vstate,
'country' => $row->vcountry,
'firstdate' => call_user_func($fmt_date, $row->firstdate),
'lastdate' => call_user_func($fmt_date, $row->lastdate)
);
}
// Legacy data from oldprods
$query = $this->db->query("SELECT oldprodsid, organization, city, state, country, firstdate, lastdate
FROM oldprods where playid=?
ORDER BY firstdate desc
LIMIT $offset,$count",$playid);
foreach ($query->result() as $row)
{
$results[] = array
(
'id' => $row->oldprodsid,
'src' => 'oldprods',
'organization' => $row->organization,
'city' => $row->city,
'state' => $row->state,
'country' => $row->country,
'firstdate' => call_user_func($fmt_date, $row->firstdate),
'lastdate' => call_user_func($fmt_date, $row->lastdate)
);
}
// IF UPCOMING
}else if ($view == 'upcoming') {
$extra_condition = "AND lastdate >= DATE_SUB(CURRENT_DATE,INTERVAL 0 DAY)"; //This line of code designates this as UPCOMING but omit the Legacy Query
$query = $this->db->query("SELECT distinct customer.customerid as customerid, organization, vcity, vstate, vcountry, firstdate, lastdate
FROM customer, orders, venue
WHERE orders.ordersid=venue.ordersid
AND orders.playid=? AND orders.active=1
AND customer.customerid=orders.customerid
AND shipped != 0
AND defunct != 1
AND orders.lastdate != 0
AND (ordertype != 'misc'
AND ordertype != 'reading')
AND hidepic!=1
AND hideprod!=1
AND num_perf>0
$extra_condition
ORDER BY firstdate desc
LIMIT $offset,$count", $playid);
foreach ($query->result() as $row)
{
$results[] = array
(
'id' => $row->customerid,
'src' => 'customerid',
'organization' => $row->organization,
'city' => $row->vcity,
'state' => $row->vstate,
'country' => $row->vcountry,
'firstdate' => call_user_func($fmt_date, $row->firstdate),
'lastdate' => call_user_func($fmt_date, $row->lastdate)
);
}
// IF PRO
}else if ($view == 'pro') {
$extra_condition = "AND (ordertype = 'pro')"; //This line of code designates this as PRO along with the Legacy Query
$query = $this->db->query("SELECT distinct customer.customerid as customerid, organization, vcity, vstate, vcountry, firstdate, lastdate
FROM customer, orders, venue
WHERE orders.ordersid=venue.ordersid
AND orders.playid=? AND orders.active=1
AND customer.customerid=orders.customerid
AND shipped != 0
AND defunct != 1
AND orders.lastdate != 0
AND (ordertype != 'misc'
AND ordertype != 'reading')
AND hidepic!=1
AND hideprod!=1
AND num_perf>0
$extra_condition
ORDER BY firstdate desc
LIMIT $offset,$count", $playid);
foreach ($query->result() as $row)
{
$results[] = array
(
'id' => $row->customerid,
'src' => 'customerid',
'organization' => $row->organization,
'city' => $row->vcity,
'state' => $row->vstate,
'country' => $row->vcountry,
'firstdate' => call_user_func($fmt_date, $row->firstdate),
'lastdate' => call_user_func($fmt_date, $row->lastdate)
);
}
// Legacy data from oldprods
$query = $this->db->query("SELECT oldprodsid, organization, city, state, country, firstdate, lastdate
FROM oldprods where playid=?
ORDER BY firstdate desc
LIMIT $offset,$count",$playid);
foreach ($query->result() as $row)
{
$results[] = array
(
'id' => $row->oldprodsid,
'src' => 'oldprods',
'organization' => $row->organization,
'city' => $row->city,
'state' => $row->state,
'country' => $row->country,
'firstdate' => call_user_func($fmt_date, $row->firstdate),
'lastdate' => call_user_func($fmt_date, $row->lastdate)
);
}
}
return array_slice($results, 0, $count);
}
<a href="prodhistory.php?playid=$playid&view=pro">PRO</a>
But since this is in the API I can't do that. I'm wondering if there is a way to do it through javascript though via an onclick or onchange? \$\endgroup\$