CODEWITHSUNDEEP
phpmysqlpaginationlimit
Phil Penny
Phil Penny

Reputation: 123

Pagination only showing first page of products; after page 1, getting "Warning: mysqli_query(): Empty query" error

When I click on the next link or any page after page 1, I get the following error:

Warning: mysqli_query() [function.mysqli-query]: Empty query

I am currently trying to view the products by group_id, here is my query:

if (isset($_GET['grpid']) && is_numeric($_GET['grpid']) ) {
$grpid = (int) $_GET['grpid'];
if ($grpid > 0) { // Overwrite the query:
$q = "SELECT company.co_id, co_name, category.cat_id, cat_name, prod_name, prod_desc, prod_tag, prod_id, prod_img FROM company, product, category WHERE company.co_id = product.co_id AND category.cat_id = product.cat_id AND product.group_id = $grpid ORDER BY company.co_name ASC, product.prod_name ASC";
}

}

I think it may be something to do with this, because if I add in the group table to the query, by adding group.group_id, group_name after the SELECT clause and group.group_id = product_group_id after the WHERE clause, I get the following error:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group WHERE

I hope I have given enough information, my database is 4 tables:

product
  prod_id
  co_id
  cat_id
  grp_id
  prod_name
  prod_tag
  prod_desc
  prod_img
company
  co_id
  co_name
  co_img
grp
  grp_id
  grp_name
category
  cat_id
  cat_name

Update

OK my query is exactly this:

if (isset($_GET['grpid']) && is_numeric($_GET['grpid']) ) {
$grpid = (int) $_GET['grpid'];
if ($grpid > 0) { // Overwrite the query:
$q = "SELECT company.co_id, co_name, grp.grp_id, grp_name, category.cat_id, cat_name, prod_name, prod_desc, prod_tag, prod_id, prod_img FROM company, product, category, grp WHERE company.co_id = product.co_id AND category.cat_id = product.cat_id AND product.grp_id = $grpid ORDER BY company.co_name ASC, product.prod_name ASC LIMIT $start, $display";
}

}

which is then immediately followed by:

$r = mysqli_query($dbc,$q) or die("Error: ".mysqli_error($dbc));

Which is the line the error points to:

Warning: mysqli_query() [function.mysqli-query]: Empty query in

I am afraid I do not know how to print out the sql and feed it directly back in?

If I don't use pagination then all of the intended products do show; the problem occurs when I am on any page other than the first page.

UPDATE 2

If I set the query to run by commenting out the if tags surrounding it, the error message changes to this:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY company.co_name ASC, product.prod_name ASC LIMIT 5, 5' at line 1

Does this shed any more light on the subject?

UPDATE 3 - solved!

Simple simple problem in the end, many thanks to BugFinder and the others who got my brain thinking about the issue properly.

The problem was that I wasn't passing the variable through to the corresponding pages when creating the url, it read:

echo '<a href="browse_products.php?&s=' . ($start + $display) . '&p=' . $pages . '">Next</a>';

But by adding "grpid=1" to make it read:

echo '<a href="browse_products.php?grpid=1&s=' . ($start + $display) . '&p=' . $pages . '">Next</a>';

It has solved all of my problems!

THANK YOU GUYS, it is hard for a designer to think about programming the way programmers do, you have literally saved me days and days, great stuff!

Upvotes: 1

Views: 800

Answers (3)

Sinan
Sinan

Reputation: 5980

The reason your are getting Warning: mysqli_query() [function.mysqli-query] error is because the query is in if block and mysql_query() is outside.

Therefore I am guessing mysql_query() is called whether $q is defined or not. So when it is not defined you are getting Warning: mysqli_query() [function.mysqli-query]:

Upvotes: 1

evilone
evilone

Reputation: 22740

Try this:

$q = "SELECT c.co_id, c.co_name, cat.cat_id, cat.cat_name, p.prod_name, p.prod_desc, p.prod_tag, p.prod_id, p.prod_img FROM company AS c, product AS p, category AS cat WHERE c.co_id = p.co_id AND cat.cat_id = p.cat_id AND p.group_id = $grpid ORDER BY c.co_name ASC, p.prod_name ASC";

Upvotes: 1

BugFinder
BugFinder

Reputation: 17858

group is a reserved word which is why it would complain. try referencing it as group with the quotes.

Upvotes: 1

Related Questions