Categories
View Replies (14)

Resolved Trying to export customer list

  1. MesquiteMan
    MesquiteMan
  2. RO CSVI
  3. Tuesday, 05 April 2016
  4.  Subscribe via email
I am running J!3.0.48 with CSVI 6.55, Virtuemart 3.0.12, and PHP 5.6.2.

I am trying to export a list of VM customers with addresses. I have a template set up with action=export, component= Virtuemart, and operation=users info. I have these fields set:

1 full_name
2 address_1
3 city
4 zip
5 state_name
6 state_2_code
7 state_3_code

When I try to do the export, I get the following error message:

Error
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3 SQL=SELECT state_name FROM dev3_virtuemart_states WHERE virtuemart_state_id =

I have tried it with and without the state id fields. What am I doing wrong?
0
Responses (14)
tharuna
tharuna
Accepted Answer Pending Moderation
Hello,
Frankly saying i don't see this error with and without state_name fields. One possibility could be that virtuemart_state_id field being empty in your dev3_virtuemart_states table. But i am really not sure how it can be empty as virtuemart_state_id field is a required field from virtuemart views. Can you check your dev3_virtuemart_states table and see what you have in virtuemart_state_id field?
Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 1
MesquiteMan
MesquiteMan
Accepted Answer Pending Moderation
The states table IS populated properly.

states.png
Attachments (1)
states.png states.png
  1. more than a month ago
  2. RO CSVI
  3. # 2
MesquiteMan
MesquiteMan
Accepted Answer Pending Moderation
Attached is my debug file after I tried running the export in case that helps any.
Attachments (1)
com_csvi.log.119.zip
  1. more than a month ago
  2. RO CSVI
  3. # 3
tharuna
tharuna
Accepted Answer Pending Moderation
My bad, it should have been #__virtuemart_userinfos table and virtuemart_state_id field. Can you check that table too?
Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 4
MesquiteMan
MesquiteMan
Accepted Answer Pending Moderation
Yes, that column/field is there and populated.
stateid.png
Attachments (1)
stateid.png stateid.png
  1. more than a month ago
  2. RO CSVI
  3. # 5
tharuna
tharuna
Accepted Answer Pending Moderation
Hello,
Can you run this query directly in phpmyadmin and tell us if you see any empty virtuemart_state_id field? Please make sure you check all the records.
SELECT `dev3_virtuemart_userinfos`.`first_name`,

`dev3_virtuemart_userinfos`.`middle_name`,

`dev3_virtuemart_userinfos`.`last_name`,

`address_1`,

`city`,

`zip`,

`dev3_virtuemart_userinfos`.`virtuemart_state_id`

FROM `dev3_virtuemart_userinfos`

LEFT JOIN `dev3_virtuemart_vmusers` ON `dev3_virtuemart_vmusers`.`virtuemart_user_id` = `dev3_virtuemart_userinfos`.`virtuemart_user_id`

LEFT JOIN `dev3_virtuemart_vmuser_shoppergroups` ON `dev3_virtuemart_vmuser_shoppergroups`.`virtuemart_user_id` = `dev3_virtuemart_userinfos`.`virtuemart_user_id`

LEFT JOIN `dev3_virtuemart_vendors` ON `dev3_virtuemart_vendors`.`virtuemart_vendor_id` = `dev3_virtuemart_vmusers`.`virtuemart_vendor_id`

LEFT JOIN `dev3_virtuemart_shoppergroups` ON `dev3_virtuemart_shoppergroups`.`virtuemart_shoppergroup_id` = `dev3_virtuemart_vmuser_shoppergroups`.`virtuemart_shoppergroup_id`

LEFT JOIN `dev3_users` ON `dev3_users`.`id` = `dev3_virtuemart_userinfos`.`virtuemart_user_id`
Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 6
MesquiteMan
MesquiteMan
Accepted Answer Pending Moderation
I have ran the query in phpmyadmin. I exported the results to an excel spreadsheet so I could sort by state id. I have a bunch that have a 0 (zero) for the state id. Those are all international customers in countries that do not have states or provinces. There are 525 of those. There are also 53 entries that all the rest of the fields are NULL but the state ID shows 0. Not sure what those are from. See attached.

sql.jpg
Attachments (1)
sql.jpg sql.jpg
  1. more than a month ago
  2. RO CSVI
  3. # 7
MesquiteMan
MesquiteMan
Accepted Answer Pending Moderation
I have done a little digging in the database on the Null entries above. I found the following on one of them.

null.jpg

This database is a migration from VM 1.x. I used VM MIgrator to do the migration. The only things I migrated were user info and orders. Could these NULL entries be causing the problem and if so, are they safe to delete? The bottom line has Null in all the fields except the IDs shown above and the state filed which is 0.
Attachments (1)
null.jpg null.jpg
  1. more than a month ago
  2. RO CSVI
  3. # 8
tharuna
tharuna
Accepted Answer Pending Moderation
Hello,
I have made a patch file to check for virtuemart_state_id and then run the query. Can you load the attached patch file and try the export again?


patch_vm_userinfo_05-04-2016.zip
Attachments (1)
patch_vm_userinfo_05-04-2016.zip
Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 9
MesquiteMan
MesquiteMan
Accepted Answer Pending Moderation
That worked! Thank you!

As for the NULL entries, are they safe to delete from the database or should I leave them alone?
  1. more than a month ago
  2. RO CSVI
  3. # 10
tharuna
tharuna
Accepted Answer Pending Moderation
Glad that it worked. For NULL entries, You can delete them if you are sure that they are of no help.
Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 11
MesquiteMan
MesquiteMan
Accepted Answer Pending Moderation
I tried adding the country to the export and now I am getting a similar error regarding the country!

Error
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3 SQL=SELECT country_name FROM dev3_virtuemart_countries WHERE virtuemart_country_id =
  1. more than a month ago
  2. RO CSVI
  3. # 12
tharuna
tharuna
Accepted Answer Pending Moderation
Hello,
Can you try applying the new patch file attached? I have added a check for country field too.


patch_vm_userinfo_05-04-2016-2.zip
Attachments (1)
patch_vm_userinfo_05-04-2016-2.zip
Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 13
MesquiteMan
MesquiteMan
Accepted Answer Pending Moderation
Thank you! It works as well!
  1. more than a month ago
  2. RO CSVI
  3. # 14
  • Page :
  • 1


There are no replies made for this post yet.
Be one of the first to reply to this post!