index.php
<!DOCTYPE html>
<html>
<head>
<script>
function showUser(str) {
if (str=="") {
document.getElementById("txtHint").innerHTML="";
return;
}
if (window.XMLHttpRequest) {
// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
} else { // code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState==4 && xmlhttp.status==200) {
document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("GET","getuser.php?q="+str,true);
xmlhttp.send();
}
</script>
</head>
<body>
<form>
<select name="nume" onchange="showUser(this.value)">
<option value="">Selectati persoana:</option>
<option value="1">Popescu</option>
<option value="2">Florescu</option>
<option value="3">Ionescu</option>
<option value="4">nimereala</option>
<option value="5">altul</option></select>
</form>
<br>
<div id="txtHint"><b>Persoanele restante la intretinere vor fi afisate mai jos.</b></div>
</body>
</html>
--------------------
getuser.php
<!DOCTYPE html>
<html>
<head>
<style>
table {
width: 100%;
border-collapse: collapse;
}
table, td, th {
border: 1px solid black;
padding: 5px;
}
th {text-align: left;}
</style>
</head>
<body>
<?php
$q = intval($_GET['q']);
$con = mysqli_connect('localhost','central5_vali','parola123','central5_vali');
if (!$con) {
die('Could not connect: ' . mysqli_error($con));
}
mysqli_select_db($con,"ajax_demo");
$sql="SELECT * FROM bloc WHERE ap = '".$q."'";
$result = mysqli_query($con,$sql);
echo "<table>
<tr>
<th>Nume</th>
<th>Prenume</th>
<th>Apa</th>
<th>Pers in loc</th>
<th>AP</th>
</tr>";
while($row = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row['nume'] . "</td>";
echo "<td>" . $row['prenume'] . "</td>";
echo "<td>" . $row['apa'] . "</td>";
echo "<td>" . $row['pers'] . "</td>";
echo "<td>" . $row['ap'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysqli_close($con);
?>
</body>
</html>
SQL inceputuri
sâmbătă, 27 februarie 2016
sâmbătă, 20 februarie 2016
PHP cu mySQL si Javascript
Adunati informatia urmatoare intr-un folder APLICATIE
1. Realizati fisierul conn.php
<?php
mysql_connect('localhost', 'central5_vali', 'parola123')or
die("Could not connect: " . mysql_error());
mysql_select_db("central5_vali");
?>
2. Intr-un fisier text scrieti comenzile urmatoare mysql:
CREATE TABLE `tbllogin` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`utilizator` varchar(45) NOT NULL,
`parola` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
----------
INSERT INTO `tbllogin` (`id`,`utilizator`,`parola`) VALUES
(1,'valentin','upadhyay'),
(2,'superadmin','q3allege');
3. Realizati un fisier delete.php
<?php
require_once('conn.php');
$idp= $_REQUEST['id'];
mysql_query("delete FROM tbllogin where id=$idp");
header("location:home.php?action");
?>
4. Realizati fisierul home.php cu structura urmatoare:
<html>
<title>Aplicatie PHP cu MySQL</title>
<h1>Aceasta este prima aplicatie web utilizand PHP, SQL si JavaScript</h1>
<script type="text/javascript">
function validateForm()
{ var utilizator=document.form1.utilizator.value;
var parola=document.form1.parola.value;
if(utilizator=="" && parola!="")
{
document.getElementById("errorMessage").innerHTML="Introduceti un utilizator";return false; }
if(utilizator!="" && parola=="")
{ document.getElementById("errorMessage").innerHTML="Introduceti o parola"; return false; }
if(utilizator=="" && parola=="")
{ document.getElementById("errorMessage").innerHTML="Ati uitat sa completati campurile";return false; }
}
</script>
<body>
<?PHP
if($_REQUEST['action']=='edit')
{
?>
<form id="form1" name="form1" method="post" action="update.php?id=<?PHP echo $_REQUEST['id']; ?>" onSubmit="return validateForm();">
<table width="200" border="1">
<tr>
<td>Utilizator</td>
<td><label for="utilizator"></label>
<input type="text" name="utilizator" id="utilizator" value="<?PHP echo $_REQUEST['utilizator']; ?>" /></td>
</tr>
<tr>
<td>Parola</td>
<td><label for="parola"></label>
<input type="text" name="parola" id="parola" value="<?PHP echo $_REQUEST['parola']; ?>" /></td>
</tr>
<tr>
<td><input type="submit" name="submit" id="submit" value="Trimite" /></td>
<td><input type="reset" name="Reset" id="Reset" value="Reset" /></td>
</tr>
</table>
</form>
<?PHP
}
else
{
?>
<form id="form1" name="form1" method="post" action="insert.php" onSubmit="return validateForm();">
<table width="200" border="1">
<tr>
<td>Utilizator</td>
<td><label for="utilizator"></label>
<input type="text" name="utilizator" id="utilizator" value="" /></td>
</tr>
<tr>
<td>Parola</td>
<td><label for="parola"></label>
<input type="text" name="parola" id="parola" value="" /></td>
</tr>
<tr>
<td><input type="submit" name="submit" id="submit" value="Trimite" /></td>
<td><input type="reset" name="Reset" id="Reset" value="Reset" /></td>
</tr>
</table>
</form>
<?PHP } ?>
<p id="errorMessage" style="color:#C00; font-style:italic;"></p>
<?php
require_once('conn.php');
$result = mysql_query("SELECT * FROM tbllogin");
echo "<table border='2'>";
echo "<th>Utilizator</th><th>Parola</th>";
while($row=mysql_fetch_array($result))
{ echo "<tr>";
$idn=$row['id'];$utilizator=$row['utilizator'];$parola=$row['parola'];
echo "<td>".$row['utilizator']." </td>";
echo "<td>".$row['parola']."</td>";
echo "<td><a href='delete.php?id=$idn'>Sterge</a></td>
<td><a href='home.php?id=$idn&action=edit&utilizator=$utilizator&parola=$parola'>Editeaza</a></td>";
echo "</tr>";
}
echo "</table>";
?>
</body>
</html>
5.Realizati fisierul index.php
<?php
header("location:home.php?action");
?>
6.Realizati fisierul insert.php
<?php
require_once('conn.php');
$utilizator= $_POST['utilizator'];
$parola= $_POST['parola'];
mysql_query("INSERT INTO tbllogin (utilizator, parola) VALUES ('$utilizator', '$parola')");
header("location:home.php?action");
?>
7.Realizati fisierul update.php
<?php
require_once('conn.php');
$idp= $_REQUEST['id'];
$utilizator= $_POST['utilizator'];
$parola= $_POST['parola'];
mysql_query("UPDATE tbllogin SET utilizator='$utilizator',parola='$parola' where id=$idp");
header("location:home.php?action");
?>
TEMA:
In home.php, inainte de </body> scrieti secventa urmatoare:
<p>Pentru tema1 apasati butonul.</p>
<button onclick="myFunction()">Tema1</button>
<script>
function myFunction() {
window.open("http://www.val.centrale-ths.ro/bloc");
}
</script>
In FTP creati un director nou bloc in care veti copia toate aceste fisiere.
Conform celor de mai sus realizati un program web care citeste, afiseaza, modifica si sterge urmatoarele informatii despre locatarii unui bloc: ap, nume, prenume, persoane in locuinta, consum apa. Asa incat tabelul va fi urmatorul:
|Ap | Persoane in locuinta | Nume | Prenume | consum apa |
-----+------------------------------+----------+-------------+-----------------+
|1 | 5 | Mitroi | Oana | 30 |
minim 3 inregistrari
1. Realizati fisierul conn.php
<?php
mysql_connect('localhost', 'central5_vali', 'parola123')or
die("Could not connect: " . mysql_error());
mysql_select_db("central5_vali");
?>
2. Intr-un fisier text scrieti comenzile urmatoare mysql:
CREATE TABLE `tbllogin` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`utilizator` varchar(45) NOT NULL,
`parola` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
----------
INSERT INTO `tbllogin` (`id`,`utilizator`,`parola`) VALUES
(1,'valentin','upadhyay'),
(2,'superadmin','q3allege');
3. Realizati un fisier delete.php
<?php
require_once('conn.php');
$idp= $_REQUEST['id'];
mysql_query("delete FROM tbllogin where id=$idp");
header("location:home.php?action");
?>
4. Realizati fisierul home.php cu structura urmatoare:
<html>
<title>Aplicatie PHP cu MySQL</title>
<h1>Aceasta este prima aplicatie web utilizand PHP, SQL si JavaScript</h1>
<script type="text/javascript">
function validateForm()
{ var utilizator=document.form1.utilizator.value;
var parola=document.form1.parola.value;
if(utilizator=="" && parola!="")
{
document.getElementById("errorMessage").innerHTML="Introduceti un utilizator";return false; }
if(utilizator!="" && parola=="")
{ document.getElementById("errorMessage").innerHTML="Introduceti o parola"; return false; }
if(utilizator=="" && parola=="")
{ document.getElementById("errorMessage").innerHTML="Ati uitat sa completati campurile";return false; }
}
</script>
<body>
<?PHP
if($_REQUEST['action']=='edit')
{
?>
<form id="form1" name="form1" method="post" action="update.php?id=<?PHP echo $_REQUEST['id']; ?>" onSubmit="return validateForm();">
<table width="200" border="1">
<tr>
<td>Utilizator</td>
<td><label for="utilizator"></label>
<input type="text" name="utilizator" id="utilizator" value="<?PHP echo $_REQUEST['utilizator']; ?>" /></td>
</tr>
<tr>
<td>Parola</td>
<td><label for="parola"></label>
<input type="text" name="parola" id="parola" value="<?PHP echo $_REQUEST['parola']; ?>" /></td>
</tr>
<tr>
<td><input type="submit" name="submit" id="submit" value="Trimite" /></td>
<td><input type="reset" name="Reset" id="Reset" value="Reset" /></td>
</tr>
</table>
</form>
<?PHP
}
else
{
?>
<form id="form1" name="form1" method="post" action="insert.php" onSubmit="return validateForm();">
<table width="200" border="1">
<tr>
<td>Utilizator</td>
<td><label for="utilizator"></label>
<input type="text" name="utilizator" id="utilizator" value="" /></td>
</tr>
<tr>
<td>Parola</td>
<td><label for="parola"></label>
<input type="text" name="parola" id="parola" value="" /></td>
</tr>
<tr>
<td><input type="submit" name="submit" id="submit" value="Trimite" /></td>
<td><input type="reset" name="Reset" id="Reset" value="Reset" /></td>
</tr>
</table>
</form>
<?PHP } ?>
<p id="errorMessage" style="color:#C00; font-style:italic;"></p>
<?php
require_once('conn.php');
$result = mysql_query("SELECT * FROM tbllogin");
echo "<table border='2'>";
echo "<th>Utilizator</th><th>Parola</th>";
while($row=mysql_fetch_array($result))
{ echo "<tr>";
$idn=$row['id'];$utilizator=$row['utilizator'];$parola=$row['parola'];
echo "<td>".$row['utilizator']." </td>";
echo "<td>".$row['parola']."</td>";
echo "<td><a href='delete.php?id=$idn'>Sterge</a></td>
<td><a href='home.php?id=$idn&action=edit&utilizator=$utilizator&parola=$parola'>Editeaza</a></td>";
echo "</tr>";
}
echo "</table>";
?>
</body>
</html>
5.Realizati fisierul index.php
<?php
header("location:home.php?action");
?>
6.Realizati fisierul insert.php
<?php
require_once('conn.php');
$utilizator= $_POST['utilizator'];
$parola= $_POST['parola'];
mysql_query("INSERT INTO tbllogin (utilizator, parola) VALUES ('$utilizator', '$parola')");
header("location:home.php?action");
?>
7.Realizati fisierul update.php
<?php
require_once('conn.php');
$idp= $_REQUEST['id'];
$utilizator= $_POST['utilizator'];
$parola= $_POST['parola'];
mysql_query("UPDATE tbllogin SET utilizator='$utilizator',parola='$parola' where id=$idp");
header("location:home.php?action");
?>
TEMA:
In home.php, inainte de </body> scrieti secventa urmatoare:
<p>Pentru tema1 apasati butonul.</p>
<button onclick="myFunction()">Tema1</button>
<script>
function myFunction() {
window.open("http://www.val.centrale-ths.ro/bloc");
}
</script>
In FTP creati un director nou bloc in care veti copia toate aceste fisiere.
Conform celor de mai sus realizati un program web care citeste, afiseaza, modifica si sterge urmatoarele informatii despre locatarii unui bloc: ap, nume, prenume, persoane in locuinta, consum apa. Asa incat tabelul va fi urmatorul:
|Ap | Persoane in locuinta | Nume | Prenume | consum apa |
-----+------------------------------+----------+-------------+-----------------+
|1 | 5 | Mitroi | Oana | 30 |
minim 3 inregistrari
SQL medium 1
DELETE
DELETE FROM Elevi
WHERE Prenume='Mihai' AND Scoala='gen.Radulescu';
realizati o stergere de inregistrare asemanatoare cu cea de mai sus si in tabla Angajati
---------------------------------------------------
SELECT LIMIT
SELECT * FROM Elevi ORDER BY NrCrtID LIMIT 2;
realizati o selectie asemanatoare cu cea de mai sus si in tabla Angajati
-------------------------------------------------------
CHEI STRAINE
CREATE TABLE clienti (id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
nume CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE tricouri (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
tip ENUM('simplu', 'polo', 'de dama') NOT NULL,
culoare ENUM('rosu', 'albastru', 'portocaliu', 'alb', 'negru') NOT NULL,
proprietar SMALLINT UNSIGNED NOT NULL REFERENCES clienti(id),
PRIMARY KEY (id)
);
INSERT INTO clienti VALUES (NULL, 'Vasilescu Ion');
SELECT @last := LAST_INSERT_ID();
INSERT INTO tricouri VALUES
(NULL, 'polo', 'albastru', @last),
(NULL, 'de dama', 'alb', @last),
(NULL, 'simplu', 'albastru', @last);
INSERT INTO clienti VALUES (NULL, 'Lilliana Franculescu');
SELECT @last := LAST_INSERT_ID();
INSERT INTO tricouri VALUES
(NULL, 'de dama', 'portocaliu', @last),
(NULL, 'polo', 'rosu', @last),
(NULL, 'de dama', 'albastru', @last),
(NULL, 'simplu', 'alb', @last);
SELECT * FROM clienti;
+----+---------------------+
| id | nume |
+----+---------------------+
| 1 | Vasilescu Ion |
| 2 | Lilliana Franculescu|
+----+---------------------+
SELECT * FROM tricouri;
+----+---------+------------+-------
| id | tip | culoare | proprietar
+----+---------+------------+---------
| 1 | polo | albastru | 1
| 2 | dress | alb | 1
| 3 | simplu | albastru | 1
| 4 | de dama | portocaliu | 2
| 5 | polo | rosu | 2
| 6 | de dama | albastru | 2
| 7 | simplu | alb | 2
+----+---------+--------+------------
SELECT s.* FROM clienti p INNER JOIN tricouri s
ON s.proprietar = p.id
WHERE p.nume LIKE 'Lilliana%'
AND s.culoare <> 'alb';
+----+---------+------------+----------
| id | tip | culoare | proprietar
+----+---------+------------+---------
| 4 | de dama | portocaliu | 2 |
| 5 | polo | rosu | 2 |
| 6 | de dama | albastru | 2 |
+----+-------+--------+-------------+
Realizati inca un program asemanator cu 2 tabele: angajati2 si obiecte, unde la obiecte vor fi laptop, veioza, dosare, pix, imprimanta fiecare din cei 3 angajati cu cate 3 obiecte diferite.
miercuri, 17 februarie 2016
Interogari simple si modificari
SQL WHERE
= | Egal |
<> | Inegal. Nu uita: In unele versiuni de SQL operatorul poate fi scris asa != |
> | Mai mare decat |
< | Mai mic decat |
>= | Mai mare sau egal decat |
<= | Mai mic sau egal decat |
BETWEEN | Continut de un interval dat |
LIKE | Cauta dupa asemanare |
IN | Se specifica mai multe valori posibile pentru coloana |
ex.1 SELECT * FROM Elevi
WHERE Nume='Petrescu';
ex.2 SELECT * FROM Elevi
WHERE NrCrtID=2;
Realizati aceasi operatiune in tabla Angajati sortand rezultatele dupa: nrcrt, prenume, firma, adresa
Realizati o operatiune de >= pentru t.Angajati pentru coloana dataangajarii
----------------------------------------------------
AND & OR
SELECT * FROM EleviWHERE Nume='Gheorghe'
AND Scoala='gen.Radulescu';
Realizati aceasi operatiune in tabla Angajati sortand rezultatele where prenume and firma
-------------------------------------------------------------------------
ORDER BY
ex1 SELECT * FROM EleviORDER BY Scoala;
ex.2 SELECT * FROM Elevi
ORDER BY NrCrtID DESC;
ex.3 SELECT * FROM Elevi
ORDER BY Scoala, Nume;
ex.4 SELECT * FROM Elevi
ORDER BY Scoala ASC, Nume DESC;
Realizati aceleasi operatiuni in tabla Angajati ordonand rezultatele dupa prenume si/sau firma
Realizati aceasi operatiune in tabla Angajati ordonand rezultatele dupa dataangajarii si/sau functie
----------------------------------------------------------
UPDATE
UPDATE EleviSET Nume='Onofrei', Scoala='gen.Radulescu'
WHERE Nume='Gheorghe';
Realizati aceasi operatiune in tabla Angajati redenumind numele a 3 angajati
Realizati aceasi operatiune in tabla Angajati redenumind functia a 3 angajati
Reguli SQL
- SELECT - extrage date din baza de date
- UPDATE - actualizeaza datele din baza de date
- DELETE - sterge date din baza de date
- INSERT INTO -insereaza date in baza de date
- CREATE DATABASE - creaza o baza de date noua
- ALTER DATABASE - modifica o baza de date
- CREATE TABLE - creaza o tabla noua
- ALTER TABLE - modifica o tabla
- DROP TABLE - sterge o tabla
- CREATE INDEX - creaza un index la o tabla (key)
- DROP INDEX - sterge un index
Exemple:
CREATE TABLE Elevi
(
(
NrCrtID int NOT NULL AUTO_INCREMENT,
Nume varchar(255),
Prenume varchar(255),
Adresa varchar(255),
Scoala varchar(255)
);
Nume varchar(255),
Prenume varchar(255),
Adresa varchar(255),
Scoala varchar(255)
);
------------------------------
Realizati o tabla Angajati cu urmatoarele caracteristici:
nrcrt,nume,prenume,email,adresa,telefon,functie,departament,firma,dataangajarii
----------------------------------------
SQL ALTER
ALTER TABLE Elevi
ADD Ziuanasterii date
----------------------------------------
SQL INSERT
INSERT INTO Elevi (Prenume, Nume, Adresa, Scoala)
VALUES ('Bogdan','Petrescu','Eminescu 21','Victor Stanculescu');
VALUES ('Bogdan','Petrescu','Eminescu 21','Victor Stanculescu');
Inserati 5 inregistrari complet diferite in tabla Angajati https://static.anaf.ro/static/10/Constanta/CT_30730.pdf
Inserati inca 5 inregistrari asemanatoare in coloane cu cele 5 inregistrari de mai inainte insa unde doar nume, prenume, adresa,email si telefon sa fie diferit
----------------------------------------
SQL SELECT
SELECT Prenume,Scoala FROM 'Elevi';Realizati o selectie in tabla Angajati dupa nume, prenume,firma
----------------------------------------
SQL SELECT DISTINCT
SELECT DISTINCT Scoala FROM Elevi;
Realizati un SELECT DISTINCT la Angajati dupa firma
Realizati un SELECT DISTINCT la Angajati dupa functie
Abonați-vă la:
Postări (Atom)