For loop Insert

billy_111 · July 29, 2010, 12:05am

Hi,

I was wondering if anyone can help me with this… I am trying to add multiple rows into the database at the same time depending on the number of text boxes…

Take a look at this page:

http://www.prima.cse.salford.ac.uk:8080/~ibrarhussain/test.html

When you click on “Add author”, it adds another textbox directly below, they are named like so:

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

This is my INSERT currently, but i know i am going wrong somewhere:


    public function insertAuthor(){

        foreach($_POST['author'] as $author):
            $sql = "INSERT INTO People
                        (Pname, Pdateadded, Pdeleted) VALUES
                        (
                        '.$author.',
                        now(),
                        0
                        )";
            $result = mysql_query($sql);
        endforeach;
        return "Successfully added author";
    }

I don’t get any errors but it just does not do the INSERT…

Any ideas how i can fix this…

Thanks again

billy_111 · July 31, 2010, 11:55am

Does this explanation even make sense hope i haven’t confused anyone…

billy_111 · July 30, 2010, 7:02pm

Ok, let me explain…

The website i am making is a web based digital library and will only be used by VERY limited people. I have had a re-think and have decided to have an email field in People table so that an author with the same name can be inserted but with different emails so that we can differentiate between them…

The digital library will contain journals, documents, papers etc that have been written by researchers…

Moving on, the purpose of the 2 tables is, the People will simply hold the Authors personal info, and the PeopleCon will contains authors that have written a specific document. Obviously most of the time, a paper is written by multiple authors. So if we look at the tables now:

People
Pid, Pname, Pemail, Pdateadded, Pdeleted

And…

PeopleCon
PCid, Pid, PCHid, PCorder, PCdateadded, PCdeleted

So “PCHid” is the ID of the paper/document what has been written. the “Pid” will be the ID of the author from the People table. And finally if for example there are 6 authors who have written the same paper the results should look like this in the PeopleCon table:

1, 1, 29, 0, 2010-07-24 17:20:49, 0
1, 2, 29, 1, 2010-07-24 17:20:49, 0
1, 3, 29, 2, 2010-07-24 17:20:49, 0
1, 4, 29, 3, 2010-07-24 17:20:49, 0
1, 5, 29, 4, 2010-07-24 17:20:49, 0
1, 6, 29, 5, 2010-07-24 17:20:49, 0

So you see ‘29’ is the ID of the document which is from a different tabal, i can JOIN this later. As you can see the PCorder will increment i have shown this as 0 to 5.

But can you see what i am trying to do here?

