Multiple rows from database only showing 1 result

[Barrett1]

<?PHP $results = mysql_query("SELECT * FROM messages, comments WHERE messages.user_on='$user2' AND comments.msg_id_fk = messages.msg_id ORDER BY messages.msg_id, comments.com_id DESC") or die(mysql_error()); ?>

[rajarajan2017]

Formation of query might be wrong, could you try with your sql editor or phpmyadmin

[Barrett1]

Formation of query might be wrong, could you try with your sql editor or phpmyadmin

It only shows 1 result and no errors

[rajarajan2017]

Then the query returns the right output. you have to form the query according to your needs. did you executed in phpadmin sql editor? If yes, then change the query, Check properly with your records and come to know why returning one record. or Post the records in the table with the tablename here?

[Barrett1]

I'm not very good with mysql
I tried with just:

<?PHP $results = mysql_query("SELECT * FROM messages, comments") OR die(mysql_error()); ?>

And it displays all comments and messages but when I add the rest it's only 1

[rajarajan2017]

SELECT * FROM messages, comments WHERE messages.user_on='$user2';

SELECT * FROM messages, comments WHERE messages.user_on='$user2'  AND comments.msg_id_fk = messages.msg_id;

SELECT * FROM messages, comments WHERE messages.user_on='$user2'  AND comments.msg_id_fk = messages.msg_id ORDER BY messages.msg_id;

SELECT * FROM messages, comments WHERE messages.user_on='$user2'  AND comments.msg_id_fk = messages.msg_id ORDER BY messages.msg_id, comments.com_id DESC;

Take a notepad, write your table records data for two tables. Then verify one by one in your sql editor and you come to know whats the problem. Got it!

[Barrett1]

Yeah

[Barrett1]

I think I got what it is comments.msg_id_fk = messages.msg_id with this it only show 1 result without shows all.
but I need comments.msg_id_fk = messages.msg_id
how do I fix it

[rajarajan2017]

Please provide your table strucutre and records to me, Let I will check

Create table...

Insert into...

[Barrett1]

Please provide your table strucutre and records to me, Let I will check

Create table...

Insert into...

Table:

messages

Roows:

msg_id
message
username
user_on
time

//////////////////////////////////////////////////////////////////////////////////////

Table:

comments

Rows:

com_id
comment
msg_id_fk
_username
user_on
time

[Barrett1]

Do You need all my records

[rajarajan2017]

Yes, I am only going to copy and paste in my databse to create the table and records. So I need the exact code to paste, not only the structure. Just going to place in sql editor and if I click go, then it should create the table and the same for records. understand.

[Barrett1]

Messages Table:

CREATE TABLE messages
(
msg_id int,
mesasage varchar(255),
username varchar(255),
useron varchar(255),
time varchar(255),
date varchar(255)
)

Message rows:

INSERT INTO `oiwounpo_users`.`messages` (`msg_id`, `message`, `username`, `user_on`, `time`, `date`) VALUES (NULL, 'test message', 'test', 'test1', '', '');

Comments Table:

CREATE TABLE comments
(
com_id int,
comment varchar(255),
msg_id_fk varchar(255),
_username varchar(255),
_useron varchar(255),
_time varchar(255),
_date varchar(255)
)

Comments rows:

INSERT INTO `oiwounpo_users`.`comments` (`com_id`, `comment`, `msg_id_fk`, `_username`, `_user_on`, `_time`, `_date`) VALUES (NULL, 'Comment1', '352', 'test66', 'test1', '1', '1'), (NULL, 'Comment2', '352', 'test45', 'test1', '1', '1'), (NULL, 'Comment3', '352', 'test45', 'test1', '', ''), (NULL, 'Comment4', '352', 'test3', 'test1', '1', '1'), (NULL, 'Comment5', '352', 'test4', 'test1', '1', '1');

[rajarajan2017]

The problem is you dont set any constraints for your table, like Primary key and foriegn key. So go thru your table and create primary key and foriegn keys.

[Barrett1]

The problem is you dont set any constraints for your table, like Primary key and foriegn key. So go thru your table and create primary key and foriegn keys.

How do I set msg_id_fk to a foriegn key in phpmyadmin?

[tyson.crouch]

i think you will need to provide a full mysql dump for us to properly look over your tables and query.

If you could post the full sql dump, it would be much much more helpful

[Barrett1]

i think you will need to provide a full mysql dump for us to properly look over your tables and query.

