Playing with arrays, display data correctly - a little tricky

A little though a tricky issue, there are 3 tables in the database. I have to run two different queries to get complete data to display on front user interface.

Now, am using foreach to iterate through the number of results. But there are two functions, both getting data. One is getting the names and other is getting messages and other details.

Want to display name for each message respectively. What are the possibilites? and the best option?

 

Can you give us table structures with some sample data?

Personally, i guess we can use JOIN here.
But it's hard to answer without knowing the tables.

 

create table users (
id int primary key auto_increment not null,
fullname varchar(50) not null,
username varchar(50) not null,
pass varchar(50) not null,
created_at datetime
)

create table tbl_messages(
msgid int primary key auto_increment not null,
userfrom int not null,
userto int not null, 
msg_title varchar(200) not null,
msg text not null,
created_at datetime not null,
modified_at datetime not null,
msg_status int default 0 not null,
delete_to tinyint default 0 not null,
delete_from tinyint default 0 not null,
foreign key (userfrom) references users(id),
foreign key (userto) references users(id)
)

create table msg_que (
id int primary key auto_increment not null,
msgid int not null,
userfrom int not null,
userto int not null, 
msg text not null,
created_at datetime not null,
msg_status int default 0 not null,
foreign key (msgid) references tbl_messages(msgid),
foreign key (userfrom) references users(id),
foreign key (userto) references users(id)
)

Code (markup):

find_messages_inbox_fullname is getting

SELECT u.fullname
				FROM users u, tbl_messages t
				WHERE t.userfrom = u.id
				AND t.userto =  %s
				AND delete_to =0

Code (markup):

and for rest of the details, we are using

select t.msg_title, t.msg, t.modified_at, t.msg_status from users u, tbl_messages t where t.userto = u.id and t.userto = %s and delete_to = 0

Code (markup):

hope that explains a little

 

Try to run this in your phpmyadmin, you can just modify the "WHERE" section.

SELECT
	messages.*,
	u1.username AS x_from_username,
	u1.fullname AS x_from_fullname,
	u2.username AS x_to_username,
	u2.fullname AS x_to_fullname
FROM
	tbl_messages messages
	LEFT JOIN users u1 ON messages.userfrom = u1.id
	LEFT JOIN users u2 ON messages.userto = u2.id
WHERE
	messages.delete_to = 0

Code (markup):

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE messages.userfrom = u1.id
    LEFT JOIN users u2 WHERE messages.userto = u2.i' at line 9 

Code (markup):

I've been a SQL user and have left that since a long time. So just having a mental block at joins. Is there any good mySQL designer? - I prefer performing all mysql operations from raw code however, just for this joins and stuff.

 

Hey, check my previous post. I misstyped the code and i have edited it

 

Works perfect. Used any query designer? or just directly typed it?

Guess I need to take a quick review of Joins. Thanks.

 

No query designer, just directly typed it.

No problem, have a nice day !