IPB

Welcome Guest ( Log In | Register )

4 Pages V  « < 2 3 4  
Reply to this topicStart new topic
> Database Design / Data Entry
Taren
post May 31 2009, 05:35 PM
Post #61


patsenner
******

Posts: 2,012
Joined: 13-April 04
From: Queen Creek, AZ


DJ Battle This User




I've written a script to port all of the VJA songs/data into the new schema, but there's one little problem: pulling Unicode data from the table turns the Unicode chars into question marks. Japanese song titles appear in phpMyAdmin just fine. When I use them in pages or input fields, I get question marks. If I overwrite them and save them back to the table, they will appear in the text fields, and only on certain webpages. Sometimes the data will show up and sometimes I'll get question marks and I have no idea why.

Is there some setting to tell it to stop being stupid?


--------------------
Go to the top of the page
 
+Quote Post
chotto kiitena
post May 31 2009, 06:44 PM
Post #62


INFERNO
************

Posts: 14,907
Joined: 22-May 04
From: Melbourne, Australia


DJ Battle This User

View User Page
Pop'n Battle This User




Sounds like something you're using along the way doesn't support Unicode. Is your mysql connection collation set to utf8_general_ci?


--------------------
Go to the top of the page
 
+Quote Post
PkerUNO
post May 31 2009, 06:55 PM
Post #63


Tenor Hamster
*******

Posts: 3,606
Joined: 8-March 04
From: London, UK


DJ Battle This User

View User Page
Pop'n Battle This User




mysql_query("SET NAMES 'utf8'");

This resolved so many problems on my websites.


--------------------
PkerUNO
Go to the top of the page
 
+Quote Post
Taren
post May 31 2009, 06:55 PM
Post #64


patsenner
******

Posts: 2,012
Joined: 13-April 04
From: Queen Creek, AZ


DJ Battle This User




QUOTE (Xythar @ May 31 2009, 04:44 PM) *
Sounds like something you're using along the way doesn't support Unicode. Is your mysql connection collation set to utf8_general_ci?


I have no idea. Where can I check/set that?

The field I'm pulling from (vj_songs.song) is utf8_unicode_ci, and the field it's going to is utf8_bin, but even displaying it to the browser yields all question marks, and I've even verified that with ord(), it's all 63's.


--------------------
Go to the top of the page
 
+Quote Post
chotto kiitena
post May 31 2009, 06:57 PM
Post #65


INFERNO
************

Posts: 14,907
Joined: 22-May 04
From: Melbourne, Australia


DJ Battle This User

View User Page
Pop'n Battle This User




QUOTE (Taren @ May 31 2009, 06:55 PM) *
I have no idea. Where can I check/set that?

The field I'm pulling from (vj_songs.song) is utf8_unicode_ci, and the field it's going to is utf8_bin, but even displaying it to the browser yields all question marks, and I've even verified that with ord(), it's all 63's.


Should be on the front page of phpmyadmin.

Have you had a look yet at writing an import script for the VJA export XML? (into the current schema, not the new) We don't have an API yet for retrieving it directly from VJA but for now let's assume that it'll return XML in the same format. If you've got the time then I need that a lot more urgently than a database migration script that we're not going to use for a while yet. Don't worry about anything beyond a basic interface for now, I can improve upon that later.


--------------------
Go to the top of the page
 
+Quote Post
chotto kiitena
post Jun 1 2009, 05:24 AM
Post #66


INFERNO
************

Posts: 14,907
Joined: 22-May 04
From: Melbourne, Australia


DJ Battle This User

View User Page
Pop'n Battle This User




By the way I just fixed the problem where comments with Japanese in them appear as ??? by changing the collation of the vja_scores table to utf8_general_ci (open the database in phpmyadmin, click the Structure button, then click 'Operations' at the bottom, set Collation there and click Save). Basically just make sure your database collation, connection collation, table collation (and maybe column collation?) is set to utf8_general_ci and also do that thing Pker said.


--------------------
Go to the top of the page
 
+Quote Post
Taren
post Jun 1 2009, 11:46 PM
Post #67


patsenner
******

Posts: 2,012
Joined: 13-April 04
From: Queen Creek, AZ


DJ Battle This User




QUOTE (Xythar @ May 31 2009, 04:57 PM) *
Should be on the front page of phpmyadmin.

Have you had a look yet at writing an import script for the VJA export XML? (into the current schema, not the new) We don't have an API yet for retrieving it directly from VJA but for now let's assume that it'll return XML in the same format. If you've got the time then I need that a lot more urgently than a database migration script that we're not going to use for a while yet. Don't worry about anything beyond a basic interface for now, I can improve upon that later.


Here it is:
http://www.vja2.superwailingbonus.com/xml/import.php.txt

I added some comments in there that should hopefully clarify things, but let me know if you need any help with it. I have tested it with my own XML file and it appears to work correctly.

