help with displaying two postgres queries in one table

gmac63

Gotta (I think) weird situation. I am coding a web app in PHP (not a newbie) and am querying two tables in a database, each with different info and result set. With my other queries within the app, I present the result rows (5) at a time. There is a "Next" and "Previous" to traverse the results up or down, (5) at a time. No problem.

This one is different. Within a table of I want to show the results of first query first (before the second), (5) at a time. Then show the second query (5) at a time. All within the same table stucture using the "Next" and "Previous".

I have been using:

<determine the number of rows in the query and make that your maximum number of rows to show>
for($i=$start;$i<end;$i++){
$row = pg_fetch_array($result,$i,$PG_ASSOC);
$line = $row["ContentID"];
...
<print lines containing result set info in some fashion>
...
}
<make sure $end does not exceed the total number of rows>

Where $start is "0" or "$end + 5" (depending on if $end incremented or not) and $end is "$start + 5".

My problem is, how do I start showing lines from the second query in the same table only after the first query lines have been shown? ie (sorry for the bad example):

(assume results 1 - 15 have been shown already)
Next - Previous
query1 result 16
query 1 result 17 <- last of first query results
query 2 result 1 <- first of second query results
query 2 result 2
query 2 result 3

The next set of course would start with query 2 result 4 and end with result 8.

How would I be able to do this? Any code examples?

TIA

-Wes Yates

ultraslacker

Use UNION and a sensible ORDER BY.

gmac63

... Thanks though.

Yes, I had thought of that, except the data types and keys are different. I did manage to get an example of the returned arrays from print_r():

QUERY 1:
Array (
[CategoryID] => 32
[categoryDescr] => Array (
[0] => English
[1] => Music )
[categoryParent] => )

QUERY 2:
Array (
[ContentID] => 2
[contentTitle] => World Playground: Starter Kit
[contentDescr] => Includes the same contents as the curriculum kit but only contains two passport journals.
[contentLength] => 00:20:00
[contentFileName] =>
[contentYear] => 1997
[contentTypeDescr] => DVD
[contentTypeIcon] => dvd.gif
[ContentTypeID] => 3 )

Yes, a UNION would work if both queries queried tables with the same datatypes in the same order (?).

I did try a array_merge_recursive() and this is what I got:

Array (
[0] => 32 # Query one
[1] => English
[2] =>
[3] => 2 # Query two
[4] => World Playground: Starter Kit
[5] => Includes the same contents as the curriculum kit but only contains two passport journals.
[6] => 00:20:00
[7] =>
[8] => 1997
[9] => DVD
[10] => dvd.gif
[11] => 3 [12] => Music )

Having slept on the subject, I wonder if I can "concatenate" the two results something like:

Query one

$result = pg_exec("SELECT blah, blah, blah... FROM table1...");

#Query two
$result = pg_exec( "SELECT blah, blah, blah... FROM table2...");

Then return the rows in some fashion:

for($i=$start;$i<$end;$i++){
$row = pg_fetch_array($result,$i,PG_ASSOC);

First Query results

$CategoryID = $row["CategoryID"];
...

Second query results

$ContentID = $row["ContentID"];
...
<format the info into the table rows>
}

Question is, does anyone think that using $result in both queries allow the second query results to append to the first query results (being $result should be in esscense an ARRAY to begin with), or would the second query results overwrite the first?

I'll experiment too. But if anyone has any experience.... HELP!

Thanks again!

-Wes Yates

ultraslacker

Oh, I thought this was a database question but now I see that it is a PHP question. The two queries have little in common, although as category and content I thought they would, and so since it is a php issue, use two queries, keep the arrays separate, and just loop through the results. Presentation worries have a way of muddling the sql and making things more involved than they need to be.

Yes, a UNION would work if both queries queried tables with the same datatypes in the same order (?).

Just FYI, you can manage a UNION by casting the fields and adding missing columns. You can even allow for the correct order of the columns in each query, resulting in the row order indicated in the first post, but UNIONs are pointless if the respective table results need to be kept separate.

Question is, does anyone think that using $result in both queries allow the second query results to append to the first query results (being $result should be in esscense an ARRAY to begin with), or would the second query results overwrite the first?

Overwrites...keep it simple and keep the resultsets separate.

gmac63

Oh, I thought this was a database question but now I see that it is a PHP question.

Actually sorta both, so you weren't far off at all.

Just FYI, you can manage a UNION by casting the fields and adding missing columns.

Casting eh? OK, I see your point.

I also toyed with creating a view then doing a simple query against that. If that doesn't work, I try casting. Graci!!

-Wes Yates