Unique Problem

[dagger2002]

Here is my problem. I have written a php script that reads from a database, mysql. Then it counts all of the records and prints them to the screen and to a flat file. I have it working but it prints the same file the number of times it is in the db.

if i have 5 records for jim it prints this:

jim 5
jim 5
jim 5
jim 5
jim 5

when all i want is this

jim 5

here is my query set:

//	Selects a Page
	$queryBody = "SELECT * FROM `temp_stats`_uniq WHERE `stat_page` NOT LIKE '/BoE/index_BoE.php?' ORDER BY stat_page ASC";
	$resBody = mysql_query($queryBody) or die/*(mysql_error());//*/("Error with the Body.");
	
	$rowBody = mysql_fetch_array($resBody);
	$numBody = mysql_num_rows($resBody);

//	Count Individual Pages
	$queryBody2 = "SELECT * FROM `temp_stats`_uniq WHERE `stat_page` LIKE '$rowBody[stat_page]' ORDER BY stat_title DESC";
	$resBody2 = mysql_query($queryBody2) or die(mysql_error());//*/("Error with the Body2.");
	
	$rowBody2 = mysql_fetch_array($resBody2);
	$numBody2 = mysql_num_rows($resBody2);

while($rowBody = mysql_fetch_array($resBody)){
//	Count Individual Pages
	$queryBody2 = "SELECT * FROM `temp_stats` WHERE `stat_page` LIKE '$rowBody[stat_page]' ORDER BY stat_title DESC";
	$resBody2 = mysql_query($queryBody2) or die(mysql_error());//*/("Error with the Body2.");
	
	$rowBody2 = mysql_fetch_array($resBody2);
	$numBody2 = mysql_num_rows($resBody2);

any ideas?

 

[bdichiara]

SELECT DISTINCT

_______________
_brian.

 

[dagger2002]

thank you

 

[dagger2002]

that didn't work it still shows the same thing

 

[dagger2002]

jim 5
jim 5
jim 5
jim 5
jim 5

any other ideas?

 

[feherke]

Hi

You select [tt]*[/tt], so maybe there is another field in that table, which value differs in those five records. So try selecting only the relevant fields.

Feherke.

http://rootshell.be/~feherke/

 

[cLFlaVA]

i agree with feherke - if the name is the only data you're after, you should only select the name from the table - not every column/field.



*cLFlaVA
----------------------------
[tt]"quote goes here"[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[dagger2002]

that is all the fields in the db. that is y i am using *

 

[feherke]

Hi

Is not a good practice to use [tt]*[/tt] anyway.

The only reason I can think too, is padding. Try to [tt]select[/tt] [tt]'['||field_with_jim||']'[/tt] to see if fields are actually padded. If yes, then [tt]trim()[/tt] them.

Feherke.

http://rootshell.be/~feherke/

 

[dagger2002]

What do u mean by padded.
I am a newbie to allmost all of mysql. Ive setup simple stuff like add records edit and that stuff. but have never really used it other then that.

Here is the table

stat_id   	int(4)  			Not Null		auto_increment
stat_date  	varchar(10) 		Not Null  	  	  	
stat_time  	varchar(8) 			Not Null
stat_ip  	varchar(15) 		Not Null
stat_page  	text 	  			Not Null
stat_title  varchar(255) 		Not Null

 

[feherke]

Hi

What do u mean by padded.

With leading and/or trailing whitespace characters. But never mind, you would already observed leading spaces, and MySQL trims trailing spaces.

But something is not clear. On 2 Nov 06 16:57 you wrote that "that is all the fields in the db", but now you post a table structure with six fields. This is abit contradictory.

So what do you have ? Please post some clear data.

Feherke.

http://rootshell.be/~feherke/

 

[dagger2002]

here is the new code.

<?php

//	Selects Index Page Hits
	$queryIndex = "SELECT stat_page, stat_title FROM `temp_stats` WHERE `stat_page` LIKE '/BoE/index_BoE.php?' ORDER BY stat_title DESC";
	$resIndex = mysql_query($queryIndex) or die/*(mysql_error());//*/("Error with the Index.");
	
	$rowIndex = mysql_fetch_array($resIndex);
	$numIndex = mysql_num_rows($resIndex);

//	Sets $ index for Print
	$index = "<tr><td align='left' nowrap>" . $rowIndex[stat_title] . "</td><td align='right'>" . $numIndex . "</td></tr>\n"; 
	
?>

<?php

//	Selects a Page
	$queryBody = "SELECT stat_page, stat_title FROM `temp_stats`_uniq WHERE `stat_page` NOT LIKE '/BoE/index_BoE.php?' ORDER BY stat_page ASC";
	$resBody = mysql_query($queryBody) or die/*(mysql_error());//*/("Error with the Body.");
	
	$rowBody = mysql_fetch_array($resBody);
	$numBody = mysql_num_rows($resBody);

//	Count Individual Pages
	$queryBody2 = "SELECT DISTINCT stat_page, stat_title FROM `temp_stats`_uniq WHERE `stat_page` LIKE '$rowBody[stat_page]' ORDER BY stat_title DESC";
	$resBody2 = mysql_query($queryBody2) or die(mysql_error());//*/("Error with the Body2.");
	
	$rowBody2 = mysql_fetch_array($resBody2);
	$numBody2 = mysql_num_rows($resBody2);
	
	$body = "<tr><td align='left' nowrap>" . $rowBody2[stat_page] . "</td><td align='right'>" . $numBody2 . "</td></tr>\n"; 

while($rowBody = mysql_fetch_array($resBody)){
//	Count Individual Pages
	$queryBody2 = "SELECT DISTINCT stat_page, stat_title FROM `temp_stats` WHERE `stat_page` LIKE '$rowBody[stat_page]' ORDER BY stat_title DESC";
	$resBody2 = mysql_query($queryBody2) or die(mysql_error());//*/("Error with the Body2.");
	
	$rowBody2 = mysql_fetch_array($resBody2);
	$numBody2 = mysql_num_rows($resBody2);
	
	$body .= "<tr><td align='left' nowrap>" . $rowBody2[stat_page] . "</td><td align='right'>" . $numBody2 . "</td></tr>\n"; 
}

?>

it has totally broke it

 

[feherke]

Hi

Please post some clear data.

We already saw your code. Now please post some clear data.

And would be useful to post only the problematic MySQL code, to help us helping you. Is easier to read, and note that not everybody knows PHP.

Feherke.

http://rootshell.be/~feherke/

 

[dagger2002]

here is the new code.

CODE

<?php

// Selects Index Page Hits
$queryIndex = "SELECT stat_page, stat_title FROM `temp_stats` WHERE `stat_page` LIKE '/BoE/index_BoE.php?' ORDER BY stat_title DESC";
$resIndex = mysql_query($queryIndex) or die/*(mysql_error());//*/("Error with the Index.");

$rowIndex = mysql_fetch_array($resIndex);
$numIndex = mysql_num_rows($resIndex);

// Sets $ index for Print
$index = "<tr><td align='left' nowrap>" . $rowIndex[stat_title] . "</td><td align='right'>" . $numIndex . "</td></tr>\n";

?>

<?php

// Selects a Page
$queryBody = "SELECT stat_page, stat_title FROM `temp_stats`_uniq WHERE `stat_page` NOT LIKE '/BoE/index_BoE.php?' ORDER BY stat_page ASC";
$resBody = mysql_query($queryBody) or die/*(mysql_error());//*/("Error with the Body.");

$rowBody = mysql_fetch_array($resBody);
$numBody = mysql_num_rows($resBody);

// Count Individual Pages
$queryBody2 = "SELECT DISTINCT stat_page, stat_title FROM `temp_stats`_uniq WHERE `stat_page` LIKE '$rowBody[stat_page]' ORDER BY stat_title DESC";
$resBody2 = mysql_query($queryBody2) or die(mysql_error());//*/("Error with the Body2.");

$rowBody2 = mysql_fetch_array($resBody2);
$numBody2 = mysql_num_rows($resBody2);

$body = "<tr><td align='left' nowrap>" . $rowBody2[stat_page] . "</td><td align='right'>" . $numBody2 . "</td></tr>\n";

while($rowBody = mysql_fetch_array($resBody)){
// Count Individual Pages
$queryBody2 = "SELECT DISTINCT stat_page, stat_title FROM `temp_stats` WHERE `stat_page` LIKE '$rowBody[stat_page]' ORDER BY stat_title DESC";
$resBody2 = mysql_query($queryBody2) or die(mysql_error());//*/("Error with the Body2.");

$rowBody2 = mysql_fetch_array($resBody2);
$numBody2 = mysql_num_rows($resBody2);

$body .= "<tr><td align='left' nowrap>" . $rowBody2[stat_page] . "</td><td align='right'>" . $numBody2 . "</td></tr>\n";
}

?>

This is the problematic mySQL code.

this is a webstats program.

So the data is ip address and page paths. the title of the page is pulled from a variable from the page. dtae and time is when the page was hit.

so a record looks like this

1 2006-11-03 12:00pm 192.168.1.1 /index.php Home Page
2 2006-11-03 01:37pm 192.168.1.74 /index.php Home Page

 

[dagger2002]

here are the actual query statements

$queryBody = "SELECT stat_page, stat_title FROM `temp_stats`_uniq WHERE `stat_page` NOT LIKE '/BoE/index_BoE.php?' ORDER BY stat_page ASC";

$queryBody2 = "SELECT DISTINCT stat_page, stat_title FROM `temp_stats`_uniq WHERE `stat_page` LIKE '$rowBody[stat_page]' ORDER BY stat_title DESC";

 

[dagger2002]

ok i have tried something and broke it more. can any 1 help me.

here is what i was doing with my logic.

create temp stats table
populate table with data excluding 1 of our ip address

create a 2nd temp stats table
populate table with data excluding the other 1 of our ip address

drop first temp table
rename 2nd 2 first

That is what I have done so far in this thread.

Now I was thinking since none of the distinct stuff has worked creating a 3rd temp table with just name and count. Then selecting the distinct records since it won't be searching for any thing. but it isn't working any thoughts on how i can do this proccess.

Thanks so much in advanced