NamePros
Welcome, Guest! Ready to make a name for yourself in the domain business? We welcome both the hobbyist and professional domainer to join the discussion as part of the NamePros community.

Click here to create your profile to start earning reputation for posting, and trader ratings for buying & selling in our free e-marketplace. Build your trader rating with each successful sale. Our system has tracked over 100,000 sales and counting!
FAQ & TOS Register Search Today's Posts Mark Forums Read

Go Back   NamePros.com > Website Development Discussion Forums > Programming
Reload this Page MySQL Not Returning the Right Results

Programming PHP, Perl, Ruby on Rails, AJAX, HTML, XHTML, CSS, JavaScript, MySQL and any other coding topics.

Advanced Search


Closed Thread
 
LinkBack Thread Tools
Old 03-24-2006, 05:09 PM THREAD STARTER               #1 (permalink)
NamePros Regular
 
Tree's Avatar
Join Date: Feb 2006
Location: Atlanta, GA, USA
Posts: 335
Tree will become famous soon enoughTree will become famous soon enough
 



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?
Tree is offline  
Old 03-24-2006, 05:16 PM   #2 (permalink)
First Time Poster!
Join Date: Mar 2006
Posts: 1
neolinux is an unknown quantity at this point
 



22 ?
neolinux is offline  
Old 03-24-2006, 05:20 PM THREAD STARTER               #3 (permalink)
NamePros Regular
 
Tree's Avatar
Join Date: Feb 2006
Location: Atlanta, GA, USA
Posts: 335
Tree will become famous soon enoughTree will become famous soon enough
 



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.
Tree is offline  
Old 03-24-2006, 05:27 PM   #4 (permalink)
Domains my Dominion
 
sdsinc's Avatar
Join Date: Aug 2005
Location: Web 1.0
Posts: 9,552
sdsinc Has achieved greatnesssdsinc Has achieved greatnesssdsinc Has achieved greatnesssdsinc Has achieved greatnesssdsinc Has achieved greatnesssdsinc Has achieved greatnesssdsinc Has achieved greatnesssdsinc Has achieved greatnesssdsinc Has achieved greatnesssdsinc Has achieved greatnesssdsinc Has achieved greatness
 


Third World Education Find Marrow Donors! Find Marrow Donors! Find Marrow Donors! Find Marrow Donors! Animal Rescue Animal Cruelty AIDS/HIV Animal Rescue Wildlife Breast Cancer Animal Rescue Wildlife
Why don't you use mysql_num_rows ?
__________________
NameNewsletter.com - free lists of available domain names
ZoneFiles.net (beta) - ccTLD and gTLD droplists
sdsinc is online now  
Old 03-24-2006, 05:32 PM THREAD STARTER               #5 (permalink)
NamePros Regular
 
Tree's Avatar
Join Date: Feb 2006
Location: Atlanta, GA, USA
Posts: 335
Tree will become famous soon enoughTree will become famous soon enough
 



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?
Tree is offline  
Old 03-24-2006, 06:34 PM   #6 (permalink)
Eating Pie
 
iNod's Avatar
Join Date: Nov 2004
Location: Canada
Posts: 2,272
iNod has much to be proud ofiNod has much to be proud ofiNod has much to be proud ofiNod has much to be proud ofiNod has much to be proud ofiNod has much to be proud ofiNod has much to be proud ofiNod has much to be proud ofiNod has much to be proud ofiNod has much to be proud of
 


Special Olympics AIDS/HIV Cystic Fibrosis Save The Children Baby Health Cystic Fibrosis
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.
iNod is offline  
Old 03-24-2006, 06:39 PM THREAD STARTER               #7 (permalink)
NamePros Regular
 
Tree's Avatar
Join Date: Feb 2006
Location: Atlanta, GA, USA
Posts: 335
Tree will become famous soon enoughTree will become famous soon enough
 



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
Last edited by Tree; 03-24-2006 at 06:47 PM.
Tree is offline  
Old 03-24-2006, 06:58 PM   #8 (permalink)
Senior Member
 
Eric's Avatar
Join Date: Mar 2005
Posts: 4,948
Eric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatness
 

Member of the Month
MOTM September 2005
Save a Life Child Abuse 9/11/01 :: Never Forget Baby Health Marrow Donor Program AIDS/HIV Breast Cancer Animal Rescue Cystic Fibrosis Ethan Allen Fund Animal Cruelty Ethan Allen Fund Ethan Allen Fund Baby Health Cancer Alzheimer's Protect Our Planet Cancer Survivorship SIDS Child Abuse Diabetes Protect Our Planet Multiple Sclerosis Autism Adoption Special Olympics
PHP Code:
<?php

