09 Lidhja me DB

Ne kete leksion

• 1. te ndertojme nje tabele nepermjet phpmyadmin

  2. te shtojme disa rreshta ne kete tabele

  3. te ndertojme nje faqe qe liston rreshtat e kesaj tabele

  4. te ndertojme nje faqe qe krijon mundesine e faqezimit te rezultatit

  5. te perdorim funksionet per te kapur metadata-t

  6. te ndertojme faqen per fshirjen e nje rreshti te tabeles

  7. te ndertojme faqet per shtimin e nje rreshti ne tabele

  8. te ndertojme faqet per modifikimin e nje rreshti te tabeles

Tabelat users qe perdoret ne shembujt me poshte

CREATE TABLE IF NOT EXISTS `users` (

`code` int(11) NOT NULL auto_increment,

`username` varchar(50) NOT NULL,

`password` varchar(250) NOT NULL,

`firstname` varchar(20) NOT NULL,

`lastname` varchar(20) NOT NULL,

`email` varchar(200) NOT NULL,

`accountstatus` int(11) NOT NULL,

PRIMARY KEY (`code`)

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

Te popullojme tabelen

INSERT INTO `users` (`username`, `password`, `firstname`, `lastname`, `email`, `accountstatus`)

VALUES

('user1', 'user1', 'John', 'Smith', 'flori@gmail.com', 1),

( 'elda', 'elda', 'Elda', 'Maci', 'elda@gmail.com', 1);

Faqa listusers versioni 1

<?php

// lidhemi me serverin

mysql_connect('192.168.0.86','endri','endri')

or die('nuk lidhem dot me serverin');

//te zgjedhim db

mysql_select_db('endri')

or die('nuk hyj dot tek database');

$qry=" select * from users ";

$res=mysql_query($qry) or die('gabim');

$kollona=mysql_num_fields($res); // numerin e kollonave qe ktheu query

echo '<p>tabela users ka '.$kollona.' kollona</p>';

// si i kapim vlerat nga rezultati

echo '<table>';

echo '<tr bgColor="lightgray">';

echo '<td>';

echo 'code';

echo '</td>';

echo '<td>';

echo 'username';

echo '</td>';

echo '<td>';

echo 'password';

echo '</td>';

echo '<td>';

echo 'firstname';

echo '</td>';

echo '<td>';

echo 'lastname';

echo '</td>';

echo '<td>';

echo 'accountstatus';

echo '</td>';

echo '</tr>';

while($rresht=mysql_fetch_array($res))

{

echo '<tr>';

echo '<td>';

echo $rresht['code'];

echo '</td>';

echo '<td>';

echo $rresht['username'];

echo '</td>';

echo '<td>';

echo $rresht['password'];

echo '</td>';

echo '<td>';

echo $rresht['firstname'];

echo '</td>';

echo '<td>';

echo $rresht['lastname'];

echo '</td>';

echo '<td>';

echo $rresht['accountstatus'];

echo '</td>';

echo '</tr>';

}

echo '</table>';

?>

FAQA LISTUSERS.PHP versioni 2

<?php

include('start.php');

include('db.php');

include('mylib.php');

include('kontrollo.php');

$qry=" select * from users order by code DESC limit 0,3 ";

$res=mysql_query($qry) or die('gabim');

$kollona=mysql_num_fields($res); // numerin e kollonave qe ktheu query

echo '<p align="center"><a href="adduser.php">Add a new user </a></p>';

echo '<p>tabela users ka '.$kollona.' kollona</p>';

// si i kapim vlerat nga rezultati

echo '<table>';

echo '<tr bgColor="lightgray">';

echo '<td>';

echo 'code';

echo '</td>';

echo '<td>';

echo 'username';

echo '</td>';

echo '<td>';

echo 'password';

echo '</td>';

echo '<td>';

echo 'firstname';

echo '</td>';

echo '<td>';

echo 'lastname';

echo '</td>';

echo '<td>';

echo 'accountstatus';

echo '</td>';

echo '<td>';

echo '--------';

echo '</td>';

echo '<td>';

echo '--------';

echo '</td>';

echo '</tr>';

while($rresht=mysql_fetch_array($res))

{

echo '<tr>';

echo '<td>';

echo $rresht['code'];

echo '</td>';

echo '<td>';

echo $rresht['username'];

echo '</td>';

echo '<td>';

echo $rresht['password'];

echo '</td>';

echo '<td>';

echo $rresht['firstname'];

echo '</td>';

echo '<td>';

echo $rresht['lastname'];

echo '</td>';

echo '<td>';

echo $rresht['accountstatus'];

echo '</td>';

echo '<td>';

echo '<a href="edituser.php?id='.$rresht['code'].'">edit</a>';

echo '</td>';

echo '<td>';

echo '<a href="deleteuser.php?id='.$rresht['code'].'">delete</a>';

echo '</td>';

echo '</tr>';

}

echo '</table>';

?>

Faqa list.php qe perdor funksionet metadata

<?php

// lidhemi me serverin

mysql_connect('192.168.0.86','endri','endri')

or die('nuk lidhem dot me serverin');

//te zgjedhim db

mysql_select_db('endri')

or die('nuk hyj dot tek database');

$tbl='users'; // si default afishon userat

if(isset($_REQUEST['tbl']))

{

$tbl=$_REQUEST['tbl'];

}

$qry=" select * from ".$tbl;

$res=mysql_query($qry) or die('gabim');

$kollona=mysql_num_fields($res); // numerin e kollonave qe ktheu query

echo '<p>tabela '.$tbl.' ka '.$kollona.' kollona</p>';

// si i kapim vlerat nga rezultati

$nr_rreshta=mysql_num_rows($res);

echo '<p>tabela '.$tbl.' ka '.$nr_rreshta.' rreshta</p>';

echo '<table>';

echo '<tr bgColor="lightgray">';

for($i=0;$i<$kollona;$i++)

{

echo '<th>';

$fldname=mysql_field_name($res,$i);

echo $fldname;

echo '</th>';

}

echo '</tr>';

while($rresht=mysql_fetch_array($res))

{

echo '<tr>';

for($j=0;$j<$kollona;$j++)

{

echo '<td>';

$fldname=mysql_field_name($res,$j);

echo $rresht[$fldname];

echo '</td>';

}

echo '</tr>';

}

echo '</table>';

?>

Faqa deleteuser.php

<?php

include('start.php');

include('db.php');

include('mylib.php');

include('kontrollo.php');

if(!isset($_REQUEST['id']))

{

exit('cilin perdorues doni te fshini?');

}

$code=$_REQUEST['id'];

$code=$code+0;

if($code==0)

{

exit('kodi i pasakte');

}

$q="delete from users where code=".$code;

mysql_query($q) or die('gabim fshirje');

?>

<script language="JavaScript">

window.location='listusers.php';

</script>

Faqa adduser.php

<?php

include('start.php');

include('db.php');

include('mylib.php');

include('kontrollo.php');

?>

<html>

<head>

<title>Add a new user</title>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

</head>

<body>

<form name="form1" method="post" action="addusersql.php">

<p>username :

<input type="text" name="username">

</p>

<p>password :

<input type="password" name="password">

</p>

<p>emri:

<input type="text" name="emri">

</p>

<p>mbiemri:

<input type="text" name="mbiemri">

</p>

<p>email:

<input type="text" name="email">

</p>

<p>account status:

<select name="accountstatus">

<option value="1" selected>Perdorues Aktiv</option>

<option value="0">Perdorues JO Aktiv</option>

</select>

</p>

<input name="Ruaj" type="submit" value="SAVE">

</form>

</body>

</html>

faqa addusersql.php

<?php

include('start.php');

include('db.php');

include('mylib.php');

include('kontrollo.php');

$username=$_REQUEST['username'];

$password=$_REQUEST['password'];

$firstname=$_REQUEST['emri'];

$lastname=$_REQUEST['mbiemri'];

$email=$_REQUEST['email'];

$accountstatus=$_REQUEST['accountstatus'];

$q=" INSERT INTO users(username,password,firstname,lastname,email,accountstatus)

VALUES('".$username."','".$password."','".$firstname."','".$lastname."','".$email."',".$accountstatus.") ";

mysql_query($q) or die('gabim ne shtim');

?>

<script language="JavaScript">

window.location='listusers.php';

</script>