I will add an email field with the author so that we can now distinguish between whether or not an author has exists by their email, i will need to change my first method to this:


    public function checkEmailExists(){

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

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

So if you look back at my code and the 3 methods, i need to somehow combine these into one, check to see if an author exists, if so insert that author as a NEW author in People, AND insert them into the PeopleCon. Now this needs to be inside a for loop and needs to be done for every author that is inserted. So there may be a time where 4 authors are inserted and only 2 are new and 2 are not and vice versa…

Does this answer you questions

And back to the main question, how can i do this

Thanks again

AnthonySterling · July 30, 2010, 5:54am

Ah, I see.

Well, you’re duplicating data. I’m sure the reason you’re uncomfortable with your current solution is, deep down, you know it’s wrong.

If the author name needs to be unique, which is what appears to be the case, declare it in the database. We can then let the database decide what to with duplicate entries, making your code cleaner/leaner.

Whether or not enforcing this restraint is the right thing to do is another issue. I’m pretty sure, in real life, there are plenty of people named the same. maybe some of them are authors too!

Can you explain the reasoning behind the 2 tables?

billy_111 · July 29, 2010, 8:01pm

Thanks Anthony,

I actually have the INSERT authors method working, but what i am stuck with is checking to see if the Person already exists, if so add them to one table, if not add them to both tables…

How can i do this?

r937 · July 29, 2010, 11:43am

no, don’t use that code

if you had bothered to read the thread, you’d know why

Raju_Gautam · July 29, 2010, 12:20pm

billy_111:

So it works perfectly, just one thing, can you please explain why i need these 2 lines:
$callback = create_function('$author','return "(\\'".mysql_real_escape_string($author)."\\',NOW(),0)";');
implode(',',array_map($callback,$_POST['author']))
I want to understand the code you see, so if something like this comes up again i know exactly what to do…

First of all see the two functions used in php manual create_function() and [URL=“http://www.php.net/array_map”]array_map() what they exactly do, then you will know why he has used those two lines.

You must have seen there is no loop to insert multiple records. So not to use loop or you can say for fast performance and easy to read, the code above is good.

Edit:
One more suggestion for your HTML part. The ID of an element must be unique so instead of


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

use this one:


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

The above one should be XHTML invalid.

oddz · July 29, 2010, 12:49am


<?php
public function insertAuthor(){
	$callback = create_function('$author','return "(\\'".mysql_real_escape_string($author)."\\',NOW(),0)";');
	$sql = sprintf(
		'INSERT INTO People (Pname, Pdateadded, Pdeleted) VALUES &#37;s'
		,implode(',',array_map($callback,$_POST['author']))
	);
	$result = mysql_query($sql);
        echo '<p>',$sql,'</p>';	
	return "Successfully added author";
}
?>

r937 · July 29, 2010, 12:35am

sorry i cannot help you with your php, as i don’t do php, but i can sorta read it

your loop will generate the following –

INSERT INTO People (Pname,Pdateadded,Pdeleted) VALUES (‘malzberg’,now(),0);
INSERT INTO People (Pname,Pdateadded,Pdeleted) VALUES (‘asimov’,now(),0);
INSERT INTO People (Pname,Pdateadded,Pdeleted) VALUES (‘niven’,now(),0);
INSERT INTO People (Pname,Pdateadded,Pdeleted) VALUES (‘haldeman’,now(),0);
INSERT INTO People (Pname,Pdateadded,Pdeleted) VALUES (‘card’,now(),0);

what you actually want is to generate this instead –

INSERT INTO People (Pname,Pdateadded,Pdeleted) VALUES
(‘malzberg’,now(),0)
,(‘asimov’,now(),0)
,(‘niven’,now(),0)
,(‘haldeman’,now(),0)
,(‘card’,now(),0)
;

see the difference? one database call instead of one per author, makes your page way faster

so you have to move your for loop down inside the sql statement

oddz · July 30, 2010, 5:48pm

The way to see if an author is being edited vs. created for a surrogate key is add a hidden input for the primary key to the form. You would than add a on duplicate key update to the inert statement to update authors that exist and insert those that don’t.


<input type="text" name="author[0]['name']">
<input type="hidden" name="author[0][id]" value="4">

<input type="text" name="author[1]['name']">
<input type="hidden" name="author[1][id]" value="5">

<input type="text" name="author[2]['name']">
<input type="hidden" name="author[2][id]" value="23">

<input type="text" name="author[3]['name']">
<!-- this will be a new author -->

billy_111 · July 29, 2010, 6:58pm

Is doing this even the right method to use?

Would it be totally impractical to implement? If so, how else could i possibly go about doing this?

AnthonySterling · July 29, 2010, 7:46pm


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>
      Demo
    </title>
  </head>
  <body>
    <form method="post" action="">
      <input name="author[][name]" type="text" />
      <input name="author[][name]" type="text" />
      <input name="author[][name]" type="text" />
      <input name="author[][name]" type="text" />
      <input name="author[][name]" type="text" />
      <input name="author[][name]" type="text" />
      <input value="Save Authors." type="submit" />
    </form>
  </body>
</html>


<?php
if(0 < count($_POST['author'])){
  $sql = 'INSERT INTO authors (name, created_on, is_deleted) VALUES';
  foreach((array)$_POST['author'] as $author){
    $sql = sprintf(
      "('%s', NOW(), false),",
      mysql_real_escape_string($author['name'])
    );
  }
  mysql_query(rtrim($sql, ','));
}
?>

RishikeshJha · July 29, 2010, 11:21am

Use this code to insert the records in loop
foreach($_POST[‘author’] as $author):

        $sql = "INSERT INTO People SET Pname='$author',
                                                   Pdateadded=now(),
                                                    Pdeleted=0";

        $result = mysql_query($sql);

    endforeach;

billy_111 · July 29, 2010, 11:15am

Thanks Oddz,

I see what you mean now by doing one INSERT statement:

This is what i got:

,INSERT INTO People (Pname, Pdateadded, Pdeleted) VALUES (‘Author 1’,NOW(),0),(‘Author 2’,NOW(),0),(‘Author 3’,NOW(),0)

So it works perfectly, just one thing, can you please explain why i need these 2 lines:


$callback = create_function('$author','return "(\\'".mysql_real_escape_string($author)."\\',NOW(),0)";');
implode(',',array_map($callback,$_POST['author']))

I want to understand the code you see, so if something like this comes up again i know exactly what to do…

Thanks again

billy_111 · July 29, 2010, 2:46pm

Thanks for that, i have applied that so now the ID’s are unique…

One more thing, lets say i wanted to make this slightly more complicated

Basically i have these 2 tables:

People
Pid
Pname
Pdateadded
Pdeleted

And…

PeopleCon
PCid
Pid
PCorder
Pdateadded
Pdeleted

Now, currently i insert the data into a table “People”… like you guys have helped me with…

But what i need to do is, first check to see if the author already exists in the People table, if so then just INSERT into PeopleCon, ELSE INSERT into both People and PeopleCon…

Also if there are 4 authors, and 2 exist in People, and 2 do not. What needs to happen is the first 2 are simply inserted into PeopleCon, but the last 2 are INSERTED into both People and PeopleCon. When the authors are inserted into PeopleCon, the PCorder row needs to increment by the number of authors byt order of author.

So this is the logic:

Multiple authors are entered in textboxes
Fire the script
Check to see if author already exists in “People” table
IF it does exist, INSERT into PeopleCon table
ELSE do the insert into the People AND PeopleCon table, first it will INSERT into the People, and GET the MAX (ID) and insert into the PeopleCon

So i currently have this, but as 3 separate methods:


class People {

    public function checkNameExists(){

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

        $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 &#37;s'
            ,implode(',',array_map($callback,$_POST['author']))
        );
        $result = mysql_query($sql);
        return "Successfully added NEW author";
    }

    public function insertAuthorCon(){

        $sql = "INSERT INTO PeopleCon
                    (Pid, PCorder, PCdateadded, PCdeleted) VALUES
                    (
                    'MAX ID WILL GO HERE',
                    'INCREMENT OF ORDER GOES HERE',
                    now(),
                    0
                    )";
        $result = mysql_query($sql);
        return "Successfully added existing author";
    }

But obviously i need to combine these together into one method to match what i am trying to achieve…

Can anyone point me in the right direction?

Thanks again…