Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Add additional element (tree?) to php array

Posted on 2009-02-14
7
Medium Priority
?
511 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

0
Comment
Question by:lanac222
  • 4
  • 3
7 Comments
 
LVL 7

Expert Comment

by:tg_wilk
ID: 23641517
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
 

Author Comment

by:lanac222
ID: 23642205
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
 
LVL 7

Expert Comment

by:tg_wilk
ID: 23642842
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:lanac222
ID: 23643489
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
 

Author Comment

by:lanac222
ID: 23644890
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
 
LVL 7

Accepted Solution

by:
tg_wilk earned 753 total points
ID: 23645316
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
 

Author Closing Comment

by:lanac222
ID: 31546914
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question