$timestamp 
substr($result['v_created'], 010);
????: NamePros.com http://www.namepros.com/showthread.php?t=180198
$y substr($timestamp04);
$m substr($timestamp42);
$d substr($timestamp62);
$h substr($timestamp810);
echo 
$y.'-'.$m.'-'.$d.' Hour: '.$h;

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

?>
Eric is offline  
Old 03-24-2006, 07:03 PM THREAD STARTER               #9 (permalink)
NamePros Regular
 
Tree's Avatar
Join Date: Feb 2006
Location: Atlanta, GA, USA
Posts: 335
Tree will become famous soon enoughTree will become famous soon enough
 



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.
Tree is offline  
Old 03-24-2006, 07:07 PM   #10 (permalink)
Senior Member
 
Eric's Avatar
Join Date: Mar 2005
Posts: 4,948
Eric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatness
 

Member of the Month
MOTM September 2005
Save a Life Child Abuse 9/11/01 :: Never Forget Baby Health Marrow Donor Program AIDS/HIV Breast Cancer Animal Rescue Cystic Fibrosis Ethan Allen Fund Animal Cruelty Ethan Allen Fund Ethan Allen Fund Baby Health Cancer Alzheimer's Protect Our Planet Cancer Survivorship SIDS Child Abuse Diabetes Protect Our Planet Multiple Sclerosis Autism Adoption Special Olympics
'tis no prob.
Eric is offline  
Old 03-24-2006, 07:12 PM THREAD STARTER               #11 (permalink)
NamePros Regular
 
Tree's Avatar
Join Date: Feb 2006
Location: Atlanta, GA, USA
Posts: 335
Tree will become famous soon enoughTree will become famous soon enough
 



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.
Tree is offline  
Old 03-24-2006, 07:18 PM   #12 (permalink)
Senior Member
 
Eric's Avatar
Join Date: Mar 2005
Posts: 4,948
Eric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatness
 

Member of the Month
MOTM September 2005
Save a Life Child Abuse 9/11/01 :: Never Forget Baby Health Marrow Donor Program AIDS/HIV Breast Cancer Animal Rescue Cystic Fibrosis Ethan Allen Fund Animal Cruelty Ethan Allen Fund Ethan Allen Fund Baby Health Cancer Alzheimer's Protect Our Planet Cancer Survivorship SIDS Child Abuse Diabetes Protect Our Planet Multiple Sclerosis Autism Adoption Special Olympics
Have you tried a SELECT DISTINCT query?
Eric is offline  
Old 03-24-2006, 07:24 PM THREAD STARTER               #13 (permalink)
NamePros Regular
 
Tree's Avatar
Join Date: Feb 2006
Location: Atlanta, GA, USA
Posts: 335
Tree will become famous soon enoughTree will become famous soon enough
 



Yes, and because the minute and seconds are different, it still returns multiple records.
Tree is offline  
Old 03-25-2006, 10:53 AM THREAD STARTER               #14 (permalink)
NamePros Regular
 
Tree's Avatar
Join Date: Feb 2006
Location: Atlanta, GA, USA
Posts: 335
Tree will become famous soon enoughTree will become famous soon enough
 



I'm upgrading to MySQL 4.1, see if that'll help anything.
Tree is offline  
Old 03-25-2006, 05:33 PM   #15 (permalink)
Senior Member
 
Eric's Avatar
Join Date: Mar 2005
Posts: 4,948
Eric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatnessEric Has achieved greatness
 

Member of the Month
MOTM September 2005
Save a Life Child Abuse 9/11/01 :: Never Forget Baby Health Marrow Donor Program AIDS/HIV Breast Cancer Animal Rescue Cystic Fibrosis Ethan Allen Fund Animal Cruelty Ethan Allen Fund Ethan Allen Fund Baby Health Cancer Alzheimer's Protect Our Planet Cancer Survivorship SIDS Child Abuse Diabetes Protect Our Planet Multiple Sclerosis Autism Adoption Special Olympics
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
Eric is offline  
Old 03-25-2006, 05:36 PM THREAD STARTER               #16 (permalink)
NamePros Regular
 
Tree's Avatar
Join Date: Feb 2006
Location: Atlanta, GA, USA
Posts: 335
Tree will become famous soon enoughTree will become famous soon enough
 



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.
Tree is offline  
Closed Thread


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


Liquid Web Smart Servers  
All times are GMT -7. The time now is 06:34 AM.

Managed Web Hosting by Liquid Web
Domain name forum recommended by Domaining.com Powered by: vBulletin® Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Search Engine Friendly URLs by vBSEO 3.6.0 Ad Management plugin by RedTyger