Tag: PHP

Me encuentro realizando un practica de un pequeño blog con php y sql y necesito relacionar 2 tablas de mi base de datos, por ejemplo que al crear una nueva publicación de mi blog se muestren la ID de la publicación y las ID’S de las categorías seleccionadas en otra tabla para almacenar esa información

Estas son las 3 tablas que tengo creadas, en POSTS se almacena todos los datos de las publicaciones y en CATEGORIAS se almacena todos los datos de las categorias.

TABLA: POST

TABLA: CATEGORIAS

Como puedo hacer que en post_category se almacenen como ya dije las ID de las publicaciones y las ID’S de las categorías seleccionadas. Como por ejemplo en este caso la publicación “El oceano” tiene de categoria “naturaleza”.

Ejemplo de la tabla post_category

post_id = (ID del post = El oceano)

categoria_id = (ID de la categoría = Naturaleza)

CODIGO: db.php

<?php

session_start();
require('connect.php');

function dd($value)
{
    echo "<pre>", print_r($value, true), "</pre>";
    die();
}


function executeQuery($sql, $data)
{
    global $conn;
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    $stmt = $conn->prepare($sql);
    $values = array_values($data);
    $types = str_repeat('s', count($values));
    $stmt->bind_param($types, ...$values);
    $stmt->execute();
    return $stmt;
}


function selectAll($table, $conditions = ())
{
    global $conn;
    $sql = "SELECT * FROM $table";
    if (empty($conditions)) {
        $sql = $sql . " ORDER BY id DESC";
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        $records = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
        return $records;    
    } else {
        // $sql = "SELECT * FROM $table";
        
        $i = 0;
        foreach ($conditions as $key => $value) {
            if ($i === 0){
                $sql = $sql . " WHERE $key=?";
                
            } else {
                $sql = $sql . " AND $key=?"; 
            }
            $i++;
        }
        
        $stmt = $conn->prepare($sql);
        $values = array_values($conditions);
        $types = str_repeat('s', count($values));
        $stmt->bind_param($types, $values);
        $stmt->execute();
        $records = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
        return $records;
    }
}



function selectOne($table, $conditions)
{
    global $conn;
    $sql = "SELECT * FROM $table ";

        $i = 0;
                foreach ($conditions as $key => $value) {
            if ($i === 0){
                $sql = $sql . " WHERE $key=?";
                
            } else {
               $sql = $sql . " AND $key=?"; 
            }
            $i++;
        }
        $sql = $sql . " LIMIT 1";
        $stmt = executeQuery($sql, $conditions);
        $records = $stmt->get_result()->fetch_assoc();
        return $records;
    }

function create($table, $data)
{
    global $conn;
    $sql = "INSERT INTO $table SET ";
      
    $i = 0;
    foreach ($data as $key => $value) {
            if ($i === 0){
                $sql = $sql . " $key=?";
                
            } else {
                $sql = $sql . ", $key=?"; 
            }
            $i++;
        }
    $stmt = executeQuery($sql, $data);
    $id = $stmt->insert_id;
    return $id;
    
}

function update($table, $id, $data)
{
    global $conn;
    $sql = "UPDATE $table SET ";
      
    $i = 0;
    foreach ($data as $key => $value) {
            if ($i === 0){
                $sql = $sql . " $key=?";
                
            } else {
                $sql = $sql . ", $key=?"; 
            }
            $i++;
        }
    
    $sql = $sql . " WHERE id=?";
    $data('id') = $id;
    $stmt = executeQuery($sql, $data);
    return $stmt->affected_rows;
    
}


function delete($table, $id)
{
    global $conn;
    $sql = "DELETE FROM $table WHERE id=?";
      
    $stmt = executeQuery($sql, ('id' => $id));
    return $stmt->affected_rows;
    
}

CODIGO: posts.php

<?php 

