SQL problems

[Roost3r]

Hi,
I have 2 tables I need to join, they were created by this php code:

// add article table
@mysql_Query("CREATE TABLE `".trim($_POST["tblprefix"])."articles` (
`id` int(11) NOT NULL auto_increment,
`articleId` int(11) NOT NULL default '0',
`title` varchar(250) NOT NULL default '',
`author` varchar(70) NOT NULL default '',
`body` text NOT NULL,
`time` int(11) NOT NULL default '0',
`page` tinyint(4) NOT NULL default '0',
`approved` tinyint(1) NOT NULL default '0',
`folderid` TINYINT NOT NULL default '1',
`type` char(1) NOT NULL default 'a',
`threadid` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1", $db);

// add headline table
@mysql_Query("CREATE TABLE `".trim($_POST["tblprefix"])."headlines` (
`id` int(11) NOT NULL auto_increment,
`body` mediumtext NOT NULL,
`title` varchar(250) NOT NULL default '',
`author` varchar(70) NOT NULL default '',
`time` int(11) NOT NULL default '0',
`approved` tinyint(1) NOT NULL default '0',
`folderid` TINYINT NOT NULL default '1',
`type` char(1) NOT NULL default 'h',
`threadid` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1", $db);

I need to join them based on a equal folderid then sort them by time and also in the article table only get the ones which have the type of 'a', ive tried:

SELECT id, title, author, time, approved, folderid, type FROM headlines, articles WHERE folderid = ".$_GET["id"]." ORDER BY time DESC LIMIT ".$slimit.", ".$itemlimit

SELECT * FROM headlines, articles WHERE folderid = '5' ORDER BY time DESC

SELECT id, title, author, time, approved, folderid, type FROM headlines WHERE folderid = '5'
UNION ALL
SELECT id, title, author, time, approved, folderid, type FROM articles WHERE folderid = '5' AND type = 'a' ORDER BY time DESC LIMIT ".$slimit.", ".$itemlimit

the last one works on one server but not another, i need to get them workingon both, is there another way to do this?

ive used or die() for each one of these and it fails for all of them

[raf]

A joins is something completely different then a union. A union will append the second recordset to the first. A join will create a recordset with fields from two o more tables.

read this thread and then try writing one or else post what you cook up, and i'll set it straight then.

http://www.codingforums.com/showthread.php?s=&threadid=31219

[Roost3r]

i dont think a inner join would get me what i want.... correct me if im wrong but a inner join would retrieve all the items from one table and only matching items from another,

i need to get just the items from both tables where headlines.folderid and articles.folderid equal a specified number, articles.type needs to be 'a', ORDER BY time on the combined result, and i have a limit for paging


SELECT headlines.id, headlines.title, headlines.author, headlines.time, headlines.approved, headlines.folderid, headlines.type, articles.id, articles.title, articles.time, articles.approved, articles.folderid FROM headlines INNER JOIN articles ON headlines.folderid = articles.folderid WHERE headline.folderid = 5 AND articles.type = 'a' ORDER BY time

^^ does not work (i removed orderby time, that doenst work either)


SELECT id, title, author, time, approved, folderid, type FROM headlines WHERE folderid = '5'
UNION ALL
SELECT id, title, author, time, approved, folderid, type FROM articles WHERE folderid = '5' AND type = 'a' ORDER BY time DESC LIMIT ".$slimit.", ".$itemlimit

if unions and joins are two different things then i may want a union since the above statemenet gives me the result im looking for on a certain server, however it doesnt work on another

[Roost3r]

i did a phpinfo() on the two servers


mysql client api version:
server1: 3.23.56
server2: 4.0.15

PHP Version:
server1: 4.3.3
server2: 4.3.4

that union all query works on server 2 but not server 1, i need it to work on both

[raf]

Originally posted by Roost3r
i dont think a inner join would get me what i want.... correct me if im wrong but a inner join would retrieve all the items from one table and only matching items from another,
You are wrong.

An inner join will only retrieve values if the columns where you join on have identical values. It will then retrieve the values from both tables. If the fields where you join on are not identical, then it will retrieve nothing.
What you describe is probably a misunderstanding version of an outer join. There you will have all records from 1 table. But the columns that you select from the other table(s) will only be filled in if the joined fields are identical. Else they remain empty (Null).

if unions and joins are two different things then i may want a union since the above statemenet gives me the result im looking for on a certain server, however it doesnt work on another
They are completely different. They have nothing in common.
Imagen these tables

table : bookcategory
id | label | manager
1| "thriller" | Jack
2| "computing" | Bill
3| "bla"| Jane

table : books
id | title | categorie
1| "foo" | 2
2| "bar" | 2
3| "test"| 3

If i would run
select * from bookcategory UNION select * from books
then i would get
id | label | manager
1| "thriller" | Jack
2| "computing" | Bill
3| "bla"| Jane
1| "foo" | 2
2| "bar" | 2
3| "test"| 3

You see? It creates the first recordset, and then appends the second one to it. Regardles of what the column of both tables mean --> you just need an even set of columns of the same type
Suppose i include a condition because i only need the books from computation
And i run
select * from bookcategory where id=2 UNION select * from books where categorie=2
then you get
id | label | manager
2| "computing" | Bill
1| "foo" | 2
2| "bar" | 2

Is that what you are after ? Your initial description sure doesn't sound like that. I need to join them based on a equal folderid which is something completely different then
i need to get just the items from both tables where headlines.folderid and articles.folderid equal a specified number


With an inner join, you get the columns from both tables in 1 record. So no appending --> all values for these joined records are combined into one record.
select *.bookcategory, *.books from bookcategory inner join books on bookcategory.id=books.category where bookcategory.id=2
will return
id | label | manager|id_books | title | categorie
2| "computing" | Bill|1| "foo" | 2
2| "computing" | Bill|2| "bar" | 2

Which is used for completely different purposes

that union all query works on server 2 but not server 1, i need it to work on both
server1: 3.23.56
Bad luck. Union is implemented from musql 4.0.0 on ...
So you just need to run two seperate querys : performanceimpact = near to zero

[Roost3r]

thanks for the help, since UNION's arnt compatible with older versions, i think i need to store the info from both tables in a 2 dimensional array and sort the array based on the time

i think theres a multidimensional php sorting function, ill look into that
thanks again



er well another problem arrises then, with my UNION ALL i was doing a LIMIT to only get the most recent 15 items from the combined tables being sorted by time

having 2 different querys i wouldnt know how many to pull from each table

another solution would be to just combine the article and headline tables in the database, the difference if only 2 columns, however i believe this would affect search speed and the 2 extra columns would be left blank for the headlines

[raf]

er well another problem arrises then, with my UNION ALL i was doing a LIMIT to only get the most recent 15 items from the combined tables being sorted by time

Upgrading server 1 would be the most obvious and long-term sollution ...
With 2 querys, you can apply the limit clause to both querys, to be sure to have not more then 30 records.
Another possebility is creating a temporarely table and then using an INSERT INTO SELECT to store the two recordsets inthere, and then a select with the limit 15 together what you need. This will be executed faster and will generate less trafic then doing it in PHP.

The fastes is off course storing everything in one table, which probably wount have any impact on performance. Unless you now have seperate table of 200K or 300K records (which i doubt, given the subject).
One of mySQLs' strong points are very fast selects. And LIMIT is optimised to instantly retunrn the results and then quit after the number of records is reached.
If you would create an index on the time column and one on the folderid + change the type-column into a numerical columntype and also create an index on that, then you will have real fast selects.

And if your table realy gets to big (number of records, number of columns is less important), then you should just archive the older records in a seperate table.

That the cells of 2 columns remains empty is no problem at all.

[Roost3r]

i ended up combining the tables, works fine now, thanks