offsprg01 Posted January 9, 2007 Share Posted January 9, 2007 ??? ??? ???this for some reason has me stumped. i can do the whole relational tables thing, it's easy enough but what if the tables have not relation. ok i'll explain my situation.i have 6 tables in which i want to show the 10 combined newest entries on my index page. http://www.3rdedition.org to see the asp version but i have no clue how to do this in php. this is an example of one of the tables http://www.3rdedition.org/site_features/agimimnon/agimimnon.php you can see the rest of the tables by follow the links under site features on the right. i need to order them by date decending. it needs to look something like the first link i posted. and thats where i'm stuck. i can write a script to pull the newst entires from all the table but what it say, one table does have any new entries but on table has say 6 new entries? i hope that all makes sense. if you follw the first link i provided it will make more sense. Link to comment https://forums.phpfreaks.com/topic/33527-multiple-tables-on-the-same-page/ Share on other sites More sharing options...
trq Posted January 10, 2007 Share Posted January 10, 2007 Sql is the same in php or asp, just use the same queries. Link to comment https://forums.phpfreaks.com/topic/33527-multiple-tables-on-the-same-page/#findComment-156932 Share on other sites More sharing options...
offsprg01 Posted January 10, 2007 Author Share Posted January 10, 2007 [quote author=thorpe link=topic=121710.msg500908#msg500908 date=1168387539]Sql is the same in php or asp, just use the same queries.[/quote]i'm guessing i don't understand the queries then. i did not create the original site. here is the code for the original.[code]<% DIM oCMD SET oCMD = Server.CreateObject("ADODB.Command") SET oCMD.ActiveConnection = oConn %><% DIM cmdtext0, oRs0 CmdText0 = "{CALL Threbb_ContentUser.sp_GetFrontPage}" SET oRs0 = Server.CreateObject("ADODB.Recordset") CALL adocommand(cmdtext0) SET oRs0=oCMD.Execute CONST FrontPageID = 0 CONST FrontPageType = 1 CONST FrontPageTitle = 2 CONST FrontPageAuthorName = 3 CONST FrontPageStartDate = 4 CONST FrontPageBlurb = 5 CONST FrontPageImage = 6 Dim FPType, Counter, Link, LinkName, LinkID%><% Counter = 0 Do While Not oRs0.EOF If oRs0(FrontPageType) = "bigstick" or oRs0(FrontPageType) = "guffaw" then Link="/index.asp" LinkName="view comic" LinkID=oRs0(FrontPageType) & "id" Else Link="/viewer.asp" LinkName="more" LinkID="id" End If%>[/code] Link to comment https://forums.phpfreaks.com/topic/33527-multiple-tables-on-the-same-page/#findComment-157067 Share on other sites More sharing options...
offsprg01 Posted January 10, 2007 Author Share Posted January 10, 2007 so i'm thinking i need something like the followingSELECT * FROM Agimimnon, Article, Review, News, Resource, BigStick, Guffaw, ORDER BY SubDate DESC LIMIT 10 would this work? also as each table as diffrent columns how what is the best way to show the data? Link to comment https://forums.phpfreaks.com/topic/33527-multiple-tables-on-the-same-page/#findComment-157091 Share on other sites More sharing options...
offsprg01 Posted January 12, 2007 Author Share Posted January 12, 2007 well i'm still having some issues writing the php to display the diffrent tables. can anyone point me n the write direction? i have no problems dispplaying data from the individual tables but my isses is that hen trying to display the data from the combined fields, i have no clue how to set up the if...else statments to use the correct php the show the correct colums from each table... for some reason this has me stumped. it seems like it would be easy enough, but i can't seem to get my head around it. ??? Link to comment https://forums.phpfreaks.com/topic/33527-multiple-tables-on-the-same-page/#findComment-159101 Share on other sites More sharing options...
offsprg01 Posted January 12, 2007 Author Share Posted January 12, 2007 seems my sql is not working either. this is getting to be a huge headache. >:(#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 'ORDER BY SubDate DESC LIMIT 10 at line 1 Link to comment https://forums.phpfreaks.com/topic/33527-multiple-tables-on-the-same-page/#findComment-159107 Share on other sites More sharing options...
play_ Posted January 12, 2007 Share Posted January 12, 2007 SELECT * FROM Agimimnon, Article, Review, News, Resource, BigStick, Guffaw, ORDER BY SubDate DESC LIMIT 10try taking out the ',' after Guffaw Link to comment https://forums.phpfreaks.com/topic/33527-multiple-tables-on-the-same-page/#findComment-159118 Share on other sites More sharing options...
offsprg01 Posted January 12, 2007 Author Share Posted January 12, 2007 yup lol i caught that right after i posted. ::) Link to comment https://forums.phpfreaks.com/topic/33527-multiple-tables-on-the-same-page/#findComment-159147 Share on other sites More sharing options...
offsprg01 Posted January 12, 2007 Author Share Posted January 12, 2007 ok new sql error #1052 - Column: 'StartDate' in order clause is ambiguous is this becuase every table has a start date? Link to comment https://forums.phpfreaks.com/topic/33527-multiple-tables-on-the-same-page/#findComment-159211 Share on other sites More sharing options...
offsprg01 Posted January 12, 2007 Author Share Posted January 12, 2007 http://www.codingforums.com/showthread.php?t=46214i this is what i'm needing to do. but as i am an sql retard can some one please explain? Link to comment https://forums.phpfreaks.com/topic/33527-multiple-tables-on-the-same-page/#findComment-159219 Share on other sites More sharing options...
play_ Posted January 12, 2007 Share Posted January 12, 2007 [quote author=offsprg01 link=topic=121710.msg503244#msg503244 date=1168620482]http://www.codingforums.com/showthread.php?t=46214i this is what i'm needing to do. but as i am an sql retard can some one please explain?[/quote]The second post on that page tells you how to solve it :) Link to comment https://forums.phpfreaks.com/topic/33527-multiple-tables-on-the-same-page/#findComment-159453 Share on other sites More sharing options...
offsprg01 Posted January 13, 2007 Author Share Posted January 13, 2007 i know it does but i can't seem to get the syntax down. :( Link to comment https://forums.phpfreaks.com/topic/33527-multiple-tables-on-the-same-page/#findComment-159709 Share on other sites More sharing options...
offsprg01 Posted January 13, 2007 Author Share Posted January 13, 2007 i think this should work but it seems kinda klunky, and the script seems to time out.[code]SELECT `Agimimnon`.ID, `Agimimnon`.Title, `Agimimnon`.AuthorName, `Agimimnon`.StartDate, `Agimimnon`.Blurb, `Agimimnon`.Image, `Article`.ID, `Article`.Title, `Article`.AuthorName, `Article`.AuthorEmail, `Article`.StartDate, `Article`.Blurb, `Article`.Image, `Bigstick`.ID, `Bigstick`.Title, `Bigstick`.AuthorName, `Bigstick`.Blurb, `Bigstick`.Thumb, `Bigstick`.StartDate, `Guffaw`.ID, `Guffaw`.AuthorName, `Guffaw`.Blurb, `Guffaw`.Image, `Guffaw`.Thumb, `Guffaw`.Title, `Guffaw`.StartDate, `News`.ID, `News`.`Type`, `News`.Title, `News`.AuthorName, `News`.StartDate, `News`.Blurb, `News`.Image, `Resources`.ResourceID, `Resources`.StartDate, `Resources`.ResourceAuthor, `Resources`.ResourceThumb, `Resources`.ResourceDescription, `Review`.Title, `Review`.AuthorName, `Review`.AuthorEmail, `Review`.StartDate, `Review`.Rating, `Review`.Blurb, `Review`.Body, `Review`.Image, `Review`.Live, `Review`.ID, `Resources`.ResourceNameFROM `Agimimnon`, `Article`, `Bigstick`, `Guffaw`, `News`, `Resources`, `Review`ORDER BY `Article`.StartDate DESC, `Agimimnon`.StartDate DESC, `Bigstick`.StartDate DESC, `Guffaw`.StartDate DESC, `News`.StartDate DESC, `Resources`.StartDate DESC, `Review`.StartDate DESCLIMIT 10[/code] Link to comment https://forums.phpfreaks.com/topic/33527-multiple-tables-on-the-same-page/#findComment-159771 Share on other sites More sharing options...
trq Posted January 13, 2007 Share Posted January 13, 2007 Just use this....[code]"CALL Threbb_ContentUser.sp_GetFrontPage"[/code]as your query. It is calling a stored procedure from within the database. Link to comment https://forums.phpfreaks.com/topic/33527-multiple-tables-on-the-same-page/#findComment-159775 Share on other sites More sharing options...
offsprg01 Posted January 13, 2007 Author Share Posted January 13, 2007 The table '#sql_fa6a_10' is full this is the error i'm getting.that stored procedure is in the old mssql database. the new database is mysql. Link to comment https://forums.phpfreaks.com/topic/33527-multiple-tables-on-the-same-page/#findComment-159777 Share on other sites More sharing options...
offsprg01 Posted January 13, 2007 Author Share Posted January 13, 2007 sorry i should clarify i don't have an mssql manager so i can't get the stored procedure from that database, or alteast i don't know anyother way to. Link to comment https://forums.phpfreaks.com/topic/33527-multiple-tables-on-the-same-page/#findComment-159779 Share on other sites More sharing options...
offsprg01 Posted January 13, 2007 Author Share Posted January 13, 2007 ok i figured out how to get the procedue this is the MSSQL ProcedureCREATE PROCEDURE [sp_GetFrontPage] ASset rowcount 8( SELECT ID, Type, Title, AuthorName, StartDate, Blurb, Image FROM Article WHERE live = 1 and STARTDATE <= GETDATE() UNION SELECT ID, Type, Title, AuthorName, StartDate, Blurb, Image FROM Review WHERE live = 1 and STARTDATE <= GETDATE() UNION SELECT ID, Type, Title, AuthorName, StartDate, Blurb, Image FROM Agimimnon WHERE live = 1 and STARTDATE <= GETDATE() UNION SELECT ID, Type, Title, AuthorName, StartDate, Blurb, Thumb as Image FROM BigStick WHERE live = 1 and STARTDATE <= GETDATE() UNION SELECT ID, Type, Title, AuthorName, StartDate, Blurb, Thumb as Image FROM Guffaw WHERE live = 1 and STARTDATE <= GETDATE() UNION SELECT ResourceID as ID, Type, ResourceName as Title, ResourceAuthor as Author, ResourceDate as StartDate, ResourceDescription as Blurb, ResourceThumb as Image FROM Resources ) ORDER BY StartDate DESCnjow i just have to figure out how to convert the syntax to mysql. :-\ Link to comment https://forums.phpfreaks.com/topic/33527-multiple-tables-on-the-same-page/#findComment-159799 Share on other sites More sharing options...
offsprg01 Posted January 13, 2007 Author Share Posted January 13, 2007 well i got the bloody thing to work finally... sort of. :oi can get the results to show on the screen, but my images won't show. well inparticular the images hosted offsite will not show, and i don't quite know why.here's the new index page. as you can see no imageshttp://www.3rdedition.org/index.phphere's the pertinate code. the only time i have a problem with the images is when it is a direct url to the image offsite.[code]$contentLocation = 'this is empty in the actual code';<?php do { ?> <div style="float:left"><a href="agimimnon_entry.php?recordID=<?php echo $row_FrontPage['ID']; ?>"><?php echo $row_FrontPage['Title']; ?></a></div> <div style="float:right"><?php echo $row_FrontPage['StartDate']; ?></div><br /> <?php echo $row_FrontPage['AuthorName']; ?><br /> <div style="float:left"><div style="width:60%"><br /><br /><?php echo $row_FrontPage['Blurb']; ?></div></div> <div align="right"> <img src="<?php echo $contentLocation ?><?php echo $row_FrontPage['Image']; ?>" width="100" height="100"></div> <div align="center"> <img src="<?php echo $contentLocation ?>forum/templates/subSilver/images/dividing_line.gif"/> </div> <br /><?php } while ($row_FrontPage = mysql_fetch_assoc($FrontPage)); ?>[/code] Link to comment https://forums.phpfreaks.com/topic/33527-multiple-tables-on-the-same-page/#findComment-159820 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.