Separating tutorials into categories

[celox]

Hey there,

I've searched my *beep* off to get this to work but can't seem find anything that could help me out figuring out this problem. Of course I also tried different stuff on my own but my skills are just a tiny bit to low to get this to work on my own.

I have a tutorial listing on my site and have 2 categories, Adobe photoshop and CSS / xHTML. Now when I go to my main tutorial page I see the whole list of tutorials of both categories, I want these results to be separated into the categories.

So you'll have tutorials/category?id=1 (for adobe photoshop) and tutorials/category?id=2 (for css / xhtml).

Can I do this with the mysql_query?

This is my mysql_query so far:

$query = "SELECT * FROM tutorials ORDER BY id DESC LIMIT 10";

Any help is really appreciated!

Celox

[omoutop]

Well assuming you have a field named "category" in your tutorial table, you need to change your query to:

SELECT * FROM tutorials WHERE category=1

This will show all of the Photoshop tutorials you have in your table.

The Limit 10 option in your query will show you only 10 tutorials based on your ORDER BY clause (in this case the id record of the table in descending order, propably the 10 last tutorials).

Now, if you want it to make your query fully dynamic:
SELECT * FROM tutorials WHERE category='".$_REQUEST['category']."'

but you must make sure that the $_REQUEST['category'] contains valid data
Hope this makes sence.

[celox]

The first part is pretty easy, but I can't get the $_REQUEST['category'] part to work in my mysql_query.

Can't I just make a variable of $_REQUEST['category']

Like:
$cat_req = $_REQUEST['category'];

then make the query:
SELECT * FROM tutorials_tutorials WHERE category=$cat_req ORDER BY id DESC LIMIT 10

And will $_REQUEST['category']; get the?id=5 part out of the URI when typed in a browser?

[mcibor]

No, the?id=5 changes to

$cat_req = $_GET['id'];

Also apply some security measures:

$cat_req = (int)$cat_req;

Then you can use:

$sql = "SELECT * FROM tutorials WHERE category='$cat_req'";

1. Always filter external values
2. try not to use request, but $_POST, $_GET, $_COOKIE, $_SERVER, etc
3. filter values before pasting to db

Hope this helps
Michal

[celox]

Ok nice, got it to work.

Thanks!

[omoutop]

Yes you can use $my_cat = $_REQUEST['id']; (if you have your url look like tutorials/category?id=1)

And use this in your sql: Select * From {table} Where category='$my_cat' ORDER BY {field} Limit {number}

Remember to check if the data in the $my_cat are valid and legal

[celox]

@mcibor, Why shouldn't I use $_REQUEST? I'd like to have more info on that. And why do I have to filter values before sending them to the db?

Thanks

[jatar_k]

the problem with $_REQUEST

Variables provided to the script via the GET, POST, and COOKIE input mechanisms, and which therefore cannot be trusted. The presence and order of variable inclusion in this array is defined according to the PHP variables_order configuration directive.

so, conceivably someone could overwrite parts of that data depending on your variables_order which results in you not getting the expected data from the right place. It can cause confusion and it definitely causes a security issue.

If you are getting posted data then use $_POST
If you are getting data from the uri then use $_GET
If you are getting cookie data then use $_COOKIE

[mcibor]

And why do I have to filter values before sending them to the db?

OK. Let me put it into an example:
You have a table tutorials, where you want to pull a tutorial:
Tutorial on php is no 1, so you pull it with link
tutorial.php?id=1
then you get this variable and use it into db:

$sql = "SELECT * FROM tutorials WHERE category='".$_GET['id']."'"; //see that the $_GET['id'] is unfiltered variable, and in this example the query is:
die($sql); //SELECT * FROM tutorials WHERE category='1'

everything is fine untill user inputs:
tutorial.php?id=1';%20TRUNCATE%20tutorials;%20SELECT%20*%20FROM%20tutorials%20WHERE %20id<>'

then your query is:
SELECT * FROM tutorials WHERE category='1'; TRUNCATE tutorials; SELECT * FROM tutorials WHERE id<>''

and you find yourself without any data in tutorials

If your server doesn't allow quotes, then there is still a way around.
However simple
(int)$_GET['id'] will take care of that security breach
and in this example it will input correct tutorial on php on both links ;)

Hope this helps you understand how important it is to implement security on your page.
Regards
Michal

[jatar_k]

you could also look at this
PHP Security [webmasterworld.com]