Why won't this for loop INSERT?

billy_111 · August 7, 2010

Hi,

I am trying to get this code to work:

class People {

    public function insertAuthor(){

        $authArray = $_POST['author'];
        $PCorder = 0;

        foreach($authArray as &$author):
            //Check if Pname exists
            $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'];

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

so basically for EVERY author that is inserted, check to see if their name exists and do the INSERT.

I don't get any errors with this code but it doesn't do the INSERT..

Any ideas why?

Thanks again

Regards

Billy

awjudd · August 7, 2010

Without any knowledge of your form ... my guess is that it actually is just getting a string for $_POST [ 'author' ] and not an array ...

~juddster

billy_111 · August 7, 2010

Well this is what the form input looks like:

<input type="text" name="author[]" id="author1" />
<input type="text" name="author[]" id="author2" />
<input type="text" name="author[]" id="author3" />
<input type="text" name="author[]" id="author4" />
...
...
...
<input type="text" name="author[]" id="author10" />

Am i doing something wrong?

Thanks

Fergal Andrews · August 7, 2010

Hi billy_111,

Your form inputs have no value so the form is returning an empty array. Try something like: 


<input type="text" name="author[]" id="author1" value="author1" />
<input type="text" name="author[]" id="author2" value="author2" />
<input type="text" name="author[]" id="author3" value="author3" />
<input type="text" name="author[]" id="author4" value="author4" />
...
...
...
<input type="text" name="author[]" id="author10" value="author10" />

Fergal

billy_111 · August 7, 2010

But will the Value not be the text that i enter into the textbox?

I have amended the input like so:

<input type="text" name="author[]" id="author1" value=""/>

So now when i enter something into the textbox it becomes the value? But it still does not work..

Also when i print out the $sql, i get this:

INSERT INTO PeopleCon(Pid, PCorder, PCdateadded, PCdeleted) VALUES (2, 0, now(), 0)

Which is correct, but then i try adding 2 authors it does not run the for loop it basically checks only for the first author and stops there. So i think my for loop may be in the wrong place?

jcbones · August 7, 2010

Add error checking:

$result = mysql_query($sql) or die($sql . ' [has an error]: ' . mysql_error());

wildteen88 · August 7, 2010

You should write your function like this

class People {

    public function insertAuthor()
    {
        $authArray = array_map('mysql_real_escape_string', $_POST['author']);
        
        $query = sprintf('SELECT Pid, Pname FROM People WHERE Pname = IN(\'%s\')', implode('\',\'' $authArray));
        $result = mysql_query($query);
        
        if($result && mysql_num_rows($result) > 0)
        {
            $PCorder = 0;
            $sqlValues = array();
            while(list($PId, $PName) = mysql_fetch_row($result))
            {
                if(in_array($PName, $authArray))
                    $sqlValues[] = sprinf("(%d, %d, now(), 0)", $PId
                                                              , $PCorder++ );
            }
            
            $sql  = "INSERT INTO PeopleCon(Pid, PCorder, PCdateadded, PCdeleted) VALUES \n";
            $sql .= implode(",\n", $sqlValues);
            
            echo "Generated SQL Query:<pre>$sql</pre>";
            $result = mysql_query($sql);
        }
    }
}

billy_111 · August 7, 2010

Thanks Wildteen,

I'm getting a red line under this:

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

Syntax error: unexpected: $authArray

Any ideas?

jcbones · August 8, 2010

He left out a comma.

implode('\',\'', $authArray)

wildteen88 · August 8, 2010

He left out a comma.
implode('\',\'', $authArray)

Sorry about that. I also made another mistake

                    $sqlValues[] = sprinf("(%d, %d, now(), 0)", $PId
                                                              , $PCorder++ );

The above should read

                    $sqlValues[] = sprintf("(%d, %d, now(), 0)", $PId
                                                              , $PCorder++ );

billy_111 · August 8, 2010

Thank for that

However the code didn't work, i didn't get any errors, but it didn't insert.

So i try checking to see where the code breaks:

    public function insertAuthor()
    {
        $authArray = array_map('mysql_real_escape_string', $_POST['author']);

        $query = sprintf('SELECT Pid, Pname FROM People WHERE Pname = IN(\'%s\')', implode('\',\'', $authArray));
        $result = mysql_query($query);
                    
        if($result && mysql_num_rows($result) > 0)
        {
            die('here');
            
            $PCorder = 0;
            $sqlValues = array();
            while(list($PId, $PName) = mysql_fetch_row($result))
            {
                if(in_array($PName, $authArray))
                    $sqlValues[] = sprintf("(%d, %d, now(), 0)", $PId
                                                              , $PCorder++ );
            }

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

            die ("Generated SQL Query:<pre>$sql</pre>");
            $result = mysql_query($sql);
        }
    }

See die('here');

Basically the code does not reach the IF statement, it does not die, so that means it does not like this line:

if($result && mysql_num_rows($result) > 0)...

I tried echoing out the $query to see what it was showing and it displays like this:

SELECT Pid, Pname FROM People WHERE Pname = IN('Testing 1','Testing 2')

So this also seems fine, that means the IF statement is not firing.

Any ideas?

Thanks

wildteen88 · August 8, 2010

Remove the equals sign before IN() on this line

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

Dont forget to remove die('here');. Now it should function as intended.

billy_111 · August 8, 2010

Thanks!

This was actually just part of the query, just finished writing the second part, but i know theres bugs in it.

This is the full query,

public function insertAuthor()
    {
        $authArray = array_map('mysql_real_escape_string', 
           $_POST['author']);
       
        $query = sprintf('SELECT Pid, Pname FROM People 
        WHERE Pname IN(\'%s\')', implode('\',\'', $authArray));
        $result = mysql_query($query);

        $PCorder = 0;
        $sqlValues = array();

        if($result && mysql_num_rows($result) > 0)
        {
            while(list($PId, $PName) = mysql_fetch_row($result))
            {
                if(in_array($PName, $authArray))
                    $sqlValues[] = sprintf("(%d, %d, now(), 0)", 
                   $PId , $PCorder++ );
            }

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

            $result = mysql_query($sql);
        } else {
            while(list($PId, $PName) = mysql_fetch_row($result))
            {
                if(in_array($PName, $authArray))
                    $sqlValues[] = sprintf("(%d, %d, now(), 0)", 
                  $PId, $PCorder++ );
                    $peopleValues[] = sprintf("(%d, now(), 0)", 
                      $_POST['author']);
            }

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

            $p_sql  = "INSERT INTO People(Pname, Pdateadded, 
            Pdeleted) VALUES \n";
            $p_sql .= implode(",\n", $peopleValues);
            $p_result = mysql_query($p_sql);
        }
    }

Firstly, i have 2 of the same INSERT statement in both sets of if conditions, which i am sure this can be prevented somehow? Also what this function should be doing is firstly checking to see if an author exists, if so, INSERT them into ONLY the PeopleCon table, which works.

If not, then INSERT them into both People AND PeopleCon..

Where am i going wrong?

billy_111 · August 8, 2010

In relation to the second insert that i want to incorporate, i think the code i posted is very wrong. The logic is to check to see if an author exists if so add in one table, if not add in both tables.

I don't think i need to do it in the ELSE part of the if statement. Because i need to check for EVERY user in the array..

So i currently have this:

    public function insertAuthor()
    {
        $authArray = array_map('mysql_real_escape_string', 
        $_POST['author']);
       
        $query = sprintf('SELECT Pid, Pname FROM People 
        WHERE Pname IN(%s)', implode('\',\'', $authArray));
        $result = mysql_query($query);

        $PCorder = 0;
        $sqlValues = array();

        if($result && mysql_num_rows($result) > 0)
        {
            while(list($PId, $PName) = mysql_fetch_row($result))
            {
                if(in_array($PName, $authArray))
                    $sqlValues[] = sprintf("(%d, %d, now(), 0)", 
                    $PId, $PCorder++ );
            }

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

            $result = mysql_query($sql);
        }

        $p_sql  = "INSERT INTO People(Pname) VALUES ...??";
    }

See this line, all i need to do is INSERT into People if $_POST['author'] does not already exist.

I am trying to do it here:

$p_sql  = "INSERT INTO People(Pname) VALUES ...??";

But i don't wuite know how to do this, and check for every author in the array.. :confused:

wildteen88 · August 8, 2010

Try this out I have recoded the insertAuthor method slightly and created a new method which deals with inserting authors into the People table.

<?php

class People {

