<?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()); ?>
Re: Multiple rows from database only showing 1 result 80 80
Member Avatar

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

Re: Multiple rows from database only showing 1 result 80 80

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

It only shows 1 result and no errors

Re: Multiple rows from database only showing 1 result 80 80
Member Avatar

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?

Re: Multiple rows from database only showing 1 result 80 80

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

Re: Multiple rows from database only showing 1 result 80 80
Member Avatar
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!

Re: Multiple rows from database only showing 1 result 80 80

Yeah

Re: Multiple rows from database only showing 1 result 80 80

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

Re: Multiple rows from database only showing 1 result 80 80
Member Avatar

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

Create table...

Insert into...

Re: Multiple rows from database only showing 1 result 80 80

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

Re: Multiple rows from database only showing 1 result 80 80

Do You need all my records

Re: Multiple rows from database only showing 1 result 80 80
Member Avatar

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.

Re: Multiple rows from database only showing 1 result 80 80

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');

Re: Multiple rows from database only showing 1 result 80 80
Member Avatar

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.

Re: Multiple rows from database only showing 1 result 80 80

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?

Re: Multiple rows from database only showing 1 result 80 80

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

Re: Multiple rows from database only showing 1 result 80 80

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', '', '');

Re: Multiple rows from database only showing 1 result 80 80

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

Re: Multiple rows from database only showing 1 result 80 80

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

Re: Multiple rows from database only showing 1 result 80 80

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
Re: Multiple rows from database only showing 1 result 80 80

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

Re: Multiple rows from database only showing 1 result 80 80

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
Re: Multiple rows from database only showing 1 result 80 80

$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

Re: Multiple rows from database only showing 1 result 80 80

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
Re: Multiple rows from database only showing 1 result 80 80

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!

commented: duplicated post +0
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.