Creating stored procedure for MySQL db using PHP script

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
lukaz1010
Forum Newbie
Posts: 3
Joined: Wed Jan 13, 2010 1:50 pm

Creating stored procedure for MySQL db using PHP script

Post by lukaz1010 »

Hi

I am not able to create stored procedure within MySQL database using PHP script.
Script bellow creates succesfully new db then it switches to it then creates table with no problems so far but then it struggles with creating stored procedure in the db. It doesn't give me any error message.

When I paste the $command echoed to the MySQL shell it ends up with "_>" and does nothing.

I tried to Google it with no success at all.

I also tried to change the DELIMITER value with no luck. If anybody tackled this problem previously or knows how to solve my problem I will really appreciate it.

My project is going to be Open Source. I work on that code because I do like scripting. Now I am stuck.

Thank you for any suggestions.

Script that doesn't finish creating stored procedures and gives no error messages. It does create the new database and new table only:

Code: Select all

 
.
.
.
  $db_server = "localhost";
  $db_user = "root";
  $db_password = "l0ngWaY";
  $db_name = "test03";
  
  $command = "
  CREATE DATABASE $db_name; 
  USE $db_name; 
  
  CREATE TABLE tags (
    id int(11) NOT NULL auto_increment,
    tag text NOT NULL default 'not categorized',
    PRIMARY KEY  (id),
    FULLTEXT KEY tag (tag)
  ) ENGINE=MyISAM ;
  
  
  DELIMITER //
  CREATE PROCEDURE tag_new(
          IN par_tag  text
  )
  BEGIN
  INSERT INTO tags
  (
   tag,
   count
  )
  VALUES
  (
   par_tag,
   1
  );
  END
  //
  
  CREATE PROCEDURE tag_update(
   IN par_id  INT,
          IN par_tag  text
          )
  BEGIN
  UPDATE tags
  SET 
          tag = par_tag
  WHERE
          id = par_id;
  END
  //
  DELIMITER ;
  ";
  
  echo $command;
  
  $connection = @mysql_connect( $db_server, $db_user ,  $db_password);
  $result = mysql_query($command, $connection);
  mysql_close($connection);
.
.
.
 
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Creating stored procedure for MySQL db using PHP script

Post by VladSun »

1. Don't use large font size in your posts!
2. Use [ php ] [ /php ] BBCode tags
3. Try this:

Code: Select all

...
 $result = mysql_query($command, $connection) or die(mysql_error());
...
There are 10 types of people in this world, those who understand binary and those who don't
lukaz1010
Forum Newbie
Posts: 3
Joined: Wed Jan 13, 2010 1:50 pm

Re: Creating stored procedure for MySQL db using PHP script

Post by lukaz1010 »

VladSun wrote:1. Don't use large font size in your posts!
2. Use [ php ] [ /php ] BBCode tags
3. Try this:

Code: Select all

...
 $result = mysql_query($command, $connection) or die(mysql_error());
...
Thank you VladSun.
Last edited by lukaz1010 on Wed Jan 13, 2010 4:00 pm, edited 1 time in total.
lukaz1010
Forum Newbie
Posts: 3
Joined: Wed Jan 13, 2010 1:50 pm

Re: Creating stored procedure for MySQL db using PHP script

Post by lukaz1010 »

VladSun wrote:1. Don't use large font size in your posts!
2. Use [ php ] [ /php ] BBCode tags
3. Try this:

Code: Select all

...
 $result = mysql_query($command, $connection) or die(mysql_error());
...

No "delimiter //" needed when going command by command from script.
I got it now working:

Code: Select all

<?php
 
$command1 = "
CREATE TABLE tags (
id int(11) NOT NULL auto_increment,
tag varchar(1000) NOT NULL default 'not categorized',
PRIMARY KEY  (id),
FULLTEXT KEY tag (tag)
) ENGINE=MyISAM ;
";
 
$command2="
CREATE PROCEDURE tag_new(
    IN par_tag  text
)
BEGIN
INSERT INTO tags
(
    tag,
    count
)
VALUES
(
    par_tag,
    1
);
END
";
 
    $db_server = "localhost";
    $db_user = "root";
    $db_password = "l0ngWay";
    $db_name = "test03";
 
    // CONNECT
    //
    $link = mysql_connect($db_server, $db_user, $db_password);
    if (!$link) { die('Could not connect: ' . mysql_error()); }
 
    // DROP DATABASE IF EXISTS
    //
    $sql = "drop database if exists $db_name";
    if (mysql_query($sql, $link))
    {
        echo "Database $db_name dropped successfully.<hr />";
    }
    else
    {
        die("Error dropping database: " . mysql_error() . "<br />");
    }
 
    // CREATE DB
    //
    $sql = "CREATE DATABASE $db_name";
    if (mysql_query($sql, $link))
    {
            echo "Database $db_name created successfully<hr />";
    }
    else
    {
            die("Error creating database $db_name: " . mysql_error() . "<br />");
    }
 
    // SELECT NEWLY CREATED DB
    //
    if (!mysql_select_db($db_name)) { die('Could not select database: ' . mysql_error()); }
 
    // TASK SQL QUERY TO CREATE TABLE
    //
    if (!mysql_query($command1)) { die('Invalid query: ' . mysql_error()); }
    echo "<hr />".$command1 ." -> Success creating table<hr />";
 
    // TASK SQL QUERY TO CREATE STORED PROCEDURE
    //
    if (!mysql_query($command2)) { die('Invalid query: ' . mysql_error()); }
    echo "<hr />".$command2 ." -> Success creating stored procedure <hr />";
 
 
    mysql_close($link);
 
 
?>
Post Reply