Help with LIKE syntax

maxwell_

I have a web page that allows the user to pick a method of filtering the rows of a recordset.
One of the search methods is choosing a key word from a drop down menu.
Once selected the recordset is filtered showing only the rows that have the id of the chosen keyword.
The way I have done this, although I know there must be better ways, is to setup a table with 2 columns
containing a list a list of keywords and keyword_id(s)
My main table has lots of columns, containing the information that I want to display, including one called keyword_id.
In this field I have placed keyword_id(s) relevant to that row, each separated with a space, one row could have 2 4 7 or 1 2 4 6 7 8

Currently when I choose the option from the keyword menu say 'Writing' which has an keyword_id of 2 the rows filter
according to the first keyword_id in the field. All rows with keyword_id(s) beginning with 2 will show.
What I need is to show all rows with a 2 somewhere in the keyword_id field!

This is the code that makes up my drop down menu

$query_rs_level = "SELECT key_id, `keyword` FROM `keywords` ORDER BY `keyword` ASC";
$rs_level = mysql_query($query_rs_level, $abe) or die(mysql_error());
$row_rs_level = mysql_fetch_assoc($rs_level);
$totalRows_rs_level = mysql_num_rows($rs_level);


<select name="softwareLevel" id="softwareLevel">
        <?php
do {  

?>
        <option value="<?php echo $row_rs_level['key_id']?>"><?php echo $row_rs_level['keyword']?></option>
        <?php
} while ($row_rs_level = mysql_fetch_assoc($rs_level));
  $rows = mysql_num_rows($rs_level);
  if($rows > 0) {
      mysql_data_seek($rs_level, 0);
	  $row_rs_level = mysql_fetch_assoc($rs_level);
  }
?>

This is the code that filters the rows based on the chosen keyword

//show software titles filtered by keyword (using Form POST variable)
$query_rsTitles = "SELECT id, title, company, `description`, resources, location, url, image, keyword, copies FROM titles WHERE titles.keyword_id = ". GetSQLValueString($_POST['softwareLevel'], "int") ." ORDER BY id ASC";

I'm sure I need to include LIKE in the query, something like

SELECT id, title, company, description, resources, location, url, image, keyword, copies FROM titles WHERE titles.keyword_id LIKE ". GetSQLValueString($_POST['softwareLevel'], "int")
</select>

johanafm

maxwell@;10988405 wrote:
My main table has lots of columns, containing the information that I want to display, including one called keyword_id.
In this field I have placed keyword_id(s) relevant to that row, each separated with a space, one row could have 2 4 7 or 1 2 4 6 7 8

This breaks one of the Normal Forms (NF). Iirc, it's First NF (1NF), the lowest level of database normalization, you break by storing compound values in one field. Wikipedias article on database normalization is a good read for an intro to this topic.

What you need to do is create a new table with two fields, one being the primary key from your titles table, the other being primary keys from your keyword table. So instead of having

table: titles
id     software_id   other stuff
1      1 5 6 9

You'd have
table: titles
id   other stuff
1

table: title_keywords
title_id  keyword_id
1		1
1		5
1		6
1		9

You really should read up on database normalization, but there are a few things I'd like to point out straight away, since there are several issues with your approach that will be hard to handle.
With your present approach, let's say you have

id     software_id   other stuff
1      2 13 24 35

and write a query such as you asked for help with, i.e.

SELECT ... FROM titles WHERE sofware_id LIKE '%3%' OR LIKE '%5%'

First off, using a % at the beginning of your string literal means string indexing can't be used, and this slows down the query considerably. Secondly, with this approach, you will not get what you want, since the 3 match the threes in both 13 and 35, and the 5 match the five in 35. So you'd need to modify your code to include whitespaces

... LIKE '% 2 %'

which will NOT match the 2 in 24, since there's no whitespace following the 2. However, it's not matching the first 2 either, since there is no whitespace before the first character. Which you could solve by storing a whitespace before all ids and one after all ids, or resort to regexp matching instead.
Compare all of these issues with one simple join and a very easy where clause

SELECT ... FROM ...
INNER JOIN title_keywords tk ON title.id = tk.title_id
WHERE tk.keyword_id = 2

The second issue regards updates. Let's say you want to delete id 24. How do you do it? Retrieve all rows containing 24 in the string, process all of these rows programatically by parsing the string and splitting it up in everything before 24 and everything after 24, concatenating these parts together and then updating the table. I get tired by just writing what you have to do.
Compare that to

-- Remove the keyword from all titles
DELETE FROM title_keywords WHERE keyword_id = 24;

-- Remove the keyword from a title with a specific id
DELETE FROM title_keywords WHERE keyword_id = 24 and title_id = 1;

-- Remove the keyword from a title found by some other means
DELETE FROM title_keywords tk
INNER JOIN titles ON tk.title_id = title.id
WHERE title.something = 'some_value' AND tk.keyword_id = 24

maxwell_

Fantastic!
I have created a new table title_keywords with columns title_id and keyword_id

Using the query bellow -

$query_rsTitles = "SELECT id, title, company, description, resources, location, url, image, keyword, copies FROM titles INNER JOIN title_keywords tk ON titles.id = tk.title_id
WHERE tk.keyword_id = ". GetSQLValueString($_POST['softwareLevel'], "int") ." ORDER BY titles.id ASC";

programme now working exactly they way I want it to!

The part title_keywords tk does this mean that title_keywords will be known as tk from that point onwards?

Also, I read wikipedia article on normalization, is a good introduction to a subject,
which I will research a lot more.

Many thanks.

Regards Maxwell

johanafm

maxwell@;10988450 wrote:
The part title_keywords tk does this mean that title_keywords will be known as tk from that point onwards?

Exactly. It's called aliasing and can be used for any identifier such as table or field names. You may also use the keyword AS which I left out

title_keywords AS tk

It can be helpful to shorten long identifiers, but is especially helpful when using aggregate functions

COUNT(*) AS count, SUM(price) AS total

and is needed when you have identifier collisions. For example

SELECT table1.id, table2.id

would need one of the identifiers aliased to something other than id if you want to retrieve the resulting rows in php as associative arrays, since otherwise only one will be able to exist as

$row['id']

naomi105813

The second code,I tried it,but it doesn't work.