select query

[youvi]

id name
1 A4_HUMAN
2 ABC3H_HUMAN
3 22P1_RAT
4 5HT1E_HUMAN

Hello ,

See above shows a database table contains 2 fields id and name..
I need to search these values and display the names with a keyword entered by user.
The key word may b a string or even a character say "A","5HT1E","4".....
The problem is i need to check the keyword only in the part before the "_" from the name field....

suppose an user enterd a keyword "A", my expected results are A4_HUMAN,ABC3H_HUMAN..
The string 22P1_RAT & 5HT1E_HUMAN also contains the keyword "A" but it is after the"_" symbol..so i don;t need it as the result..

So kindly provide me the query using 'like' operator...
How can i write the query?????? :(
Thanks

[metalix]

Hi there
Firstly I would recommend you seperate the code and species into different fields.
otherwise it will take more coding, and take longer to process.

However here is some code that should work:

<?PHP

$conn = ''; //connection info
$input = $_POST['input']; //the users input
@mysql_select_db('database');
$sql_input = mysql_real_escape_string($input);//sanitize input
$sql = "SELECT * FROM table_name WHERE name LIKE '%$sql_input%'";
//we query with the %LIKE% so as to slightly reduce databse consumption for next event
$query = @mysql_query($sql,$conn); //execute the query
$result = array(); //prepare the array
while($row = @mysql_fetch_assoc($query)){ //loop through the table
	$name = explode('_',$row['name']); //split the result where the underscore is
	$key = $name[0]; //the explode array starts at 0 for first found
	$species = $name[1]; // the species
	if(strpos($key,$input)){ //detect if the input is in the returned key
		$result[] = $row['name']; //add row to result
	}
}
//now you can loop through results

foreach($result as $output){
	echo $output.'<br />';
}
?>

[youvi]

sorry... i have a correction in my question...

actually i need to display..the entire row...matching the keyword..

now how will be the query??

[metalix]

Just output the row as the query is running

<?PHP

$conn = ''; //connection info
$input = $_POST['input']; //the users input
@mysql_select_db('database');
$sql_input = mysql_real_escape_string($input);//sanitize input
$sql = "SELECT * FROM table_name WHERE name LIKE '%$sql_input%'";
//we query with the %LIKE% so as to slightly reduce databse consumption for next event
$query = @mysql_query($sql,$conn); //execute the query
while($row = @mysql_fetch_assoc($query)){ //loop through the table
	$name = explode('_',$row['name']); //split the result where the underscore is
	$key = $name[0]; //the explode array starts at 0 for first found
	$species = $name[1]; // the species
	if(strpos($key,$input)){ //detect if the input is in the returned key
		echo $row['id'].' - '.$row['name'];
	}
}
?>

[youvi]

Just output the row as the query is running

<?PHP

$conn = ''; //connection info
$input = $_POST['input']; //the users input
@mysql_select_db('database');
$sql_input = mysql_real_escape_string($input);//sanitize input
$sql = "SELECT * FROM table_name WHERE name LIKE '%$sql_input%'";
//we query with the %LIKE% so as to slightly reduce databse consumption for next event
$query = @mysql_query($sql,$conn); //execute the query
while($row = @mysql_fetch_assoc($query)){ //loop through the table
	$name = explode('_',$row['name']); //split the result where the underscore is
	$key = $name[0]; //the explode array starts at 0 for first found
	$species = $name[1]; // the species
	if(strpos($key,$input)){ //detect if the input is in the returned key
		echo $row['id'].' - '.$row['name'];
	}
}
?>

thanks!!

[metalix]

No worries.

Remember if thread is solved to mark as solved :D