Write a php program that will query the supermarket database and display the fol

Question

Write a php program that will query the supermarket database and display the following results:

From the transaction table, for each hour of the day (represented by trans_time), show the total of the sales_value column for that hour and find and display the most sold item for that hour of the day.

Notes:

a. The trans_time column in that table is an integer in the range 0 to 2359, with 0 representing midnight and 2359 representing 23:59 (11:59pm). The time values from 0 to 59 represent 00:00 - 00:59 or 12:00 midnight to 12:59 am; The values from 100 to 159 represent 1:00am to 1:59am; etc.

b. You might find the integer divide operation div useful.

c. It might be easier to write a couple (or more) smaller queries and combine their results for each row of output.

d. Your program should have 24 rows of output -- 0, 1, 2, ... 22, 23 (or midnight, 1:00am, 2:00am, ... 10:00pm, 11:00pm)

WILLING TO PAY FOR WALKTHROUGH HELP AND NOT JUST THE ANSWER!!!!!!!!!

Explanation / Answer

<?php

$servername='put your server name here';
$username='put your databse login username here';
$password='put your database login password here';
// Create connection
$conn = mysql_connect($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";

$db_selected = mysql_select_db('supermarket', $conn);
if (!$db_selected) {
    die ('Can't select database : ' . mysql_error());
}

//define the sql query to fetch data
$query = "select from item_id,trans_time,sales_value from transaction order by trans_time;";
//execute the sql query
$result = mysql_query($query);
if (!$result)
die("Database error...");
$num = mysql_num_rows($result);

if ($num == 0) {    //if no record is present
    die("No transaction present");
}

$total_sales=array(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);   //array that hold total sales in each hour
$items_sold = array(array());   //array that holds item ids sold in each hour
$start=0;

$end=59;

while($start<=2300){

   $query = "select from item_id,trans_time,sales_value from transaction where trans_time>=".$start." AND $trans_time<=".$end.";";
   //execute the sql query
   $result = mysql_query($query);
   $i=0;
   while ($row = mysql_fetch_array($result)) { //loop through each record
           $item_id=$row["item_id"];
           $trans_time = $row["trans_time"];
           $sales_value = $row["sales_value"];

           if(strlen($trans_time)<=2){   //tans_time is between 0 to 59

               $hour=0;
      
           }

           if(strlen($trans_time)==3){   //tans_time is between 100 to 959

               $hour=substr($trans_time, 0, 1);

           }

           if(strlen($trans_time)==4){   //tans_time is between 1000 to 2359

               $hour=substr($trans_time,0,2);

           }

           $total_sales[$hour]=$total_sales[$hour]+$sales_value;   //calculate sum of sales_value for present hour

           $items_sold[$hour][$i++]=$item_id;   //store each item id sold in present hour

   }
  
   /*start of code to find the mode of item ids stored for present hour*/
  
   $values = array_count_values($items_sold[$hour]);
   $mode = array_search(max($values), $values);
  
   /*end of code to find the mode of item ids stored for present hour*/

   $query = "select from item_id,item_name from item where item_id=".$mode.";";   //query to fetch the item name of max sold item id for present hour
   //execute the sql query
   $result = mysql_query($query);
   while ($row = mysql_fetch_array($result)) { //loop through each record
       $name=$row["item_name"];
       $max_sold_item[$hour]=$name;   //store the name of max sold item for present hour
   }
   $start=$start+1;   //increment start to next hour
   $end=$start*100+59;   //increment end to next hour and 59 sec
}

$i=0;
while($i<24){
   echo "Hour: ".$i." Total Sales Value: ".$total_sales[$i]." Most sold item: ".$max_sold_item[$i]." ";   //display the hour, total sales and most sold item anme
}

?>

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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