Associative array within foreach select query

Hi folks,

I’ve come across a problem with a foreach loop not behaving as I think it should, so I’m guessing that I’m doing something wrong!

Basically, I’m creating a query which outputs some report statistics. When building it, I just used actual values until I was sure the SQL and calculations were was working as they should. Here they are for reference.

Controller code:


//count of article tag Good For Quotes
$sqlTagGfq = "SELECT cutting.cutting_id, publication_date, GROUP_CONCAT(tag_name ORDER BY tag_name SEPARATOR '<br />&bull; ') AS tags
FROM cutting
INNER JOIN cutting_tag ON cutting_tag.cutting_id = cutting.cutting_id
INNER JOIN tag ON tag.tag_id = cutting_tag.tag_id
WHERE publication_date BETWEEN '$pub_first' AND '$pub_last'
AND tag_name = 'Good for quotes'
GROUP BY cutting_tag.cutting_id
ORDER BY publication_date DESC";

$resultTagGfq = mysqli_query($link, $sqlTagGfq);
$tagGfq = mysqli_num_rows($resultTagGfq);
$tagGfqPcTotal = (($tagGfq / $resultTotal) * 100);

This controller code was then used in my reports page:

<p>
<?php echo $tagGfq;?> cutting(s) were tagged with 'Good for quotes'. (<?php echo number_format($tagGfqPcTotal, 2);?>%)<br>
</p>

Which gave the output on the reports page of:
3 cutting(s) were tagged with ‘Good for quotes’. (30.00%)

That’s all working perfectly, and I’m quite happy with it.

So, since there are lots of tags (and new tags can be added at any point by the users of the database), I decided a foreach loop was the way to go to automatically do this set of querying and calculations for each one without having to duplicate my code over and over…

So I used some code which I use elsewhere in the app to create a list of all the tags:

$sql = "SELECT tag_id, tag_name FROM tag GROUP BY tag_name";
$result = mysqli_query($link, $sql);
if (!$result)
	{
		$error = 'Error fetching list of article tags. ' . mysqli_error($link);
		include $_SERVER['DOCUMENT_ROOT'] . '/includes/error.html.php';
		exit();
	}
	
while ($row = mysqli_fetch_array($result))
	{
		$tags[] = array(
				'tag_id' => $row['tag_id'],
				'tag_name' => $row['tag_name'],
				'selected' => FALSE);		
	}

and then added a foreach loop:

foreach($tags as $tag):
$tagName = $tag['tag_name'];
$sqlTag = "SELECT cutting.cutting_id, publication_date, GROUP_CONCAT(tag_name ORDER BY tag_name SEPARATOR '<br />&bull; ') AS tags
FROM cutting
INNER JOIN cutting_tag ON cutting_tag.cutting_id = cutting.cutting_id
INNER JOIN tag ON tag.tag_id = cutting_tag.tag_id
WHERE publication_date BETWEEN '$pub_first' AND '$pub_last'
AND tag_name = '$tagName'
GROUP BY cutting_tag.cutting_id
ORDER BY publication_date DESC";

$resultTag = mysqli_query($link, $sqlTag);
$tagNumber = mysqli_num_rows($resultTag);
$tagPcTotal = (($tagNumber / $resultTotal) * 100);
endforeach;

which outputs to:

<p>
<?php foreach($tags as $tag):
	echo $tagNumber;?> cutting(s) were tagged with '<?php htmlout($tag['tag_name']); ?>' "<?php htmlout($tagName); ?>". (<?php echo number_format($tagPcTotal, 2);?>%)<br>
<?php endforeach;?>
</p> 

My original idea had been to use

AND tag_name = '$tag[\\'tag_name\\']'

as I know that $tag[‘tag_name’] gives me the exact value that I need to be placed in the query at that point but you apparently can’t place an associative array within a select query, which is why I created the $tagName variable ($tagName = $tag[‘tag_name’]; ) prior to the query.

I thought this would do the trick, but despite being within the foreach loop, the $tagName variable always returns the value of the last value in the list. I tested this by including “<?php htmlout($tagName); ?>” to appear directly after ‘<?php htmlout($tag[‘tag_name’]); ?>’ the output. - and when I echo $sqlTag, I can see that the last value is always the one which is substituted in.

Since $tagName = $tag[‘tag_name’] I would have thought that the output of <?php htmlout($tagName); ?> and <?php htmlout($tag[‘tag_name’]); ?> should have been exactly the same, but their not, the output on the reports page that I get is:

