search mysql for result like query string.

[secoxxx]

Ok, once again im stumped, dont know if its my code, which im sure it is.

 

i have a query string like so:

starring.php?tag=bob

 

in mysq i have a field named starring with data in it like so:

 

joe, jim, rob, bob, gary

 

another one like

 

kim, arnold, gary, jimmy, arron

 

 

 

what would i do to only pull the results with bob into the page?

[marcus]

$sql = "SELECT * FROM `table` WHERE `field` LIKE '%".$tag."%'";
$res = mysql_query($sql) or die(mysql_error());

[secoxxx]

Thank you for the quick reply, but that seems to returning all and not singled out to just whose name is in the query string, any idea?

[MasterACE14]

use the WHERE clause.

 

<?php
$sql = "SELECT * FROM `table` WHERE `name`='" . $_GET['tag'] . "' ";
$res = mysql_query($sql) or die(mysql_error());

 

Regards ACE

[secoxxx]

Hey MasterACE14, thats what i used to start and nothing is pulled from the db, I don't understand whats going on.

[mmarif4u]

Can you show us your code.

[gizmola]

Hey MasterACE14, thats what i used to start and nothing is pulled from the db, I don't understand whats going on.

 

So first off, disregard MasterACE14's reply, it's sorry to say just plain wrong.

 

Mgallforever's answer was correct, so if that's returning too many results it's because you have some problem with your code.  What it boils down to is use of mysql LIKE, which allows you to do wildcard searches against strings --- simple stuff.

 

Now here's the bad news.  Your design has implemented in your "starring" column, what in essence is known as a repeating group.  It may be beyond your current abilities to remedy this design flaw, but what you should have, is a seperate "star" table with one row for each star, and then a table between your movie (or whatever) table and star with *one row for each star* that is in that movie. 

 

You might ask why all that complexity is important.  Entire books have been written on the subject so I won't try and do more than to suggest you read up on database design and normalization.    In layman's terms however, not only would you not have to use a LIKE, but you also would have a design that would not degrade in performance the way yours will as your database gets large.  Unfortunately, when using a %LIKE% query as the one you need to find a name anywhere inside the list of names, mysql can no longer utilize an index.  So your query will result each and every time in a "table scan" where mysql will have to read the entire table from top to bottom -- and look in every row, scanning through it to find if the substring you are looking for is inside the list of names in the starring column.

 

This may not be anything of consequence to you, as your database may not be very large, or your site may never have enough users to make this a viable concern to you, even though your design isn't relationally correct.

[secoxxx]

Very useful info, so my reply to that is

 

Ill definitely have to look into this more. 

 

What if i made a new table and did something like

 

star_id

 

dvd_id

 

star_name

 

I would in return be able to pull the values into the dvd_starring results for the movie and at the same time with the search be able to pull in the dvd_id results from the query string, correct?