Help with using variable in code.

[ShadyTyrant]

I am trying to refactor some code that selects a category from a database and then displays the listing on a website. Basicly it looks like this:

if($_GET['category'] == 'All') {
    $results = mysql_query("SELECT * FROM members ORDER BY company ASC");
}
elseif($_GET['category'] == 'Alterations') {
    $results = mysql_query('SELECT * FROM members WHERE category1="Alterations" OR category2="Alterations" OR category3="Alterations" OR category4="Alterations" OR category5="Alterations" ORDER BY premium DESC, featured DESC, CASE WHEN featured = 1 THEN RAND() ELSE company END ASC');
}
elseif($_GET['category'] == 'Apparel') {
    $results = mysql_query('SELECT * FROM members WHERE category1="Apparel" OR category2="Apparel" OR category3="Apparel" OR category4="Apparel" OR category5="Apparel" ORDER BY premium DESC, featured DESC, CASE WHEN featured = 1 THEN RAND() ELSE company END ASC');
}
elseif($_GET['category'] == 'Bachelor') {
    $results = mysql_query('SELECT * FROM members WHERE category1="Bachelor" OR category2="Bachelor" OR category3="Bachelor" OR category4="Bachelor" OR category5="Bachelor" ORDER BY premium DESC, featured DESC, CASE WHEN featured = 1 THEN RAND() ELSE company END ASC');
}

... (there are a ton of categorys)

I am trying to replace the code above (100s of lines) with something like the following:

if(isset($_GET['category'])) {
    $category = $_GET['category'];
    $results = mysql_query('SELECT * FROM members WHERE category1="{$category}" OR category2="{$category}" OR category3="{$category}" OR category4="{$category}" OR category5="{$category}" ORDER BY premium DESC, featured DESC, CASE WHEN featured = 1 THEN RAND() ELSE company END ASC');
}
else {
    $results = TRUE;   
}

However when I run the above code nothing gets echoed to the webpage. Can anyone shed some light as to why this might be? (I relize I have not included the code that echos to the page but it runs fine with the first code snipbit I posted).

[diafol]

This sounds as though you may have a schema issue. A change in the number of allowed categories will change the table structure, which means that you need to change the php. Not easily maintainable - expect problems.
I'm assuming the following...

members

member_id (PK, int) | ... | category1 (varchar) | category2 (varchar) | ...

The fact that the category fields are textual is also an issue.
Consider normalizing by creating a linked tables for categories and for member_categories.

There are a number of ways in which you could do this. Probably the easiest would be...

categories

cat_id | label

[The above is v. useful for creating dropdowns or checkboxes in html]

member_categories

mc_id | member_id (FK) | category_id (FK) | ranked (optional - only if you need the order of categories)

data example...

1 | 45 | 7 | 1
2 | 34 | 8 | 2
3 | 2 | 2 | 3
4 | 61 | 5 | 4

So...

SELECT m.*, c.label FROM members AS m INNER JOIN member_categories AS mc ON m.member_id = mc.member_id INNER JOIN categories AS c ON mc.category_id = c.category_id ORDER BY m.surname, m.firstname, mc.ranked

Ok, downside is that you get multiple records for each member, but this can be sorted with GROUP_CONCAT...

SELECT m.*, GROUP_CONCAT(c.label) FROM members AS m INNER JOIN member_categories AS mc ON m.member_id = mc.member_id INNER JOIN categories AS c ON mc.category_id = c.category_id GROUP BY m.member_id ORDER BY m.surname, m.firstname, mc.ranked

Alternatively, if you have less than 32 categories (and always will do) and order of categories is not important, you could use a bitwise operator, but that's another story.

[Atli]

Can anyone shed some light as to why this might be?

While I wholeheartedly agree with diafol's advice, and strongly recommend you follow it, the reason why your code is failing is actually quite simple; a basic misunderstanding of how PHP processes strings. You can read all about how that works in the manual entry on Strings.

To put it simply:

$var = "world!";

// Single-quoted strings do not process variables injected into them. 
// So this will print: "Hello, {$var}"
echo 'Hello, {$var}';

// Double-quoted strings will, however, process injectd variables.
// So this will print: "Hello, world!"
echo "Hello, {$var}";

You are using single-quoted strings to create your SQL queries, which means that when you inject the variable, the literal name of the variable will be used, not it's value. You need to change it to a double-quoted string or concat the values by closing the string, appending the variable, and then opening the string again.

It's also worth mentioning that in MySQL queries strings should be quoted using single-quotes, not double-quotes. You can sometimes get away with using double-quotes, but whether that works depends on the configuration of the server. So to be safe, alwasy use single-quotes.

[harry_2]

Hello Friends,
Please tell me why we use mysql_query.
What is it's work?

[minitauros]

It queries a MySQL database and, for example, fetches the data you need, or updates it, or deletes it, or creates it. If you need help to start with PHP or MySQL, I guess you'd be better of starting a new topic than asking your questions in this one :).

[ShadyTyrant]

Wow exactly what I needed! Both a very learned answer, and a simple one to fix my imediate problem. Thanks so much both Atli & Diafol.