Solved

MySQL failed to select...help?

Posted on 2015-02-06
7
207 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 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
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.

 
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 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

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

Suggested Solutions

Title # Comments Views Activity
Whether to use true/false, yes/no or 0/1 11 67
update joined tables 2 55
MS SQL Server - Looking to filter rows based on column value 3 48
MySQL Query Using Up Memory 6 43
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 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