mysql_query() question 1

[mrtopher]

Is it possible in your PHP code to instead of writing out your SQL script in your code (by inserting it into a variable in the script) to instead put it in a separate file and then have the function run the query from that file?

I have tried this, by first reading the contents of the SQL file into a string and then using that string as my query but I keep getting SQL errors and I’m not sure why. I know you can do this in phpMyAdmin, but I’m not sure how they do it.

 

[sleipnir214]

There's no technical reason why it cannot be done the way you describe. I just don't see what benefit the extra code complexity gives you.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[DRJ478]

There is the concept of a SQL phrase-book where you externally store the queries used within an application. For applications which don't have a lot of "reporting" to do and which don't contain customizable queries it is probably not necessary.
Sometimes, however, (in my experience) it would have come in handy for reviewing the SQL statements in one place instead of scanning through all the code of complex applications.

 

[mrtopher]

That is my purpose for doing this. I would like to store the complex database creation script outside of a script in my application for easy reading.

Is there a different function I should be using other than mysql_query() ??

 

[sleipnir214]

Are you taking a file with multiple SQL queries and passing the whole thing at one go to mysql_query(), or are you running each query individually?

The former won't work -- PHP doesn't allow multiple queries to run in a single invocation of mysql_query().



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Chacalinc]

may be mrtopher is pointing out to something like stored procedures? just a though... may be that's could be an answer.

 

[mrtopher]

That is just what I was trying to do. Is there another way to execute a number of sql queries, for example create tables, with a single function call? Is there an alternative function to use other than mysql_query()?

Just out of curiosity, does anyone know how the function works in phpMyAdmin where you can choose a .sql file to create tables from?

 

[Chacalinc]

as sleipnir posted, mysql_query() can run only one query at a time, so you can not use it to run several queries qith only one mysql_query() call. There are several scripts in the "user contributed notes" in the

http://cl2.php.net/manual/en/function.mysql-query.php

page. read them and you could get an idea.

Example (from that page), Predrag Supurovic wrote:

"If you need to execute sevaral SQL commands in a row (usually called batcg SQL) using PHP you canot use mysql_query() since it can execute single command only.

Here is simple but effective function that can run batch SQL commands. Take cere, if string contains semicolon ( anywhere except as command delimiter (within string expression for example) function will not work."

function mysql_exec_batch ($p_query, $p_transaction_safe = true) {
  if ($p_transaction_safe) {
     $p_query = 'START TRANSACTION;' . $p_query . '; COMMIT;';
   };
  $query_split = preg_split ("/[;]+/", $p_query);
  foreach ($query_split as $command_line) {
   $command_line = trim($command_line);
   if ($command_line != '') {
     $query_result = mysql_query($command_line);
     if ($query_result == 0) {
       break;
     };
   };
  };
  return $query_result;
}

Cheers.

 

[mrtopher]

Thats just what I was looking for, thanks a lot!!

 

[Chacalinc]

anytime

 

[sleipnir214]

mrtopher:
The coe Chacalinc has posted does have one "gotcha":

The script is splitting the file at the semicolon character, which is used by MySQL to denote the end of a query in a multi-query SQL statement. But that script splits the file at every semicolon, so if you have a query that contains a semicolon within it, the script will barf.




Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Chacalinc]

Hi sleipnir, as I noted, that code was a copy/paste from the php online manual. There are several script that mrtopher could use or pick up and modify. I just pointed him out to the source page to get more info.

Anyway, thanks for your post, I didn't see that bug, I just read the message and copy/paste.

 

[mrtopher]

Right, but I dont plan on using it in many places. The one place I plan to use it will have controlled data being passed to the function.

Thanks for the heads up!