Searching My Database

Help me out.

[skynyrd]

I'm trying to make a search page on my site and need some help.
So far I have a page with a search box titled keyword that posts to my search.php page.
I want the results to come out Artist Matches and Song Matches and the search to look for the keyword in both database columns.

So far my script is:

<?
$username="root";
$password="*****";
$database="tabs";
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = "SELECT * FROM tabs WHERE $_POST['keyword'] IN ('songname','artistname')";
mysql_query($query);

I don't know if that is right and how to write the information to HTML, please help.

[dkin]

this is the query I would usually use

$key = '$_POST['keyword']';

$result = mysql_query("select * from table where column regexp '$key'", $connect) or die ("query 1: " . mysql_error());

$mysql_num = mysql_num_rows($result);

if ($mysql_num >= 1)
{echo 'query successful, display results in a loop';}
else
{echo 'query failed, post for more help.';}

try that and let me know how it goes.

[skynyrd]

it did work except i added

$result = mysql_query("select * from tabs where songname regexp '$key' OR artistname regexp '$key'") or die ("query 1: " . mysql_error());

so it will search both the SONGNAME and ARTISTNAME column

now i want to display info of the results that match in a table that has columns for Song, Artist, Version, Guitar Type, Tab Type
so how would i go about doing that?

[dkin]

try something like this

while ($row = mysql_fetch_array($result))
{
echo $row['song'].''.$row['artist'].''.$row['version'].''.$row['guitar'].''.$row['type'].''.$row['tab type'];

}

just make sure that the variables are all correct(names of your columns) and that should work for you.

need anything else let me know.

Dylan

[skynyrd]

it works!
thank you so much
but i want to order it so if i searched for a song called ABC it would go
the numbers are the version so basically i want it sorted by artist/song/version in that order

ArtistA ABC 1
ArtistA ABC 2
ArtistA ABC 3
ArtistB ABC 1
etc...

[skynyrd]

i got the order on my own
but now i need to make it say something when the search comes back with no results...

[ergophobe]

if (!mysql_num_rows($result))
{
echo "Sorry Charlie";
}

[skynyrd]

How could I make a message come up if no keyword is entered instead of an error?

[dkin]

if ($key == "")
{
echo 'No search term entered.';
}

this may work too, I am not sure what is better code though

if (empty($key))
{
echo 'No search term entered.';
}

[skynyrd]

Thank you!
Now when someone clicks a search result they are taken to a link with an view.php?id=
This shows them all the information in that row
Now I added a column named views
I want the view to increase by 1 when someone looks at the page

[ergophobe]

I am not sure what is better code though

If you initialize the variable to "" at the top of the script so it has a known value in all cases, then both are equivalent and #1 saves you a function call.

If you do not initialize the variable you are counting on PHP default values and will get an undefined variable notice if you display notices; I prefer not to count on default values.

[skynyrd]

Thank you!
Now when someone clicks a search result they are taken to a link with an view.php?id=
This shows them all the information in that row
Now I added a column named views
I want the view to increase by 1 when someone looks at the page

[skynyrd]

^^^About my question above...
So far I have:

<?
$username="root";
$password="*****";
$database="tabs";
$key = $_GET['id'];

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$result = mysql_query("select * from tabs where id regexp '$key'") or die (mysql_error());

$row = mysql_fetch_array($result);

what would i use to change the value of the views column for the id that is being displayed?

[dkin]

$sql = "UPDATE tabs SET views = '$row[views]' + 1 WHERE id = $id";

Something along those lines should get it done for you.

Let me know, Dylan

[skynyrd]

$username="root";
$password="*****";
$database="tabs";
$key = $_GET['id'];

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$result = mysql_query("select * from tabs where id regexp '$key' UPDATE tabs SET views = '$row[views] + 1' WHERE id = $id") or die (mysql_error());

$row = mysql_fetch_array($result);

$sql = "UPDATE tabs SET views = '$views' WHERE id = $id";
mysql_query($sql)

its not working...

[dkin]

Im not exactly sure what your trying to do here

$result = mysql_query("select * from tabs where id regexp '$key' UPDATE tabs SET views = '$row[views] + 1' WHERE id = $id") or die (mysql_error());

$row = mysql_fetch_array($result);

$sql = "UPDATE tabs SET views = '$views' WHERE id = $id";

you have the update script input twice.

Im not sure if this will work, but just cut out the 1st update script like so

$result = mysql_query("select * from tabs where id regexp '$key'") or die (mysql_error());

$row = mysql_fetch_array($result);

$sql = "UPDATE tabs SET views = '$views' WHERE id = $id";

Also, here

"select * from tabs where id regexp '$key'"

I dont see people searching your site for the id of the item, I may be wrong but just to bring this to your attention, may want to change the column name to query.

[skynyrd]

let me explain
the search returns links that you can click to go to that page with the corresponding row

i basically want it to show the information and then increase the number in the views column by 1

that was a typo having the UPDATE string twice

[skynyrd]

This is what I have so far and it is not working:

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$result = mysql_query("select * from tabs where id regexp '$key'") or die (mysql_error());
$row = mysql_fetch_array($result);
$newview = $row['views'] + 1;
$sql= "UPDATE tabs WHERE id = $key SET views = $newview";
mysql_query($sql);

[skynyrd]

So far this will work until the:
where id = $key
part is added and without that part it updates every single row in the database

any ideas?
here the script again:

<?
$username="root";
$password="*****";
$database="tabs";
$key = $_GET['id'];
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$result = mysql_query("select * from tabs where id regexp '$key'") or die (mysql_error());
$row = mysql_fetch_array($result);
mysql_query("UPDATE tabs where id = $id SET views = views+1");
?>

[dkin]

Use this script, it should work for you

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$result = mysql_query("select * from tabs where id regexp '$key'") or die (mysql_error());

$row = mysql_fetch_array($result);
$newview = $row['views'] + 1;

$sql= "UPDATE tabs SET views = $newview WHERE id = '$key'";
mysql_query($sql);

If not please respond and I will see what I can do.

[skynyrd]

I tried that before.
Thanks for your help but I figured it out.
In case you were wondering, here is the script that works:

$result = mysql_query("select * from tabs where id regexp '$key'") or die (mysql_error());

$row = mysql_fetch_array($result);

$query = "UPDATE tabs SET views = views + 1 " .
"WHERE id = '$key'";
mysql_query($query);

mysql_close()
?>

Now I need a script that displays the last 20 entries in the database... I know what the search things would be but how would I limit the display to only 20?

[dkin]

$result = mysql_query("select * from tabs where id regexp '$key' limit 20") or die (mysql_error());