MySQL Not Returning the Right Results

Tree · 03-24-2006, 05:09 PM

I have this code:

PHP Code:

  dbConnect();

$query = mysql_query("SELECT * FROM `visitor`");

$result = mysql_fetch_array($query);

$siteStats = array("tot_page" => count($result)); 

????: NamePros.com http://www.namepros.com/programming/180198-mysql-not-returning-the-right-results.html

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

neolinux · 03-24-2006, 05:16 PM

22 ?

Tree · 03-24-2006, 05:20 PM

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.

sdsinc · 03-24-2006, 05:27 PM

Why don't you use mysql_num_rows ?

Tree · 03-24-2006, 05:32 PM

Because I'm dumb

. 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 · 03-24-2006, 06:34 PM

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 · 03-24-2006, 06:39 PM

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:

PHP 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);

????: NamePros.com http://www.namepros.com/showthread.php?t=180198
            echo "$m.$d.$y &nbsp;&nbsp;&nbsp; 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

PHP Code:

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

????: NamePros.com http://www.namepros.com/showthread.php?t=180198

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 · 03-24-2006, 06:58 PM

PHP Code:

  <?php

 
$timestamp = substr($result['v_created'], 0, 10);

????: NamePros.com http://www.namepros.com/showthread.php?t=180198
$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 · 03-24-2006, 07:03 PM

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.

PHP Code:

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

????: NamePros.com http://www.namepros.com/showthread.php?t=180198
echo mysql_num_rows($query2);?>

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

Eric · 03-24-2006, 07:07 PM

'tis no prob.

Tree · 03-24-2006, 07:12 PM

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 · 03-24-2006, 07:18 PM

Have you tried a SELECT DISTINCT query?

Tree · 03-24-2006, 07:24 PM

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

Tree · 03-25-2006, 10:53 AM

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

Eric · 03-25-2006, 05:33 PM

I'll have to say kinda stumped on this one

Will need to give it more thought, will let ya know if I come up w/something

Tree · 03-25-2006, 05:36 PM

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.

03-24-2006, 05:09 PM	THREAD STARTER #1 (permalink)
Tree NamePros Regular Join Date: Feb 2006 Location: Atlanta, GA, USA Posts: 335	MySQL Not Returning the Right Results I have this code: PHP Code: dbConnect(); $query = mysql_query("SELECT * FROM `visitor`"); $result = mysql_fetch_array($query); $siteStats = array("tot_page" => count($result)); ????: NamePros.com http://www.namepros.com/programming/180198-mysql-not-returning-the-right-results.html There should be something like 10,000 returned, but instead it only reports 22. Any idea why?

03-24-2006, 05:27 PM	#4 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 9,552	Why don't you use mysql_num_rows ? __________________ NameNewsletter.com - free lists of available domain names ZoneFiles.net (beta) - ccTLD and gTLD droplists

03-24-2006, 06:34 PM	#6 (permalink)
iNod Eating Pie Join Date: Nov 2004 Location: Canada Posts: 2,272	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 __________________ I feel old.

03-24-2006, 06:39 PM	THREAD STARTER #7 (permalink)
Tree NamePros Regular Join Date: Feb 2006 Location: Atlanta, GA, USA Posts: 335	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: PHP 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); ????: NamePros.com http://www.namepros.com/showthread.php?t=180198 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 PHP Code: `echo "$m.$d.$y     Hour: $h"; ????: NamePros.com http://www.namepros.com/showthread.php?t=180198` 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 Last edited by Tree; 03-24-2006 at 06:47 PM.

03-24-2006, 06:58 PM	#8 (permalink)
Eric Senior Member Join Date: Mar 2005 Posts: 4,948	PHP Code: `<?php $timestamp = substr($result['v_created'], 0, 10); ????: NamePros.com http://www.namepros.com/showthread.php?t=180198 $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 ?>`

03-24-2006, 05:16 PM	#2 (permalink)
neolinux First Time Poster! Join Date: Mar 2006 Posts: 1	22 ?

03-24-2006, 05:20 PM	THREAD STARTER #3 (permalink)
Tree NamePros Regular Join Date: Feb 2006 Location: Atlanta, GA, USA Posts: 335	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.

03-24-2006, 05:32 PM	THREAD STARTER #5 (permalink)
Tree NamePros Regular Join Date: Feb 2006 Location: Atlanta, GA, USA Posts: 335	Because I'm dumb . 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?

03-24-2006, 07:03 PM	THREAD STARTER #9 (permalink)
Tree NamePros Regular Join Date: Feb 2006 Location: Atlanta, GA, USA Posts: 335	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. PHP Code: <?$query2 = mysql_query("SELECT `v_created` FROM `visitor` WHERE `v_created` IN ($timestamp)"); ????: NamePros.com http://www.namepros.com/showthread.php?t=180198 echo mysql_num_rows($query2);?> Nevermind, if I use LIKE instead of IN, it works.

03-24-2006, 07:07 PM	#10 (permalink)
Eric Senior Member Join Date: Mar 2005 Posts: 4,948	'tis no prob.

03-24-2006, 07:12 PM	THREAD STARTER #11 (permalink)
Tree NamePros Regular Join Date: Feb 2006 Location: Atlanta, GA, USA Posts: 335	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. Last edited by Tree; 03-24-2006 at 07:16 PM.

03-24-2006, 07:18 PM	#12 (permalink)
Eric Senior Member Join Date: Mar 2005 Posts: 4,948	Have you tried a SELECT DISTINCT query?

03-24-2006, 07:24 PM	THREAD STARTER #13 (permalink)
Tree NamePros Regular Join Date: Feb 2006 Location: Atlanta, GA, USA Posts: 335	Yes, and because the minute and seconds are different, it still returns multiple records.

03-25-2006, 10:53 AM	THREAD STARTER #14 (permalink)
Tree NamePros Regular Join Date: Feb 2006 Location: Atlanta, GA, USA Posts: 335	I'm upgrading to MySQL 4.1, see if that'll help anything.

03-25-2006, 05:33 PM	#15 (permalink)
Eric Senior Member Join Date: Mar 2005 Posts: 4,948	I'll have to say kinda stumped on this one Will need to give it more thought, will let ya know if I come up w/something

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

03-25-2006, 05:36 PM	THREAD STARTER #16 (permalink)
Tree NamePros Regular Join Date: Feb 2006 Location: Atlanta, GA, USA Posts: 335	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. Last edited by Tree; 03-25-2006 at 05:39 PM.