This php code here isn't working... I think it needs to be converted to SQL

[loligator]

I'm pretty much a newb when it comes to PHP and MYSQL (taught myself, so I make a lot of mistakes :confused: ) So here's the code...

$query = "SELECT DISTINCT(t_tag) FROM tags ORDER BY t_tag desc LIMIT 50";
	$results = mysql_query($query) or die(mysql_error());
	
	while ($row = mysql_fetch_array($results)) {
		extract($row);
		
		$tag[$x] = $t_tag;
		$x++;	
	}
	
	echo "<table border=1>";
	for ($i=1; $i<=$x; $i++) {
		$total_tag_usage = mysql_result(mysql_query("SELECT COUNT(t_tag) AS NUM FROM tags WHERE t_tag = '$tag[$i]'"),0);
		echo "<tr><td>" .$t_tag[$i]. "</td><td>" .$total_tag_usage. "</td></tr>";
	}
	echo "</table>";

What I want to accomplish is pull each unique tag from the database (from the tags table, in a column named t_tag), and place the number of times that tag has been used next to it in a table. As of now, the code above looks as if it's spitting out random numbers and it breaks up each tag letter by letter into a cell in the table. For example, if I have the tag "Apple", it's broken up into 5 different cells on the table, one for each letter with the neighboring cell in the same row displaying a random number. I think what I'm trying to do can be done with a single SQL statement, unfortunately, I was unable to write one that works. I figured since this was php code, I should post it in the php forum. Sorry if it should have gone in the SQL forum.

[MVied]

I know there's probably a better way, but this is the first thing that comes to mind.

for ($i=1; $i<=$x; $i++) {
		$sql = mysql_query("SELECT t_tag FROM tags WHERE t_tag = '".$tag[$i]."';");
		$result = mysql_result($sql) or die (mysql_error());
		$total_tag_usage = mysql_num_rows($sql);
		echo "<tr><td>" .$t_tag[$i]. "</td><td>" .$total_tag_usage. "</td></tr>";
	}

[loligator]

I gave that a try and the same thing still happens. The results don't appear like they should. Thanks though for trying!

[MVied]

Then the problem is with whatever you were trying to do before the piece that I fixed. Try this.

$sql = "SELECT DISTINCT(t_tag) FROM tags ORDER BY t_tag desc LIMIT 50";
	$results = mysql_query($sql) or die(mysql_error());
	
	echo "<table border=\"1\">\n";
	while ($row = mysql_fetch_array($results)) {
		$sql = mysql_query("SELECT t_tag FROM tags WHERE t_tag = '".$row['t_tag']."';");
		$result = mysql_result($sql) or die (mysql_error());
		$total_tag_usage = mysql_num_rows($sql);
		echo "<tr><td>" .$row['t_tag']. "</td><td>" .$total_tag_usage. "</td></tr>\n";
	}
	echo "</table>\n\n";

[loligator]

that works beautifully! do you know why what i had wasn't working? it worked in my head... also, i know \n creates a new line, but why is it necessary to put that after each html tag at the end of each echo statement? is that just to keep the page cleaner? sorry for all the questions, i'm just trying to learn from my mistakes.

[MVied]

Honestly, it's hard for me to say what's wrong with it because I've never actually come in contact with the extract function. For one thing you didn't declare $x as 0 before your loop, so that would cause problems the first time it is called.

I've noticed that a lot of developers don't consider the output of the scripts they write, but I always make sure that my code's output is very clean. All of the scripts I write come out in perfectly indented and semantically correct code. So, out of habit, I cleaned yours up a bit. Not to the full extent that I would on my own code, though.

[loligator]

To be honest, I'm not really sure why I use the extract function... the book I picked up that I taught myself PHP/MySQL from used it in every example, so it just stuck with me. As for not declaring x, I did so in the header of the page, I just didn't put it in the code here. Thanks so much for helping me out, I was completely befuddled. Looks like it's time to find out what exactly what the extract function does...

[MVied]

I looked it up, but I still don't see its use, lol.