1 cutting(s) were tagged with ‘Good for quotes’ “Young People”. (10.00%)
1 cutting(s) were tagged with ‘New Chief Executive’ “Young People”. (10.00%)
1 cutting(s) were tagged with ‘New Home Project’ “Young People”. (10.00%)
1 cutting(s) were tagged with ‘Political Coverage’ “Young People”. (10.00%)
1 cutting(s) were tagged with ‘Season 10:11’ “Young People”. (10.00%)
1 cutting(s) were tagged with ‘Season 11:12’ “Young People”. (10.00%)
1 cutting(s) were tagged with ‘Season 12:13’ “Young People”. (10.00%)
1 cutting(s) were tagged with ‘Sponsorship’ “Young People”. (10.00%)
1 cutting(s) were tagged with ‘Total’ “Young People”. (10.00%)
1 cutting(s) were tagged with ‘Young People’ “Young People”. (10.00%)

So my question is, can anyone tell me why the $tagName variable is always giving me the last value? OR, is there any other way to include an associative array within a select statement.

Thanks in anticipation!
C :slight_smile:

I think it’s not only the $tagName variable that always shows the last value, but $tagNumber and $tagPcTotal as well.

It’s because they are two different foreach loops. So anything you calculate in the first foreach loop, if not stored in an array, will go lost. Finished the loop, all that remains are the values of the last looping.

So… let me see if i get your structure correct.

cutting_tag is a join table between tag and cutting.

cutting_tag has a unique constraint on (cutting_id,tag_id) [IE: One Clipping can only have one entry for a given tag]

You want to figure out how many cuttings are tagged with each tag.

Why not…
SELECT tag.tag_name, COUNT(tag_name) FROM tag LEFT JOIN cuttings_tag ON tag.id = cuttings_tag.tag_id GROUP BY tag_id;

?

You’ve already got the total number of clippings ($tagGfq), so use that for your percentages; the COUNT of tags for a given tag_id divided by the number of clippings = the percentage of clippings with that tag.

Hey StarLion!

Sorry, I should have given detail about the database too!

There are three tables (out of nine) which are relevant here.
cutting (which contains - amongst many others - fields for cutting_id)
tag (which lists all possible tag_id and tag_name)

There’s a many to one relationship between tags (many) and cuttings (one) so the third table cutting_tag is the lookup table that creates the primary key (cutting_id, tag_id)

I just tried the SQL you suggested, but it’s giving me completely the wrong results for the count… v odd.

Cheers,
C

Hi guido2004!

Thanks, in my blinkered state of “these two foreach loops are related therefore they are the same!” state of mind, I hadn’t considered that the two foreach loops would be behaving independently like that… Blinded by the obvious once again!

Guess I’ll have to go back to the drawing board…!

Thanks,

C

Hey StarLion!

My bad! Just looked into the back end of the database and realised there was some redundant test data still lying in the cutting_tag table, so your query is in fact perfect! Apologies for doubting your code would be perfect as always!

I’ll now head away and try to get the results of the query appearing in my results page using this new method!

Thanks so much for your help!

C

Don’t forget to setup INDEXes in your tables, JOINs are bad bad bad without them.

Off Topic:

My queries are -not- always perfect. Just ask anyone in the MySQL forum.

Tee-hee!

Well they were last time you gave me a hand!

By the way, I’ve got it working perfecto now! :smiley:

This in the controller:

$sqlTag = "SELECT tag.tag_name, COUNT(tag_name) AS tag_count
FROM cutting 
INNER JOIN cutting_tag ON cutting_tag.cutting_id = cutting.cutting_id
INNER JOIN tag ON tag.tag_id = cutting_tag.tag_id
WHERE publication_date BETWEEN '$pub_first' AND '$pub_last'
GROUP BY tag.tag_id
ORDER BY tag_name";

$resultTag = mysqli_query($link, $sqlTag);
	
if (!$resultTag)
{
	$error = 'Error fetching list of article tags. ' . mysqli_error($link);
	include $_SERVER['DOCUMENT_ROOT'] . '/includes/error.html.php';
	exit();
}

while ($row = mysqli_fetch_array($resultTag))
{
	$tagDetails[] = array(
		'tag_name' => $row['tag_name'],
		'tag_count' => $row['tag_count']);
}

and this in the output:

<p>
<?php foreach ($tagDetails as $tagDetail):

echo $tagDetail['tag_count'];?> cutting(s) were assigned the tag: <?php echo $tagDetail['tag_name'];?>. (
<?php 
$tagPcTotal = (($tagDetail['tag_count'] / $resultTotal) * 100);
echo number_format($tagPcTotal, 2);?>
%)<br>

<?php endforeach; ?>
</p>

Had to change LEFT JOIN to INNER JOIN as it was giving me a COUNT value of 1 for tags that weren’t attached to any articles, but INNER JOIN doesn’t display the tag_name at all if COUNT = 0.

Thanks again for your help!

C :slight_smile:

See, told you they arnt perfect :stuck_out_tongue_winking_eye: