software/RELEASES/ircservices/achurch.org/services/lists/ircservices-coding/2007/003309.html

   1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
   2 <HTML>
   3  <HEAD>
   4    <TITLE> [IRCServices Coding] XML Export to mySQL with PHP
   5    </TITLE>
   6    <LINK REL="Index" HREF="index.html" >
   7    <LINK REL="made" HREF="mailto:ircservices-coding%40ircservices.za.net?Subject=%5BIRCServices%20Coding%5D%20XML%20Export%20to%20mySQL%20with%20PHP&In-Reply-To=">
   8    <META NAME="robots" CONTENT="index,nofollow">
   9    <META http-equiv="Content-Type" content="text/html; charset=us-ascii">
  10    <LINK REL="Previous"  HREF="003308.html">
  11    <LINK REL="Next"  HREF="003311.html">
  12  </HEAD>
  13  <BODY BGCOLOR="#ffffff">
  14    <H1>[IRCServices Coding] XML Export to mySQL with PHP</H1>
  15     <B>Dereck Martin</B>
  16     <A HREF="mailto:ircservices-coding%40ircservices.za.net?Subject=%5BIRCServices%20Coding%5D%20XML%20Export%20to%20mySQL%20with%20PHP&In-Reply-To="
  17        TITLE="[IRCServices Coding] XML Export to mySQL with PHP">dmartin at tekconxus.com
  18        </A><BR>
  19     <I>Sun Aug  5 01:14:11 PDT 2007</I>
  20     <P><UL>
  21         <LI>Previous message: <A HREF="003308.html">[IRCServices Coding] FTBFS on amd64
  22 </A></li>
  23         <LI>Next message: <A HREF="003311.html">[IRCServices Coding] ircservices-church ITP
  24 </A></li>
  25          <LI> <B>Messages sorted by:</B>
  26               <a href="date.html#3309">[ date ]</a>
  27               <a href="thread.html#3309">[ thread ]</a>
  28               <a href="subject.html#3309">[ subject ]</a>
  29               <a href="author.html#3309">[ author ]</a>
  30          </LI>
  31        </UL>
  32     <HR>
  33 <!--beginarticle-->
  34 <PRE>-----BEGIN PGP SIGNED MESSAGE-----
  35 Hash: SHA1
  36
  37 Hello All,
  38
  39 I have wrote a php script that will essentially sync up the services xml
  40 export found in the /dbaccess/xml-export/ of http server to mysql.  It
  41 doesn't do all the values, but most of the them (at least the ones most
  42 care about).  I will run this as a night cron job.
  43
  44 This script basically takes the most current xml export, parses it, and
  45 then compares it to the mysql database.  If there are new or modified
  46 entries it will add or update them.  If there are any entries that are
  47 old and need to be removed (expired nicks/channels/unlinked nicks), it
  48 deletes them.
  49
  50 I am sharing this script in hopes someone else may find it useful.  The
  51 are two scripts.  One script creates the tables in mysql.  The second
  52 script does all the other work.
  53
  54 The script is designed to only be ran once a day.  I am using DATE
  55 stamps to distinguish between out of date entries and new ones.
  56 Multiple runs in the same day will only update or add new entries.
  57 Expired entries will only be removed the following day.
  58
  59 You can find my script here:
  60   <A HREF="http://www.tekconxus.com/files/ircservices-xml2mysql-0.5.tag.gz">http://www.tekconxus.com/files/ircservices-xml2mysql-0.5.tag.gz</A>
  61
  62 Setup Instructions:
  63
  64 1. You will need to perform this command in mysql:
  65
  66         CREATE DATABASE ircservices;
  67
  68 2. Modify the create_tables.php database variables.
  69 3  Run &quot;php -f create_tables.php&quot; (only needs to be done once)
  70 4. Modify the ircservices-xml2mysql.php database &amp; xml variables.
  71 5. Run &quot;php -f ircservices-xml2mysql.php&quot;
  72
  73 It will do its magic and even display some little progress graphics.
  74         (+) Add, (-) Delete, (^) Update
  75
  76 If there is enough curiosity in this script, I will add all entries from
  77 the XML file.  For those interested in the output...during this run one
  78 nick was added and one deleted.  The rest of the entries were synced.
  79
  80 ###########################################################
  81 fatbastard scripts # php -f ircservices-xml2mysql.php
  82 - --04:10:49--  <A HREF="http://10.10.0.4:8080/dbaccess/xml-export/">http://10.10.0.4:8080/dbaccess/xml-export/</A>
  83            =&gt; `ircservices.xml'
  84 Connecting to 10.10.0.4:8080... connected.
  85 HTTP request sent, awaiting response... 200 OK
  86 Length: unspecified [text/plain]
  87
  88     [ &lt;=&gt;
  89                                         ] 11,225        --.--K/s
  90
  91 04:10:49 (13.59 MB/s) - `ircservices.xml' saved [11225]
  92
  93 Processing NickInfo Section:
  94 ^^+^-
  95 Processing ChannelInfo Section:
  96 ^^^^
  97 Processing NickGroupInfo Section:
  98 ^^^^
  99 Updated: 11
 100 Added: 1
 101 Deleted: 1
 102
 103 ###########################################################
 104 -----BEGIN PGP SIGNATURE-----
 105 Version: GnuPG v1.4.7 (MingW32)
 106 Comment: Using GnuPG with Mozilla - <A HREF="http://enigmail.mozdev.org">http://enigmail.mozdev.org</A>
 107
 108 iD8DBQFGtYbTZgcWLP4PXgERAouDAJ4/g8VQMCO1vgEX5Yd4SZtAJLgsPQCgnwdm
 109 BXbK3a2j5efoIMru76pxkKA=
 110 =pxA1
 111 -----END PGP SIGNATURE-----
 112 -------------- next part --------------
 113 A non-text attachment was scrubbed...
 114 Name: ircservices-xml2mysql-0.5.tar.gz
 115 Type: application/gzip
 116 Size: 2814 bytes
 117 Desc: not available
 118 Url : <A HREF="../attachments/20070805/bc355b84/ircservices-xml2mysql-0.5.tar.bin">../attachments/20070805/bc355b84/ircservices-xml2mysql-0.5.tar.bin</A>
 119 </PRE>
 120
 121
 122
 123 <!--endarticle-->
 124     <HR>
 125     <P><UL>
 126         <!--threads-->
 127         <LI>Previous message: <A HREF="003308.html">[IRCServices Coding] FTBFS on amd64
 128 </A></li>
 129         <LI>Next message: <A HREF="003311.html">[IRCServices Coding] ircservices-church ITP
 130 </A></li>
 131          <LI> <B>Messages sorted by:</B>
 132               <a href="date.html#3309">[ date ]</a>
 133               <a href="thread.html#3309">[ thread ]</a>
 134               <a href="subject.html#3309">[ subject ]</a>
 135               <a href="author.html#3309">[ author ]</a>
 136          </LI>
 137        </UL>
 138
 139 </body></html>