from RSS into multiple sql statements

Report · Nov 16, 2007

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,channel_copyright,channel_managingeditor,channel_webmaster,channel_pubdate,channel_lastbuilddate,channel_generator,channel_docs,channel_cloud,channel_ttl,channel_image,channel_rating,channel_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

Report · Nov 16, 2007

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.

Report · Nov 17, 2007

"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

Report · Nov 17, 2007

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/

Report · Nov 19, 2007

"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?

Report · Nov 19, 2007

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/

Report · Nov 19, 2007

"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