Why won't this for loop INSERT?

[billy_111]

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]

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]

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]

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]

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]

Add error checking:

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

[wildteen88]

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]

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]

He left out a comma.

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

[wildteen88]

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]

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]

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]

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]

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..

[wildteen88]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.