Assignment: Backend Development using MySQL$-$Apache$-$PHP

$$

$$

$$ Open and read the PHP file $$indexForDMLPractice$.$php$$ using Notepad$++.$ This file shows you how to connect to the database $$universitydb$$ you created in previous homework. In addition, this file shows you how can you manipulate data in your database using PHP$.$ This file is an example of backend of database application.

$$ Run and practice the PHP file $$indexForDMLPractice$.$php$.$ How to run a PHP file? Create a folder named $$MCIS$5133$ in htdocs folder inside your xampp folder $($i$.$e$.,$ C:$\backslash$xampp$\backslash$htdocs$\backslash$ MCIS$5133).$ Put the PHP file $$indexForDMLPractice$.$php$$ into MCIS$5133$ folder you created, i$.$e$.,$ C:$\backslash$xampp$\backslash$htdocs$\backslash$MCIS$5133\backslash$ indexForDMLPractice.php$.$ Now, in your xampp control panel, start both Apache and MySQL$.$ Finally, in your web browser $($e$.$g$.,$ Google Chrome$),$ put localhost$/$mcis$5133/$indexForDMLPractice$.$php and press Enter. You should be able to see the output. $$

$$ Following the codes in this file $$indexForDMLPractice$.$php$,$ add codes to perform the followings:

o$$ Insert following data in the department table:

$$ English$-$Jennings$-10000$

$$ Journalism$-$Morrill$-1500$

o$$ Insert following data in the Student table

$11111-$david$-$physics$-3\mathrm{.}5$

o$$ Increase the budget of English department by $10$ percent

o$$ Change the building of Journalism department to Jennings.

o$$ Print all data of student table

o$$ Print the building of English department

$$

What to submit: Create a report by putting snapshots showing your indexForDMLPractice.php is running successfully $($that is output screens in web browser for each SQL command in php file$).$ Submit the report and revised indexForDMLPractice.php which includes the codes you added.

connect$\_$error$)\{$

die$("$Connection failed: $".$ $conn$->$connect$\_$error$)$;

$\}$

else$\{$

echo "Successfully connected";

$\}$

$//$ perform some data manipulation

$//$ insert a row in department table

$sql $=$ "insert into department values $(\text{'}$Physics$\text{'},$ 'Business', $99999\mathrm{.}00)"$;

if $($$conn$->$query$($$sql$)===$ TRUE$)\{$

echo $""$; $//$ new line

echo "Insertion successful";

$\}$ else $\{$

echo $""$; $//$ new line

echo "Error accessing database: $".$ $conn$->$error;

$\}$

$//$ Practice $1--$ insert a new department Law$--$Morril$--10000$

$//$ Practice $2---$insert a row in student table

$//$ Test $3---$update: increase the budget of Physics department by $5$ percent

$sql $=$ "update department set budget $=$ budget$*1\mathrm{.}05$ where dept$\_$name $=$ 'Physics'";

if $($$conn$->$query$($$sql$)===$ TRUE$)\{$

echo $""$; $//$ new line

echo "Update successful";

$\}$ else $\{$

echo $""$; $//$ new line

echo "Error accessing database: $".$ $conn$->$error;

$\}$

$//$ Select command

$sql $=$ "SELECT $*$ FROM department";

$result $=$ $conn$->$query$($$sql$)$;

$//$number of rows

echo "Total records: $result$->$num$\_$rows";

$//$display the records

if $($$result$->$num$\_$rows $>0)\{$

$//$ output data of each row

while$($$row $=$ $result$->$fetch$\_$assoc$())\{$

echo "dept name: $".$ $row$["$dept$\_$name"$]."-$ building: $".$ $row$["$building$"]."".$ $row$["$budget$"].""$;

$\}$

$\}$ else $\{$

echo $"0$ results";

$\}$

$//$ Test $4--$show the budget of CSC department

$sql $=$ "select budget from department where dept$\_$name $=$ 'biology'";

$result $=$ $conn$->$query$($$sql$)$;

echo "Total records: $result$->$num$\_$rows";

$values $=$ $result$->$fetch$\_$assoc$()$;

echo $values$["$budget$"]$;

$//$terminate connection

$conn$->$close$()$;

$?>$