This is just a script that reads from a file and inserts it into a DB, I'll leave it up to you to figure out how to auto-download users' scores from VJA or allow them to upload their own files. Right now this script isn't too robust, so if users modify their XML, they could potentially add some bogus scores, cause an error in the importing, or potentially attack the databases. I highly recommend either finding out how to download user data directly from VJA, or adding in a bunch of mysql_real_escape_string commands.

With some fancy curl work you could write a script to automatically go fetch those XML files, assuming the export script and login pages remained in the same place... Do you know where/how users can get their data after the site shuts down?


--------------------
Go to the top of the page
 
+Quote Post
chotto kiitena
post Jun 1 2009, 11:52 PM
Post #68


INFERNO
************

Posts: 14,907
Joined: 22-May 04
From: Melbourne, Australia


DJ Battle This User

View User Page
Pop'n Battle This User




The site will still be up as far as exports go for another year or so.

And yeah, there's going to be an API added to VJA that we can call to directly get that XML.

Thanks for that, although is there a reason we're not using an XML parser? That way we could check the XML for validity before parsing it.


--------------------
Go to the top of the page
 
+Quote Post
Taren
post Jun 1 2009, 11:58 PM
Post #69


patsenner
******

Posts: 2,012
Joined: 13-April 04
From: Queen Creek, AZ


DJ Battle This User




I looked at the XML stuff for PHP, and it was terribly confusing. I had no idea where to start in the doc pages.


--------------------
Go to the top of the page
 
+Quote Post
chotto kiitena
post Jun 2 2009, 12:05 AM
Post #70


INFERNO
************

Posts: 14,907
Joined: 22-May 04
From: Melbourne, Australia


DJ Battle This User

View User Page
Pop'n Battle This User




I'll see if I can integrate an XML parser into your script when I add it in, then.


--------------------
Go to the top of the page
 
+Quote Post
Taren
post Jun 3 2009, 12:46 AM
Post #71


patsenner
******

Posts: 2,012
Joined: 13-April 04
From: Queen Creek, AZ


DJ Battle This User




I noticed that the XML exports don't contain expert score dumps...

Would you be interested in preserving those as well? I can throw together a script to download/parse all the data from the pages, and then we can provide that to the users that choose to transfer their old VJA scores over. y/n?

also...

I was able to install Wamp okay, and I was able to check out the trunk from SVN into Eclipse (which is a pretty huge accomplishment for me, a pretty staunch Notepadian) but I can't get the pages to parse PHP or SQL properly. I don't expect you to hold my hand and/or do it for me (it'd be nice). Maybe I'll figure it out another day.

It made me wonder, what are the roles people are taking on this project? You seem to be in charge of organizing stuff, somebody else might be doing web development... what else is there to do? I'd really like to contribute somewhere while I still have the time.


--------------------
Go to the top of the page
 
+Quote Post
chotto kiitena
post Jun 3 2009, 12:57 AM
Post #72


INFERNO
************

Posts: 14,907
Joined: 22-May 04
From: Melbourne, Australia


DJ Battle This User

View User Page
Pop'n Battle This User




That's a good point, actually. But I don't think Remy is going to delete the data any time soon and there's no point in putting in expert scores until we write a course session page, so no rush.

Click WAMP Server in your taskbar, go to the PHP options and make sure php_short_tags (or whatever it's called) is set to on. IIRC by default it was off which means that PHP will only be processed if it's in <?php ?> tags, and the sites as they are now use <? and ?>.

I don't know at the moment, honestly. Right now it's pretty much just you and me. I do need someone to do web development and interface stuff because I'm not so great at HTML and CSS formatting.

I'll make us a proper programmer's forum once we migrate the forum over (the current one will also be kept for discussion of APIs and stuff)


--------------------
Go to the top of the page
 
+Quote Post
jammitch!
post Jun 3 2009, 10:11 PM
Post #73


Tranoid
****

Posts: 969
Joined: 6-September 04


DJ Battle This User

View User Page
Pop'n Battle This User




I should be able to pitch in some web dev starting in July.


--------------------
Ode to RAM
ALL RIGHT, ACT HIGH and LOW
SWITCH to the LAB and FEEL IT SNOW



QUOTE
He had found a Slake-o-Matic machine which had provided him with a compact disc filled with a sound that was almost, but not quite, entirely unlike music.
Go to the top of the page
 
+Quote Post
chotto kiitena
post Jun 3 2009, 10:38 PM
Post #74


INFERNO
************

Posts: 14,907
Joined: 22-May 04
From: Melbourne, Australia


DJ Battle This User

View User Page
Pop'n Battle This User




Sweet. I desperately need someone for HTML/CSS formatting and Javascript voodoo because Javascript is one area I am somewhat inexperienced at and most of the stuff currently used by the site is a bit hard for me to understand.


--------------------
Go to the top of the page
 
+Quote Post

4 Pages V  « < 2 3 4
Reply to this topicStart new topic

 



RSS Lo-Fi Version Time is now: 10th December 2019 - 02:03 PM