Skipping Through MySQL Rows

tonyr1988

First, I know that there is another forum for database-related issues, but I think that my problem lies in my coding, so I put it here...if I'm wrong, feel free to move this to Database.

I have a database of products. They are split by team, category, and pages...I only want 14 items per page. Here is the part of my code giving me trouble:

$x = 1;
$pagenum = $HTTP_GET_VARS["pagenum"] - 1;
while($row = mysql_fetch_array($result))
    {
		if ($row["Team_"]==$HTTP_GET_VARS["team"] and $row["Category_"]==$HTTP_GET_VARS["page"]) { //Check to see if the item is the right team/category
			if ($x > $HTTP_GET_VARS["pagenum"] * 14) { //$x is the number of items in correct team/category - checks to see if it can start the page or not. ex) if it's page #2, makes sure that $x is at least 15
			    //Code for putting items into page template...taken out for your convenience. :-)
	            $x = $x + 1; //Move on to the next one.
            } else { //This is the right format, right?
            	$x = $x + 1; //If we're not at 15 (or whatever number) yet, add another one.
            }
		}
	}

If you have any questions at all, please ask.

The first page returns items like they should, but page #2 returns nothing (I have 17 or so items).

laserlight

Have you considered using WHERE and LIMIT in your SQL queries?

tonyr1988

Nuh uh, I'm a total n00b.

Where on the php.net site would I find information on WHERE and LIMIT? I'm swimming around the documentation and function references, but I can't find it...probably because I have no idea what I'm looking for. :queasy:

Wow - I suck.

tonyr1988

If I do this:

  	$query = "SELECT * FROM nfl
    	WHERE (team_ = $HTTP_GET_VARS[team])";
	$result = mysql_query($query);

Then I can't use:

while($row = mysql_fetch_array($result))

What's the alternative?

laserlight

That's SQL, not PHP.

For example, the mysql docs on mysql.com have something to say about them.

Then I can't use:

Of course you can.

tonyr1988

When I try to use it it returns:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\test.php on line 191

Could something else that I did cause this?

laserlight

That's because your query is malformed.

For example, you could use:

$query = "SELECT * FROM nfl
	WHERE team_ ='{$HTTP_GET_VARS['team']}'";
if ($result = mysql_query($query)) {
	while ($row = mysql_fetch_array($result)) {
		//etc
	}
} else {
	echo mysql_error();
}

Also, note that you should use $_GET['team'] instead of $HTTP_GET_VARS['team'], and you should check this variable with isset() or empty() first, and perhaps perform some validation to avoid SQL injection.

tonyr1988

Thank you very much laserlight!

I fixed my original problem (In the code that I hid, I had a break; if $x was over 15...so obviously none of the second page could be displayed - really stupid mistake), PLUS I got some tips on optimization and security!