Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL failed to select...help?

Posted on 2015-02-06
7
Medium Priority
?
213 Views
Last Modified: 2015-02-06
Hi,

This is going to drive me nuts.
I can't seem to select a particular email address in mySQL database.

I have this simple :

SELECT * FROM `User` WHERE email = "xxxxxxxx@xxxxx.ca";

Which returns a value.

But not this :

SELECT * FROM `User` WHERE email = "xx00xx@xxxxx.ca";

No error messages. Simply 0 result set. However, since the email address is top in the database, i know it exists. I am staring at it.
What could possibly be a common mistake I am doing?

Thanks,

Phil
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 

Author Comment

by:NeverEndingFlashStories
ID: 40594327
When going through the phpadmin, it looks like there is a new line after the email address.

I can delete the field through phpadmin, but i need to fix my select statement so that it will remove these kinds of emails when an admin goes through the CMS.

Is there any way to do a select that also selects any kinds of whitespace?
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40594368
Just for test purpose, try:

SELECT * FROM `User` WHERE Trim(email) = "xx00xx@xxxxx.ca";

or even:

SELECT * FROM `User` WHERE email = "%xx00xx@xxxxx.ca%";

It is never a good idea to have function on the left side of = in Where clause. My though was to see if the email in the database accidentally is padded by blank spaces.
0
 

Author Comment

by:NeverEndingFlashStories
ID: 40594409
Hi eghtebas,

Thanks. Neither of those actually worked. It still returned empty result set. What are the other possible gotchas ?
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40594424
"xx00xx@xxxxx.ca" is some variation to "xxxxxx@xxxxx.ca"

Are there some other variations so you can test to see how they behave. This way we may see the cause and effect a bit clearly.

This again is shooting in the dark. But this time it may reveal something new.
0
 

Author Comment

by:NeverEndingFlashStories
ID: 40594499
Hi Eghtebas,

I have tested SELECT against other values in the database.

Apparently, users with emails entered by a mass subscription script are all not selectable. Furthermore, using phpadmin edit mode, and phpstorm database tables edit mode, i can confirm that these users have 2 lines in their email fields.

On the other hand, users who self registered, can be selected, and have only 1 line for their emails.

I've dug out the script the original coder used, and it looks like he used the following script to mass subscribe users :

<?php 
	include 'common.php';

	$handle = @fopen("/var/www/staff/email.txt", "r");
	global $db;
	if ($handle) {
		while (($buffer = fgets($handle, 4096)) !== false) {
			$sql = "INSERT INTO User (fName, email, type, institution) VALUES(
				'User',
				'".$buffer."',
				'0',
				'Brock'
			);";
			//echo $sql;
			$db->query($sql);
			
		}
		if (!feof($handle)) {
			echo "Error: unexpected fgets() fail\n";
		}
		fclose($handle);
	}

Open in new window


What seems to be the problem with this insert Script?
How can i do a select statement that will select these emails irrelevant of whether there is an extra line or not in the email field?
If that is not possible, is there a way to remove the extra lines only from emails that has an extra empty line?

Thank you
0
 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 2000 total points
ID: 40594525
But in that case I am surprised why the following doesn't work?

SELECT * FROM `User` WHERE email = "xx00xx@xxxxx.ca%";

------------------
Also, I think after identifying return character in MySQL (FYI, I use SQL Server not familiar much with MySQL), we can do something like:

SELECT * FROM `User` WHERE LEFT(email, InStr(email,ReturnChar) -1) = "xx00xx@xxxxx.ca";

I need your help to adapt something like LEFT(email, InStr(email,ReturnChar )-1) in MySQL.
0
 

Author Comment

by:NeverEndingFlashStories
ID: 40594592
Thank you !!!

This worked :

SELECT * FROM `User` WHERE LEFT(email, InStr(email,'\n') -1) = "xx00xx@xxxx.ca" ORDER BY `email` ASC
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question