Can someone help with this array in a sql query

PHP
1

Hi,

Take a look at this method:


    public function insertAuthor($authArray, $PCorder=0)
    {
        $query = sprintf('SELECT Pid, Pname FROM People 
        WHERE Pname IN(\\'%s\\') ORDER BY Pid ASC', 
        implode('\\',\\'', $authArray));
        $result = mysql_query($query);

        $maxquery = "SELECT MAX(CPid) as max FROM ConfPaper";
        $maxresult = mysql_query($maxquery);
        $max = mysql_fetch_array($maxresult);
        $CPid = $max['max'];

        if($result && mysql_num_rows($result) > 0)
        {
            $sqlValues = array();
            while(list($PId, $PName) = mysql_fetch_row($result))
            {
                if(in_array($PName, $authArray))
                {
                    $sqlValues[] = sprintf("(%d, ".$_GET['CPRid'].", 
                    ".$CPid.", %d, now(), 0)", $PId, $PCorder++ );
                    // Author already exists within the Pname table
                    // remove user from $authArray
                    $key = array_search($PName, $authArray);
                    unset($authArray[$key]);
                }
            }

            $sql  = "INSERT INTO PeopleCon(Person_id, PCHid, Pid, 
            PCorder, PCdateadded, PCdeleted) VALUES \
";
            $sql .= implode(",\
", $sqlValues);
            $result = mysql_query($sql);

            // If there are Authors left within the $authArray
            // Add them to the Pname table
            if(count($authArray) > 0)
            {
                $this->insertPersons($authArray); // call insertPersons 
                method for remaining authors
                $this->insertAuthor($authArray, $PCorder); // insert 
                the remaining auhtors into PeopleCon
            }
        }
    }

Then i call the method like so:


        $p = new People();

        $authors = array_filter(array_map('mysql_real_escape_string',
        $_POST['author']));
        $p->insertAuthor($authors);

The query works fine, but it only does the $this->insertPersons($authArray); code IF a person exists, so if this query is true:


        $query = sprintf('SELECT Pid, Pname FROM People WHERE Pname IN(\\'%s\\') ORDER BY Pid ASC', implode('\\',\\'', $authArray));
        $result = mysql_query($query);

It works, but i need to have it so if there are no matches for Pname still do the insert methods…

Any ideas?

2

Eliminate the check? :shifty:

3

Hey,

I was just looking for a quick fix really…

P.S. Anthony, Are you referring to the thread about the 10 tables? This isn’t one of them, this is totally separate… :wink:

4
Off Topic:

I’m pretty sure that, on more than one occasion, you’ve been told that if you just re-factor your database schema instead of your code, you wouldn’t have all these issues.

Is this a possibility or are you just looking for a quick fix?*

*which it isn’t really

5

Which check?

This:


            if(count($authArray) > 0)
            {
                People::insertPersons($authArray);
                $this->insertAuthor($authArray, $PCorder);
            }

I tried taking the IF out, but it went into a never ending loop and kept inserting… :confused: