Why is my WHERE instruction not working?

Asked

Viewed 59 times

0

##index.php

define('DB_HOSTNAME', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', null);
define('DB_CHARSET', 'utf8');
define('DB_DATABASE', 'website');

$conn = new PDO('mysql:host=' . DB_HOSTNAME . ';dbname=' . DB_DATABASE . ';charset=' . DB_CHARSET . ';', DB_USERNAME, DB_PASSWORD);
$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$isPost = !empty($_GET['publicacao']);
$slug =  'home';
if ($isPost) {
    $slug = $_GET['publicacao'];
} elseif (!empty($_GET['pagina'])) {
    $slug = $_GET['pagina'];
}
try {
    $stmt = $conn->prepare('SELECT `title`, `text`, `capa`, `alt`, `slug` FROM diario WHERE `slug` = :slug AND `entry_type` = :entry_type'); 
    $stmt->execute([
        ':entry_type' => $isPost ? 'post' : 'pagina', 
        ':slug' => $slug
    ]);
    if (!$UN = $stmt->fetch(\PDO::FETCH_ASSOC)) {

        throw new \InvalidArgumentException('Post title ' . htmlentities($title, \ENT_QUOTES, 'UTF-8', false) . ' not found in database');
    }

    $titulo = $shareTitulo = $UN['title'];
    $descricao = $shareDescricao = $UN['text'];
    $ogUrl = $urlCanonico = $UN['capa'];
    $imgAlt = $UN['alt'];

    $slug = $UN['slug']; 


    $keywords = $shareImge = '';
} catch (\InvalidArgumentException $e) {
    header('Location: index.php?pagina=home'); 
    exit;
} catch (\Exception $e) {
    throw $e;
}

function sanitize($data, $filter = \FILTER_SANITIZE_STRING) {
    if ($data = filter_var(trim($data), $filter)) {
        $data = preg_replace('/http(s)?:\/\//', '', $data);
    }

    return $data;
}

$loadPage = null;
if ($sanitizedName = sanitize($isPost ? $titulo : $slug)) {
    $loadPageSuffix = ($isPost ? '/postagem/' : '/file_');
    $loadPage =  __DIR__ . $loadPageSuffix . $sanitizedName . '.php';
}
if (null === $loadPage || !is_file($loadPage)) {
    header('HTTP/1.1 404 Not Found'); 
    exit;
}


 $pages = $conn->query('SELECT title, slug FROM diario WHERE "entry_type" = "pagina"') 
     ->fetchAll(\PDO::FETCH_NUM); 
?>

<!DOCTYPE html>
<html>
<head><title><?php echo htmlentities($titulo, \ENT_QUOTES, 'UTF-8', false); ?></title>
</head>
<body>
    <?php foreach($pages as list($pageTitle, $pageSlug)) { ?>
    <li>
        <a href="?p=<?php echo htmlentities($pageSlug, \ENT_QUOTES, 'UTF-8', false); ?>"><?php echo htmlentities($pageTitle, \ENT_QUOTES, 'UTF-8', false); ?></a>
    </li>
    <?php } ?>

<?php require_once $loadPage; ?>

</body>
</html>


##file_home.php

<?php

    $stmtIDrow1 = 'SELECT * FROM diario WHERE "entry_type" = "post"';
    $IDrow1 = $conn->query($stmtIDrow1)->fetchAll();

     ?>

    <?php foreach ($IDrow1 as $IDr1) { ?>
      <a href="index.php?post=<?php echo htmlentities($IDr1['slug'], \ENT_QUOTES, 'UTF-8', false); ?>"><h2><?php echo htmlentities($IDr1['title'], \ENT_QUOTES, 'UTF-8', false); ?></h2></a>
    <?php } ?>

You’re just carrying the header and the footer page, shows no error on the page, simply does not print the records of the database, the page is blank, only with the header and the footer, already if I take that part of the code 'WHERE "entry_type" = "post"/"pagina"' records are printed normally, but that’s not how it should work...

How do I fix it? I couldn’t find the error.

To clear any doubt about mine Banco de dados, I’ll leave two images of my screen with sample of my current Banco de dados.

inserir a descrição da imagem aqui inserir a descrição da imagem aqui

php database

1 answer

1


In the code below there is a large error of sql syntax. Notice that entry_type is in double quotes, which makes the database treat it as a string. 

$pages = $conn->query('SELECT title, slug FROM diario WHERE "entry_type" = "pagina"') 
     ->fetchAll(\PDO::FETCH_NUM);
// ("entry_type"= "pagina") sempre retornará FALSE.

The correct SQL would be 'SELECT title, slug FROM diario WHERE entry_type = "pagina"'

Please check if that’s all and give us feedback ;)

  • Yes, that was the mistake, vlw !

Browser other questions tagged php database

You are not signed in. Login or sign up in order to post.