Show contents of a database with php

Question

Show contents of a database with php

Navigation

#1 by (3 votes)

0

I'm doing a job for the school, in which they ask me to do a "mini replica" of what would be twitter, so we can see data handling with php and so on. Well, I have reached the point of registering users, logging in and saving tweets in a database, but something as simple as showing these, because it does not work out. You see, I have two databases;

Database users: I keep the fields name, username, password and id, in that order. Database tweets where I keep: id of the tweet, text (content of the tweet), date (date it was published) and userId (id of the user who published the tweet), also, in that order.

Well, I have the following function, which is responsible for recovering the content of the tweets

function obtenirTuits(){
    $conn = connectar();

    $sqlTweets = "SELECT * FROM tweets";
    $rowsTweets = $conn->query($sqlTweets);
    $rowsTweets = $rowsTweets -> fetch();

    if (!$rowsTweets){
        echo "<br>"."<br>"."No hi ha tuits a mostrar!";
    }else{

    }
}

As we can see, if the variable $ rowsTweets has no content, it will show us that there are no tweets to show, instead, if not, it should show the following:

Posted by "user" at the "publication date" Content of the tweet

But I do not know how to do this, besides, I would have to give the option that "x" user could erase his tweet, appearing a button in the following way:

Posted by "user" at the "publication date" Remove tweet Content of the tweet

Let's see if anyone can give me a hand, please.

mysql sql php xampp

asked by THR4SH3RP0L0 06.04.2018 в 18:49

source

1 answer

Can a variable be used in different loops? Is it correct to create a class with static methods to connect to a MySQL database?

score 3 · Accepted Answer

I assume you tried to say that you have 2 tables and not 2 databases, what is your code is wrong because it uses the fetch and the query will not get the name of the user that corresponds to the Tweet just its id according to your relationship.

Having the correct query using INNER JOIN , you can access the username that I publish the tweet , also as it has not parametrized the query I did not use queries prepared in the answer, to know if there are no rows you can compare num_rows of the result that returns ->query() if it is 0 there are no results, otherwise we iterate the rows with the while of always. (the format for presenting the data is already at your request)

Code

$sqlTweets = "SELECT t.id, t.date, u.name as usuario
                FROM tweets t
                INNER JOIN usuarios u ON  t.userId = u.id";
if($sqlTweets  = $conn->query($sqlTweets)){
    if($sqlTweets->num_rows === 0)
    {
        echo "<br>"."<br>"."No hi ha tuits a mostrar!";
    }else{
         while($fila = $sqlTweets->fetch_assoc())
        {
            echo "<h1>Publicado por " . $fila['usuario']. " a las " . $fila['date']. "";
            echo "<button>  Eliminar Tweet</button>";
        }
    }
}