If you could post the full sql dump, it would be much much more helpful

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE TABLE IF NOT EXISTS `comments` (
`com_id` int(11) NOT NULL auto_increment,
`comment` varchar(200) default NULL,
`msg_id_fk` int(11) default NULL,
`_username` varchar(255) NOT NULL,
`_user_on` varchar(255) NOT NULL,
`_time` time NOT NULL,
`_date` varchar(255) NOT NULL,
PRIMARY KEY (`com_id`),
KEY `msg_id_fk` (`msg_id_fk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=119 ;

INSERT INTO `comments` (`com_id`, `comment`, `msg_id_fk`, `_username`, `_user_on`, `_time`, `_date`) VALUES
(59, 'u', 295, 'Support', 'Barrett', '00:00:00', 'March 12 at 8:08 pm'),
(71, 'test', 315, 'Aly!', 'bob', '00:00:00', 'March 14 at 1:05 am'),
(73, 'test2', 314, 'Aly!', 'bob', '00:00:00', 'March 14 at 1:05 am'),
(74, 'test2', 315, 'Aly!', 'bob', '00:00:00', 'March 14 at 1:05 am'),
(75, 'test3', 315, 'Aly!', 'bob', '00:00:00', 'March 14 at 1:05 am'),
(77, 'Test 33', 313, 'Aly!', 'bob', '00:00:00', 'March 14 at 1:06 am'),
(78, 'test#33 coment 1', 316, 'Aly!', 'bob', '00:00:00', 'March 14 at 1:17 am'),
(79, 'test#33 coment 2', 316, 'Aly!', 'bob', '00:00:00', 'March 14 at 1:17 am'),
(80, 'he', 295, 'Aly!', 'bob', '00:00:00', 'March 14 at 12:08 pm'),
(81, 'Message #1 Comment #1', 318, 'test_user', 'bob', '00:00:00', 'March 14 at 12:50 pm'),
(82, 'Message #1 Comment #2', 318, 'test_user', 'bob', '00:00:00', 'March 14 at 12:51 pm'),
(83, 'Message #1 Comment #3', 318, 'test_user', 'bob', '00:00:00', 'March 14 at 12:52 pm'),
(84, 'Message #2 Comment #1', 319, 'test_user', 'bob', '00:00:00', 'March 14 at 12:53 pm'),
(85, 'Message #2 Comment #2', 319, 'test_user', 'bob', '00:00:00', 'March 14 at 12:54 pm'),
(98, 's', 346, 'Wille3a', 'Wille3a', '00:00:00', 'July 4 at 5:16 pm'),
(99, 'message1_commnet1', 347, 'test98', 'test', '00:00:00', ''),
(100, 'message1_commnet2', 347, 'test45', 'test', '00:00:00', ''),
(101, 'message1_commnet3', 347, 'test23', 'test', '00:00:00', ''),
(102, 'message4_commnet1', 350, 'test67', 'test', '00:00:00', ''),
(103, 'message4_commnet2', 350, 'test44', 'test', '00:00:00', ''),
(104, 'message1_commnet1', 347, 'test98', 'test', '00:00:01', '1'),
(105, 'message1_commnet2', 347, 'test45', 'test', '00:00:01', '1'),
(106, 'message1_commnet3', 347, 'test23', 'test', '00:00:01', '1'),
(107, 'message4_commnet1', 350, 'test67', 'test', '00:00:01', '1'),
(108, 'message4_commnet2', 350, 'test44', 'test', '00:00:01', '1'),
(109, 'Comment1', 352, 'test66', 'test1', '00:00:00', ''),
(110, 'Comment2', 352, 'test45', 'test1', '00:00:00', ''),
(111, 'Comment3', 352, 'test45', 'test1', '00:00:00', ''),
(112, 'Comment4', 352, 'test3', 'test1', '00:00:00', ''),
(113, 'Comment5', 352, 'test4', 'test1', '00:00:00', ''),
(114, 'Comment1', 352, 'test66', 'test1', '00:00:01', '1'),
(115, 'Comment2', 352, 'test45', 'test1', '00:00:01', '1'),
(116, 'Comment3', 352, 'test45', 'test1', '00:00:00', ''),
(117, 'Comment4', 352, 'test3', 'test1', '00:00:01', '1'),
(118, 'Comment5', 352, 'test4', 'test1', '00:00:01', '1');

CREATE TABLE IF NOT EXISTS `messages` (
`msg_id` int(11) NOT NULL auto_increment,
`message` varchar(200) default NULL,
`username` varchar(255) NOT NULL,
`user_on` varchar(255) NOT NULL,
`time` varchar(255) NOT NULL,
`date` varchar(255) NOT NULL,
PRIMARY KEY (`msg_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=353 ;

INSERT INTO `messages` (`msg_id`, `message`, `username`, `user_on`, `time`, `date`) VALUES
(278, 'test Barrett', 'Barrett', 'Barrett', '', 'March 5 at 5:13 pm'),
(295, 'tr', 'Barrett', 'Barrett', '', 'March 6 at 11:17 pm'),
(299, 'nice', 'Wille3a', 'bob', '', 'March 8 at 8:17 pm'),
(318, 'Message #1', 'test_user', 'bob', '', 'March 14 at 12:48 pm'),
(319, 'Message #2', 'test_user', 'bob', '', 'March 14 at 12:49 pm'),
(320, 'Message#3', 'Aly!', 'bob', '', 'March 14 at 12:49 pm'),
(346, 'd', 'Wille3a', 'Wille3a', '', 'July 4 at 5:16 pm'),
(347, 'message1', 'test1', 'test', '', ''),
(348, 'message2', 'test3', 'test', '', ''),
(349, 'message3', 'test2', 'test', '', ''),
(350, 'message4', 'test1', 'test', '', ''),
(351, 'message5', 'test3', 'test', '', ''),
(352, 'test message', 'test', 'test1', '', '');

[Barrett1]

I set msg_id_fk INDEX and to a foriegn key and still do's not work

[tyson.crouch]

First off, your foreign key constraints are not actually set up at all. To do this, you should read the MySQL manual on foreign key constraints as it provides very good examples and explinations. The basics for setting up a foreign key is simple;

FOREIGN KEY (msg_id_fk) REFERENCES messages(msg_id)
ON DELETE CASCADE
ON UPDATE CASCADE

This should be done in your create table statement

[tyson.crouch]

As for your query, try this;

SELECT * FROM 
messages 
INNER JOIN comments 
ON (messags.msg_id = comments.msg_id_fk) 
WHERE (messages.user_on = '$user2') 
ORDER BY messages.msg_id, comments.com_id 
DESC

[Barrett1]

As for your query, try this;

SELECT * FROM 
messages 
INNER JOIN comments 
ON (messags.msg_id = comments.msg_id_fk) 
WHERE (messages.user_on = '$user2') 
ORDER BY messages.msg_id, comments.com_id 
DESC

Your code only displays messages that have comments. I need it to display all and I already have it set to
FOREIGN KEY (msg_id_fk) REFERENCES messages(msg_id)
ON DELETE CASCADE
ON UPDATE CASCADE

SELECT * FROM 
messages 
INNER JOIN comments 
ON (messags.msg_id = comments.msg_id_fk) 
WHERE (messages.user_on = '$user2') 
ORDER BY messages.msg_id, comments.com_id 
DESC

Can you please help thanks

[Stylish]

Can you please supply the data that is being passed in the "$user2" variable?

You can do a little count like this

SELECT m.msg_id, count(*)
FROM messages m, comments c
WHERE m.msg_id *= c.msg_id_fk
GROUP BY m.msg_id

If you knock out the count and group by you will see each message + comment combo:

SELECT m.msg_id, m.user_on, c.com_id
FROM messages m, comments c
WHERE m.msg_id *= c.msg_id_fk

[Barrett1]

$user2 is just the session users name.
And what will your code do?
This is what your code gives me:
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 '= c.msg_id_fk GROUP BY m.msg_id' at line 3

[tyson.crouch]

Change from an Inner join to an outter Join (left / right joins)

SELECT * FROM 
messages 
LEFT JOIN comments 
ON (messags.msg_id = comments.msg_id_fk) 
WHERE (messages.user_on = '$user2') 
ORDER BY messages.msg_id, comments.com_id 
DESC

[Stylish]

I loaded your table and data to test.

mysql> select m.msg_id, count(*)
from messages m
left join comments c on (m.msg_id = c.msg_id_fk)
group by m.msg_id
having count(*) > 1;

+--------+----------+
| msg_id | count(*) |
+--------+----------+
|    295 |        2 |
|    318 |        3 |
|    319 |        2 |
|    347 |        6 |
|    350 |        4 |
|    352 |       10 |
+--------+----------+
6 rows in set (0.00 sec)

And then a listening of every message + comment:

mysql> select m.msg_id, m.user_on, c.com_id
    -> from messages m
    -> left join comments c on (m.msg_id = c.msg_id_fk);

Example for "test1":

mysql> select m.msg_id, m.user_on, c.com_id from messages m left join comments c on (m.msg_id = c.msg_id_fk) where m.user_on = 'test1';
+--------+---------+--------+
| msg_id | user_on | com_id |
+--------+---------+--------+
|    352 | test1   |    109 |
|    352 | test1   |    110 |
|    352 | test1   |    111 |
|    352 | test1   |    112 |
|    352 | test1   |    113 |
|    352 | test1   |    114 |
|    352 | test1   |    115 |
|    352 | test1   |    116 |
|    352 | test1   |    117 |
|    352 | test1   |    118 |
+--------+---------+--------+
10 rows in set (0.00 sec)

Good luck!