Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

SQL works in myphpadmin but tep_db_query return error?


Sway

Recommended Posts

Hi,

 

I'm using PHP4 and mySQL 3.23.53a on my host.

I'm trying to build a dynamic table of manufacturers and whether they have a product in a category.

 

It would look like this:

MANUFACTURER1 MANUFACTURER2 ....

Category1 3 4

Category2 2 0

 

It will display the number of products in a category that a manufacturer has. I want to create this dynamically. I've got the SQL to work (using myphpadmin and even embedding it in the new_products.php page.

It returns the results I want but is also return a SQL bad syntax error message.

 

Here's the SQL (It works and after the error message does correctly display the data - but I don't want the error message?

 

$result2 = tep_db_query("SELECT `categories_name`, `categories_id` FROM `categories_description` WHERE 1 AND `language_id` = 1 ORDER BY `categories_name` ASC");

 

for ($x = 0; $x < mysql_num_rows($result2); $x++)

{

/* Print the Catgory (Program) Row Column Heading */

print"<tr></tr>";

$row = mysql_fetch_object($result2);

print"<td>" . $row->categories_name . $row->categories_id . "</td>";

 

/* Now Lets Print the Manufactures Cells for this Row by Category*/

$Catid = $row->categories_id;

print"<td>" . $Catid . "</td>";

 

$rowquery = "SELECT count(*) as pcount,

`products_to_categories`.`products_id`,

`products_to_categories`.`categories_id`,

`products`.`manufacturers_id`

FROM `products_to_categories` LEFT JOIN `products`

ON (`products`.`products_id` =

`products_to_categories`.`products_id`)

WHERE `products_to_categories`.`categories_id` = " .

$Catid . " Group by products`.`manufacturers_id`";

 

$result3 = tep_db_query($rowquery);

 

for ($x = 0; $x < tep_db_num_rows($result3); $x++)

{

$row3 = mysql_fetch_object($result3);

print"<td>" . $row3->manufacturers_id . " No. Prod = " . $row3- >pcount . "</td>";

}

 

}

 

 

Here's the error message displayed in the page:

1064 - You have an error in your SQL syntax near 'Group by `products`.`manufacturers_id`' at line 5

 

SELECT count(*) as pcount, `products_to_categories`.`products_id`, `products_to_categories`.`categories_id`,`products`.`manufacturers_id` FROM `products_to_categories` LEFT JOIN `products` ON (`products`.`products_id` = `products_to_categories`.`products_id`) WHERE `products_to_categories`.`categories_id` = Group by `products`.`manufacturers_id`

 

[TEP STOP]

 

BUT it correctly displays the rows fetched from results3?

tep_db_query is displaying an error message (I think it doesn't like assigned the $Catid variable in constructing the SQL) - But the mysql_fetch)object does return the expected rows and data?

 

HELP!

 

Thanks,

Sway

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...