Jump to content


Photo

SELECT FROM only returning first 74 entrys


Best Answer dlan1, 25 April 2014 - 08:54 PM

Ok, sorry, I guess I didn't understand what was being said about white spaces, and yes that was the problem. As I went back and looked at the file I dumped and there they were. I didn't mean to be a thick head.

 

Thanks for putting up with me.

 

Don...

Go to the full post


  • Please log in to reply
7 replies to this topic

#1 dlan1

dlan1

    Newbie

  • New Members
  • Pip
  • 5 posts

Posted 23 April 2014 - 10:38 AM

I am using a simple membership on my site. I'm a cut and paste expert not a programer. I was able to get it working, It worked fine till I got over 100 members. Now it is only finding the first 74

 

So if I use;

 

$sql="SELECT * FROM users WHERE email_address = 'me@you.com' "; 

 

It will only find me@you.com  if it is one of the first 74 records.

 

I'm sure there is something I don't know that is causeing the problem, Just don't know what.

 

If someone could point me to the right direction I'd be greatfull.

 

Thanks, Don..

 

 

MySQL 5.5

CREATE TABLE IF NOT EXISTS `users` (
  `userid` int(10) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(25) NOT NULL DEFAULT '',
  `last_name` varchar(25) NOT NULL DEFAULT '',
  `email_address` varchar(45) NOT NULL,
  `password` varchar(10) NOT NULL DEFAULT '',
  `zipcode` varchar(11) NOT NULL,
  `user_level` enum('0','1','2','3') NOT NULL DEFAULT '0',
  `signup_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_login` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `activated` enum('0','1') NOT NULL DEFAULT '0',
  `userIP` varchar(20) NOT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Membership Information' AUTO_INCREMENT=115 ;



#2 mac_gyver

mac_gyver

    Advanced Member

  • Administrators
  • 2,358 posts

Posted 23 April 2014 - 11:48 AM

you are going to need to troubleshoot the problem to narrow it down. either the data being stored in your table isn't what you think (has white-space/non-printing characters as part of it so it doesn't match what the query is using) or your code forming that query, running it, and displaying the result contains a problem.

 

how are your email values getting inserted into the table (some type of csv import or a registration form)? is the code filtering/validating them so that you know they don't contain any extra white-space/non-printing characters?

 

what's your full code from the point of inputting/selecting the email address (a form/dropdown menu...) through to the end of the code that is telling you the email address doesn't exist?


multi-purpose programming fool and resident naysayer [We try not be negative in replies, but telling someone what they're doing wrong, while staying politically correct, isn't always going to happen.]

#3 dlan1

dlan1

    Newbie

  • New Members
  • Pip
  • 5 posts

Posted 24 April 2014 - 10:18 AM

This started when I received an email saying that someone could not log-in. They were a message Account not active.

I looked in DB file and it should have been ok.. FYI, the site is ohioeuchre.com

 

I've been testing using a page that just lists all members. If  I select using 'userID'  it will show the complete list. however if I Cut+P directly from a membership list, ( using the WHERE email_address statement ) sometimes it will work and somedtimes not. This page and the actual that is used in the membership program(chenckuser.php)  both do the same thing. some thing will work some won't.

 

I may have been wrong with the 74 number. This morming I found some higher records that it would find..

 

The php error log show nothing

 

This is the test page:

 

    <?php

    // Connection to the database
    include 'inc/db_connect.php';
    
    //$sql="SELECT * FROM users ORDER BY userid";
    
    $sql="SELECT * FROM users WHERE email_address = 'me@you.com' ";
    $result=mysql_query($sql);

    // Start of html that is displayed
    ?>
    
<div align="center">
<div id="textbox">

<h2>OhioEuchre.Com Membership List</h2><br>
<table  border="1" cellpadding="3" cellspacing="1">
<?$num = 0;?>
<tr bgcolor="<?=(++$num%2==0 ? "#E6D78E" : "#dcc55d")?>">
<td align="center" width="20"><strong>Count</strong></td>
<td align="center" width="20"><strong>ID #</strong></td>
<td align="center" width="60"><strong> First Name</strong></td>
<td align="center" width="120"><strong>Email</strong></td>
<td align="center" width="20"><strong>Zip Code</strong></td>
<td align="center" width="20"><strong>Activated</strong></td>
<td align="center" width="20"><strong>Password</strong></td>

</tr>

<?php
$Count = 1;
while($rows=mysql_fetch_array($result)){
?>
<tr bgcolor="<?=(++$num%2==0 ? "#E6D78E" : "#dcc55d")?>">
<td align="center"><? echo $Count++; ?></td>
<td align="center"><? echo $rows['userid']; ?></td>
<td align="left"><? echo $rows['first_name']; ?></td>
<td align="center"><? echo $rows['email_address']; ?></td>
<td align="center"><? echo $rows['zipcode']; ?></td>
<td align="center"><? echo $rows['activated']; ?></td>
<td align="center"><? echo $rows['password']; ?></td>
</tr>

<?php } ?>
 </table>

 

END OF TEST PAGE.................



#4 dlan1

dlan1

    Newbie

  • New Members
  • Pip
  • 5 posts

Posted 25 April 2014 - 12:04 PM

Anybody have any thoughts on this?

 

I've been playing with this for days now and I keep coming up with the same results. I created a new DB dumped the table from the old one and inserted it into new DB

 

Tried query directly in phpMyadim SQL  using the statment  SELECT email_address FROM users WHERE email_address = 'me@you.com'   where 'me@you' is replaced with  emails cut directly from a browse listing.  Again some emails will show a result and some will show zero return.

 

It makes no sense to me and I'm out of ideas or things to try.



#5 Ch0cu3r

Ch0cu3r

    Advanced Member

  • Moderators
  • 2,122 posts

Posted 25 April 2014 - 12:44 PM

So phpMyAdmin is also not showing the correct results?

 

to me that sounds like some email address stored in the email_address field may also contain some extra characters, such as white space (spaces, newlines etc), before and/or after the users email address which is causing the WHERE clause to fail.



#6 dlan1

dlan1

    Newbie

  • New Members
  • Pip
  • 5 posts

Posted 25 April 2014 - 08:12 PM

I've tried cut and paste, I've tried typing the email into the myphpAdmin statement. Nothing works. It seems that your saying what I've got should work.  I think my next step is file a ticket with BlueHost.com (they host the site) and see if the problem is with something on their end.

 

Thanks for your time...

Don



#7 mac_gyver

mac_gyver

    Advanced Member

  • Administrators
  • 2,358 posts

Posted 25 April 2014 - 08:25 PM

the problem's not very likely anything to do with your web host. two people have suggested the symptom is due to non-printing/white-space characters that are stored in the database table. you were even asked specifically how the data got inserted into the table and if the code doing that was validating the data -

 

 

how are your email values getting inserted into the table (some type of csv import or a registration form)? is the code filtering/validating them so that you know they don't contain any extra white-space/non-printing characters?

 


multi-purpose programming fool and resident naysayer [We try not be negative in replies, but telling someone what they're doing wrong, while staying politically correct, isn't always going to happen.]

#8 dlan1

dlan1

    Newbie

  • New Members
  • Pip
  • 5 posts

Posted 25 April 2014 - 08:54 PM   Best Answer

Ok, sorry, I guess I didn't understand what was being said about white spaces, and yes that was the problem. As I went back and looked at the file I dumped and there they were. I didn't mean to be a thick head.

 

Thanks for putting up with me.

 

Don...






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com