Solved

MySQL failed to select...help?

Posted on 2015-02-06
7
198 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Creating and Managing Databases with phpMyAdmin in cPanel.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

747 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

15 Experts available now in Live!

Get 1:1 Help Now