select defined string value with mysql query of a table

Question

i have a table called username which has for example John Doe and i want to get this value in a php action for $username! I use this

mysql_query("SELECT * FROM photos WHERE username = '$username'");

it works fine. Im using this for a searchfield to return different Users and their profile. The question is, how can i search with eg only the first name John and he gives me John Doe back. I dont want to create a first and last name!

Thanks Ted

If you want exact match means John to John Doe then it is not possible but it want that search JOhn can give two or three results say John Doe or Steve John then it is possible using 'Like'... tell me if you want query for that ... — alwaysLearn, Mar 14 '13 at 09:50

score 2 · Accepted Answer · answered Mar 14 '13 at 09:47

2

SELECT * FROM photos WHERE username LIKE '%$username%';

answered Mar 14 '13 at 09:47

Ashwini Agarwal

4,828
2
42
59

score 2 · Answer 2 · answered Mar 14 '13 at 09:55

With a word on security: If $username is the value of a search field in your site, you should escape the given search pattern correctly, not to get your database injected. This is simply done by the following code:

$escaped_username = mysql_real_escape_string($_REQUEST['username']);
mysql_query("SELECT * FROM photos WHERE username LIKE '%$escaped_username%'");

This will perform a secure search in your table, with the possibility of using just first or last name.

Legend: $_REQUEST['username'] is the value of your search form.

score 1 · Answer 3 · edited May 23 '17 at 12:04

You can use LIKE for pattern matching,

SELECT * FROM photos WHERE username LIKE CONCAT('%','$username', '%')

but this will perform slow in large database because it will not use index causing to perform FULL TABLE SCAN.

$query = "SELECT * FROM photos WHERE username LIKE CONCAT('%',$username, '%')"
mysql_query($query);

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

hey thanks a lot, i know that sql injection problem and i made the code a lil bit smaller for showing here! thanks anyways, exactly what i wanted :) — supersize, Mar 14 '13 at 10:02

score 1 · Answer 4 · edited Mar 14 '13 at 09:48

1

You can use LIKE MySQL clause in the query

SELECT * FROM photos WHERE username LIKE '$username%'

edited Mar 14 '13 at 09:48

Dipesh Parmar

27,090
8
61
90

answered Mar 14 '13 at 09:47

Rohit Subedi

560
3
13

score 0 · Answer 5 · answered Mar 14 '13 at 09:53

0

You can use CONCAT Function to get Full name

  SELECT CONCAT(First_name, ' ', Lastnale,) 
  FROM photos 
  WHERE username LIKE '$username%'

answered Mar 14 '13 at 09:53

Sharad

3,562
6
37
59

select defined string value with mysql query of a table

5 Answers5