Problems with a join query

[Fenhopi]

Hi, I have this join query that's supposed to take the information from both tables, but it only takes from the commentstatus table, any idea why?

 

Query:

$GetStatus = "SELECT * FROM commentstatus, wallposts WHERE (commentstatus.status='$Naw' AND commentstatus.person='$username') OR (wallposts.stat='$Naw' AND wallposts.person='$username')";

[suresh_kamrushi]

Hey Fenhopi ,

You can try this, as still I am not sure about your table relations. If this query does not work please provide table structure with their relation so that we can map it accorindly to fetch the correct records.

 

$GetStatus = "SELECT * FROM commentstatus, wallposts WHERE

    (commentstatus.status='$Naw' AND wallposts.stat='$Naw') OR

    (commentstatus.person='$username' AND wallposts.person='$username')";

[Fenhopi]

Thanks, but it didn't work. Here's table structure:

CREATE TABLE `wallposts` (
  `postid` int(11) NOT NULL AUTO_INCREMENT,
  `message` varchar(1000) DEFAULT NULL,
  `byuser` varchar(25) DEFAULT NULL,
  `dtime` varchar(50) DEFAULT NULL,
  `stat` int(11) DEFAULT NULL,
  `person` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`postid`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1


CREATE TABLE `commentstatus` (
  `commentid` int(11) NOT NULL AUTO_INCREMENT,
  `shairid` int(11) NOT NULL,
  `message` varchar(250) DEFAULT NULL,
  `byuser` varchar(50) DEFAULT NULL,
  `dtime` varchar(50) DEFAULT NULL,
  `person` varchar(50) NOT NULL,
  `status` int(4) DEFAULT NULL,
  PRIMARY KEY (`commentid`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

[Muddy_Funster]

couple of things.

why are you selecting * ? do you actualy need all the fields from both the tables?

you didn't state what the connection between the tables was and you have no forign keys listed. Does commentstatus.shairid = wallposts.postid ?

max size for a varchar field should be 255, that you were able to create that table with a value of 1000 is a bit surprising.  if you want a field that size you need to use TEXT(1000).

 

can you let us know what you want out of each of the tables as well as how they relate to each other so we can better help create the query?

[fenway]

max size for a varchar field should be 255, that you were able to create that table with a value of 1000 is a bit surprising.  if you want a field that size you need to use TEXT(1000).

Not true at all.

 

VARCHAR can be up to 65,535 in version 5 -- and it's much better to use this than TEXT -- which, by the way, has no "size".