/    Sign up×
Community /Pin to ProfileBookmark

Help w/ nested query output?

Copy linkTweet thisAlerts:
Feb 01.2005

This is a little out of my league. I’ve had someone help me with the MySQL query — which does what I need it to. Can you guys please help me with the PHP for the nested output? Here’s the query:

select presspublishDate
, presscategoryName
, pressTitle
from press
inner
join presspublish
on press.pressPublish
= presspublish.presspublishID
inner
join presscategories
on press.pressCategory
= presscategories.presscategoryID
order
by presspublishDate
, presscategoryName
, pressTitle

Here’s what the tables look like:

presspublish
=======================


presspublishID* || presspublishDate
=======================

1 || 2005-01-30
2 || 2005-01-31

3 || 2005-02-01
=======================

presscategories
===========================


presscategoryID** || presscategoryName
===========================

1 || Auto
2 || Business

3 || Home
===========================

press
====================================


pressID || pressPublish* || pressCategory** || pressTitle
====================================

1 || 1 || 1 || Title #1
2 || 1 || 1 || Title #2
3 || 1 || 3 || Title #3

4 || 2 || 2 || Title #4
====================================

And here’s the output I need:

2005-01-30
Auto
1. Title #1
2. Title #2
Home
1. Title #3

2005-01-31
Business
1. Title #4

Any help will be greatly appreciated. Thank you so much!

to post a comment
PHP

7 Comments(s) ↴

Copy linkTweet thisAlerts:
@ShrineDesignsFeb 01.2005 — cake, i do a similar thing for my site[code=php]<?php
foreach($category as $parent)
{
// output parent category

if(/* current element IS NOT a parent category */)
{
foreach($category as $child)
{
if(/* current element is a child category or item */)
{
// output child category or item
}
}
}
// close any open HTML tags
}
?>[/code]
Copy linkTweet thisAlerts:
@msmith29063authorFeb 01.2005 — Looks good, but I'm going to need some help with the script. In the past, I've done queries inside queries (incorrect), so this is new to me. Any help will be greatly appreciated!
Copy linkTweet thisAlerts:
@msmith29063authorFeb 02.2005 — Can anyone help me with this script? Any help will be appreciated. Thank you so much!
Copy linkTweet thisAlerts:
@ShrineDesignsFeb 02.2005 — chill

whats is used for the "category", i assume it is presscategoryID?

what is used to check for "sub-categories" or "items", i assume it is pressCategory?

from the looks of your query you did not include these fields to be return in a result

example:[code=php]<?php
foreach($category as $parent)
{
// output parent category
echo "<p>" . $parent['presscategoryName'];

foreach($category as $child)
{
if($parent['presscategoryID'] == $child['pressCategory'])
{
// output child category or item
echo $child['pressTitle'] . "<br />n";
}
}
// close any open HTML tags
echo "</p>n";
}
?>[/code]
Copy linkTweet thisAlerts:
@msmith29063authorMar 02.2005 — I'm still having trouble. The query works, but I'm not familiar enough with using foreach and a MySQL array to make this output format correctly. Here's my revised query:

[code=php]// Build query to fetch items from database
$query = "SELECT * FROM press INNER JOIN presspublish ON press.pressPublish = presspublish.presspublishID INNER JOIN presscategories ON press.pressCategory = presscategories.presscategoryID WHERE presspublish=$presspublishID ORDER BY presscategoryName";

// Execute query
$result = @mysql_query($query);

// If query was okay AND we have at least 1 item...
if ($result && @mysql_num_rows($result) > 0) {

// Nested results here...

} else {

// Tell no items were found
print "No articles were found<br>n";
print "<br>n";
}[/code]

$presspublishID is passed to this page. I need the results formatted like this:

<bold>presscategoryName</bold> <-- from presscategories db

pressID/pressTitle/pressDate/pressPublication/pressContacted <-- from press db

pressID/pressTitle/pressDate/pressPublication/pressContacted <-- from press db

pressID/pressTitle/pressDate/pressPublication/pressContacted <-- from press db

<bold>presscategoryName</bold> <-- from presscategories db

pressID/pressTitle/pressDate/pressPublication/pressContacted <-- from press db

pressID/pressTitle/pressDate/pressPublication/pressContacted <-- from press db

Etc...

Any help would be greatly appreciated. Thank you!
Copy linkTweet thisAlerts:
@msmith29063authorMar 02.2005 — Here's my improved database query:

[code=php]$query2 = "SELECT presscategories.presscategoryName, press.pressID, press.pressTitle, press.pressDate, press.pressPublication, press.pressURL, press.pressContacted FROM press INNER JOIN presscategories ON press.pressCategory = presscategories.presscategoryID WHERE press.pressPublish=$presspublishID ORDER BY presscategories.presscategoryName";[/code]

Again, the issue is listing results by presscategoryName:

presscategoryName #1
===============


pressID, etc. (#1)

pressID, etc. (#2)

presscategoryName #2
===============


pressID, etc. (#3)

...

Thank you for any help!
Copy linkTweet thisAlerts:
@NogDogMar 02.2005 — I think something like this would work for you:
[code=php]
<?php
$query2 = <<<EOD
SELECT presscategories.presscategoryName, press.pressID, press.pressTitle,
press.pressDate, press.pressPublication, press.pressURL,
press.pressContacted
FROM press INNER JOIN presscategories
ON press.pressCategory = presscategories.presscategoryID
WHERE press.pressPublish=$presspublishID
ORDER BY presscategories.presscategoryName
EOD;

$result = mysql_query($query2);
$header = ""; # keep track of last header (row[0]) used
$first = TRUE; # indicate we're doing the first one, so don't need </p>
while($row = mysql_fetch_array($result))
{
if($row[0] != $header) # new header, so start new paragraph and show header
{
$header = $row[0];
if($first) # first time, don't do </p>
{
$first = FALSE;
}
else
{
echo "</p>n";
}
echo "<strong>$header</strong>";
}
echo "<br>n";
for($ix=1; $ix<count($row); $ix++); # display rest of row data
{
echo $row[$ix];
if($ix != count($row) - 1)
{
echo "/"; # add a slash unless last item in row
}
}
}
if(mysql_num_rows($result) >= 1)
{
echo "</p>n"; # if we found any rows, then we need a terminal </p>
}
?>
[/code]
×

Success!

Help @msmith29063 spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.17,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...