This works in phpmyadmin but not in php

Discussion in 'Programming/Scripts' started by Colum, Sep 7, 2008.

  1. Colum

    Colum New Member

    I have a script that adds a user to mysql and makes them a database. This is the php code:
    PHP:
                    $user mysql_escape_string($_POST['user']);
                    
    $pass mysql_escape_string($_POST['user']);
                    
    //add the mess
                    
    $sql 'CREATE USER \'free_'.$user.'\'@\'localhost\' IDENTIFIED BY \''.$pass.'\';'
                        
    ' GRANT USAGE ON *.* TO \'free_'.$user.'\'@\'localhost\' IDENTIFIED BY \''.$pass.'\' ;'
                        
    ' CREATE DATABASE IF NOT EXISTS `free_'.$user.'`;'
                        
    ' GRANT ALL PRIVILEGES ON `free_'.$user.'`.* TO \'free_'.$user.'\'@\'localhost\';'
                    
    mysql_query($sql) or die("<pre>".mysql_error()."<br />".$sql);
    and when I run the code, I get this error:
    Code:
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';# MySQL returned an empty result set (i.e. zero rows). GRANT USAGE ON *.* TO 'f' at line 1
    CREATE USER 'free_testing'@'localhost' IDENTIFIED BY 'testing';
    GRANT USAGE ON *.* TO 'free_testing'@'localhost' IDENTIFIED BY 'testing' ;CREATE DATABASE IF NOT EXISTS `free_testing`;
    GRANT ALL PRIVILEGES ON `free_testing`.* TO 'free_testing'@'localhost';
    now if I take the sql code from above, which is this:
    Code:
    CREATE USER 'free_testing'@'localhost' IDENTIFIED BY 'testing';
    GRANT USAGE ON *.* TO 'free_testing'@'localhost' IDENTIFIED BY 'testing' ;CREATE DATABASE IF NOT EXISTS `free_testing`;
    GRANT ALL PRIVILEGES ON `free_testing`.* TO 'free_testing'@'localhost';
    and run it in phpmyadmin it works. This is all on the same server. Whats up?
     
  2. falko

    falko Super Moderator ISPConfig Developer

    I'd use double quotes for SQL statements:

    Code:
    $sql = "CREATE USER 'free_".$user."'@'localhost' IDENTIFIED BY '".$pass."';"
           . " GRANT USAGE ON *.* TO 'free_".$user."'@'localhost' IDENTIFIED BY '".$pass."' ;"
           . " CREATE DATABASE IF NOT EXISTS `free_".$user."`;"
           . " GRANT ALL PRIVILEGES ON `free_".$user."`.* TO 'free_".$user."'@'localhost';";
     

Share This Page