Help > General

GUIDE: Import Reviews via PHP MyAdmin

(1/1)

madali:
To other newbies like me, I wanted to write a small, basic tutorial that could be of help for them. This is targetted towards very basic newbies and it is written by someone with lack of technical expertise, so anyone else reading this might find my method very inelegant and messy.

But anyway, here we go:


Goal: To speed up the data entry part of reviews, when you have lots of reviews.

We are going to import it directly into the database rather than working in Joomla's backend as it can be time-consuming.

In my case, I have lots of reviews, but in my blog, so I can't export them directly to CSV. I did it manually, but here is how I did it.


Step 1) Enter One Review in Joomla Backend: Don't work with empty databases. Better to make one or two reviews so you have something to work with. My assumption is you have:
a)   Several Categories
b)   1 Review in Category One Already Entered
c)   Three Additional Custom Fields

We're going to work with each category separately, so we don?t get confused.  So, write your first review in your chosen category in Simple Review admin section.

Step 2) Export Review Database Table: I'm assuming you have phpMyAdmin on your server (I think most hosts have them). Type mysql.domain.com (where domain.com is your domain name - or access via cPanel). You need to enter your user name and password for your SQL DATABASE (not your site).

a)   Click on jos_simplereview_review on the right.
b)   Click Export.
c)   I chose Excel 97-2003 XLS Workbook (2007 didn't work for me). I checked the box 'Put fields names in the first row'
d)   Make sure 'Save as file' is checked and click GO

Step 3) Enter Data in Excel: You can either import the Data in your Excel using CSV (if you have it) or do it somewhat manually, as I was forced to. But here are some ways to speed it up. I handled each column as follows.

Columns:
a)   ReviewID: Entered the next number, then drag it down, to increase the numbers incrementally.
b)   CategoryID: Drag it down, making sure all numbers remain the same (this is why it is better to work on each category separately, I find it is easier and less confusing)
c)    awardID: I didn't give any awards, so I dragged down -1 for all
d)   Templatename: Dragged down my chosen template name for all
e)   Score: Manually enter your scores
f)   Name: Enter your review title name
g)   Pagenames: I wanted my page name (Alias) to be lowercase of the review name with hyphens instead of spaces. I used this formula instead of doing it manually:
=LOWER(SUBSTITUTE(F2," ","-"))
where F2 is the column for Name. However, make sure you keep an eye on this, I manually changed it for titles that were too long or had stuff like semicolons in it, and those I did it manually
h)   Content: You need to paste your review. For me, the only problem was copying it directly in Excel would not have the <p></p> html codes that it needed to separate the paragraphs. For me, I copied my text in the below site,
http://www.textfixer.com/html/convert-text-html.php
Clicked Convert to HTML and it would add the <p> code to it. Then I'd click on the text below, press Ctrl-C and click INSIDE the cell in Excel and press Ctrl-P
i)   Blurb: Mine was null, so dragged it all down
j)   Thumbnail: You can either enter them manually or do what I did. Always save your images as the same name as your pagenames (alias). For me, the only difference between my thumbnail and normal image is that at the end of thumbnail image, I have added '-tn' to the name. With this in mind, my formula will make sense.
="http://urllink/"&G2&"-tn.jpg"
Whereas urllink is your url link up to your image folder. G2 is the pagename column.
k)   Image: Same as above formula without the '-tn' additional, so it will look as:
="http://urllink/"&G2&".jpg"
l)   CreatedDate: I dragged the date down
m)   lastModifiedDate: Dragged it down
n)   Createdby: If you are using the same user, then drag the same number down. If you are using several users to enter reviews, you need to know each user's code. I use several users and have put the numbers with their names in a different tables so I won't forget.
o)   lastModifiedByID: I don't think this is that important, mine has the same code as the administrator even though the user is different.
p)   Published: 1 and drag it all down  making all 1
q)   userReview: for me it was all 0
r)   status: all pending for me
s)   statusReason: all NULL
t)   metakey: empty
u)   metadesc: empty
v)   metadata: empty

Just a small tip. When you enter the content, everything messes up, because of how big it is. To make sure row size doesn't change, choose all rows, right click on them, choose 'Row Height', choose '15', press enter.

Save this and we will import it later.

Step 4) Export Custom Title Database Table: The custom titles are in a different database table. The name is jos_simplereviews_review_title

Export it as the other table in step 2.

Step 5) Enter Custom Fields in Excel: I'm assuming you have 3 custom fields, and we are going to work on a specific category on make it easier.

Columns:
a)   ReviewTitleID: This needs to be incremental, so make sure dragging it down, it number increases by 1.
b)   Category TitleID: This is linked to specific custom field name. As I had three, my three were 'Author', 'Genre', 'Year', but represented in the database with the following numbers
15
16
27
I wanted to drag the same numbers down so I entered
=B2
In the cell after 27. B2 refers to 15. I then dragged it down, making all the numbers 15,16,27,15,16,27 and so forth
c)   reviewID: This is what links your custom titles to your specific review. As I was entering them in order, I just had to increase each by 1 in batches of three. So my first three rows were
12
12
12
And my next three should have been
13
13
13
Therefore, I entered the formula
=C2+1
on the row after the 3rd 12 (row 4) and dragged the formula down
d)   title:  Here you have to manually enter your custom fields.
e)   titleOrder : Mine showed as
1
2
3
So I had to repeat it all down in same sets, so I did the same as CategoryTitleID
f)   titleSetup: Null, dragged down

Save for later use.

Step 6) Time to import them: One thing you can probably do is remove the rows in your Excel that are already in your site, or they will prevent it from being imported.

Once that's done, go to myadminPHP again. Click on the review database table (jos_simplereview_review)
Click Import
Click on 'Excel 97-2003 XLS Workbook' at the bottom.
Check the box 'Column names in first row'
Browse the file (your review in xls format file) from the top
Click Go

Do the same with jos_simplereview_review_title

And you are done!

Navigation

[0] Message Index