0

Im not even sure if this is possible (Im new to php)

Anyway, what I want to do is this:

mysql_query("SELECT * FROM user_table WHERE concat(username,'@',domain)='$username' LIMIT=1");

Ok, so the $username is an email address that is submitted by a user to search the database to check that they exist. In the user_table usernames are not stored in a single column, they are stored in several with the domain and the actual username being separate.

for example username could be bob and the domain could be website.com.au

Then when the user wants to search for that user the type in bob@website.com.au

This goes to the query above.

So, should it work or not? If not how can I make this work or what suggestions do you have for me?

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
user940021
  • 1
  • 1
  • Welcome to Stack Overflow. You can format source code with the `{}` toolbar button. I've done it for you this time. – Álvaro González Sep 12 '11 at 07:11
  • 1
    `Manning - Bobby Tables in Action` again:P – Petar Minchev Sep 12 '11 at 07:13
  • `concat` looks right from what I've looked up. Try removing the ' from around $username. My PHP knowledge is also limited, but it's the first thing I'd try – James Webster Sep 12 '11 at 07:16
  • read [this reference](http://stackoverflow.com/questions/6198104/reference-what-is-a-perfect-code-sample-using-the-mysql-extension/6198584#6198584) about how to use mysql. – OZ_ Sep 12 '11 at 07:20
  • According to the manual paragraph 11.5 what you wrore is correct: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat – mishau Sep 12 '11 at 07:22
  • 1
    Bear in mind that this will be quite a slow query. Storing the entire value as a single, indexed field would be much faster. – Bobby Jack Sep 12 '11 at 07:35
  • You should follow @Bobby Jack's suggestion. Also, there is no such thing as `LIMIT=1`, it should be `LIMIT 1`. – Shef Sep 12 '11 at 07:44

2 Answers2

0

As BobbyJack has mentioned, this is a slow way of locating a user record.

If you cannot store email address in a single column and place an index on that column, split the string in PHP and make your query:

SELECT * FROM user_table WHERE `username` = '$username' AND `domain` = '$domain'

You could then create a unique index combining domain + username so you wouldn't need LIMIT 1

cloakedninjas
  • 4,007
  • 2
  • 31
  • 45
0

probably worded the question slightly wrong.

Anyway this is what I have done "SELECT * FROM virtual_user WHERE concat_ws('@',username,domain)='$username'"

I no longer need to use the LIMIT=1, I probably never needed to as all results in the table are individual, so it will always only return a limit of 1 or nothing at all.

It isn't slow in my opinion, but then again Im not really sure what to compare it to. We have about 7000+ records it sorts through so yeah. Is there anyway to get it to tell you how long the query took to complete?

I would like to put both the username and domain into just a single indexed field but its for a postfix mail server and I'm not allowed or game to play with the queries it uses. Especially not on a functioning server that actually handles mail.

user940021
  • 1
  • 1