0
from RSS into multiple sql statements
LEGEND
,
/t5/dreamweaver-discussions/from-rss-into-multiple-sql-statements/td-p/489708
Nov 16, 2007
Nov 16, 2007
Copy link to clipboard
Copied
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
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
TOPICS
Server side applications
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Explorer
,
/t5/dreamweaver-discussions/from-rss-into-multiple-sql-statements/m-p/489709#M95813
Nov 16, 2007
Nov 16, 2007
Copy link to clipboard
Copied
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.
$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.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Newsgroup_User
AUTHOR
LEGEND
,
/t5/dreamweaver-discussions/from-rss-into-multiple-sql-statements/m-p/489710#M95814
Nov 17, 2007
Nov 17, 2007
Copy link to clipboard
Copied
"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
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
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Newsgroup_User
AUTHOR
LEGEND
,
/t5/dreamweaver-discussions/from-rss-into-multiple-sql-statements/m-p/489711#M95815
Nov 17, 2007
Nov 17, 2007
Copy link to clipboard
Copied
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/
> 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/
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Newsgroup_User
AUTHOR
LEGEND
,
/t5/dreamweaver-discussions/from-rss-into-multiple-sql-statements/m-p/489712#M95816
Nov 19, 2007
Nov 19, 2007
Copy link to clipboard
Copied
"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?
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?
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Newsgroup_User
AUTHOR
LEGEND
,
/t5/dreamweaver-discussions/from-rss-into-multiple-sql-statements/m-p/489713#M95817
Nov 19, 2007
Nov 19, 2007
Copy link to clipboard
Copied
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/
> 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/
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Newsgroup_User
AUTHOR
LEGEND
,
LATEST
/t5/dreamweaver-discussions/from-rss-into-multiple-sql-statements/m-p/489714#M95818
Nov 19, 2007
Nov 19, 2007
Copy link to clipboard
Copied
"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
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more

