MySQL Count and PHP
 

Hey

1. Will $categorycount = mysql_query("select count(*) from games where catid = '" . $row['id'] ."'"); count the total rows in the games table but only if catid is $row['id'];

2. Do I need to do a mysql_fetch_array on the query or is there any other way I can show the count result?

Hey

1. Yeh that looks fine.

2. You can use mysql_result to return the first column of the first row in the result set.

Example:

Thanks for that Karl.

Thus using the result just gave you, you can also make your query a lot more safe. Even if you are a beginner. Though this does looks a lot better than when I first started out. Props on that one. First I should advise you NOT to use double quotes ("") but single quotes (''). Why? Since defining what ever in a query, it's wise to open a identifier with "'.$username.'". Thus the query will look like this:
First it looks a lot better, second, you can use `` at any given option in the query. Thus looking like this: On a final note, when it comes to the category listing of a specific item, use LIMIT 1 to shorten the code and result. (thus preventing the expanded use of mysql_free_result()) Next to that, use the tablenames instead of *. :)

Although it is a bit off topic, it still might help you.

Good luck crazyryan!

Thanks for the advice, I have one more problem.

My site uses mod_rewrite on the category pages and I want to apply pagination, will that be a problem? My .htaccess code is RewriteRule "^category/([0-9]+).html$" category.php?id=$1 [Last]

And my PHP code is
I'm not sure if this will conflict if I use pagination and what not?

Any help?

I'm not all too fond of .htaccess files, so I've chosen to stick to the server-side programming PHP and mySQL. So maybe you should wait for a confirming reply from a little bit more experienced scripter than me. For example, Karl, Salathe of Wildhoney.

On to the posted PHP field. I've checked it our and it should not pose any problem. I reckon it's for some kind game browser or progress page? I'm very fond of those scripts, so if you could share a little bit more, that would be very graceful.

Anyways, returning to the pagination part. There, since you said you were kind of a beginner, are a few ways to do pagination. Through classes or simple methods like $_GET[page] and LIMIT in the query. Simply use the TOTAL of results to generate a page.

For exmaple, you've got a limit of 20 results a page.

LIMIT 0 20
LIMIT 20 40
LIMIT 40 60
LIMIT 60 80

And so on. Now how do you get it dynamic? If NO $_GET is defined, LIMIT is simply 0 to $max ($max = 20;)
Then, if get is set, you can retrieve it (like $_GET[page] is 20), then you simply add it to $new = $_GET[page] + $max.
LIMIT '.$_GET[page].' '.$new.' :)

It should NOT pose any conflicts since the while loop simply echo'd what you retrieve, thus you choose WHAT to retrieve with the LIMITS.

Make sure to build in simply checks like if (!is_numeric($_GET[page])) {
exit('trying to be a h4xxOR!?');
}

DONE.

Good luck! Hopefully you kinda understand my jibberish up there.

Can you explain how that makes the query 'more safe'? Or why it's wise to format the string that way?

I agree with Karl, with the exception of using quotes around the `catid` field. If it's an INT type column you should forgo using quotes as if it were a string, e.g.
INT or FLOAT/DOUBLE etc columns should not be using quotes.

Always single quote your variables, otherwise injection is easy (even if cleaned). For instance
Even if you clean that, the injection will work, now if you change it to
The hacker would have to put a single quote to put a command in, the single quote would be escaped. Turning the query into
All types must be quoted, mysql knows how to deal with it.

That entirely depends on the method used to 'clean' the incoming data. Any decent filtering method will take this situation into account and protect your queries -- Village Idiot, if your current cleaning method does not cater for this situation then it's time to rethink things. Inventing a rule to wrap everything in quotes is just putting a band-aid over the problem rather than treating the injury properly.

String values should be wrapped in quotes; integer, double, etc. columns should not be wrapped in quotes. It's just good SQL.

If you don't mind me asking, what does r stands for?

Just an educated guess, maybe results?

Thanks for that, it's a really good point; I forget not everyone is conscientious enough (or aware) to properly filter and validate data prior to use. Ok, if you've already validate, filtered, escaped your data, don't surround INT values in quotes. :-D

Should use p instead which stands for pointer otherwise face the wrath of Adam. :-P

http://www.talkphp.com/showpost.php?p=3956&postcount=23

After doing allot of looking around on google regarding that, here are basic ways of doing this and their problems (please correct me if I missed something)

1. Check all numeric/floating/ect. before running the query with is_numeric, die with error if not numeric.
Problem: Future developers may not be so wise to catch on to what you are doing. You may also forget once.

2. Have another cleaning function for expected numeric variables
Problem: Same as 1

3. Quote everyting
Problem: Same as 1 and 2, but much simpler. It is the difference of 2 characters opposed to a different or extra function.

Its not bad SQL, as taken from http://dev.mysql.com/doc/refman/5.0/...uidelines.html

Right, BUT. What is allowed in MySQL isn't necessarily going to follow ANSI SQL or standardized 'best practices'. In other words, saying "its not bad SQL" isn't the same as saying "well, MySQL let's you do this...". It's sort of like PHP and data types. PHP is very weakly typed, but forgiving and very easy to work with. There's a trade-off.

We could argue about it all night. If you want to wrap your INT type data in quotes, I won't report you. ;-)

According to the mysql docs, its the recommended way, its not just allowed.

Perhaps is not good otherSQL, I don't have any real experience in anything besides mysql.

I think it would be in everyone's best interest to at least try PDO

Why? Because it's very secure, you don't need to manually protect queries, it does it for you :-)

Also.. it's easier, I think ^^

You could try this:

RewriteRule ^category/([0-9]+)/page\-([0-9]+)\.html$ category.php?id=$1&page=$2 [L,QSA]

and have the following URLs:

/category/1/page-1.html

Of course you will need to work on the query to make the pagination work. Talk a look at this tutorial or this one (more advanced).

I agree; unfortunately it may not be installed by default on your host, whereas PEAR DB or MDB2 is likely already available. I deal with two web hosts, neither use PDO, but both have PEAR DB installed.