PHP With MySQL help... spaces or non alphabet chracters causing problems

[Mr Rich UK]

I'll try to explain this the best I can.

 

Basically I have a site which is for a film company.  I have a table for directors, a table for categories the directors come under, then a table for the films.

 

Now, I really should have done this with joins, I know... but I had to do this quickly with limited knowledge.  Up until now, it works perfectly fine for what I need it to, but I have come across an issue.

 

The categories are listed down the right hand side of the page, by calling this script every page you go to (It's not the full script, but you get the idea)

 

echo '<tr><td width="144px align="center" bgcolor="#f0f0f0" onmouseover="bgr_color(this, \'#f0cb59\')" onmouseout="bgr_color(this, \'#f0f0f0\')">
<a href="category.php?category_id=' . $row['category_id'] . '&category=' . $row['category'] . '">' . $row['category'] . '</a></td></tr>';

 

So as you can see, when you see a category written there, if you click on that it goes to the category.php page, using the category_id and the category name, for example ACTION, to populate the next page.

 

So on next page, I do the usual if setup for example $category_id = $_GET['category_id']; and $category = $_GET['category']; so it knows which category it's looking for.  The reason I have done it this way is also I have to populate pages further on down the line... I won't go into it now.

 

The problem I'm having is that I used to have two categories "FOOD" and "TABLETOP" (my categories are always written in capitals, server is case sensitive too).  Now they want to have "FOOD & TABLETOP"

 

When I changed the columns in the relevant tables to read FOOD & TABLETOP instead of just FOOD, this is the error I get back:

 

Sorry it cannot be displayed.

 

Unknown column 'FOOD' in 'where clause'

 

Query: SELECT director_id, shortname, fullname, linkname, pic1 FROM directors WHERE FOOD = 'y' ORDER BY fullname ASC

 

So it doesn't recognise the & TABLETOP at the end.  I tried doing the category as FOOD/TABLETOP, and it still gives the same error.  The problem is not in the information coming through on the if clause at the beginning of the script, it's getting it from the database.

 

This is the query that seems to be the problem:

 

$query = "SELECT director_id, shortname, fullname, linkname, pic1 FROM directors WHERE $category = 'y' ORDER BY fullname ASC ";

 

Because basically in the director's table, there are a number of columns, all called for example FOOD & TABLETOP so that if I stick a y in one of those columns, it means they appear in the FOOD & TABLETOP page etc.  Anyway, the problem is where it is saying WHERE $category = 'y' it is only bringing up FOOD and not FOOD & TABLETOP.  How do I get it to recognise the whole FOOD & TABLETOP in this query?

 

As I said before, I have tried FOOD-TABLETOP, FOOD/TABLETOP, FOOD+TABLETOP as the category, doesn't work.  FOODTABLETOP does, but that doesn't look right.

 

[sader]

can u show table structure?

[PFMaBiSmAd]

You need to use urlencode() when you put data into a URL that contains characters that are not permitted as part of a URL - http://us.php.net/urlencode

[Mr Rich UK]

You need to use urlencode() when you put data into a URL that contains characters that are not permitted as part of a URL - http://us.php.net/urlencode

 

It's not the problem of the data in the URL.  When say for instance FOOD & TABLETOP are to be used as part of the URL, that works as if you go to the next page FOOD & TABLETOP is part of the url.

 

The problem is that the $category in the query which is supposed to be FOOD & TABLETOP in the WHERE clause doesn't work.  Underscores work, hyphens, slashes, space, anything between FOOD and TABLETOP means the query won't work.

 

My table structure:

 

I have a table called directors

 

This has stuff like, director_id, biography (various crap) and then it has columns which are the categories "ACTION, FOOD, COMEDY" etc.  If a director has a showreel for a category, I put a why in this field.

 

I have a table called films

 

This has fields like film_id, title, url, width, height, then again it the ACTION, FOOD, COMEDY columns.  I put a numerical value in these if a film is to appear in a showreel, this value denotes where it will be placed (1 through to 10)

 

Then lastly I have a table called categories

 

This has the field category_id, category, director 1, director 2, director 3

 

So I put the Category name in the category field, then in the director fields, names of the people who have reels in this category.

 

The site can be seen here:  http://www.freshfilmprod.com

 

So it constantly references the first name of a director and categories to cross-reference when calling up the biography page of a director, which directors have showreels in particular categories, which showreels a director should have (when you look at any page of his/her work there is a sub menu).

 

This is probably not at all how I should have done this site, but the problem I have is now that I want to add a category with a space in it, or any other character apart from an underscore, the query to find the category doesn't work.

[PFMaBiSmAd]

The URL might look correct, but because it is broken by spaces and the &, all you are receiving when you click on the URL is the first word (anything up to a 'stop' character.) I was going to suggest echoing your query to see exactly what is in it, but you can see from the error message that your data only consists of the first word because the data in the URL has not been passed through urlencode()

 

Also, if you actually have column names that contain anything other than alphanumeric characters (in the current character set) or an underscore or a dollar-sign, you must enclose those column names within back-ticks ` ` in every query so that syntax of the query is not broken. If you have column names with spaces, &, and commas, you must use back-ticks in the query.

[Mr Rich UK]

I had solved this already (in a very roundabout way) just now, until I read what you said.... completely forgot about the backticks.

 

Tried it with the backticks and worked a treat.

 

Thanks for the help.