MySQL Not Returning the Right Results

Tree · 03-24-2006, 04: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));

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

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

22 ?

Tree · 03-24-2006, 04: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, 04:27 PM

Why don't you use mysql_num_rows ?

Tree · 03-24-2006, 04: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, 05: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, 05: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);

            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";

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, 05:58 PM

PHP Code:

  <?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 · 03-24-2006, 06: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)");

echo mysql_num_rows($query2);?>

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

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

'tis no prob.

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

Have you tried a SELECT DISTINCT query?

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

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

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

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

**Eric** · 03-25-2006, 04: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, 04: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, 04:09 PM	#1 (permalink)
Tree NamePros Regular Join Date: Feb 2006 Location: Atlanta, GA, USA Posts: 335 13.25 NP$ (Donate)	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)); There should be something like 10,000 returned, but instead it only reports 22. Any idea why?

03-24-2006, 04:27 PM	#4 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 6,285 1,095.94 NP$ (Donate)	Why don't you use mysql_num_rows ? __________________ Buy now - MassDeveloper.com $500

03-24-2006, 05:34 PM	#6 (permalink)
iNod Eating Pie Join Date: Nov 2004 Location: Canada Posts: 2,289 126.05 NP$ (Donate)	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, 05:39 PM	#7 (permalink)
Tree NamePros Regular Join Date: Feb 2006 Location: Atlanta, GA, USA Posts: 335 13.25 NP$ (Donate)	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); 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";` 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 05:47 PM.

03-24-2006, 05:58 PM	#8 (permalink)
Eric NPQ's PA, Slave, and On Call Coder Technical Services Join Date: Mar 2005 Posts: 4,545 0.71 NP$ (Donate)	PHP Code: `<?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 ?>` __________________

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

03-24-2006, 04:20 PM	#3 (permalink)
Tree NamePros Regular Join Date: Feb 2006 Location: Atlanta, GA, USA Posts: 335 13.25 NP$ (Donate)	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, 04:32 PM	#5 (permalink)
Tree NamePros Regular Join Date: Feb 2006 Location: Atlanta, GA, USA Posts: 335 13.25 NP$ (Donate)	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, 06:03 PM	#9 (permalink)
Tree NamePros Regular Join Date: Feb 2006 Location: Atlanta, GA, USA Posts: 335 13.25 NP$ (Donate)	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)"); echo mysql_num_rows($query2);?> Nevermind, if I use LIKE instead of IN, it works.

03-24-2006, 06:07 PM	#10 (permalink)
Eric NPQ's PA, Slave, and On Call Coder Technical Services Join Date: Mar 2005 Posts: 4,545 0.71 NP$ (Donate)	'tis no prob. __________________

03-24-2006, 06:12 PM	#11 (permalink)
Tree NamePros Regular Join Date: Feb 2006 Location: Atlanta, GA, USA Posts: 335 13.25 NP$ (Donate)	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 06:16 PM.

03-24-2006, 06:18 PM	#12 (permalink)
Eric NPQ's PA, Slave, and On Call Coder Technical Services Join Date: Mar 2005 Posts: 4,545 0.71 NP$ (Donate)	Have you tried a SELECT DISTINCT query? __________________

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

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

03-25-2006, 04:33 PM	#15 (permalink)
Eric NPQ's PA, Slave, and On Call Coder Technical Services Join Date: Mar 2005 Posts: 4,545 0.71 NP$ (Donate)	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, 04:36 PM	#16 (permalink)
Tree NamePros Regular Join Date: Feb 2006 Location: Atlanta, GA, USA Posts: 335 13.25 NP$ (Donate)	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 04:39 PM.

Technical Services

Technical Services

Technical Services

Technical Services