DaniWeb Forum Index > Web Development > PHP

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

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Sep 2008

Posts: 26

Reputation: loligator is an unknown quantity at this point

Solved Threads: 1

loligator

Offline

Light Poster

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

Sep 11th, 2008

I'm pretty much a newb when it comes to PHP and MYSQL (taught myself, so I make a lot of mistakes

) So here's the code...

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
        $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>";
        $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.

•

Join Date: Aug 2008

Posts: 84

Reputation: MVied is an unknown quantity at this point

Solved Threads: 5

MVied

Offline

Junior Poster in Training

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

Sep 11th, 2008

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

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
	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>";
	}
	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>";
	}

•

Join Date: Sep 2008

Posts: 26

Reputation: loligator is an unknown quantity at this point

Solved Threads: 1

loligator

Offline

Light Poster

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

Sep 11th, 2008

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

•

Join Date: Aug 2008

Posts: 84

Reputation: MVied is an unknown quantity at this point

Solved Threads: 5

MVied

Offline

Junior Poster in Training

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

Sep 11th, 2008

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

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
	$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";
	$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";

Last edited by MVied; Sep 11th, 2008 at 3:44 pm.

•

Join Date: Sep 2008

Posts: 26

Reputation: loligator is an unknown quantity at this point

Solved Threads: 1

loligator

Offline

Light Poster

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

Sep 11th, 2008

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.

•

Join Date: Aug 2008

Posts: 84

Reputation: MVied is an unknown quantity at this point

Solved Threads: 5

MVied

Offline

Junior Poster in Training

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

Sep 12th, 2008

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.

Last edited by MVied; Sep 12th, 2008 at 12:18 am.

•

Join Date: Sep 2008

Posts: 26

Reputation: loligator is an unknown quantity at this point

Solved Threads: 1

loligator

Offline

Light Poster

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

Sep 12th, 2008

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...