row name clashing

[Jaynesh]

Hello

 

I've got two tables, one of the rows has the same name in both tables.

 

When I try to echo the rows it stops working. How do I echo a row from a specific table?

 

while( $i < $row = mysql_fetch_array($feed_query, $votefeed_query))
  {
  
  echo $row['post'] . "<br>";
  echo $row['username'] . "<br>";

[MasterACE14]

post your SQL query.

[AyKay47]

while( $i < $row = mysql_fetch_array($feed_query, $votefeed_query))
  {
  
  echo $row['post'] . "<br>";
  echo $row['username'] . "<br>";

 

if both your arguments in your mysql_fetch_array function actually are queries...it won't work, can you post more of your code please

[Psycho]

In addition to what AyKay47 stated (i.e. you can use mysql_fetch_array() on two different query results) the "$i < " in your while() clause makes no sense.

 

one of the rows has the same name in both tables

 

Then do a single query using a JOIN between the tables using the name value to match up the records.

[AyKay47]

or even table declaration

[Jaynesh]

Basically here is what I am trying to do.

 

So far I have set up a friends system. Here is my table layout.

 

User

id

1

2

3

4

username

John

Billy

Jacob

Tina

password

eqfnj1k3

123ejkn13

132kjenf

123ekn

email

email@john.com

email@billy.com

email@jacob.com

email@tina.com

 

Friends

User_id

1

2

3

4

1

4

Friend_id

2

1

4

3

4

1

 

Posts

Post_id

1

2

3

4

Username_id

2

1

4

3

 

post

Hello I'm billy

Hello I'm John

Hello I'm Tina

Hello I'm Jacob

 

So John is friends with Billy and Tina.

Jacob is friends with Tina.

 

If John posts something, it will only be viewable by his friends, billy & tina.

(All the above has already been setup, the next bit is what I'm stuck on)

 

Tina has the ability to share John's post.

If she shares it, it will be viewable by all her friends. (Jacob)

How do I go about doing this?

 

I've setup a new table, I'm just not sure how I would go about putting it in my query.

 

posts_share

Share_id

1

2

3

User_id

1

4

3

Post_id

21

33

45

 

Here is the SQL query I am using, to display posts to friends.

$friendfeed = "SELECT Posts.post, Users.username FROM Posts, Friends, Users, Posts_share WHERE Posts.username_id = Users.id AND Friends.user_id = $user AND Friends.friend_id = Posts.username_id";

$i = "0";
while( $i < $row = mysql_fetch_array($friendfeed_query))
  {
  echo $row['post'] . "<br>";
  echo $row['username'] . "<br>";
  }?>

 

 

[Jaynesh]

I have figured out how to display all the shared posts to friends. The only issue is how do I combine both queries into one output.

 

$friendfeed is the query for displaying all the friends posts

$friendshare is the query for displaying all the shared posts

 

How do I combine both queries? I've tried using OR either I'm not using it correctly or that does not work.

 

$friendfeed = "SELECT * FROM Posts, Friends, Users WHERE Posts.username_id = Users.id AND Friends.user_id = $user AND Friends.friend_id = Posts.username_id";
$friendfeed_query = mysql_query($friendfeed);

$friendshare = "SELECT * FROM Posts, Users, Posts_share, Friends WHERE Posts.username_id = Users.id AND Posts_share.user_id = Friends.friend_id AND Friends.user_id = $user AND Posts_share.post_id = Posts.post_id";
$friendshare_query = mysql_query($friendshare);

[DavidAM]

You need to look into the UNION clause. This combines two queries into a single resultset. However, the two queries must return the same number of columns with the same data-types in the same order; and the column names in the result set will be the column names used in the FIRST query. This should not be a problem, though. You should not use SELECT * in a query unless you actually intend to use ALL of the columns returned.

 

(SELECT "Room" AS ResType, ID, Name, Description FROM Rooms)
UNION
(SELECT "Equipment", ID, Name, Manufacturer FROM Equipment)
ORDER BY Description

This query will return four columns. They will be named "ResType", "ID", "Name", and "Description". The first column is a literal indicating (to me) what type of resource the row describes - I showed this as an example of adding data to the query AND using an Alias - the name of this column is "ResType" (in PHP $type = $row['ResType']). The fourth column is named Description eventhough it will contain Manufacturer for "Equipment" rows, it will still be named "Description". The result will be sorted by the fourth column, regardless of which table it came from.

 

[Jaynesh]

Hi thank you for your response.

What if I don't have the same number of columns?

 

As you can see from my code, the first query has 3 columns and the second one has 4.

When I tried adding the fourth column to the first query, the query stopped working.

 

 

[Jaynesh]

Sorry, I just looked at your code again. Thank you it works

[AyKay47]

you could also use table notation

 

"SELECT Rooms.ResType, Rooms.ID, Rooms.Name, Rooms.Description,Equipmen.ID,Equipment.Name,Equipment.Manufacturer FROM Rooms, Equipment ORDER BY Rooms.Description"

 

however a UNION in this case would be more effificent