1

Answer: https://stackoverflow.com/a/24034862/1173155

I am testing a Stored procedure as follows:

DELIMITER $$
CREATE PROCEDURE `test1`(IN `tab_name` VARCHAR(40), IN `value_of` VARCHAR(40), OUT `the_id` INT(1))
BEGIN
 SET @t1=CONCAT('
    BEGIN 
        IF EXISTS (SELECT ',tab_name,'.id from ',tab_name,' where ',tab_name,'.',tab_name,' = ',value_of,')
            THEN
            select id into ',the_id,' from ',tab_name,' where ',tab_name,'.',tab_name,' = ',value_of,';
        ELSE 
            insert into ',tab_name,' values (NULL,',value_of,');
END IF;');
 PREPARE stmt3 FROM @t1;
 EXECUTE stmt3;
 DEALLOCATE PREPARE stmt3;
END $$
DELIMITER ;

Trying to select the id, or insert. I have a single row in the table, when I try and use this Procedure as is; it doesn't return anything.

The identifier for the value I want is the table name for simplicities sake. Have spent many hours and I'm at a loss.

Have tried everything, even now just thought adding the NULL, would work as the table has two values and I had forgotten to put it in only to realise I was trying it within the first if clause... IE, entering data to ensure that the first statement would be invoked

Thanks for any help

Update

Still having trouble with this. Trying to make it as simple as possible now.

I have a city table, with 1 record it in. Structure is like so city.city = 'Dublin'

Value and table name are the same. The follow select statement works when executed via phpMyAdmin

BEGIN
     SET @t1= CONCAT("

         SELECT id 
             FROM ",tab_name," 
                     WHERE ",tab_name," = '",value_of,"'


                     ");
     PREPARE stmt3 FROM @t1;
     EXECUTE stmt3;
     DEALLOCATE PREPARE stmt3;
END

Now I just need to ensure that if the select statement doesn't return anything, then insert the value and return the select statement.

This seems like such a simple problem, yet I can't find the solution anywhere

--

INSERT IGNORE is not good because it increments the ID regardless of inserting. Is there a work around for this because I could just use that then.

INSERT IGNORE... // Without increment?

SELECT ^ ....

Further Update

My procedure now looks like this (takes two VARCHAR parameters) and this is currently working. It doesn't increment the ID if the value already exists either which is simply fantastic.

BEGIN
 SET @t1= CONCAT("INSERT INTO ",tab_name,"(",tab_name,")
SELECT '",city_name,"' FROM dual
 WHERE NOT EXISTS (SELECT 1 
    FROM ",tab_name," 
     WHERE ",tab_name," = '",city_name,"');
        ");
   PREPARE stmt3 FROM @t1;
     EXECUTE stmt3;
     DEALLOCATE PREPARE stmt3;
    END

All I want now is for a simple SELECT statement to return the id of the row with that city name. eg SELECT id FROM tab_name WHERE tab_name = 'city_name';

But adding this in causes an error :( Thanks if anyone has a solution to this ridiculous problem

Improve this question
4
  • What does PDO have to do with this? Are you having trouble creating the procedure? You should probably not be creating it via PDO, but instead with a regular MySQL client (the DELIMITER implies that's what you're doing) Commented Jun 3, 2014 at 18:22
  • @MichaelBerkowski Apologies, you are correct; I have updated the title and tags. I am creating it in MySQL and going to call it through PDO Commented Jun 3, 2014 at 18:25
  • So is the problem now that the statement won't CREATE, or that it executes incorrectly? What looks a little suspect is tab_name.tab_name, unless you always have a database with the same name as the input tab_name (which seems unlikely) Commented Jun 3, 2014 at 18:30
  • Its not returning anything, through phpmyadmin. Tab name thing is how i have these named, although i understand your query about that Commented Jun 3, 2014 at 18:32

2 Answers 2

Reset to default
1

The solution that is working via phpMyAdmin. Will have to test in PHP and PDO too and see if all is good.

CREATE PROCEDURE `select_insert`(IN `the_value` VARCHAR(150), IN `tab_name` VARCHAR(50))
BEGIN
 SET @t1= CONCAT("INSERT INTO ",tab_name,"(",tab_name,")
SELECT '",the_value,"' FROM dual
 WHERE NOT EXISTS (SELECT 1 
    FROM ",tab_name," 
     WHERE ",tab_name," = '",the_value,"');
        ");
   PREPARE stmt FROM @t1;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;

     SET @t2 = CONCAT("SELECT id FROM ",tab_name," WHERE ",tab_name," = '",the_value,"'");
      PREPARE stmt1 FROM @t2;
   EXECUTE stmt1;
   DEALLOCATE PREPARE stmt1;
    END

This is a generic procedure for inserting or retrieving values that are unique in the table. I used a city example while trying to figure this out.

The first prepared statement inserts the value provided into the table name provided. It doesn't increase the auto increment value if no insertion is made.

The second prepared statement retrieves the value. There shouldn't really be a case where no value is returned due to the nature of the first query.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

The statement you wrote should not return results. Both the select into and insert statements do not return results.

It's a stored procedure.

Do the SELECT first, then follow it with the insert(s). The select will be the first output result set of the procedure.

Improve this answer

3 Comments

I really don't know what you mean by doing the select first. I thought I was doing the select first, ie. if the statement exists, select; else insert it
Write a select statement before the "IF". In your solution, you called a select after the insert.
Do you mind checking my answer? stackoverflow.com/a/24034862/1173155 Thanks by the way, but I have it working now as I intended

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.