Jump to content


Photo

**SOLVED** Using an array in a query, possible?


  • Please log in to reply
8 replies to this topic

#1 cstegner

cstegner
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 09 April 2006 - 06:07 PM


I have a page simular to myspace and on peoples homepages I want to display the last seven bulletins there friends posted. But only the bulletins from their friends.

No idea how to do this and keep two needed things intact.

1) exactly seven bulletins listed.
2) only bulletins from friends.

Sould I make an array of all of the persons friends then use the array in query

so say the array is $friends

SELECT * FROM bulletins WHERE poster=$friends LIMIT 7

I have not started any development on this so there is no need to make do with large mistakes made on my behalf.

Thanks for the help as always.

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 09 April 2006 - 07:12 PM

If you have an array of friends' usernames

$friends = array ('user1', 'user2', 'user3')

then you can select posts from just those friends by putting them in a comma-separated list so you have a WHERE clause like "... WHERE poster IN ('user1', 'user2', 'user3')"

$friend_list = join ("','", $friends);

$sql = "SELECT * FROM bulletins WHERE poster IN ('$friend_list') LIMIT 7";

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 cstegner

cstegner
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 09 April 2006 - 08:05 PM

Awesome exactly what I was hoping, I will go and try it out and replt back. Thanks

#4 cstegner

cstegner
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 09 April 2006 - 09:26 PM

Can't get to work, this is where I am with it.


$a_friend_query = "SELECT * FROM friends WHERE asked='$username' and yes='1' or asking='$username' and yes='1' ORDER BY id DESC";
$a_friend_sql = mysql_query($a_friend_query);
$friends_array = mysql_fetch_array($a_friend_sql);

$friend_list = join ("','", $friends_array);
$clean_bulletin_query = "SELECT * FROM bulletin WHERE poster IN ('$friend_list') LIMIT 7";
$clean_bulletin_result = mysql_query("$clean_bulletin_query");


And it is not returning anything. Please let me know, this is all I now have left haha ;)

#5 cstegner

cstegner
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 09 April 2006 - 10:11 PM

Help please

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 09 April 2006 - 11:12 PM

Because you use "SELECT * " there is no way of knowing the columns in your table so I am going to assume there is a column "friendname" containing the required names.

Try
$friends_array = array();
$a_friend_query = "SELECT * FROM friends 
          WHERE ((asked='$username') OR(asking='$username')) 
          AND yes='1' 
          ORDER BY id DESC";

$a_friend_sql = mysql_query($a_friend_query);

while ($row = mysql_fetch_array($a_friend_sql)) {
       $friends_array[] = $row['friendname'];
}

$friend_list = join ("','", $friends_array);
$clean_bulletin_query = "SELECT * FROM bulletin WHERE poster IN ('$friend_list') LIMIT 7";
$clean_bulletin_result = mysql_query("$clean_bulletin_query");

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 cstegner

cstegner
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 09 April 2006 - 11:41 PM

Hey thanks for sticking with me on this, got it figured out thanks a lot for all your help!!!

#8 cstegner

cstegner
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 19 April 2006 - 04:21 PM

I thought the problem was solved...

Here is the deal. The technique does work, except for the fact that I have--since it is my site--around 8,000 friends. So I don't think it is able to process the array for me in time for query. It works for everyone else, even people with a few hundred friends but not for me.

So new question. Is there a way to pause the rendering of the page till the array is finished? Or some way to get past this. Not a huge problem this second but a few months down the road when people get more friends could be a disaster.

Thanks again, - chris

#9 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 19 April 2006 - 07:51 PM

As you only show 7 in second query, limit the first query to a lower number of friends.

Or do it all ina single query...

$clean_bulletin_query = "SELECT b.* FROM friends f
          INNER JOIN bulletin b ON f.friendname = b.poster
          WHERE ((f.asked='$username') OR(f.asking='$username'))
          AND f.yes='1'
          ORDER BY b.id DESC
          LIMIT 7";

$clean_bulletin_result = mysql_query($clean_bulletin_query);

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users