Aqui os traigo este sistema de noticias + comentarios + paginacion realizado en PDO
DB
Código SQL:
Ver original--
-- Estructura de tabla para la tabla `comments`
--
CREATE TABLE IF NOT EXISTS `comments` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`id_news` text NOT NULL,
`user` VARCHAR(20) NOT NULL,
`comments` text NOT NULL,
`date` VARCHAR(19) NOT NULL,
`ip` VARCHAR(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;# MySQL ha devuelto un conjunto de valores vacío (es decir: cero columnas).
-- --------------------------------------------------------
--
-- Estructura de tabla para la tabla `news`
--
CREATE TABLE IF NOT EXISTS `news` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`user` VARCHAR(20) NOT NULL,
`title` text NOT NULL,
`content` text NOT NULL,
`date` VARCHAR(19) NOT NULL,
`ip` VARCHAR(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;# MySQL ha devuelto un conjunto de valores vacío (es decir: cero columnas).
-- --------------------------------------------------------
--
-- Estructura de tabla para la tabla `user`
--
CREATE TABLE IF NOT EXISTS `user` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(20) NOT NULL,
`password` VARCHAR(60) NOT NULL,
`date` VARCHAR(19) NOT NULL,
`ip` VARCHAR(10) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `id_2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;# MySQL ha devuelto un conjunto de valores vacío (es decir: cero columnas).
Código PHP:
<?php
$connection = new PDO("mysql:host=localhost;dbname=base de dato","usuario","contraseña");
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
?>
Index.php Código PHP:
<?php
session_start();
require('/config/index.php');
if(isset($_SESSION['username'])):
if(isset($_POST['news'])):
if(empty($_POST['title']) || empty($_POST['content'])):
echo 'Hay campos en blanco';
else:
$title = $connection->prepare("SELECT title FROM news WHERE title = :title");
$title->bindParam(':title',$_POST['title']);
$title->execute();
if($title->fetchColumn() == $_POST['title']):
echo 'Existe una noticia con el mismo titulo';
else:
$news = $connection->prepare("INSERT INTO news(id,user,title,content,date,ip) VALUES ('', '".$_SESSION['username']."', :title, :content, '".date('H:i:s d/m/Y')."', :ip)");
$news->bindParam(':title',$_POST['title']);
$news->bindParam(':content',$_POST['content']);
$news->bindParam(':ip',$_SERVER['REMOTE_ADDR']);
$news->execute();
echo 'Noticia creada correctamente';
endif;
endif;
endif;
echo '<form action="" method="post">
<input name="title" placeholder="Titulo de la noticia"><br>
<textarea name="content" placeholder="Contenido de la noticia" rows="10" cols="40"></textarea><br>
<input name="news" type="submit" value="Crear noticia">
</form>
<a href="/news.php">Noticias</a> | <a href="/logout.php">Logout</a>';
else:
if(isset($_POST['login'])):
if(empty($_POST['username']) || empty($_POST['password'])):
echo 'No dejes campos en blanco';
elseif(strlen($_POST['username']) > 20):
echo 'El usuario no puede tener mas de 20 caracteres';
elseif(strlen($_POST['password']) > 20):
echo 'La contraseña no puede tener mas de 20 caracteres';
else:
$login = $connection->prepare("SELECT username FROM user WHERE username = :username AND password = :password");
$login->bindParam(':username',$_POST['username']);
$login->bindParam(':password',crypt($_POST['password'], '$2a$07$rieh3693fjarjeuf38cw27fg2$'));
$login->execute();
if($login->fetchColumn() > 0):
$_SESSION['username'] = $_POST['username'];
header('Location: /');
exit();
else:
echo 'Datos incorrectos';
endif;
endif;
endif;
echo '<form action="" method="post">
<input name="username" placeholder="Username"><br>
<input name="password" placeholder="Password"><br>
<input name="login" type="submit">
</form>
<a href="/register.php">Registrate</a>';
endif;
?>
Register.php Código PHP:
<?php
session_start();
require('/config/index.php');
if(isset($_SESSION['username'])):
header('Location: /');
exit();
else:
if(isset($_POST['register'])):
if(empty($_POST['username']) || empty($_POST['password'])):
echo 'No dejes campos en blanco';
elseif(strlen($_POST['username']) > 20):
echo 'El usuario no puede tener mas de 20 caracteres';
else:
$user = $connection->prepare("SELECT username FROM user WHERE username = :username");
$user->bindParam(':username',$_POST['username']);
$user->execute();
if($user->fetch(PDO::FETCH_ASSOC)):
echo 'El usuario ya existe';
elseif(strlen($_POST['password']) > 20):
echo 'La contraseña no puede tener mas de 20 caracteres';
elseif($_POST['password'] <> $_POST['password2']):
echo 'Las contraseñas no coinciden';
else:
$register = $connection->prepare("INSERT INTO user(id,username,password,date,ip) VALUES ('', :username, :password, '".date('H:i:s d/m/Y')."', :ip)");
$register->bindParam(':username',$_POST['username']);
$register->bindParam(':password',crypt($_POST['password'], '$2a$07$rieh3693fjarjeuf38cw27fg2$'));
$register->bindParam(':ip',$_SERVER['REMOTE_ADDR']);
$register->execute();
$_SESSION['username'] = $_POST['username'];
header('Location: /');
exit();
endif;
endif;
endif;
echo '<form action="" method="post">
<input name="username" placeholder="Username"><br>
<input name="password" placeholder="Password"><br>
<input name="password2" placeholder="Vuelve a ingresar la contraseña"><br>
<input name="register" type="submit">
</form>';
endif;
?>
News.php Código PHP:
<?php
session_start();
require('/config/index.php');
if(isset($_GET['id'])):
$news = $connection->prepare("SELECT user,title,content,date FROM news WHERE title = :title");
$news->bindParam(':title',urldecode($_GET['id']));
$news->execute();
if($news1 = $news->fetch(PDO::FETCH_ASSOC)):
echo '<h1>'.$news1['title'].'</h1>'.$news1['content'].'<br> <strong>Autor:</strong> '.$news1['user'].' <strong>Fecha:</strong> '.$news1['date'].'<hr>';
if(isset($_SESSION['username'])):
if(isset($_POST['send'])):
if(empty($_POST['comments'])):
echo 'No puedes dejar el comentario en blanco';
else:
$comments = $connection->prepare("INSERT INTO comments(id,id_news,user,comments,date,ip) VALUES ('', :id_news, '".$_SESSION['username']."', :comments, '".date('H:i:s d/m/Y')."', :ip)");
$comments->bindParam(':id_news', urldecode($_GET['id']));
$comments->bindParam(':comments', $_POST['comments']);
$comments->bindParam(':ip', $_SERVER['REMOTE_ADDR']);
$comments->execute();
header('Location: /news.php?id='.urlencode($_GET['id']).'');
exit();
endif;
endif;
echo '
<form action="" method="post">
<textarea name="comments" placeholder="Comentario" rows="10" cols="40"></textarea><br>
<input name="send" type="submit" value="Publicar comentario">
</form>';
else:
echo 'Para comentar tienes que iniciar session <br>';
endif;
$comments = $connection->prepare("SELECT user,comments,date FROM comments WHERE id_news = :id_news ORDER BY date DESC");
$comments->bindParam(':id_news',urldecode($_GET['id']));
$comments->execute();
while($comments1 = $comments->fetch(PDO::FETCH_ASSOC)):
echo '<strong>Comentario escrito por:</strong> '.$comments1['user'].' <strong>Fecha:</strong> '.$comments1['date'].'<br>'.$comments1['comments'].'<br><br>';
endwhile;
$comments = $connection->prepare("SELECT COUNT(*) user,comments,date FROM comments WHERE id_news = :id_news");
$comments->bindParam(':id_news',urldecode($_GET['id']));
$comments->execute();
if($comments->fetchColumn() == 0):
echo 'No hay comentarios';
endif;
else:
echo 'La noticia no existe';
endif;
else:
$desde = @$_GET['pag'] * 10;
$hasta = (@$_GET['pag'] * 10) + 10;
$news = $connection->prepare("SELECT id,title FROM news LIMIT $desde,$hasta");
$news->execute();
while($news1 = $news->fetch(PDO::FETCH_ASSOC)):
echo '<h1><a href="/news.php?id='.urlencode($news1['title']).'">'.$news1['title'].'</a></h1>';
endwhile;
$count_news = $connection->query("SELECT COUNT(*) title FROM news")->fetch(PDO::FETCH_ASSOC);
for($i = 0; $i < round($count_news['title'] / 10 + 1); $i++):
echo '<a href="/news.php?pag='.$i.'">'.$i.'</a>';
endfor;
endif;
?>
Logout.php Código PHP:
<?php
session_start();
session_destroy();
header('Location: /');
exit();
?>
Si veis alguna forma de mejorar el codigo, comentalo porfavor.