    public function insertPersons($PName)
    {
        $query = "INSERT INTO People (Pname) VALUES ('" . implode("'), ('", $PName) . "')";
        $result = mysql_query($query);
        
        if($result)
        {
            echo "Inserted " . implode(', ', $PName) . " into People table";
            return true;
        }
        
        return false;
    }

    public function insertAuthor($authArray, $PCorder=0)
    {        
        $query = sprintf('SELECT Pid, Pname FROM People WHERE Pname IN(\'%s\')', implode('\',\'', $authArray));
        $result = mysql_query($query);
        
        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, %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(Pid, PCorder, PCdateadded, PCdeleted) VALUES \n";
            $sql .= implode(",\n", $sqlValues);
            
            echo "Generated SQL Query:<pre>$sql</pre><hr />";
            $result = mysql_query($sql);
            
            // If there are Authors left within the $authArray
            // Add them to the Pname table
            if(count($authArray) > 0)
            {
                // call insertPersons method for remaining authors
                $this->insertPersons($authArray);
                
                // now we call this method again and insert the remaining auhtors into PeopleCon
                $this->insertAuthor($authArray, $PCorder);
            }
        }
    }
}

if(isset($_POST['submit']))
{
    $conn = mysql_connect('localhost', 'root', 'pass') or die('MySQL Error: ' . mysql_error());
    mysql_select_db('test') or die('MySQL Error: ' . mysql_error());
    
    $p = new People;
    
    $authors = array_filter(array_map('mysql_real_escape_string', $_POST['author']));
    $p->insertAuthor($authors);
}

