Looping through INSERT

PHP
1

Hi,

I was wondering if this was possible, i am INSERTING an array of authors like so in a table, see insertAuthor() method below:


<?php
class People {

    public function checkNameExists(){

       $query = "SELECT * FROM People WHERE Pname = '". mysql_real_escape_string($_POST['Pname'])."'";

        $result = mysql_query($query);
        if(mysql_num_rows($result) > 0):
            $row = mysql_fetch_array($result);
            return true;
        else:
            return false;
        endif;
    }

    public function insertAuthor(){
        $callback = create_function('$author','return "(\\'".mysql_real_escape_string($author)."\\',NOW(),0)";');
        $sql = sprintf(
            'INSERT INTO People (Pname, Pdateadded, Pdeleted) VALUES %s'
            ,implode(',',array_map($callback,$_POST['author']))
        );
        $result = mysql_query($sql);
        return "Successfully added author";
    }

    public function insertAuthorCon(){

        $sql = "INSERT INTO PeopleCon
                    (Pid, PCorder, PCdateadded, PCdeleted) VALUES
                    (
                    'MAX ID WILL GO HERE',
                    (select MAX(PCorder) + 1),
                    now(),
                    0
                    )";
        $result = mysql_query($sql);
        return "Successfully added event";
    }
}
?>

However what i need to do is, check to see if an Author exists, if so INSERT into a table called PeopleCon, if the Author does NOT exist i need to INSERT into 2 different tables, People and PeopleCon…

The reason i have the 2 tables is because People simply holds the persons personal details, but PeopleCon holds details linking to different tables.

So i already have the authors looping through and INSERTING into the People table, but i now need to check to see if the author exists which will be checked by the name.

I do this in the front end:


if(isset($_POST['add_author'])):
        People::insertAuthor();
endif;

So now i need to change the checkNameExists AND insertAuthorCon method and somehow combine the 3 so i can accomplish this.

Is this possible?

I would appreciate any help.

Thanks

2

Yes i had a look at it, but can’t see how to incorporate the same method in my code :frowning:

I think the DUPLICATE KEY UPDATE looks like it will work well with what i am trying to achieve, as it would remove the duplicated queries i have in my current code…

Can you point me in the right direction? :confused:

3

Did you see the link I posted? Did you try it? Didn’t it work?

4

Or better still how can i use INSERT INGORE in my code to get it to work properly…?

5

Well i tried having a go at showing you guys what i am trying to accomplish, i know there will be flaws in the below code but i’m sure you will see what i am trying to achieve:


class People {

    public function insertAuthor(){

        //Check if Pname exists
        $query = "SELECT * FROM People WHERE Pname = '". mysql_real_escape_string($_POST['Pname'])."'";
        $result = mysql_query($query);

        if(mysql_num_rows($result) > 0):
            $row = mysql_fetch_array($result);
            $Pid = $row['Pid'];
            
            //If exists Get their Pid and insert into PeopleCon
            $sql = "INSERT INTO PeopleCon
            (Pid, PCorder, PCdateadded, PCdeleted) VALUES (".$Pid.", (select MAX(PCorder) + 1), now(),0)";
            $result = mysql_query($sql);
            return "Added existing Author";
        else:
            //Else Get MAX Pid
            $query = "SELECT MAX(Pid) as max FROM People";
            $result = mysql_query($query);
            $max = mysql_fetch_array($result);
            $Pid = $max['max'];

            //Insert into People
            $callback = create_function('$author','return "(\\'".mysql_real_escape_string($author)."\\',NOW(),0)";');
            $sql = sprintf(
                'INSERT INTO People (Pname, Pdateadded, Pdeleted) VALUES %s'
                ,implode(',',array_map($callback,$_POST['author']))
            );
            $result = mysql_query($sql);
            return "Successfully added author";
            
            //Insert into PeopleCon
            $sql = "INSERT INTO PeopleCon
            (Pid, PCorder, PCdateadded, PCdeleted) VALUES (".$Pid.", (select MAX(PCorder) + 1), now(),0)";
            $result = mysql_query($sql);
            return "Added existing Author";
        endif;
    }
}

There’s a few problems i can see here, as it needs to be looping through for each author that is inserted…

Can you see what i am trying to do? Can you help?

Thanks

6

Maybe INSERT IGNORE resolves your problem?

http://dev.mysql.com/doc/refman/5.1/en/insert.html

7

I have amended my code as per below:


    public function insertAuthor(){

        $authArray = array();
        array_push($authArray, mysql_real_escape_string($_POST['author']));
        
        //Check if Pname exists
        foreach($authArray as $author):
            $query = "SELECT * FROM People WHERE Pname = '".$author."'";
            $result = mysql_query($query);

            if(mysql_num_rows($result) > 0):
                $row = mysql_fetch_array($result);
                $Pid = $row['Pid'];

                //If exists Get their Pid and insert into PeopleCon
                $sql = "INSERT INTO PeopleCon(Pid, PCorder, PCdateadded, PCdeleted) VALUES (".$Pid.", (select MAX(PCorder) + 1), now(), 0)";
                $result = mysql_query($sql);
            else:
                //Else Get MAX Pid
                $query = "SELECT MAX(Pid) as max FROM People";
                $result = mysql_query($query);
                $max = mysql_fetch_array($result);
                $Pid = $max['max'];

                //Insert into People
                $sql = "INSERT INTO People (Pname, Pdateadded, Pdeleted) VALUES ('".$author."', now(), 0)";
                $result = mysql_query($sql);

                //Insert into PeopleCon
                $sql = "INSERT INTO PeopleCon(Pid, PCorder, PCdateadded, PCdeleted) VALUES (".$Pid.", (select MAX(PCorder) + 1), now(),0)";
                $result = mysql_query($sql);
            endif;
        endforeach;
    }

Just to make it slightly more readable… I think i can somehow remove the need to have 2 insert statements…