Multiple tables on the same page.

[offsprg01]

??? ??? ???
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.

[trq]

Sql is the same in php or asp, just use the same queries.

[offsprg01]

[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]

[offsprg01]

so i'm thinking i need something like the following

SELECT * 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?

[offsprg01]

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.  ???

[offsprg01]

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

[play_]

SELECT * FROM Agimimnon, Article, Review, News, Resource, BigStick, Guffaw, ORDER BY SubDate DESC LIMIT 10

try taking out the  ',' after Guffaw

[offsprg01]

yup lol i caught that right after i posted.  ::)

[offsprg01]

ok new sql error

#1052 - Column: 'StartDate' in order clause is ambiguous

is this becuase every table has a start date?

[offsprg01]

http://www.codingforums.com/showthread.php?t=46214

i this is what i'm needing to do. but as i am an sql retard can some one please explain?

[play_]

[quote author=offsprg01 link=topic=121710.msg503244#msg503244 date=1168620482]
http://www.codingforums.com/showthread.php?t=46214

i 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 :)

[offsprg01]

i know it does but i can't seem to get the syntax down. :(

[offsprg01]

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`.ResourceName
FROM
  `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 DESC
LIMIT 10[/code]

[trq]

Just use this....

[code]
"CALL Threbb_ContentUser.sp_GetFrontPage"
[/code]

as your query. It is calling a stored procedure from within the database.

[offsprg01]

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.

[offsprg01]

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.

[offsprg01]

ok i figured out how to get the procedue this is the MSSQL Procedure

CREATE PROCEDURE [sp_GetFrontPage] AS
set 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 DESC

njow i just have to figure out how to convert the syntax to mysql.  :-\

[offsprg01]

well i got the bloody thing to work finally... sort of. :o

i 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 images

http://www.3rdedition.org/index.php


here'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]