Get username where id=1

artis23 · December 3, 2016, 6:30pm

Hello its again me xd
So i am making little website where everyone can post topic something like forum and when someone post topic its add in database not his username but his id i am trying to make it finds username from users where id=1 and in author page it shows his username
Example:
Mysql database
title= Topic Title
text= Topic Text
author= 1

It is simple but i don’t know why my code doesn’t work…
My code

[php]<?php
$sql = “SELECT id, pid, title, text, main_text, views, date FROM posts ORDER BY date DESC”;
$result = $conn->query($sql);
while($row = $result->fetch_assoc()) {
$gcomments = mysqli_query($conn,“SELECT * FROM comments WHERE pid=’”.$row[‘id’]."’");
$comments = mysqli_num_rows($gcomments);
$glikes = mysqli_query($conn,“SELECT * FROM likes WHERE pid=’”.$row[‘id’]."’");
$likes = mysqli_num_rows($glikes);
$gposter = mysqli_query($conn,“SELECT username FROM users WHERE id=’”.$row[‘pid’]."’");
$poster = mysqli_num_rows($gposter);
echo"

“. $row[‘title’] .”

“. $row[‘text’] .”

View

“. $row[‘views’] .” Views

Posted by $poster

$likes

$comments

“. $row[‘date’] .”

";}
?>[/php]

pid= poster id
in post author i am getting 1 not his username

benanamen · December 3, 2016, 7:48pm

You can’t do those queries in a loop like that. You need to join the tables. Post a dump of your DB SQL.

Also, dont use SELECT *. Name the columns you want.

artis23 · December 3, 2016, 7:55pm

Well i am beginner i dont know what to use i just use that what i have seen on internet
my db
[php]CREATE TABLE bans (
id int(11) NOT NULL,
username text NOT NULL,
ip text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

–
– Table structure for table `comments`

CREATE TABLE comments (
id int(11) NOT NULL,
pid text NOT NULL,
myid text NOT NULL,
text text NOT NULL,
ip text NOT NULL,
date text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

–
– Table structure for table `likes`

CREATE TABLE likes (
id int(11) NOT NULL,
pid text NOT NULL,
myid text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

–
– Table structure for table `posts`

CREATE TABLE posts (
id int(11) NOT NULL,
pid text NOT NULL,
title text NOT NULL,
text text NOT NULL,
main_text text NOT NULL,
views text NOT NULL,
ip text NOT NULL,
date text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

–
– Dumping data for table `posts`

INSERT INTO posts (id, pid, title, text, main_text, views, ip, date) VALUES
(1, ‘1’, ‘aaaaaa’, ‘aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa’, ‘aaaaaaaaaaaaaaaassad\r\nas\r\ndasd\r\nasd\r\nas\r\ndsa\r\ndsadasd’, ‘13’, ‘::1’, ‘11.30.2016 16:46:32’),
(3, ‘1’, ‘sdasdasdjkj’, ‘kjk’, ‘jk’, ‘6’, ‘::1’, ‘11.30.2016 16:52:10’);

–
– Table structure for table `users`

CREATE TABLE users (
id int(11) NOT NULL,
img text NOT NULL,
username text NOT NULL,
email text NOT NULL,
password text NOT NULL,
ip text NOT NULL,
date text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

–
– Dumping data for table `users`

INSERT INTO users (id, img, username, email, password, ip, date) VALUES
(1, ‘http://localhost/GrabPoints/img/avatar.jpg’, ‘admin’, ‘[email protected]’, ‘admin’, ‘::1’, ‘11.29.2016’);[/php]

When someone post comment it goes to comments id,pid,myid,text,ip,date so i am selecting all comments from comments where pid(comment for that post)= post id

while i am creating new website i am learning new things thats how i learn i have created some websites
where you can register login change user info admin banned and banning users but something like this
i do for first time

benanamen · December 3, 2016, 9:07pm

Your DB design isn’t right. I made some changes and added foreign keys. Your data is incomplete and what you want to see is not clear so I couldn’t do a query.

Possible view options

All posts by a certain user
All comments to a certain post…
3…

[code]SET FOREIGN_KEY_CHECKS=0;

– Table structure for bans

DROP TABLE IF EXISTS bans;
CREATE TABLE bans (
ban_id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
ip text NOT NULL,
PRIMARY KEY (ban_id),
KEY user_id (user_id),
CONSTRAINT bans_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

– Records of bans

– Table structure for comments

DROP TABLE IF EXISTS comments;
CREATE TABLE comments (
comment_id int(11) NOT NULL AUTO_INCREMENT,
post_id int(11) NOT NULL,
user_id int(11) NOT NULL,
text text NOT NULL,
ip text NOT NULL,
date text NOT NULL,
PRIMARY KEY (comment_id),
KEY post_id (post_id),
KEY user_id (user_id),
CONSTRAINT comments_ibfk_1 FOREIGN KEY (post_id) REFERENCES posts (post_id),
CONSTRAINT comments_ibfk_2 FOREIGN KEY (user_id) REFERENCES users (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

– Records of comments

– Table structure for likes

DROP TABLE IF EXISTS likes;
CREATE TABLE likes (
like_id int(11) NOT NULL AUTO_INCREMENT,
post_id int(11) NOT NULL,
user_id int(11) NOT NULL,
PRIMARY KEY (like_id),
KEY post_id (post_id),
KEY user_id (user_id),
CONSTRAINT likes_ibfk_1 FOREIGN KEY (post_id) REFERENCES posts (post_id),
CONSTRAINT likes_ibfk_2 FOREIGN KEY (user_id) REFERENCES users (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

– Records of likes

– Table structure for posts

DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
post_id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
title text NOT NULL,
text text NOT NULL,
main_text text NOT NULL,
views text NOT NULL,
ip text NOT NULL,
date text NOT NULL,
PRIMARY KEY (post_id),
KEY id (post_id),
KEY user_id (user_id),
CONSTRAINT posts_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

– Records of posts

INSERT INTO posts VALUES (‘1’, ‘1’, ‘aaaaaa’, ‘aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa’, ‘aaaaaaaaaaaaaaaassad\r\nas\r\ndasd\r\nasd\r\nas\r\ndsa\r\ndsadasd’, ‘13’, ‘::1’, ‘11.30.2016 16:46:32’);
INSERT INTO posts VALUES (‘3’, ‘1’, ‘sdasdasdjkj’, ‘kjk’, ‘jk’, ‘6’, ‘::1’, ‘11.30.2016 16:52:10’);

– Table structure for users

DROP TABLE IF EXISTS users;
CREATE TABLE users (
user_id int(11) NOT NULL AUTO_INCREMENT,
img text NOT NULL,
username text NOT NULL,
email text NOT NULL,
password text NOT NULL,
ip text NOT NULL,
date text NOT NULL,
PRIMARY KEY (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

– Records of users

INSERT INTO users VALUES (‘1’, ‘http://localhost/GrabPoints/img/avatar.jpg’, ‘admin’, ‘[email protected]’, ‘admin’, ‘::1’, ‘11.29.2016’);
SET FOREIGN_KEY_CHECKS=1;
[/code]

artis23 · December 3, 2016, 9:14pm

I dont know what does that keys do but table is same like i made it just changed text and added keys
i just wanted it understands what is username from id in table i just dont know how to make it right

benanamen · December 3, 2016, 9:18pm

Unless you ask a coherent question, I can’t help you.

artis23 · December 3, 2016, 9:22pm

i post topic
mysql add user_id(author)=1
when i open page to see my post i see my details text,title etc…
[php]<?php
$sql = “SELECT id, pid, title, text, main_text, views, date FROM posts ORDER BY date DESC”;
$result = $conn->query($sql);
while($row = $result->fetch_assoc()) {
$gcomments = mysqli_query($conn,“SELECT * FROM comments WHERE pid=’”.$row[‘id’]."’");
$comments = mysqli_num_rows($gcomments);
$glikes = mysqli_query($conn,“SELECT * FROM likes WHERE pid=’”.$row[‘id’]."’");
$likes = mysqli_num_rows($glikes);
$gposter = mysqli_query($conn,“SELECT username FROM users WHERE id=’”.$row[‘pid’]."’");
$poster = mysqli_num_rows($gposter);
echo"

“. $row[‘title’] .”

“. $row[‘text’] .”

View

“. $row[‘views’] .” Views

Posted by $poster

$likes

$comments

“. $row[‘date’] .”

";}
?>[/php]
I need it show author = username
but in databse author= 1(his id)
so i need one more funtion that reads data and finds from users which one have id=1 and shows me his nickname not id

i just need this

SELECT username FROM users WHERE id=1

astonecipher · December 4, 2016, 4:59am

You need to learn about joins

artis23 · December 4, 2016, 9:04pm

SOLVED!

[php]<?php
$sql = “SELECT id, pid, title, text, main_text, views, date FROM posts ORDER BY date DESC”;
$result = $conn->query($sql);
while($row = $result->fetch_assoc()) {
$gcomments = mysqli_query($conn,“SELECT * FROM comments WHERE pid=’”.$row[‘id’]."’");
$comments = mysqli_num_rows($gcomments);
$glikes = mysqli_query($conn,“SELECT * FROM likes WHERE pid=’”.$row[‘id’]."’");
$likes = mysqli_num_rows($glikes);
$gposter = mysqli_query($conn,“SELECT * FROM users WHERE id=’”.$row[‘pid’]."’");
while($row_username = mysqli_fetch_assoc($gposter))
{
$dbusername = $row_username[‘username’];
}
echo"

“. $row[‘title’] .”

“. $row[‘text’] .”

View

“. $row[‘views’] .” Views

Posted by $dbusername

$likes

$comments

“. $row[‘date’] .”

";}
?>[/php]

benanamen · December 4, 2016, 9:15pm

Yeah, no. That’s all kinds of wrong. You were already told what you need to learn about. You don’t need 4 queries. You only need one.

artis23 · December 4, 2016, 9:45pm

Well i dont know anything about it i am just finding codes in internet and making them together for me important is result
i can see poster username that what i needed

astonecipher · December 4, 2016, 9:51pm

Any interest in doing it properly, or just want to make it work how you think it should?

artis23 · December 4, 2016, 9:54pm

Of course i want learn how it is right but i don’t have time & enough good English skills to do that and private teacher or someone who can learn me cost money which i don’t have so i am making my projects on my own to earn some $$
You now will say it is not possible earn money with php, mysql with skillz level like i have but believe me i have earned some money from making websites. Buyer sees that what he want see if he want register he get register he don’t give fu*k about code

astonecipher · December 4, 2016, 9:58pm

So you are learning on your own while selling this? And most of what you are doing is just modifying what you find without thinking about what it does? That is a problem waiting to happen.

artis23 · December 4, 2016, 10:01pm

Not all the time selling but i would like to learn everything i know some other programming languages syntaxes are similar just words and way are different it is not like i am 100% copying form internet without know what it does just change text i know what i need just i dont know how to write it

astonecipher · December 4, 2016, 10:01pm

What do you want out of these table queries?

[php]$gcomments = mysqli_query($conn,“SELECT * FROM comments WHERE pid=’”.$row[‘id’]."’");
$comments = mysqli_num_rows($gcomments);
$glikes = mysqli_query($conn,“SELECT * FROM likes WHERE pid=’”.$row[‘id’]."’");
$likes = mysqli_num_rows($glikes);[/php]

Because using the num_rows just gives you a count of how many rows are returned, not actual data. For likes that may be fine, but I wouldn’t think you would want that from the comments table.

artis23 · December 4, 2016, 10:05pm

I will try explain

So i have comments table when someone post comment it goes there also pid(Topic ID)
i am SELECTING all comments for that topic to see how many people have commented on that topic in total
every comment have his own id + pid(Post ID it shows what post does it belong)
same with likes

maybe there is another way but i just have made this one

astonecipher · December 4, 2016, 10:07pm

I would imagine this would give you what you want, with n^4 less database hits.

SELECT p.id, p.pid, p.title, p.text, p.main_text, p.views, p.date, c.*, l.*, u.username FROM posts INNER JOIN comments c ON p.id = c.pid INNER JOIN likes l ON p.id = l.pid INNER JOIN username u ON p.pid = u.id ORDER BY date DESC

artis23 · December 4, 2016, 10:10pm

I dont understand this well maybe because my mind cant get it or because i didn’t study that Joins tutorial that you showed me reason of that is i dont understand English so well to understand every word so it maybe will take me some time to get it but thanks for help

astonecipher · December 4, 2016, 10:14pm

You are working with a relational database. That means, the tables have some relation to records in another table. Example,

Comments relate to a topic.

Joins link the relations to the tables in a single query, rather than several queries.

Run the query I posted and see if it gives back what you expect. The difference is, mine will pull everything now, because there isn’t a where clause.

Get username where id=1

– – Table structure for table comments

– – Table structure for table likes

– – Table structure for table posts

– – Dumping data for table posts

– – Table structure for table users

– – Dumping data for table users

–
– Table structure for table `comments`

–
– Table structure for table `likes`

–
– Table structure for table `posts`

–
– Dumping data for table `posts`

–
– Table structure for table `users`

–
– Dumping data for table `users`