By esmailzadeh on

hi all
db_query just allow one line sql command to run
i need to run this sql command:

db_query('
SET @nid = ( SELECT node.nid
FROM content_type_news
INNER JOIN node ON node.nid = content_type_news.nid
WHERE content_type_news.field_home_position_value =0
ORDER BY created DESC
LIMIT 1 ) ;
update content_type_news inner join node on node.nid=content_type_news.nid SET field_home_position_value =1 where node.nid=@nid;
');

how can i do that?

⋅ Categories: Drupal 5.x

Comments

esmailzadeh’s picture

esmailzadeh commented

my purpose of multiline sql command is multi statement sql command

Tommy Sundstrom’s picture

Tommy Sundstrom commented

The SQL command does not have to be split on several lines, you could just write it on one line.

esmailzadeh’s picture

esmailzadeh commented

thanks for your quick replay
i am suspended for this requierment in a project and waiting for a solution
but this is pussible in pure php and mysql to use these multi state sql command.
i think unfortuately this command cant replace with one line sql command

gpk’s picture

gpk commented

The usual way of doing this in Drupal would be as 2 queries, the first getting the nid you want to update and the second doing the update on that nid, i.e.

$sql2 = 'UPDATE content_type_news INNER JOIN node ON node.nid=content_type_news.nid SET field_home_position_value = %d WHERE node.nid=%d';
db_query($sql2, 1, $nid);

Having said that I don't know of any reason offhand why your version wouldn't work...

gpk
----
www.alexoria.co.uk

gpk
----
www.alexoria.co.uk

esmailzadeh’s picture

esmailzadeh commented

also this is optimum to use multi statement command instead of 1 line command for update sql

cog.rusty’s picture

cog.rusty commented

You could write a function which takes a multi-query string, explodes it into an array of queries, iterates and runs them one by one with db_query, and returns an array of results which then you need to handle.

Although I don't see the point. Multiple queries are still multiple queries. They can be completely unrelated to one another and the fact that they come in a single sql string doesn't change anything.

nevets’s picture

nevets commented

What is the context from when you are trying to do this, for example is this from a hook?

nevets’s picture

nevets commented

According to the mySQL documentation this should work

UPDATE content_type_news inner SET field_home_position_value =1
WHERE content_type_news.field_home_position_value =0
ORDER BY nid DESC
LIMIT 1

It orders by nid instead of created but the result should be the same, it will not work with the JOIN.

esmailzadeh’s picture

esmailzadeh commented

thanks a lot, i think my problem can be resolved by gpk and cog.rusty suggestions.
but i still eager to know why drupal dont allow multi line (multi statement) sql command?
in very cases (e.g batch update) we need this.

nevets’s picture

nevets commented

Check you UPDATE statement, according to the mySQL documentation the syntax is wrong (there is no JOIN)

cog.rusty’s picture

cog.rusty commented

I think JOIN is OK, although it can be tricky. It is the second syntax, using table_references, in http://dev.mysql.com/doc/refman/5.0/en/update.html, and table references can use JOINs like in http://dev.mysql.com/doc/refman/5.0/en/join.html

There may be a way to turn that into a single query as nevets suggested, but I assumed that the question was more general.

esmailzadeh’s picture

esmailzadeh commented

i think join can work with update properly but this is not needed for this update statement

wstein’s picture

wstein commented

db_query is Drupal's wrapper for mysql_query or mysqli_query (or whatever is appropriate for the selected database, such as pg_query for PostgreSQL database server).

Since the PHP functions mysql_query, mysqli_query and pg_query can only execute one SQL statement, the db_query wrapper can also only execute one SQL statement.
("One SQL statement" means one statement regardless of line breaks..., It can optionally end with a semicolon, but cannot be more than one statement separated by semicolons).

See my comment here http://drupal.org/node/129647#comment-5277492 for a function "my_multi_query" that may be able to do what you want.