Member Avatar for HansK

Hi there, most interesting forum and I hope I can get some help here.

I have 2 record sets, one which looks up a product by SKU and the table contains a field with "related SKU's" in a comma separated text field.

I have created a Var with the "related SKU's" field and need to do a second query where I get all the related products. So far I have been unsuccessful using the "implode" function and just cannot get it to work, I keep getting all sorts of errors.
The query I am using is as follows:

RecProd=$row_Rs_recProd['PR_UpSales'];


$colname_RsShowItem = $RecProd;

}
mysql_select_db($database_Conn_PDG, $Conn_PDG);
$query_RsShowItem = sprintf("SELECT * FROM PRODUCTS WHERE PR_SKU in %s and PRODUCTS.PR_IsInactive=0", GetSQLValueString($colname_RsShowItem, "text"));
$RsShowItem = mysql_query($query_RsShowItem, $Conn_PDG) or die(mysql_error());
$row_RsShowItem = mysql_fetch_assoc($RsShowItem);
$totalRows_RsShowItem = mysql_num_rows($RsShowItem);

As it stands, the above gives me the 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 ''PROF,45,48,49,4,3,11' and PRODUCTS.PR_IsInactive=0' at line 1

The variable $RecProd returns a comma separated list like: PROF,45,48,49,4,3,11 which are the related product's SKU, which I need returned.

I did see some other examples of such queries, but if I use the code:

$RecProd=$row_Rs_recProd['PR_UpSales'];
$RecProd= implode("','", $RecProd);

I get an error:

Warning: implode() [function.implode]: Invalid arguments passed in recommended-items.php on line 49
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 'NULL and PRODUCTS.PR_IsInactive=0' at line 1

Any help with this would be most welcome.

Thanks

  • 2 Contributors
  • 4 Replies
  • 130 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by HansK
Member Avatar for pritaeas

You query should look like this:

SELECT * FROM PRODUCTS WHERE PR_SKU in ('PROF','45','48','49','4','3','11') AND PRODUCTS.PR_IsInactive=0

That would mean that you need the following code:

$RecProd=$row_Rs_recProd['PR_UpSales'];
$in_array = explode(',', $RecProd);
$RecProd= "('" . implode("','", $RecProd) . "')";
Member Avatar for HansK

Thanks priteas.

I tried this, but the eventual result of the var $RecProd is empty when I tested it. Is there a step missing? There seems to be no additional definition of $in_array for the 3rd line.

Maybe I am missing something, but I did use the suggested code to pass the new "var" to the query, and got an 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 ''(\'\')' and PRODUCTS.PR_IsInactive=0' at line 1

I also created a new page with the code, up to line 3 and then wanted to see what was generated and all I got was ('').

I would really appreciate any help with this as I need to try and complete this display of items.

Many thanks

Hans

Member Avatar for pritaeas

My bad

$RecProd=$row_Rs_recProd['PR_UpSales'];
$in_array = explode(',', $RecProd);
$RecProd= "('" . implode("','", $in_array) . "')";

$query_RsShowItem = sprintf("SELECT * FROM PRODUCTS WHERE PR_SKU in %s and PRODUCTS.PR_IsInactive=0", $RecProd);
Edited by pritaeas because: n/a
Member Avatar for HansK

Great, thanks, that works a treat. Much appreciated

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.