Select data from 2 tables mysql

Question

I am new at stack-overflow. I have a question, please help me if anyone know .... I build a follower system in my web application where user can follow others and then he can see updates on his timeline. I have 2 tables

|     tbl_follow           |
|---------------------------
| id | follow | follow_by  |
| 01 | joe    | mark       |
----------------------------

And second table is:

|       timeline_tbl           |
|-------------------------------
| id | staus        | username |
| 01 | demo text    | joe      |
| 01 | demo text    | adem     |
--------------------------------

Now suppose login user is "mark". so mark will be able to see only "joe" status on his timeline because he followed him (see tbl_follow) " Can someone please help me... and thanks in advance for your time and sorry for my bad english

Currently i used this code

$sql = ("select * from tbl_follow where username = 'mark' ");
while($rows=mysql_fetch_assoc($sql))
{
    $follow=$rows["follow"];
$asql = ("select * from timeline where username = '$follow' ");
$get = mysql_fetch_assoc($asql);
$status = $get['status'];
echo $status "<br>";
}

The follow by should be an int type as a foreign key. Actually, I would suggest another table. You could have a user's table, a follow_relation table, and a timeline_tbl. Relation table could store the user ID and the following user ID. — newGuy, Jan 14 '17 at 22:16
you never executed the queries and you're mixing mysql APIs; that's why your code isn't working. And the connection api is unknown. — Funk Forty Niner, Jan 14 '17 at 22:26
my code is working ... it's a typing mistake. i want to execute in 1 query is there anyway? @Fred-ii- -ii — joe, Jan 14 '17 at 22:28
@joe you did successfully connect with `mysql_` and not `mysqli_` right? You were using a `mysqli_` function earlier `mysqli_fetch_assoc()`. — Funk Forty Niner, Jan 14 '17 at 22:33
Plus, and again; you never executed any of those queries with `mysql_query()`. You say your code works; not the way you presented it in your question. `mysql_error()` right now, should be throwing you a whole bunch of errors. — Funk Forty Niner, Jan 14 '17 at 22:35
you have to make a joint query : http://dev.mysql.com/doc/refman/5.7/en/join.html — ᴄʀᴏᴢᴇᴛ, Jan 14 '17 at 22:36
We would hope that the 'error' message would read "The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead..." — Strawberry, Jan 14 '17 at 23:40
table structure didn't match with your code.cause *tbl_follow * has no username column. — denny, Jan 16 '17 at 09:19

score 0 · Answer 1 · answered Jan 16 '17 at 09:03

Try this:

$sql = "select timeline_tbl.* from tbl_follow JOIN timeline_tbl ON timeline_tbl.username =  tbl_follow.follow where follow_by = 'mark' "

$result = mysql_query($sql);

if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "No rows found";
    exit;
}


while ($row = mysql_fetch_assoc($result)) {
    echo $row["staus"];
}

score 0 · Answer 2 · answered Jan 16 '17 at 09:13

0

select t.* 
from timeline_tbl t
join tbl_follow f on f.username = t.follow
where t.follow = 'mark'

It will return all records which are followers of MARK.

answered Jan 16 '17 at 09:13

Naveed Ramzan

3,565
3
25
30

Select data from 2 tables mysql

2 Answers2