Jump to content


Photo

News Feed Design

social php sql

  • Please log in to reply
32 replies to this topic

#1 derekshull

derekshull

    Advanced Member

  • Members
  • PipPipPip
  • 107 posts

Posted 25 February 2013 - 04:07 PM

I've create a table of "followers" where users can follow other users. It's set up as such:

ID username followname

username is the name of the person that is doing the action/following and followname is the person who is being followed.

I want to set up a news feed for each user on the profile.php page. The news feed should look at who the user is following (followname) and then fill the news feed with all the posts from those people in order of what time they were posted. The posts are in a different table called "needs" and each post is timestamped.

Can anyone help me in started to code this?

#2 derekshull

derekshull

    Advanced Member

  • Members
  • PipPipPip
  • 107 posts

Posted 25 February 2013 - 04:08 PM

There will be many people that the user follows too. The goal is to have each post shown according to the time it was posted, with the newer posts being on top.

#3 derekshull

derekshull

    Advanced Member

  • Members
  • PipPipPip
  • 107 posts

Posted 25 February 2013 - 04:38 PM

I know this is quite barbaric but it's a step in the right direction. I've coded something that works but it's not in the right order:

$following = mysql_query("SELECT * FROM follow WHERE username='$username'");
while ($followrow = mysql_fetch_array($following)) {
$followname = $followrow['followname'];


$lookupposts = mysql_query ("SELECT * FROM needs WHERE needsusername='$followname'");
while ($postrow = mysql_fetch_array($lookupposts)) {
$description = $postrow['description'];
$needsusername = $postrow['needsusername'];
$datesubmitted = $postrow['datesubmitted'];


echo "<b>User:</b> $needsusername<br><br><b>Description:</b><br>$description<br><br><b>Date Submitted:</b>  $datesubmitted<br><br>----------------------------------------<br><br>";

It shows it in order of username THEN timestamp, but I need it by just timestamp. Any suggestions?

#4 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 25 February 2013 - 04:40 PM

Never do queries in loops. Join the two tables, and use an ORDER BY clause.
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#5 derekshull

derekshull

    Advanced Member

  • Members
  • PipPipPip
  • 107 posts

Posted 25 February 2013 - 04:43 PM

I can't join the two tables. One is for linking accounts as followers/following and the other is for user data like emails passwords. How would I join the two?

#6 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 25 February 2013 - 04:44 PM

...
Google "mysql join"

My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#7 derekshull

derekshull

    Advanced Member

  • Members
  • PipPipPip
  • 107 posts

Posted 25 February 2013 - 04:56 PM

Ok I'm on the right track! Thanks for the help but I'm still stumped, could you look at this and see what's wrong?

$following = mysql_query("SELECT * FROM follow WHERE username='$username'");
$followrow = mysql_fetch_array($following);
$followname = $followrow['followname'];



$lookupposts = mysql_query("SELECT * FROM follow a, needs b WHERE a.username='$username' AND b.needsusername='$followname' AND b.status='posted' ORDER BY b.datetime");
while ($postrow = mysql_fetch_array($lookupposts)) {
$description = $postrow['description'];
$needsusername = $postrow['needsusername'];
$datesubmitted = $postrow['datesubmitted'];


echo "<b>User:</b> $needsusername<br><br><b>Description:</b><br>$description<br><br><b>Date Submitted:</b>  $datesubmitted<br><br>----------------------------------------<br><br>";



}

It's only showing one user's posts that I'm following and I'm not quite sure why. It also shows the same posts multiple times.

#8 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 25 February 2013 - 05:01 PM

Specify what columns you join on. 

If you're going to use table aliases make them make sense. a and b are meaningless.

You also should be using a primary key ID instead of the username. Username should only be stored in the user table, everything else should use their user id. 


You are running one query to get a user, and then selecting the information for that ONE USER. You need to do just ONE query joining the two tables only limiting it to the logged in user, not BOTH users.
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#9 derekshull

derekshull

    Advanced Member

  • Members
  • PipPipPip
  • 107 posts

Posted 25 February 2013 - 05:07 PM

All that went over my head...still confused.

#10 derekshull

derekshull

    Advanced Member

  • Members
  • PipPipPip
  • 107 posts

Posted 25 February 2013 - 05:07 PM

I've looked up examples of Joining and this is the best I can see.

#11 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 25 February 2013 - 05:09 PM

You want all the users you follow right? Right now you select all the users in your SQL, but your PHP only pulls the first row. Then you do ANOTHER query to get that users posts. You're doing the same thing you were before.

Do only ONE query.
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#12 derekshull

derekshull

    Advanced Member

  • Members
  • PipPipPip
  • 107 posts

Posted 25 February 2013 - 05:15 PM

This is what I have now:

$lookupposts = mysql_query("SELECT * FROM follow a, needs b WHERE a.username='$username' AND b.username='$username' b.status='posted'");
while ($postrow = mysql_fetch_array($lookupposts)) {
$description = $postrow['description'];
$needsusername = $postrow['needsusername'];
$datesubmitted = $postrow['datesubmitted'];


echo "<b>User:</b> $needsusername<br><br><b>Description:</b><br>$description<br><br><b>Date Submitted:</b>  $datesubmitted<br><br>----------------------------------------<br><br>";



}

I don't feel like it's accomplishing what I want but it gives me this error:
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given on line 46

#13 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 25 February 2013 - 05:18 PM

You have a syntax error in your query. Read the link in my signature on debugging SQL.
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#14 derekshull

derekshull

    Advanced Member

  • Members
  • PipPipPip
  • 107 posts

Posted 25 February 2013 - 05:20 PM

I understand why the error is being caused. How to fix it is the reason that I come on here for help.

#15 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 25 February 2013 - 05:21 PM

You have a syntax error in your query. Read the link in my signature on debugging SQL.


You're the fastest reader I've ever seen! 

OR there's no way you read the post, implemented proper error checking and got the actual error. 
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#16 derekshull

derekshull

    Advanced Member

  • Members
  • PipPipPip
  • 107 posts

Posted 25 February 2013 - 05:22 PM

And you're the most sarcastic person I've ever seen!

I know it's because I'm reusing the variables in my while loop, doesn't change the fact that I still don't know how to fix the issue.

#17 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 25 February 2013 - 05:24 PM

It has nothing to do with that. 

My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#18 derekshull

derekshull

    Advanced Member

  • Members
  • PipPipPip
  • 107 posts

Posted 25 February 2013 - 05:32 PM

Look I don't know the answer, I've searching forums and looked at debugging crap all day and I need answers...that's why I've come to a forum. Answers that I can understand because I'm not as advanced at this than you. But instead I get snide remarks. I'll look elsewhere.

#19 derekshull

derekshull

    Advanced Member

  • Members
  • PipPipPip
  • 107 posts

Posted 25 February 2013 - 05:50 PM

here is what my php is after trying to fathom what the heck you're talking about:

$sql = "SELECT * FROM follow, needs WHERE follow.username=needs.username ORDER BY needs.datetime";
$lookupposts = mysqli_query($sql) or trigger_error("Query Failed! SQL: $sql - Error: ".mysqli_error(), E_USER_ERROR);
while ($postrow = mysql_fetch_array($lookupposts)) {
$description = $postrow['needs.description'];
$needsusername = $postrow['needs.needsusername'];
$datesubmitted = $postrow['needs.datesubmitted'];


echo "<b>User:</b> $needsusername<br><br><b>Description:</b><br>$description<br><br><b>Date Submitted:</b>  $datesubmitted<br><br>----------------------------------------<br><br>";


}



and here what your debug code showed me:
Warning: mysqli_query() expects at least 2 parameters, 1 given in /home/content/17/9932517/html/ProjectX/profile.php on line 45

Warning: mysqli_error() expects exactly 1 parameter, 0 given in /home/content/17/9932517/html/ProjectX/profile.php on line 45

Fatal error: Query Failed! SQL: SELECT * FROM follow, needs WHERE follow.username=needs.username ORDER BY needs.datetime - Error: in /home/content/17/9932517/html/ProjectX/profile.php on line 45


Still no clue how to solve this issue......

#20 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 25 February 2013 - 05:57 PM

You can't mix mysqli and mysql. You need to use the mysql version of the error function when you're using a mysql connection instead of mysqli. 

However, you did actually fix the syntax error, so good job. You still need to specify what column to do your join ON, but otherwise good.
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com