Leksion 05

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 list.php ( version 2)

<?php

//$cnn=mysql_connect

$cnn=mysql_connect('localhost','root','')

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

mysql_select_db('jak',$cnn)

or die('nuk zgjedh dot DB');

$table="miqte";

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

{

$table=$_REQUEST['tabela'];

}

$start=0;

$pagesize=5;

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

{

$start=$_REQUEST['fillim'];

}

$sql=" SELECT * FROM $table limit $start,$pagesize ;" ;

// kjo eshte pewr debuding

//echo $sql;

$res=mysql_query($sql)

or die(mysql_error());

$n=mysql_num_rows($res);

$col=mysql_num_fields($res);

?>

<html>

<head>

<title>List Users</title>

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

</head>

<body>

<?php

if($n==0)

{

echo "<h1>Komanda Juaj ka kthyer $n rreshta.</h1>";

exit();

}

?>

Ketu do te afishohen <?php echo $table; ?>.

<br>

<table border="1" >

<thead>

<tr>

<?php

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

{

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

echo "<th>$colname</th>";

}

?>

</tr>

</thead>

<tbody>

<?php

for($i=1;$i<=$n;$i++)

{

// si te kapim nje rresht nga bashkesia e rreshtave

$rresht=mysql_fetch_array($res);

echo "<tr>";

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

{

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

echo "<td>".$rresht[$colname]."</td>";

}

echo "</tr>";

}

?>

</tbody>

</table>

<br><br>

<table border="1" width="400">

<tr>

<td>

<?php

if ($start>0)

{

$s=$start-$pagesize;

echo " <a href='list.php?fillim=$s'>

majtas

</a>

";

}

?>

</td>

<td>

<?php

if ($n==$pagesize)

{

$s=$start+$pagesize;

echo " <a href='list.php?fillim=$s'>

djathtas

</a>

";

}

?>

</td>

</tr>

</table>

</body>

</html>

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>

Faqa list users me paging

<?php

include('start.php');

include('db.php');

include('mylib.php');

include('kontrollo.php');

$q2="select code from users";

$res2=mysql_query($q2) or die('gabim2');

$totalrows=mysql_num_rows($res2);

$start=0;

$pagesize=4;

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

{

$start=$_REQUEST['start'];

$start=$start+0;

}

$qry=" select * from users limit ".$start.",".$pagesize." ";

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

$n=mysql_num_rows($res); // numri i rreshtave maksimalisht eshte 4

if($n==$pagesize)

{

?>

<p>

<a href="listusers.php?start=<?php echo $start+$pagesize;?>">

Forward

</a>

</p>

<?php

}

if(($start-$pagesize)>=0)

{

?>

<p>

<a href="listusers.php?start=<?php echo $start-$pagesize;?>">

Backward

</a>

</p>

<?php

}

$page=0;

$currentrow=0;

while(($currentrow+$pagesize)<=$totalrows)

{

echo '<a href="listusers.php?start='.$currentrow.'">';

echo 'Faqa '.($page+1);

echo '</a>';

echo '&nbsp;&nbsp;&nbsp;&nbsp;';

$page++;

$currentrow=$currentrow+$pagesize;

}

?>

Faqa edituser.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="select * from users where code=".$code;

$res=mysql_query($q) or die(mysql_error());

$n=mysql_num_rows($res);

if($n==0) exit('nuk ka perdorues me kete kod');

$rresht=mysql_fetch_array($res);

$code=$rresht['code'];

$username=$rresht['username'];

$password=$rresht['password'];

$firstname=$rresht['firstname'];

$lastname=$rresht['lastname'];

$email=$rresht['email'];

$accountstatus=$rresht['accountstatus'];

?>

<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="editusersql.php">

<p>code :

<input type="text" name="code" value="<?php echo $code; ?>" readonly>

</p>

<p>username :

<input type="text" name="username" value="<?php echo $username; ?>" >

</p>

<p>password :

<input type="password" name="password" value="<?php echo $password; ?>">

</p>

<p>emri:

<input type="text" name="emri" value="<?php echo $firstname; ?>">

</p>

<p>mbiemri:

<input type="text" name="mbiemri" value="<?php echo $lastname; ?>">

</p>

<p>email:

<input type="text" name="email" value="<?php echo $email; ?>">

</p>

<p>account status:

<input type="text" name="accountstatus" value="<?php echo $accountstatus; ?>" >

</p>

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

</form>

</body>

</html>

Faqa editusersql.php

<?php

include('start.php');

include('db.php');

include('mylib.php');

include('kontrollo.php');

$code=$_REQUEST['code'];

$username=$_REQUEST['username'];

$password=$_REQUEST['password'];

$firstname=$_REQUEST['emri'];

$lastname=$_REQUEST['mbiemri'];

$email=$_REQUEST['email'];

$accountstatus=$_REQUEST['accountstatus'];

$q="

update users

set

username='".$username."',password='".$password."',

firstname='".$firstname."', lastname='".$lastname."',

email='".$email."',accountstatus=".$accountstatus."

where code=".$code;

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

?>

<script language="JavaScript">

window.location='listusers.php';

</script>