Why is my WHERE statement not working?

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 are only loading the header and the footer of the page, it does not show any errors on the page, it simply does not print the database records, the page is blank, only with header and footer , already if I take that part of the code 'WHERE "entry_type" = "post"/"pagina"' the records are printed normally, but that's not how it should work ...

How do I resolve this? I could not find the error.

To heal any doubts about my Banco de dados , I'll leave two images of my screen with a sample of my current Banco de dados .

    
asked by anonymous 19.09.2018 / 17:20

1 answer

1

In the code below you have a large sql syntax error. Note that entry_type is enclosed in double quotation marks, which causes the database to 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 this is the only thing and give us feedback;)

    
19.09.2018 / 19:08