Lidhja e PHP me MySQL

Ushtrim 1:

Ndertoni duke perdorur php dhe mysql nje sistem qe simulon menaxhimin e studenteve.Krijoni nje database test dhe ne te nje tabele studente qe mban te dhena si emrin,mbiemrin,dhe degen per cdo studente.

Ndertoni nje nderfaqe ne php qe shton studente te rinj, dhe nje nderfaqe qe menaxhon studentet(afishon,modifikon dhe fshin).

Database

-- phpMyAdmin SQL Dump

-- version 3.2.0.1

-- http://www.phpmyadmin.net

-- Host: localhost

-- Generation Time: Feb 11, 2013 at 10:13 AM

-- Server version: 5.1.36

-- PHP Version: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

-- Database: `test`

-- --------------------------------------------------------

-- Table structure for table `studente`

CREATE TABLE IF NOT EXISTS `studente` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`emri` varchar(20) NOT NULL,

`mbiemri` varchar(30) NOT NULL,

`dega` varchar(100) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;

-- Dumping data for table `studente`

INSERT INTO `studente` (`id`, `emri`, `mbiemri`, `dega`) VALUES

(11, 'John', 'Smith', 'Computer Sciences'),

(12, 'Tom', 'Davolio', 'Bussines Administration'),

(13, 'Nick', 'Fuller', 'Finance'),

(14, 'Robert', 'King', 'Chemistry'),

(15, 'Laura', 'Callahan', 'Biology'),

(16, 'Loren', 'Dodsworth', 'Mathematics ');

index.php

<!DOCTYPE html>

<html>

<head>

<title>Shto Studente</title>

</head>

<body>

<table>

<tr><td> Mbiemri : </td> <td><input type="text" id="mbiemri" name="mbiemri"/></td></tr>

</table>

</form>

</body>

</html>

addStudent.php

<?php

if(isset($_POST["add"])){

$emri=$_POST["emri"];

$mbiemri=$_POST["mbiemri"];

$dega=$_POST["dega"];

$conn = mysql_connect("localhost","root","") or die("Could not establish connection");

mysql_select_db("test") or die("Database not found");

$query="INSERT INTO studente(emri,mbiemri,dega) VALUES ('".$emri."', '".$mbiemri."', '".$dega."');";

$result = mysql_query($query);

if($result){

header("Location: listStudents.php");

}else

{

echo "User could not be added";

}

mysql_close($conn);

}

listStudent.php

<div align="center"><b><i><a href="index.php">Shto Studente</a></i></b></div>

<br/>

<?php

$conn = mysql_connect("localhost","root","") or die("Could not establish connection");

mysql_select_db("test") or die("Database not found");

$query="SELECT * FROM studente;";

$result = mysql_query($query);

echo"<table border='1' align='center'>";

echo "<tr align='center'>

<td><b>Mbiemri</b></td>

<td><b>Veprime</b></td>

</tr>";

$i=0;

while($row= mysql_fetch_array($result)){

echo "<tr align='center'>

<td><b> [ <a href='deleteStudent.php?id=".$row[0]."'>Fshi</a> ] |

[ <a href='editStudent.php?id=".$row[0]."'>Modifiko</a> ] </b></td>

</tr>";

}

echo"</table>";

mysql_close($conn);

deleteStudent.php

<?php

$id=$_REQUEST["id"];

$conn = mysql_connect("localhost","root","") or die("Could not establish connection");

mysql_select_db("test") or die("Database not found");

$query="DELETE FROM studente WHERE id=".$id;

$result = mysql_query($query);

if($result){

header("Location: listStudents.php");

}

mysql_close($conn);

editStudent.php

<?php

if(isset($_REQUEST["id"])){

$id=$_REQUEST["id"];

$conn = mysql_connect("localhost","root","") or die("Could not establish connection");

mysql_select_db("test") or die("Database not found");

if(isset($_POST["edit"]))

{

$emri=$_POST["emri"];

$mbiemri=$_POST["mbiemri"];

$dega=$_POST["dega"];

$query="UPDATE studente SET emri='".$emri."', mbiemri='".$mbiemri."', dega='".$dega."' WHERE id=".$id;

$result = mysql_query($query);

if($result)

header("Location: listStudents.php");

}

$query="SELECT * FROM studente WHERE id=".$id;

$result = mysql_query($query);

@ $row = mysql_fetch_row($result);

<form action="editStudent.php?id=<?php echo $id;?>" method="GET">

<table>

<tr><td> Mbiemri : </td> <td><input type="text" id="mbiemri" name="mbiemri" value="<?php echo $row[2]?>"/></td></tr>

</table>

</form>

<?php

mysql_close($conn);

}else

header("Location: listStudents.php");

Rezultati:

index.php

listStudent.php

editStudent.php

deleteStudent.php

Ushtrim 2:

Duke perdorur bazen e te dhenave Northwind, afishoni ne formen e nje tabele HTML te gjithe detajet e porosive qe ka bere nje klient te cilin duhet ta zgjidhni paraprakisht nga nje dropdown list.

index.php

<?php

$conn = mysql_connect("localhost","root","") or die("Could not establish connection");

mysql_select_db("northwind") or die("Database not found");

$is_set=FALSE;

$customerID='';

if(isset($_POST["selected"]))

{

$customerID=$_POST["selected"];

$is_set= TRUE;

}

function selectedCompany(){

var sel = document.getElementById('companies');

var selectedCMP=sel.options[sel.selectedIndex].value;

document.getElementById("selected").value = selectedCMP;

}

</script>

<?php

$query="SELECT CustomerID,CompanyName FROM customers;";

$result = mysql_query($query);

<option value="">Select Company</option>

<?php

while ($row = mysql_fetch_array($result)) {

<?php

}

</select>

</form>

<?php

if($is_set)

{

$query="SELECT customers.CompanyName, employees.FirstName, employees.LastName, orders.OrderID, order_details.UnitPrice, order_details.Quantity

FROM orders

INNER JOIN order_details ON orders.OrderID = order_details.OrderID

INNER JOIN customers ON orders.CustomerID = customers.CustomerID

INNER JOIN employees ON orders.EmployeeID = employees.EmployeeID

WHERE customers.CustomerID = '".$customerID."'";

$result = mysql_query($query);

echo"<table border='1' align='center'>";

echo "<tr align='center'>

<td><b>Company Name</b></td>

<td><b>Employee</b></td>

<td><b>Order ID</b></td>

<td><b>Unit Price</b></td>

<td><b>Quantity</b></td>

</tr>";

$i=0;

while($row= mysql_fetch_array($result)){

echo "<tr align='center'>

</tr>";

}

echo"</table>";

}

mysql_close($conn);

Rezultati:

Lidhja e PHP me MySQL