?>
<form action="" method="post">
<?php for($i = 1; $i <= 10; $i++): ?>
    <input type="text" name="author[]" value="author<?php echo $i; ?>" /><br />
<?php endfor; ?>
    <input type="submit" name="submit" value="submit" />
</form>

Note how I am now passing $_POST['author'] to your insertAuthor method.

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

billy_111 · August 8, 2010

thanks that works perfectly!

Just one thing, i thought i would need to add a SELECT MAX () to get the ID of the row inserted?

How has this worked in the code you have posted?

Thanks again

wildteen88 · August 8, 2010

Because the code is now inserting more than one row at a time there isn't any easy way of gathering all the ids for the newly inserted rows. You can however get the very last row's id using mysql_insert_id.

billy_111 · August 8, 2010

Ah ok, well i was just testing the statement and managed to find one minor issue.

When i add one existing and 2 NEW authors it inserts like this:

People
12, Existing Author

23, Testing Author 1

14, Testing Author 2

PeopleCon

11, 0, 2010-08-08 15:37:18, 0

14, 1, 2010-08-08 15:37:18, 0

23, 2, 2010-08-08 15:37:18, 0

You see the problem? The PeopleCon table Pid's should have been inserted in this order or Pid 11, 23, 14 but instead inserted like this 11, 14, 23, so this means that the order of insertion is wrong.

Can this be overcome?

wildteen88 · August 8, 2010

Both tables should have some form of an auto_increment id field should they not? I was assuming the Pid field for the People table was set to auto_increment?

Post your table structure for both People and PeopleCon

billy_111 · August 8, 2010

Ok table structure is as follows:

CREATE TABLE IF NOT EXISTS `People` (

`Pid` int(11) NOT NULL auto_increment,

`Pname` varchar(255) NOT NULL,

PRIMARY KEY (`Pid`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;

CREATE TABLE IF NOT EXISTS `PeopleCon` (

`PCid` int(5) NOT NULL auto_increment,

`Pid` int(5) NOT NULL,

`PCorder` int(2) NOT NULL,

`PCdateadded` datetime NOT NULL,

`PCdeleted` int(1) NOT NULL,

PRIMARY KEY (`PCid`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

Pid is the auto_increment.

Am i doing something wrong here? :-\

wildteen88 · August 8, 2010

Your People tables Pid field is set to auto_increment so Testing Author 1 (pid of 23) will always be inserted after Testing Author 2 (pid of 14).

How your records are positioned within your database shouldn't matter.

billy_111 · August 8, 2010

Yes but the PCorder column is causing an issue, take a look at these of records:

11, 0, 2010-08-08 15:37:18, 0

14, 1, 2010-08-08 15:37:18, 0

23, 2, 2010-08-08 15:37:18, 0

the 0, 1, and 2 is the PCorder column. 23 is the Pid which has been inserted fine but the PCorder for this column should have been 1 and not 2.

I'm not sure but is the PCorder++ increment causing an issue?

wildteen88 · August 8, 2010

That is not possible. Clear your tables, now change the following

                    $sqlValues[] = sprintf("(%d, %d, now(), 0)", $PId
                                                               , $PCorder++ );

to

                    $sqlValues[] = sprintf("(%d, %d, now(), 0)", $PId
                                                               , $PCorder++ );
                                                               
                    echo "<p>The current record is: Pid = $PId and PName = '$PName' and has been given the PCorder of - $PCorder<p>";

Run your script again and post the output here. You'll see the records are being inserted in their current order within the Person table, and you'll see PCorder being incremented by 1

billy_111 · August 8, 2010

Ok i just tried that and i got this:

The current record is: Pid = 1 and PName = 'Shehzad Muhammad Hanif' and has been given the PCorder of - 1

The current record is: Pid = 2 and PName = 'Lionel Prevost' and has been given the PCorder of - 2

The current record is: Pid = 19 and PName = 'Cat' and has been given the PCorder of - 3

The current record is: Pid = 18 and PName = 'Dog' and has been given the PCorder of - 4

Th ones in bold are the wrong way around. Cat should have an PCorder of 4 and Dog should be 3. I added them in the form in this order:

Shehzad Muhammad Hanif
Lionel Prevost
Dog
Cat

So do you see why Cat should have an PCorder of 4?

wildteen88 · August 8, 2010

Change

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

To

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

Test it again.

Sign In

Why won't this for loop INSERT?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information