Solved

MySQL failed to select...help?

Posted on 2015-02-06
7
201 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
  • 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 33

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 33

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 33

Accepted Solution

by:
Mike Eghtebas earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now