How to execute multiple sentences in php

[itaym02]

Greetings,

When I run those sentences in phpmyadmin, they work fine. So Assume the bug is not in them.

SELECT @parent_name:=CONCAT('|||',name,'|||') FROM test.categories WHERE id=6;SELECT @num_dauhters:=COUNT(*) FROM lma_categories WHERE parent=90;SELECT @num_link:=COUNT(*) FROM lma_links WHERE category=90;UPDATE lma_categories SET parentnames=@parent_name,numsub=@num_dauhters,numlinks=@num_link,processed=1 WHERE id=90;

When I try to run this multiple sentence in php, nothing happens.
Here is what I do:

$sql="SELECT @parent_name:=CONCAT('|||',name,'|||') FROM test.categories WHERE id=6;SELECT @num_dauhters:=COUNT(*) FROM lma_categories WHERE parent=90;SELECT @num_link:=COUNT(*) FROM lma_links WHERE category=90;UPDATE lma_categories SET parentnames=@parent_name,numsub=@num_dauhters,numlinks=@num_link,processed=1 WHERE id=90;";
mysql_query($sql,$link_id);

What have I missed here?

[Sxooter]

Yes, you did miss something.

Most versions of PHP around now disallow this. For one thing, it's a security issue. Suppose someone sneaks in a text field like :

';delete from sometable

into a field and you don't check it and dont' have magic_gpc_quotes turned on. Poof, empty table.

Plus, if ONE (or heaven forbid, TWO) of those statements error out, which one(s) do we return in the mysql_error function?

Easiest way to do this is:

$querys[]="select * from sometable...";
$querys[]="insert into ...";
$querys[]="delete from table ...";

foreach ($querys as $q){
    $res[] = mysql_query($q);
}

That way you now have a little array of result codes you can check for errors, etc...

[itaym02]

Thank, Irealy apperciate it.

It just that I have a script with all the job done in sql, I hoped I could write it all in sql and use the sql variables. That way everything is on the MySql server. Isn't that better from performance point of view?

(I Can't use only sql cause I have some minor adjustment which need php for that)

[Sxooter]

Performance wise, there'd be almost no difference, because the bulk of the cpu/io time is used by the dbms to process your queries, not by php to send them to the database.

If you have a SQL file with each statement on an individual line, it's pretty easy to read it and toss the queries one at a time at the database. If you have a file where the queries contain carriage returns, it'll take a bit more parsing to read them and toss them to the backend one at a time.

[itaym02]

thanks

By the Way, the link in your signature to slony is broken

I M

[Sxooter]

The slony link is broken on their end. It should be fixed soon. I'll let the folks who run gborg know.

[itaym02]

OK

Have a good day

Itay

[Sxooter]

oh yeah, it's fixed. There's been some ongoing problem with the site that hosts gborg this week. sigh.