Querying the Hikashop database tables

Hi,

I'm trying to write a custom PHP script that querys the Hikashop database and prints out the results in a list.

As an example I'm trying to target all orders of a certain product that are confirmed and then print the shipping address.

The problem is that I don't know how to target the shipping address only and with the code below I'm getting duplicate lines in the list (Presumably this is because it's printing all addresses associated with the order.)

I've managed to join the below tables together (using code from another forum post that I found) to allow me to search them but I think I should be selecting all from the order table rather than the address table. Could that be why I'm getting duplicate results?

How does the below need to change for my query to work correctly?

$result = mysqli_query($con,"SELECT * 
  FROM waw_hikashop_address
  JOIN waw_hikashop_order
  ON
  waw_hikashop_address.address_user_id=waw_hikashop_order.order_user_id
  JOIN waw_hikashop_order_product
  ON
  waw_hikashop_order.order_id=waw_hikashop_order_product.order_id 
  WHERE order_product_code
  LIKE `3_month_subscription` 
  AND order_status
  LIKE `confirmed`
  ");

while($row = mysqli_fetch_array($result))
  {
    $csv_line = $row['address_title'] . ", " . $row['address_firstname'] . ", " . $row['address_lastname'] . ", " . $row['address_post_code'] . ", " . $row['order_product_name'] . ", " . $row['order_payment_method'] . ", " . $row['order_number'] . ";";
    echo $csv_line;
  };

Hi there,

Here is what you should use:

$database = JFactory::getDBO();
$query = 'SELECT *  FROM '.hikashop_table('order_product').' as op LEFT JOIN '.hikashop_table('order').' as o ON op.order_id=o.order_id LEFT JOIN '.hikashop_table('address').' ON o.order_shipping_address_id=a.address_id WHERE order_product_code  LIKE `3_month_subscription` AND order_status LIKE `confirmed`';
$database->setQuery($query);
$rows = $database->loadObjectList('o.order_id');

Thanks for the info, I'll give that a go.

Regarding using the JFactory::getDBO() function, how do I get that to work if I'm just using a standalone PHP script without any ties to Joomla? Is there a way to tie in my script with Joomla so that I can make use of some of the Joomla functions such as that one?

Also, could you give me an example of using var_dump() to print out the rows? Would it just be:

var_dump($rows);

I though you were coding in a Joomla plugin so that's why I gave you a dynamic way to make a query.
In your case, you can use what you did the first time but with the order I gave you (order_product, order, address) and using LEFT JOIN.
About the var dump, yes, it's that simple.

Hello,

I have a simular issue on this. we have a php script to directly acces the database so outside of joomla.. .. and we need to get data out which will be sent by the script to our bookkeeping. but somehow. it won't get the data.. the query's we use will fetch all data if we run them in the phpmyadmin.. so i think in the other part we are missing something.. here's a part with the rows of data we want.. please we need some help. I self think it is in the call of the rows?

select * from
PREF_hikashop_user,
PREF_hikashop_address
where PREF_hikashop_user.user_id =
PREF_hikashop_address.address_user_id and
PREF_hikashop_user.user_id > 1
EOT;
$res = $db->do_query($qry);
while($row = $res->fetch_assoc()) {
// Map this on to a cRelatie object
$map = new stdClass();
$suffix = 170000 + $row;
$map->Code = {$db->ac_prefix}{$suffix};
$map->Contactpersoon = "{$row["address_firstname"]} {$row["address_lastname"]}";
if ($row["adress_company"]) {
$map->Bedrijf = "{$row["adress_company"]}";
}
else {
$map->Bedrijf = $map->Contactpersoon;
}
$timezone = new DateTimeZone('CET');
#$time = "{$row["date_added"]}";
$date = new DateTime($time,$timezone);
$map->AddDatum = $date->format("c");
$map->Adres = "{$row["address_street"]}";
$map->Adres2 = "{$row["address_street"]}";
$map->Postcode = "{$row["address_post_code"]}";
$map->Postcode2 = "{$row["address_post_code"]}";
$map->Plaats = "{$row["address_city"]}";
$map->Plaats2 = "{$row["address_city"]}";
$map->Land = "NL";
$map->Land2 = "NL";
$map->Telefoon = "{$row["address_telephone"]}";
$map->FAX = "{$row["address_fax"]}";
$map->Email = "{$row["user_email"]}";
#$map->Geslacht = "afdeling";
$map->Site = "";

oke i have altered my script, in this way you told me..
but does that mean, that i need to put it into the public_html or can i leave the folder outside the public_html as we have now?

Hello again.
I tried also to set this script also in the public_html but don't know why nothing is going to our accounting. The original script i made was working fine with mijoshop outside the public_html. The part i sent u was that i altered the query's for hikashop. But somehow it won't work. Can u take a look at my script and tell me where i am wrong? so if yes please send me the email where i can send the script. i first originally made..