We help IT Professionals succeed at work.

Add additional element (tree?) to php array

lanac222
lanac222 asked
on
Medium Priority
542 Views
Last Modified: 2013-12-12
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

Comment
Watch Question

Commented:
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

Author

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

Commented:
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

Author

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.

Author

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!

Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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 :)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.