Api Get States

Hi,

I've extended the API to get list of states. My function is as below and giving empty results. Any idea what went wrong?

namespace Tygh\Api\Entities;

use Tygh\Api\AEntity;

use Tygh\Api\Response;

use Tygh\Registry;
class State extends AEntity

{

public function index($id)

{

list($data) = fn_cm_get_state($id);
    if (!empty($data) || empty($id)) {
        $status = Response::STATUS_OK;
    } else {
        $status = Response::STATUS_NOT_FOUND;
    }

    return array(
        'status' => $status,
        'data' => $data,
		'country_id' => $id
    );
}

… 

function fn_cm_get_state($country_id)
{
	$states = array();
    if (!empty($country_id)) {
        $states = db_get_array("SELECT
								a.state_id,
								a.country_code,
								a.code,
								a.status,
								b.state,
								c.country 
							FROM
								?:cscart_states as a
								LEFT JOIN
										cscart_state_descriptions as b
									 ON 
										(
											b.state_id = a.state_id
										AND
											b.lang_code = 'en'
										)
								LEFT JOIN
										cscart_country_descriptions as c
									 ON 
										(
											c.code = a.country_code
										AND
											c.lang_code = 'en'
										) 
							WHERE
									1
								AND
									a.country_code = ?s 
							ORDER BY
								c.country ASC,
								b.state ASC", $country_id);
    }
	
    return $states;
}

Try cleaning up your table names and see if that fixes it.

?:cscart_states => ?:states

cscart_state_descriptions => ?:state_descriptions

cscart_country_descriptions => ?:country_descriptions

if you run your query in phpMyAdmin do you get the expected results? I'm assuming there are no PHP errros in your error_log related to this class.

Oops, posted at same time as @straygecko. He's right. Do not include the table prefix (cscart_) in your table names when using the expansion code of '?:'.

Try to use built-in fn_get_states function (app/functions/fn.locations.php)

It has the country_code parameter to meed your needs

Thanks all for the help. Managed to do it.