2015-05-24 00:06
浏览 54


I have created a function in MySQL :

CREATE DEFINER=`DBadmin`@`localhost` FUNCTION `insert_color`(
     p_color_Name VARCHAR(45)
) RETURNS int(11)
        color_count INT;
    SELECT COUNT(p_color_Name) INTO color_count FROM Color
    WHERE p_color_Name = Color.Color_Name;

    IF color_count >= 1 THEN
        RETURN -1;
    ELSEIF color_count = 0 THEN
        INSERT INTO Color(Color_Name)
        VALUES (p_color_Name);
        RETURN 1;
    END IF;

And I want to call it in PHP to insert using the information that was typed in but I am having trouble.


$conn = new mysqli('localhost','DBadmin','dbadmin','BirdDatabase');

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error); 
    echo "-1"; 
    return false; 

$var1 = $_GET['selectedOption'];  
$var2 = $_GET['newNameText'];  
$var3 = $_GET['selectedType'];   

        else if ($var1 == "Color"){ 
            $sqlVariableUser = 'SELECT insert_color(?);';
            if(!$stmt = $conn->prepare($sqlVariableUser)){
                return false;
            } else {
                $result= $stmt->get_result();
                exit ($result);

Im new to MySQL, I know how to do this with an oracle database but I am having trouble with the MySQL connection. Any suggestions would be helpful :)

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

1条回答 默认 最新

  • douxing1850 2015-05-24 01:26

    Seems a lot of rigmarole to accomplish a fairly straightforward task. We tend to avoid creating MySQL stored programs when we can accomplish what we need with just SQL.

    There's a potential race condition in the function, when it's executed simultaneously from two (or more) sessions. The SELECT in both executions may not find the specified color_name, and both sessions will then attempt to insert that same color_name.

    If color_name should be UNIQUE in the color table, then declare a UNIQUE constraint, and let the database check it for you, and return an error when an attempt to insert a duplicate color_name into the table. Or, use INSERT IGNORE if you don't want to bother throwing the error. Or, an INSERT ... ON DUPLICATE KEY statement.

    If there's some reason that color_name can't be made UNIQUE, you could still accomplish what that function is doing, using just one INSERT ... SELECT statement that performs a check if a row already exists.

    INSERT INTO color (color_name) 
    SELECT v.color_name
      FROM ( SELECT c.color_name 
               FROM ( SELECT ? AS color_name ) c
               JOIN color d
                 ON d.color_name = c.color_name
              WHERE d.color_name IS NULL
                AND c.color_name IS NOT NULL
           ) v

    Inline view c returns the a row containing the supplied value, the value of color_name we want to insert to the table, if a matching row doesn't already exist.

    Inline view v uses an anti-join pattern, to return the row from c only if a matching row doesn't already exist in the table. The outer INSERT ... SELECT will attempt to insert a row from v.

    (You asked for suggestions; those are my suggestion.)

    解决 无用
    打赏 举报

相关推荐 更多相似问题