Add additional element (tree?) to php array

I have a php array (see CODE1) that I populate from MYSQL.
As you can see it's a simple list of many webpages.

Now my problem is that attached to EACH of these webpages I have a range of tags. I would like to add this to the newarray as a seperate element? called tags? as well but not sure how to yet. I want to sub-loop through that part on my page.
The SQL Query for example is in CODE 2 - where tag.webpage_id is naturally referenced to webpage.id from the webpage table.

What would be the best way to accomplish this? - how do I loop properly through the webpages and get the associated tags in the same array of the webpage.

Thanks!
CODE 1:
mysql_select_db($database_cn, $cn);
$query_rsWebPages = "SELECT webpage.id, webpage.title FROM webpages";
$rsWebPages = mysql_query($query_rsWebPages, $cn) or die(mysql_error());
$totalRows_rsWebPages = mysql_num_rows($rsWebPages);
	
$newarray = array();
while($row=mysql_fetch_assoc($rsWebPages)) {array_push($newarray, $row);} 
 
 
CODE 2:
SELECT tag.id, tag.webpage_id, tag.name FROM tags

Open in new window

lanac222Asked:
Who is Participating?
 
tg_wilkCommented:
Would something like that below work? You'd have two concatenated fields - one with tag id's and the other with matching tag names.
SELECT webpage.id as wid, webpage.title as wt, 
GROUP_CONCAT(tag.name ORDER BY tag.id ASC) as tag_names,
GROUP_CONCAT(tag.id ORDER BY tag.id ASC) as tag_ids
FROM webpage 
LEFT JOIN tag ON tag.webpage_id = webpage.id
GROUP BY webpage.id

Open in new window

0
 
tg_wilkCommented:
I didn't test the code, so it may have some bugs in it.

The general idea is as follows. Modified query will return rows of webpage id, title and one of the associated tags. When the page has many tags, webpage id and title will exist more then once as well. To prevent duplicate webpages I modified $newarray a little. Now webpage id is a key in that table. After fetching the row, I check whether the key of this id exists in an array (which would mean, that it was created while reading some previous rows) and if so, just add another tag. If the key doesn't exist - create it with the title and a tag (or empty array if webpage doesn't have any tags).
Left join means that even if the page doesn't have any tags, it will still occur in the list.
<?php
mysql_select_db($database_cn, $cn);
$query_rsWebPages = "SELECT webpage.id as wid, webpage.title as wt, tag.name as tn FROM webpage LEFT JOIN tag ON tag.webpage_id = webpage.id";
$rsWebPages = mysql_query($query_rsWebPages, $cn) or die(mysql_error());
$totalRows_rsWebPages = mysql_num_rows($rsWebPages);
        
$newarray = array();
while($row=mysql_fetch_assoc($rsWebPages)) 
{
	if (isset($newarray[$row['wid']])) $newarray[$row['wid']]['tags'][]=$row['tn'];
	else
		$newarray[$row['wid']] = array('title'=>$row['wt'],'tags'=>(count($row['tn'])>0)?array($row['tn']):array());
}
?>

Open in new window

0
 
lanac222Author Commented:
Thanks for your response, however that will result in multiple records with the same data (using a left join), I'd rather do a seperate query to gather all the tags associated to the webpage_id
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
tg_wilkCommented:
Oh, so maybe GROUP_CONCAT will be more to your liking. I don't know, if it will work with webpage.title though - you have to check.
In theory it should return a list of tag names separated by coma.
SELECT webpage.id as wid, webpage.title as wt, GROUP_CONCAT(tag.name) as tags 
FROM webpage 
LEFT JOIN tag ON tag.webpage_id = webpage.id
GROUP BY webpage.id

Open in new window

0
 
lanac222Author Commented:
No, per my question I want to add it to the existing array. - search the array, add it to the specific webpage_id.

Using the SQL solution does not work for me since it would not give me the flexibility that i need.
For example the tags will need to be linked using <a href in the html code, and this will be different formatting for various websites.....some using a ul list other doing a <p> etc. that's why I just need the raw data in the array.

Thanks.
0
 
lanac222Author Commented:
Actually, my previous post was wrong. It looks like you are on the right track with the GROUP_CONCAT.
The only think I need back properly is in the format of for example:
TAG.ID => TAG.NAME, TAG.ID => TAG.NAME.

So I would be able to explode it in php and get the values properly.

Thanks!

0
 
lanac222Author Commented:
Thanks, I managed to get it working based on the GROUP_CONCAT, something new for me which resolved the issue somewhat - at least it's working the way I want it now :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.