Mysql search tool

[Blezz]

I need a php script that will search a mysql database for the specified entry(An html form, user inputs text and the script searches in the specific database field...(If this doesn't make sense, ask and Ill try to go further into explanation)

[kratsg]

User specifies column to search in and text to search for?

 

$column = mysql_real_escape_string($_POST['column']);
$keyword = mysql_real_escape_string($_POST['keyword']);

$blah = "SELECT $column FROM table1 WHERE $column LIKE '%$keyword%'";
$query = mysql_query($blah);

[Blezz]

Thats looks like what I need, Ill try it in an hour or so(I'm busy right now) thank you!

[Blezz]

Alright, heres the code I ended up with, but it doesn't seem to work so something I did must be wrong, anything you can see specifically?

There are no errors, all it shows is " - "(without quotes)

 

And yes, I did put -'s in place of the mysql information

<?php

$username="db-------4-------";
$password="-G---X----b";
$host="db----.-----.net";
$database="db---2-4----6-7--";
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$column = mysql_real_escape_string('artist');
$keyword = mysql_real_escape_string('avenged');

$blah = "SELECT " . $column . " FROM lyrics_lyrics WHERE " . $column . " LIKE '%" . $keyword."%'";
$query = mysql_query($blah);

$num= mysql_numrows($query);
$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$artist=mysql_result($result,$i,"artist");
$lyrics=mysql_result($result,$i,"lyrics");
$album=mysql_result($result,$i,"album");
$user=mysql_result($result,$i,"user");
$email=mysql_result($result,$i,"email");
$web=mysql_result($result,$i,"website");
$song=mysql_result($result,$i,"song");


echo $artist."-".$song."<br>";
$i++;
}

mysql_close();



?>

[~n[EO]n~]

Your query will look like this

 

Select artist FROM lyrics_lyrics WHERE artist LIKE "%avenged";

 

So this will return the result if there is any data avenged in column artist.

 

EDIT:

See kratsg's post above, well what he is trying to do is searching from user input via a form. 

<?php 
// this is from a text field when user fills something in it and is then searched from the database and displayed
$column = mysql_real_escape_string($_POST['column']);
$keyword = mysql_real_escape_string($_POST['keyword']);
?>

[Blezz]

n~ link=topic=168145.msg741501#msg741501 date=1195402027]

Your query will look like this

 

Select artist FROM lyrics_lyrics WHERE artist LIKE "%avenged";

 

So this will return the result if there is any data avenged in column artist.

 

EDIT:

See kratsg's post above, well what he is trying to do is searching from user input via a form. 

<?php 
// this is from a text field when user fills something in it and is then searched from the database and displayed
$column = mysql_real_escape_string($_POST['column']);
$keyword = mysql_real_escape_string($_POST['keyword']);
?>

 

I know what that part is, but for the sake of testing I put my own words in there.

 

And what you said above is what Kratsg said basically, I know what it does but it doesn't seem to work, I put some data into the database with avenged sevenfold under artist, and yet it doesn't seem to find it apparently. And actually it would look more like

LIKE '%avenged%'

if I'm correct

[rab]

Heres some security tips...

 

1) mysql_real_escape_string wont help you if you don't wrap the variable with quotes, (',")..

2) mysql_real_escape_string does not escape wildcards like % and _, which you are using.

 

 

/**
do mysql connecting...
**/

$columns = array('artist', 'album', 'song');

if( !in_array($_POST['column'], $columns) ) {
$_POST['column'] = 'artist'; // Or a nother default value
}

$_POST['keyword'] = mysql_real_escape_string(addcslashes($_POST['keyword'],'%_'));
$resource = mysql_query("SELECT * FROM lyrics_db WHERE {$_POST['column']} LIKE '%{$_POST['keyword']}%'");
/**
continue on with query & display
**/

 

[~n[EO]n~]

<?php
$username="db-------4-------";
$password="-G---X----b";
$host="db----.-----.net";
$database="db---2-4----6-7--";
mysql_connect($host,$username,$password);
mysql_select_db($database) or die( "Unable to select database");
$column = mysql_real_escape_string('artist');
$keyword = mysql_real_escape_string('avenged');

$sql = "SELECT " . $column . " FROM lyrics_lyrics WHERE " . $column . " LIKE '%" . $keyword."%'";
$result = mysql_query($sql);

//$num= mysql_num_rows($query);

while ($row = mysql_fetch_array($result)) 
{
    echo $artist."-".$song."<br>";
}

mysql_close();
?>

will this work

[kratsg]

Heres some security tips...

 

1) mysql_real_escape_string wont help you if you don't wrap the variable with quotes, (',")..

2) mysql_real_escape_string does not escape wildcards like % and _, which you are using.

 

 

/**
do mysql connecting...
**/

$columns = array('artist', 'album', 'song');

if( !in_array($_POST['column'], $columns) ) {
$_POST['column'] = 'artist'; // Or a nother default value
}

$_POST['keyword'] = mysql_real_escape_string(addcslashes($_POST['keyword'],'%_'));
$resource = mysql_query("SELECT * FROM lyrics_db WHERE {$_POST['column']} LIKE '%{$_POST['keyword']}%'");
/**
continue on with query & display
**/

 

 

This sounds like utter nonsense. mysql_real_escape_string is a miracle, it filters all USER INPUTS to make them safe for querying, the % are wildcards that are used inside the query, not inside the variable. And you don't even need to check if a column is a valid column in the database, just use the

 

mysql_query("BLAH") or die(mysql_error());

 

To cover up any problems. Here's my fix on your code, it seems like a lot of concatenating to me.. Really. The only problem was the columns you were selecting in the query, were not the columns you wanted information from, try this code instead (FIXED?)

 

<?php

$username="db-------4-------";
$password="-G---X----b";
$host="db----.-----.net";
$database="db---2-4----6-7--";
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$column = mysql_real_escape_string('artist');
$keyword = mysql_real_escape_string('avenged');

$blah = "SELECT id,artist,lyrics,album,user,email,website,song FROM lyrics_lyrics WHERE $column LIKE '%$keyword%' ";
$query = mysql_query($blah) or die(mysql_error());

while(list($id,$artist,$lyrics,$album,$user,$email,$web,$song) = mysql_fetch_array($query)){

echo $artist."-".$song."<br>";
}

mysql_free_result($query);
?>

 

I removed a lot of the concatenating I saw in there, modified the while() statement so it auto-listed the array in the corresponding variables (that's the gist of the list() function!)