include($_SERVER('DOCUMENT_ROOT').'/app/database/db.php');
include($_SERVER('DOCUMENT_ROOT').'/app/helpers/validatePost.php');

$table = 'posts';
$categorias = selectAll('categorias');

$errors = array();
$id = '';
$title = '';
$body = '';
$topic_id = '';

$posts = selectAll($table);

    
    if (!empty($_FILES('image')('name'))) {
        $image_name = time() . '_' . $_FILES('image')('name');
        $destination = "../../images/" . $image_name;
        
        $result = move_uploaded_file($_FILES('image')('tmp_name'), $destination);
        
        if ($result){
            $_POST('image') = $image_name;
        } else {
            array_push($errors, "¡Algo fallo al subir la imagen!");
        }
        
        
    } else {
      array_push($errors, "¡Necesitas subir una imagen!");
    }
    
    if (count($errors) === 0){
        unset($_POST('add-post'));
        $post_id = create($table, $_POST);
        $_SESSION('message') = '¡Anime creado correctamente!';
        $_SESSION('type') = 'success';
        header('location: ../../admin/posts/index.php');
        exit();
    } else {
        $title = $_POST('title');
        $body = $_POST('body');
        $categoria = $_POST('')

    }


if (isset($_GET('id'))){
    $id = $_GET('id');
    $post = selectOne($table, ('id' => $id));
}

if (isset($_GET('del_id'))){
    $id = $_GET('del_id');    
    $count = delete($table, $id);
    $_SESSION('message') = '¡Anime eliminado correctamente!';
    $_SESSION('type') = 'success';
    header('location: ../../admin/posts/index.php');
    exit();
}

if (isset($_POST('update-post'))){
    $errors = validateEdit($_POST); 
    
    if (count($errors) === 0){
        $id = $_POST('id');
        unset($_POST('update-post'), $_POST('id'));
        $post_id = update($table, $id, $_POST);
        $_SESSION('message') = '¡Anime actualizado correctamente!';
        $_SESSION('type') = 'success';
        header('location: ../../admin/topics/index.php');
        exit();        
    } else {
        $id = $_POST('id');
        $title = $_POST('title');
        $body = $_POST('body');

}
} 
?>      

               

CODIGO: categorias.php

<?php 

include($_SERVER('DOCUMENT_ROOT').'/app/database/db.php');
include($_SERVER('DOCUMENT_ROOT').'/app/helpers/validateCategoria.php');

$table = 'categorias';

$errors = array();
$id = '';
$name = '';
$body = '';
$topic_id = '';

$categorias = selectAll($table);


if (isset($_POST('add-post'))) {
    $errors = validateCategoria($_POST);
    
    if (count($errors) === 0){
        unset($_POST('add-post'));
        $post_id = create($table, $_POST);
        $_SESSION('message') = '¡Categoria creada correctamente!';
        $_SESSION('type') = 'success';
        header('location: ../../admin/categorias/index.php');
        exit();
    } else {
        $name = $_POST('name');

    }
}

if (isset($_GET('id'))){
    $id = $_GET('id');
    $post = selectOne($table, ('id' => $id));
}

if (isset($_GET('del_id'))){
    $id = $_GET('del_id');    
    $count = delete($table, $id);
    $_SESSION('message') = '¡Categoria eliminada correctamente!';
    $_SESSION('type') = 'success';
    header('location: ../../admin/categorias/index.php');
    exit();
}

if (isset($_POST('update-post'))){
    $errors = validateEdit($_POST); 
    
    if (count($errors) === 0){
        $id = $_POST('id');
        unset($_POST('update-post'), $_POST('id'));
        $post_id = update($table, $id, $_POST);
        $_SESSION('message') = '¡Categoria actualizada correctamente!';
        $_SESSION('type') = 'success';
        header('location: ../../admin/categorias/index.php');
        exit();        
    } else {
        $id = $_POST('id');
        $name = $_POST('name');

}
} 
?>