sâmbătă, 27 februarie 2016

PHP cu mySQL si Javascript - 2

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>
Publicat de la Niciun comentariu:

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
Publicat de la Niciun comentariu:

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. 
Publicat de la Niciun comentariu:

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 Elevi
WHERE Nume='Gheorghe'
AND Scoala='gen.Radulescu';

Realizati aceasi operatiune in tabla Angajati sortand rezultatele where prenume and firma
 -------------------------------------------------------------------------

ORDER BY

ex1 SELECT * FROM Elevi
ORDER 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 Elevi
SET 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
Publicat de la Niciun comentariu:

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

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

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

Publicat de la Niciun comentariu:
Abonați-vă la: Postări (Atom)