Displaying a MySQL table using API

Posted by admin at January 4, 2017

In this tutorial, we are going to display the records in a table using values retrieved from an API service. The API service publishes the contents of a MySQL table in JSON format which is then translated to HTML rows. This is basically an API sourced read section of a CRUD template. Please refer to the link to see how a table is displayed using values directly from a local MySQL table. The API service will be implemented with Slim.

The SQL: The SQL for this tutorial can be downloaded here

Setting up Slim:

You can download and install the Slim framework or download zip and extract to a folder of your choice. Then we edit the index.php file accordingly.



// import the required CRUD functions
require('../inc/crud.php');

// ...

// define the url command and the function to run: get_service();
$app->get('/service/:serviceid', 'get_service');

// ...

// the function get_service();
function get_service($serviceid)
{
    
	// return the SELECT sql required for the service
	// get_read_sql() function contained in crud.php 
	$sql = get_read_sql($serviceid);
    
	// connect to db and process the sql
	try {
		// connect to database
		$db = getConnection();

		// run the query
		$stmt = $db->query($sql);
		
		// get the values
		$posts = $stmt->fetchAll(PDO::FETCH_OBJ);

		// clear out the db resource
		$db = null;
	
		// clean up collected data
		$posts = utf8($posts);
		
        // complete the json
		$json = '{"rows": ' . json_encode($posts) . '}';

	}
	catch(PDOException $e)
	{
        // the error message
		$json = '{"error":{"text":'. $e->getMessage() .'}}';
 	}

	// pad the json (jsonp)
	jsonp ($json);
    
}

Testing Slim:

Test this run the following link in the browser.

http://path/to/api/service/airport

You should have result similar to the one in the screenshot below:

inc/crud.php


function get_read_sql($tbl)
{
    switch($tbl)
    {
        case 'airport':
        case 'airports':
        case 'tbl_airport':
            $sql = 'SELECT'
                    .' *'
                . ' FROM'
                    .' `tbl_airports`'
                . ' WHERE 1'
                    . ' AND NOT ISNULL(`tbl_airports`.`airport_id`)'
                    . ' AND NOT ISNULL(`tbl_airports`.`airport_code`)'
                    . ' AND NOT ISNULL(`tbl_airports`.`airport_desc`)'
                . ' ORDER BY'
                    .' `tbl_airports`.`airport_code` ASC'
            ;
            break;
        case 'tbl_states':
        default:
            $sql = 'SELECT * FROM `'.tbl_states.'` WHERE 1';
            break;
            
    }
    return $sql;
}

The index file


// import CRUD support
require('inc/support.php');
...

<body>

...

<strong>Display a Table with values from an API</strong><br>

<?php
    $get_act = mysql_real_escape_string($_GET['a']);
    $get_act = (strlen($get_act) > 0) ? $get_act : 'r';
?>
    
<?php if ($get_act == 'c') { ?>
    
<?php //*********************************************CREATE ?>

    <?php include ('inc/c.php') ;?>
    
<?php //*********************************************CREATE ?>
    
<?php } else if ($get_act == 'r'){ ?>
    
<?php //*********************************************READ ?>
    
    <?php include ('inc/r.php') ;?>
    
<?php //*********************************************READ ?>

<?php } else if ($get_act == 'u') { ?>
    
<?php //*********************************************UPDATE ?>

    <?php include ('inc/u.php') ;?>
    
<?php //*********************************************UPDATE ?>
    
<?php } else if ($get_act == 'd'){ ?>
    
<?php //*********************************************DELETE ?>

    <?php include ('inc/d.php') ;?>
    
<?php //*********************************************DELETE ?>


    
    © BlinkWIki
    
</body>

We focus on the read section below:


<?php //*********************************************READ ?>
    
    <?php include ('inc/r.php') ;?>
    
<?php //*********************************************READ ?>

inc/r.php

inc/r.php would look like this.


<?php //*********************************************READ ?>
    
    <hr>
    
    <strong>Read</strong><br><br>
    
    <?php
    
    // the service is to retrieve airport records
    $service = 'airport';
    
    // the api url : the table determines the service to pull from the API
    $api_url = 'http://localhost/csys/api_sel_box/api/service/'.$service;
    
    // get the values from the api link
    {
        // allow for file_get_contes to run by
        // setting the PHP allwo_url_open value to 1
        ini_set("allow_url_fopen", 1);
        
        // get the contents of the url
        $json = file_get_contents($api_url);
        
        // decode the results
        $obj = json_decode($json);
    }
    
    ?>
    
    <table width="100%">
        <thead class="fw_b" valign="top">
            <tr>
                <td width="5%">SNrows); $i++)
            {
            ?>
                <tr>
                    <td><?php echo $i+1; ?></td>
                    <td><?php echo $obj->rows[$i]->airport_code; ?></td>
                    <td><?php echo $obj->rows[$i]->airport_iata_code; ?></td>
                    <td><?php echo $obj->rows[$i]->airport_desc; ?></td>
                    <td><?php echo $obj->rows[$i]->airport_city; ?></td>
                    <td><?php echo $obj->rows[$i]->airport_notes; ?></td>
                    <td></td>
                </tr>
            <?php } ?>
        </tbody>
    </table>
    
<?php //*********************************************READ ?>

The final screen would look like this

Putting it all together

You can download the complete application here

0 likes