MySQL Count and PHP

crazyryan · 12-07-2007, 09:24 PM

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?

Karl · 12-07-2007, 09:36 PM

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:

PHP Code:


			
$pResult = mysql_query("select count(*) from games where catid = '" . $row['id'] ."'");
$iCount = mysql_result($pResult, 0);

crazyryan · 12-07-2007, 09:41 PM

Thanks for that Karl.

ReSpawN · 12-07-2007, 09:52 PM

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:

PHP Code:


			
$rSql = mysql_query('SELECT username FROM respawn_users WHERE username = "'.$username.'"');

First it looks a lot better, second, you can use `` at any given option in the query. Thus looking like this:

PHP Code:


			
$rSql = mysql_query('SELECT username FROM `respawn_users` WHERE `username` = "'.$username.'"');

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!

SOCK · 12-08-2007, 06:45 PM

Quote:

Originally Posted by ReSpawN

Thus using the result just gave you, you can also make your query a lot more safe. ... 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:

PHP Code:


			
$rSql = mysql_query('SELECT username FROM respawn_users WHERE username = "'.$username.'"');

First it looks a lot better...

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

Haris · 12-08-2007, 09:10 PM

Quote:

Originally Posted by ReSpawN

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:

PHP Code:


			
$rSql = mysql_query('SELECT username FROM respawn_users WHERE username = "'.$username.'"');

First it looks a lot better, second, you can use `` at any given option in the query. Thus looking like this:

PHP Code:


			
$rSql = mysql_query('SELECT username FROM `respawn_users` WHERE `username` = "'.$username.'"');

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!

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

bdm · 12-08-2007, 09:14 PM

Quote:

Originally Posted by Haris

r stands for?

Just an educated guess, maybe results?

Haris · 12-08-2007, 10:38 PM

Quote:

Originally Posted by bdm

Just an educated guess, maybe results?

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

[Tip] Variable Prefixes

crazyryan · 12-07-2007, 10:02 PM

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

PHP Code:


			
<?php
            
            $query = mysql_query("select * from games where catid = '$id' limit 0,10");
            while($row = mysql_fetch_array($query)) {
            $url = '/game/' . strtolower(str_replace('--', '-', str_replace(' ', '-', preg_replace('/[^\sA-Za-z0-9]+/', '', $row['title'])))) . '-' . $row['id'] . '.html';
                echo '<p style="margin-bottom: 10px;"><img src="' . $row['thumbnail'] . '" style="float: left; margin-right: 10px;" />
            <strong><a href="' . $url . '">' . $row['title'] . '</a></strong> - <small>Played ' . $row['plays'] . ' times!</small><br />
            ' . $row['description'] . '</p>';
            }
            ?>

I'm not sure if this will conflict if I use pagination and what not?

Any help?

Matt83 · 12-09-2007, 02:27 PM

Quote:

Originally Posted by 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

PHP Code:


			
<?php

            

            $query = mysql_query("select * from games where catid = '$id' limit 0,10");

            while($row = mysql_fetch_array($query)) {

            $url = '/game/' . strtolower(str_replace('--', '-', str_replace(' ', '-', preg_replace('/[^\sA-Za-z0-9]+/', '', $row['title'])))) . '-' . $row['id'] . '.html';

                echo '<p style="margin-bottom: 10px;"><img src="' . $row['thumbnail'] . '" style="float: left; margin-right: 10px;" />

            <strong><a href="' . $url . '">' . $row['title'] . '</a></strong> - <small>Played ' . $row['plays'] . ' times!</small><br />

            ' . $row['description'] . '</p>';

            }

            ?>

I'm not sure if this will conflict if I use pagination and what not?

Any help?

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).

ReSpawN · 12-07-2007, 10:14 PM

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.

SOCK · 12-08-2007, 06:49 PM

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.

PHP Code:


			
$pResult = mysql_query("select count(*) from games where catid = {$row['id']}");

INT or FLOAT/DOUBLE etc columns should not be using quotes.

Village Idiot · 12-08-2007, 07:16 PM

Always single quote your variables, otherwise injection is easy (even if cleaned). For instance

PHP Code:


			
 $var = "1 OR 1=1 --";
$query = mysql_query("SELECT * FROM `users` WHERE `id` = $var");
//the query is now
$query = mysql_query("SELECT * FROM `users` WHERE `id` = 1 OR 1=1 --");

Even if you clean that, the injection will work, now if you change it to

PHP Code:


			
 $query = mysql_query("SELECT * FROM `users` WHERE `id` = '$var'");

The hacker would have to put a single quote to put a command in, the single quote would be escaped. Turning the query into

PHP Code:


			
 $query = mysql_query("SELECT * FROM `users` WHERE `id` = '1 OR 1=1 --'");

All types must be quoted, mysql knows how to deal with it.

Salathe · 12-08-2007, 07:45 PM

Quote:

Originally Posted by Village Idiot

Always single quote your variables, otherwise injection is easy (even if cleaned). For instance ... Even if you clean that, the injection will work

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.

Village Idiot · 12-09-2007, 04:49 AM

Quote:

Originally Posted by Salathe

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.

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

Quote:

A common mistake is to protect only string data values. Remember to check numeric data as well. If an application generates a query such as SELECT * FROM table WHERE ID=234 when a user enters the value 234, the user can enter the value 234 OR 1=1 to cause the application to generate the query SELECT * FROM table WHERE ID=234 OR 1=1. As a result, the server retrieves every row in the table. This exposes every row and causes excessive server load. The simplest way to protect from this type of attack is to use single quotes around the numeric constants: SELECT * FROM table WHERE ID='234'. If the user enters extra information, it all becomes part of the string. In a numeric context, MySQL automatically converts this string to a number and strips any trailing non-numeric characters from it.

SOCK · 12-09-2007, 05:15 AM

Quote:

Originally Posted by Village Idiot

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.

SOCK · 12-08-2007, 10:20 PM

Quote:

Originally Posted by Village Idiot

Always single quote your variables, otherwise injection is easy (even if cleaned). For instance...

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.

Village Idiot · 12-09-2007, 05:30 AM

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.

Jay · 12-09-2007, 06:29 AM

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

SOCK · 12-09-2007, 05:48 PM

Quote:

Originally Posted by Jay

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

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.