6 Replies Latest reply: Nov 19, 2007 3:13 AM by Newsgroup_User RSS

    from RSS into multiple sql statements

    Newsgroup_User Community Member
      Hi all, I'm trying to import RSS into my database for this I created an XSLT
      stylesheet that transforms the XML into a series of SQL statements like
      below

      INSERT INTO rss_feed(feed_title,feed_link,feed_description)
      VALUES('',' http://...','');

      SELECT @feed_id := MAX(feed_id) FROM rss_feed;

      INSERT INTO
      rss_channel(feed_id,channel_title,channel_link,channel_description,channel_language,chann el_copyright,channel_managingeditor,channel_webmaster,channel_pubdate,channel_lastbuilddat e,channel_generator,channel_docs,channel_cloud,channel_ttl,channel_image,channel_rating,ch annel_skiphours,channel_skipdays)
      VALUES(@feed_id,'...','...','','','','','','','','','','','','','','','');

      SELECT @channel_id := MAX(channel_id) FROM rss_channel;

      INSERT INTO
      rss_item(channel_id,item_title,item_link,item_description,item_author,item_comments,item_ guid,item_pubdate)
      VALUES(@channel_id,'...','','...','Joris van Lier','','','');

      SELECT @item_id := MAX(item_id) FROM rss_item;

      INSERT INTO
      rss_enclosure(item_id,enclosure_url,enclosure_type,enclosure_length)
      VALUES(@item_id,' http://...','...','...');


      My problem is: I can pipe this into a command-line sql session but when
      executing it from PHP it gives me a sql syntax error, running the statements
      separately does not preserve the needed context with the foreign key
      variables.

      Second problem: how do I select the last inserted id in MySQL; is there an
      equivalent to @@IDENTITY?

      mysql Ver 14.7 Distrib 4.1.13, for unknown-linux-gnu (x86_64) using
      readline 4.3

      --
      Joris van Lier

        • 1. Re: from RSS into multiple sql statements
          Angell EYE Community Member
          To get the ID of your inserted record you use the mysql_insert_id() function.

          $result = mysql_query($SQL);
          if(!$result)
          {
          echo $SQL . "<br /><br />";
          die("MySQL Error: " . mysql_error());
          }

          $FeedID= mysql_insert_id();

          As for the syntax errors I can't help you w/o seeing the full SQL statements.
          • 2. Re: from RSS into multiple sql statements
            Newsgroup_User Community Member
            "Angell EYE" <webforumsuser@macromedia.com> wrote in message
            news:fhm5gn$7k6$1@forums.macromedia.com...
            > To get the ID of your inserted record you use the mysql_insert_id()
            > function.
            >
            > $result = mysql_query($SQL);
            > if(!$result)
            > {
            > echo $SQL . "<br /><br />";
            > die("MySQL Error: " . mysql_error());
            > }
            >
            > $FeedID= mysql_insert_id();
            >
            > As for the syntax errors I can't help you w/o seeing the full SQL
            > statements.

            Thanks for responding, however I need to execute the whole batch of SQL
            statements as a transaction,
            I've found the mysql function LAST_INSERT_ID()
            http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id,
            but I'm stuck witch executing multiple SQL statements separated by ";"
            semicolons from PHP


            • 3. Re: from RSS into multiple sql statements
              Newsgroup_User Community Member
              Joris van Lier wrote:
              > Thanks for responding, however I need to execute the whole batch of SQL
              > statements as a transaction,

              The mysql_query() function accepts only a single SQL query. To perform
              multiple queries in a single operation, you need to use the mysqli
              multi_query() method.

              http://docs.php.net/manual/en/function.mysqli-multi-query.php

              Since your version of MySQL is 4.1, you should be able to use mysqli as
              long as it has been enabled in PHP.

              --
              David Powers, Adobe Community Expert
              Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
              Author, "PHP Solutions" (friends of ED)
              http://foundationphp.com/
              • 4. Re: from RSS into multiple sql statements
                Newsgroup_User Community Member
                "David Powers" <david@example.com> wrote in message
                news:fhmcqt$hr7$1@forums.macromedia.com...
                > Joris van Lier wrote:
                >> Thanks for responding, however I need to execute the whole batch of SQL
                >> statements as a transaction,
                >
                > The mysql_query() function accepts only a single SQL query. To perform
                > multiple queries in a single operation, you need to use the mysqli
                > multi_query() method.
                >
                > http://docs.php.net/manual/en/function.mysqli-multi-query.php
                >
                > Since your version of MySQL is 4.1, you should be able to use mysqli as
                > long as it has been enabled in PHP.


                Hi David, thanks for explaining that.
                I'm stuck with the standard MySQL extension in php,
                however I noticed that phpMyAdmin reports the following
                MySQL client version: 4.1.13
                Used PHP extensions: mysql <- notice there's NO mysqli here,

                but it can execute my query
                How do they do that?

                • 5. Re: from RSS into multiple sql statements
                  Newsgroup_User Community Member
                  Joris van Lier wrote:
                  > I'm stuck with the standard MySQL extension in php,
                  > however I noticed that phpMyAdmin reports the following
                  > MySQL client version: 4.1.13
                  > Used PHP extensions: mysql <- notice there's NO mysqli here,

                  Have you checked phpinfo()? phpMyAdmin decides which extension to use
                  based on the settings in config.inc.php. If mysqli isn't enabled, it
                  sounds as though your server is still running PHP 4. If so, that's
                  crazy. Support for PHP 4 ends on December 31. It's time to demand that
                  your hosting company upgrades to PHP 5.2.

                  > but it can execute my query
                  > How do they do that?

                  I have no idea how phpMyAdmin does it. I presume that it uses explode()
                  to separate the queries into an array, using the semicolon as the
                  separator. You can then loop through the array to execute each query
                  independently.

                  --
                  David Powers, Adobe Community Expert
                  Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
                  Author, "PHP Solutions" (friends of ED)
                  http://foundationphp.com/
                  • 6. Re: from RSS into multiple sql statements
                    Newsgroup_User Community Member


                    "David Powers" <david@example.com> wrote in message
                    news:fhrnou$j4g$1@forums.macromedia.com...
                    > Joris van Lier wrote:
                    >> I'm stuck with the standard MySQL extension in php,
                    >> however I noticed that phpMyAdmin reports the following
                    >> MySQL client version: 4.1.13
                    >> Used PHP extensions: mysql <- notice there's NO mysqli here,
                    >
                    > Have you checked phpinfo()? phpMyAdmin decides which extension to use
                    > based on the settings in config.inc.php. If mysqli isn't enabled, it
                    > sounds as though your server is still running PHP 4. If so, that's crazy.
                    > Support for PHP 4 ends on December 31. It's time to demand that your
                    > hosting company upgrades to PHP 5.2.
                    >
                    >> but it can execute my query
                    >> How do they do that?
                    >
                    > I have no idea how phpMyAdmin does it. I presume that it uses explode() to
                    > separate the queries into an array, using the semicolon as the separator.
                    > You can then loop through the array to execute each query independently.

                    There's no mysqli support on this server, phpMyAdmin has an internal parser
                    that splits the queries and tries to handle delimiters in strings, and then
                    uses mysql_unbuffered_query or mysql_query, so it seems that using one of
                    these functions with multiple consecutive queries preserves the context of
                    the previous query.

                    Using explode will split strings that contain a semicolon, I'm now trying
                    to escape the semicolons in strings to avoid writing my own parser, so far
                    I've been thinking about HEXing them, but hexing complete input will
                    seriously hurt my ability to read the queries, the alternative CONCAT('foo',
                    0x3b, 'bar') still isn't pretty.

                    Do you know if it's possible to embed hexed characters into strings (without
                    introducing a semicolon)?


                    Joris