Question

This assignment will assess the competency 6. Construct an application that successfully utilizes MySQL features. Directions: Create short description of a PHP/MySQL project you could develop. Include a description of how it would be used, why it would be used and how MySQL and a PHP page would interact.

Answer 1

First all the basic details about making connection with database in php and how to use json is explained in detail. If you already know that then fine otherwise just go through it ones. and in the last complete code of given table is given to extract name of states according to given query.

First we will create config.php , which contains database details.

<?php

      $user_name="user";
      $password="pass";
      $mysql:host="localhost";
      $db_name="us_states";

      ?>

To establish db connection, we have to pass various db parameters like:

$con=mysqli_connect($host,$user_name,$password,$db_name);

if($con->connect_error){ die("Connection failed:".$con-->connect_error)

$con ==> will hold handler for db

Now construct sql statements as

$sql="select * from data;";

" put your sql statements within this part ";

Note: we have two semicolon.One for php statement and one for sql statement.

Now to execute sql query we a function in php mysqli_query()

mysqli_query(connection handler,sql query);

$result=mysqli_query($con,$sql);

$result will hold output of sql query

Note:$result may have results from various rows ,it is easier for programmer ,if it in array/json etc formats,to perform various operations.   

Function mysqli_fetch_array(query result) ,will fetch result row by row.

row=mysqli_fetch_array($result))
{

array_push($response,array("name"=>$row["name"],"age"=>$row["age"]));

}

this code will put all result in array fomat in $response variable.

If you want it in json, simply use as

json_encode(array("abc"=>$response));

Important :Don't for forgot to close db connection ,after using it

mysqli_close($con);

To perform different operations, create a operation.php

Operation.php has a code to establish db connection + execute sql query +close db connection

we have to call config.php in php to have access to varios db parameters.

like this

require_once 'config.php';

Complete code to display all results.php

<?php

require_once 'config.php';
$con=mysqli_connect($mysql:host,$user,$pass,$us_states,$min_income, $max_income, $min_home_value, $max_home_value);

$sql="SELECT State FROM economic_data WHERE (MedianIncome BETWEEN $min_income AND $max_income) AND (MedianHomePrice BETWEEN $min_home_value AND $max_home_value) ;";
$result=mysqli_query($con,$sql);

$row=mysqli_fetch_array($result));

$state=$row[State];

echo $state;

mysqli_close($con);
?>