kind of recursive SQLstr
Dear listers, can anyone shine a light on this one... I have some HTML page-titles which i'd like to put in a dropdown menu. Least load for server is when just using a server-cursor. And going trough the results just once. So best thing is to order the titles in the right way when recieving them from the database: - pagesetup - pgID pgMemberOf pgName hasMembers - like this: 1.HomePage (hasMembers 0, pgMemberOf = 0) 1.1. Products 1.1.1 Toys Tools (hasMembers = 0) 1.1.2 Shoestrings 1.2. Services 1.2.1 Pinball repair 1.2.1.1 New Balls 1.2.1.2 Empty your machine 1.2.2 We tie your shoes 1.2.2.2 Left Feet Ties 1.2.2.2.1 Untying A Knot 1.2.2.2 Right Feet Ties 1.3. Links 1.4.. you get the picture .. - So far, I've made a recursive function which returns all data as a string HTML: -pseudo-CODE- buildTree(HomepageID) function buildTree(myPgID) mySQL = getSQL(myPgID) myObjRS = getRecordset(mySQL) Loop Trough records (myObjRS) If this page(pgID) has Members strReturn = ... *!Recursive!-!* strReturn += buildTree(pgID) else strReturn = ... end if Loop close recordset return strReturn end function -END-pseudo-CODE--- Bad thing about this function is that it opens up lots of recordsets (for each sub of a sub,...) Positive: it works !! Now, I was wondering if it couldn't be done in the mySQL-database itself. ??? ( MyISAM tables / MySQL 3.23.54 ) I tried out this one ... -SQLstring-- SELECT ps2.pgID As parentID, ps2.pgName AS parentPage, ps1.pgID, ps1.pgName, CONCAT_WS( '.' , LPAD( ps5.pgNr ,2, '0' ), LPAD( ps4.pgNr ,2 , '0' ), LPAD( ps3.pgNr ,2 , '0' ), LPAD( ps2.pgNr ,2 , '0' ), LPAD( ps1.pgNr ,2 , '0' ) )AS myOrder FROM pagesetup AS ps1 LEFT OUTER JOIN pagesetup AS ps2 ON ps1.pgMemberOf = ps2.pgID LEFT OUTER JOIN pagesetup AS ps3 ON ps2.pgMemberOf = ps3.pgID LEFT OUTER JOIN pagesetup AS ps4 ON ps3.pgMemberOf = ps4.pgID LEFT OUTER JOIN pagesetup AS ps5 ON ps4.pgMemberOf = ps5.pgID ORDER BY myOrder; like in: http://lists.hampshire.edu/pipermail/computerscience/2001-September/003304.h tml As you can see, this is limited to just 5 sub-levels (ps5) and 99 pages on each level(LPAD-2). Again, it works ;) but couldn't there perhaps be a more elegant and 'open' method to use? What way would you guys tackle this recursiveness in MySQL? cheers, and Tx for staying this far down ;) Bart - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
making arrays while GROUP-ing
Hi list,
I'd like to generate arrays into a field for the result-set
by means of grouping...
myTable ( MySQL 3.23.33 / MyISAM table )
++--+
| id| type |
++--+
| 002 | 'CD' |
| 002 | 'LP' |
| 011 |'DVD' |
| 081 | 'CD' |
| 081 | 'LP' |
| 081 |'DVD' |
| 087 | 'MC' |
| 087 | 'LP' |
++--+
myWish
*** SELECT id, makeArray(type) FROM myTable GROUP BY id;
++---+
| id| ? makeArray(type) |
++---+
| 002 | ('CD','LP') |
| 011 | ('DVD') |
| 081 | ('CD','LP','DVD') |
| 087 | ('MC','LP') |
++---+
I know, when GROUPing, you can ask for a COUNT like:
*** SELECT id, COUNT(*) FROM myTable GROUP BY id;
++--+
| id| COUNT(*) |
++--+
| 002 |2 |
| 011 |1 |
| 081 |3 |
| 087 |2 |
++--+
How should I tackle this ?
Thanks !
Best Regards,
Bart
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Unix-Timestamp() in myODBC 02.50
There seems to be a flaw in the myODBC driver 02.50 when working with the Unix-Timestamp() function. Apparently, the driver doesn't know which data-type to use... When I send the sql statement: -- Select ( UNIX_TIMESTAMP(U.lastTime) - UNIX_TIMESTAMP(D.prevTime) ) as dateDiff FROM ... -- I get an empty recordset, but no Error-message. When I change to ... -- Select concat( ( UNIX_TIMESTAMP(U.myTime) - UNIX_TIMESTAMP(D.latestDwnl) ) , ' testDummy' ) as dateDiff FROM ... -- I get the results as to be expected 1294572 testDummy 652223 testDummy -19864771 testDummy This workaround would be complete if I could convert this string to an integer. Or is there a beter way altogether, to calculate the difference between two dates? Not the same as the SUBDATE()-function, is it? best regards, bart - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Unix-Timestamp() in myODBC 02.50
(answer to myself anyone interested ;0) After setting my date to Unix_Timestamp mode, I convert it to a 10-base number: - CONV(UNIX_TIMESTAMP(U.lastTime),10,10) - Then, I perform the substraction: - Select ( CONV(UNIX_TIMESTAMP(U.lastTime),10,10) - CONV(UNIX_TIMESTAMP(D.prevTime),10,10) ) as dateDiff FROM ... - which give the correct results as: dateDiff 1294572 652223 -19864771 This convertion isn't needed when I set up the query in my mySQL-GUI but when executed by VBs/ASP, an empty RecordSet is returned. So firstly, I had to find the cause. After some trial/error, the Unix_Timestamp seemed to be the source of trouble. This convertion is a solution that does the trick. My guess is that the ODBC driver is behaving badly in this case ... Cheers, bart Van: Bart Goormans Verzonden: zondag 16 december 2001 9:21 There seems to be a flaw in the myODBC driver 02.50 when working with the Unix-Timestamp() function. Apparently, the driver doesn't know which data-type to use... When I send the sql statement: -- Select ( UNIX_TIMESTAMP(U.lastTime) - UNIX_TIMESTAMP(D.prevTime) ) as dateDiff FROM ... -- I get an empty recordset, but no Error-message. When I change to ... -- Select concat( ( UNIX_TIMESTAMP(U.myTime) - UNIX_TIMESTAMP(D.latestDwnl) ) , ' testDummy' ) as dateDiff FROM ... -- I get the results as to be expected 1294572 testDummy 652223 testDummy -19864771 testDummy This workaround would be complete if I could convert this string to an integer. Or is there a beter way altogether, to calculate the difference between two dates? Not the same as the SUBDATE()-function, is it? best regards, bart - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Migrating from MS SQLserver to MySQL
Thanks Schajee, Did you let Access make an SQL-script to build your tables ? You could then use such scripts in mySQL to produce a copy of the database. And what would you be using for the import of the data ? cheers, bart -Oorspronkelijk bericht- Van: Schajee Achmad [mailto:[EMAIL PROTECTED]] Verzonden: donderdag 9 augustus 2001 7:45 Aan: Bart Goormans Onderwerp: Re: Migrating from MS SQLserver to MySQL I had an almost similar problem with conversion from Access to MySQL and that was easily solved by generating an SQL query. What access did was it generated an SQL query that was run on MySQL and that sort of imported/converted all data to MySQL. Try that same thing MS SQL. I'm sure there'll be someting that might do that. --- Bart Goormans [EMAIL PROTECTED] wrote: Hi all, There's a fairly large MS SQLserver Database that my client wants me to convert to MySQL. Can someone advice me on how to tackle this, or direct me to some help. There aren't any BLOBs-fields or other exotic field types, mainly straightforward text/integers, so I don't expect too much problems on that. The main issue will be its size, so an intermediate tab-delimited textfile might not be useable. (?) Thanks, Bart -- --- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Migrating from MS SQLserver to MySQL
Hi all, There's a fairly large MS SQLserver Database that my client wants me to convert to MySQL. Can someone advice me on how to tackle this, or direct me to some help. There aren't any BLOBs-fields or other exotic field types, mainly straightforward text/integers, so I don't expect too much problems on that. The main issue will be its size, so an intermediate tab-delimited textfile might not be useable. (?) Thanks, Bart - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Migrating from MS SQLserver to MySQL
'DTS' the information ? Sorry Edgart, what exactly do you mean by that ? bart From: Edgart Gonzalez [mailto:[EMAIL PROTECTED]] augustus 9, 2001 3:39 You can DTS you information from MsSql to MySQL via ODBC. -Original Message- From: Bart Goormans [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 08, 2001 6:11 PM To: [EMAIL PROTECTED] Subject: Migrating from MS SQLserver to MySQL Hi all, There's a fairly large MS SQLserver Database that my client wants me to convert to MySQL. Can someone advice me on how to tackle this, or direct me to some help. There aren't any BLOBs-fields or other exotic field types, mainly straightforward text/integers, so I don't expect too much problems on that. The main issue will be its size, so an intermediate tab-delimited textfile might not be useable. (?) Thanks, Bart - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Migrating from MS SQLserver to MySQL
I'll look into this, Thanks, bart goormans (will be following up on this thread shortly ...) Van: Gerald R. Jensen [mailto:[EMAIL PROTECTED]] Verzonden: donderdag 9 augustus 2001 3:53 Bart: There are many ways to skin this cat, but this is one we have used recently with good success. Create a script in MSSQL7 (you can do it through the Enterprise Manager) so can re-create the database schema in MySQL. There are some syntax differences between MSSQL7 and MySQL, but it is pretty easy to figure out. Next, create an ODBC DSN to the MSSQL7 database(s). The MySQL Server can be installed eithe ron the same machine or another machine that can connect to MSSQL7. MySQLFront (http://www.mysqlfront.de/) can help you import the MSSQL7 row information into your MySQL database. Alternatively, you could link tables with MSAccess, but MySQLFront is easier and cleaner. Gerald Jensen From: Bart Goormans [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, August 08, 2001 8:11 PM Hi all, There's a fairly large MS SQLserver Database that my client wants me to convert to MySQL. ... The main issue will be its size, so an intermediate tab-delimited textfile might not be useable. Bart - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
