Suppose you have an SQL database named us_states with the following (ficticious)

Question

Suppose you have an SQL database named us_states with the following (ficticious) table named economic_data with columns ID, State, MedianIncome, MedianHomePrice, and IncomeTax.

Suppose that you are writing a website to help a user find a good state to live in. Suppose the user has supplied values for the following variables:

$min_income, $max_income, $min_home_value, $max_home_value.

Write some PHP code that queries the above table and prints out the names of the states whose median incomes and median home values sit between the bounds defined by these variables. You may assume the DSO for the above database is "mysql:host=localhost;dbname=us_states", the username is "user" and the password is "pass".

ID State MedianIncome MedianHomePrice IncomeTax 1 California 90000 850000 TRUE 2 Texas 35000 70000 FALSE 3 New Jersey 76000 320000 TRUE 4 Michigan 43000 110000 TRUE ... ... ... ... ...

Explanation / Answer

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);
?>

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.