MySQL Not Returning the Right Results

[Tree]

I have this code:

dbConnect();
$query = mysql_query("SELECT * FROM `visitor`");
$result = mysql_fetch_array($query);
$siteStats = array("tot_page" => count($result));

There should be something like 10,000 returned, but instead it only reports 22. Any idea why?

 

[neolinux]

22 ?

 

[Tree]

Yes, 22. When I do a SELECT DISTINCT query later in the script, it only returns 2, where it should return many more.

I just emptied the table, and both queries (SELECT, SELECT DISTINCT) return 1.

 

[Kate]

Why don't you use mysql_num_rows ?

 

[Tree]

Because I'm dumb :p. Let me try it and see if it works.

It works, thanks sdsinc! If I had NP$, I'd give you some.

Anyone know why my first code wasn't working?

 

[iNod]

Weird.. That actually should work.. But for some reason it isn't.. I suspect there is another problem somewhere causing it only to extract 22 rows..

Hrm.. I shall investigate..
- Steve

 

[Tree]

I have another problem. Completely unrelated to the original thread, but I didn't think starting another thread would be a good idea.

I have this code:

$query = mysql_query("SELECT * FROM `visitor`");
while ($result1 = mysql_fetch_assoc($query))
{
?>
	<tr>
		<td>
			<?$timestamp = substr("$result1[v_created]",0,10);
			$y = substr("$result1[v_created]",0,3);
			$m = substr("$result1[v_created]",4,6);
			$d = substr("$result1[v_created]",7,8);
			$h = substr("$result1[v_created]",9,10);
			echo "$m.$d.$y     Hour: $h";?>
		</td>
		<td>
			<?$query2 = mysql_query("SELECT `v_created` FROM `visitor` WHERE `v_created` IN (".$timestamp.")");
			echo mysql_num_rows($query2);?>
		</td>
	</tr>
<?}?>

But this code

echo "$m.$d.$y     Hour: $h";

returns weird values such as "032420.4200609.200 Hour: 00609". What's stored is a standard MySQL 4.0.25 timestamp. Example: '20060324200609' (yyyy mm dd hh mm ss).

I don't need the minute or second for this, so I try to get them out with a substr statement, but it doesn't work correctly. I tried date("m/d/Y - H",$result1['v_created']), but that didn't work either. It returned dates from 2030-something.

Any help would be appreciated

 

[Eric]

<?php

$timestamp = substr($result['v_created'], 0, 10);
$y = substr($timestamp, 0, 4);
$m = substr($timestamp, 4, 2);
$d = substr($timestamp, 6, 2);
$h = substr($timestamp, 8, 10);
echo $y.'-'.$m.'-'.$d.' Hour: '.$h;

// Used 20060324200609 for an example, output:
// 2006-03-24 Hour: 20

?>

 

[Tree]

I'm sad that I "must spread some Reputation around before giving it to SecondVersion again."

Thank you very much, 2v! Although my query to display how many people in that hour still returns nothing.

<?$query2 = mysql_query("SELECT `v_created` FROM `visitor` WHERE `v_created` IN ($timestamp)");
echo mysql_num_rows($query2);?>

Nevermind, if I use LIKE instead of IN, it works.

 

[Eric]

'tis no prob.

 

[Tree]

Alright, now it displays many records for the same hour.

Site: http://www.vestieo.com/manage
Username: foo2
Password: foo2

My Sites > Any of the site names

Anyone know how to make it display only one record per hour?

If all of you would please register via the link in my sig, I'd appreciate it. It is for this script that I'm working on. All you need to put in is your age and gender. As per policy, you must be at least 13 years old to register.

 

[Eric]

Have you tried a SELECT DISTINCT query?

 

[Tree]

Yes, and because the minute and seconds are different, it still returns multiple records.

 

[Tree]

I'm upgrading to MySQL 4.1, see if that'll help anything.

 

[Eric]

I'll have to say kinda stumped on this one :red: Will need to give it more thought, will let ya know if I come up w/something

 

[Tree]

Upgrade is complete, and MySQL 4.1 stores timestamps as "YYYY-MM-DD HH:MM:SS". So would an "explode" function work instead of a "substr"?

Wow, I didn't know it was possible to stump 2V. I thought about all the different ways, but couldn't find one that worked.

Just so everyone knows, this isn't a private conversation, feel free to post any of your input as well.

Please register in the link in my sig. All you need to put in is your age and gender. I just need some random data from all over the world.