Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

PHP Beginner: adding a new member to a table

Posted on 2006-04-18
66
Medium Priority
?
435 Views
Last Modified: 2013-12-12
Hello.  I just started learning PHP.  I'm also not very good at programming :(

I want to add a new member to a table in my database.  The table is set up so that the person_id is an automatically incremented number (so with each new member added, the person_id is +1 from the previous person_id).  I don't know how to add the information I pull off a form from a webpage to the database.  I keep getting an error telling me that it's unable to insert into the database.  Everything I have is set up on localhost using Windows XP IIS 5.0 and MySQL Server.  I made a screencap of the ER-diagram I drew of my database.  I'm only using the member and person tables for now (I think): http://img105.imageshack.us/img105/2056/db1do.jpg.  Please let me know what I did wrong, or if you need more information.  Thank you.

Partial coding:
-----------------------------------------------------------

...
  if ($isPostback) {
        $username = getRequestValue("username");
        $password = getRequestValue("password");
        $firstname = getRequestValue("firstname");
        $middlename = getRequestValue("middlename");
        $lastname = getRequestValue("lastname");
        $employer = getRequestValue("employer");
        $graduation_date = getRequestValue("graduation_date");
        $subnewsletter = getRequestValue("subnewsletter");

            $validated = true;

            if ($validated) {
              // $staff_id = $_SESSION["LoggedOnPersonId"];
          $sql = "INSERT INTO person (NULL, username, password, firstname, middlename, lastname) VALUES ";

// person_id is supposed to be on auto-increment

              $sql = $sql."('NULL', '$username', '$password', '$firstname', '$middlename', '$lastname')";

          $sql = "INSERT INTO member (employer, NULL, graduation_date, NULL, subnewsletter, firstname, middlename, lastname, NULL,) VALUES ";

// person_id is supposed to be on auto-increment

              $sql = $sql."('$employer', 'NULL', '$graduation_date', 'NULL', '$subnewsletter', '$firstname', '$middlename', '$lastname', 'NULL', )";

              //echo($sql);
                  $conn = getConnection();
                  if (mysql_query($sql, $conn)) {
                    $feedback = "Activity inserted into database successfully.";
                  }
                  else {
                    $feedback = "Error, unable to insert record into database.";
                  }
            }
            else {
              $feedback = "Invalide input.";
            }
      }
?>

...
        <form name="form1" method="post">
          <p><strong>Member Entry Form </strong></p>

            <table border="0">
            <!-- This gathers information for the person table. -->
          <tr>
            <th>UserName:</th>
            <td><input name="username" id="username"><?php echo(getRequestValue("username"))?></td>
          </tr>
          <tr>
            <th>Password:</th>
            <td><input name="password" id="password"><?php echo(getRequestValue("password"))?></td>
          </tr>
          <tr>
                <th>First Name:</th>
                <td><input name="firstname" id="firstname"><?php echo(getRequestValue("firstname"))?></td>
          </tr>
          <tr>
                <th>Middle Name:</th>
                <td><input name="middlename" id="middlename"><?php echo(getRequestValue("middlename"))?></td>
          </tr>
          <tr>
                <th>Last Name:</th>
                <td><input name="lastname" id="lastname"><?php echo(getRequestValue("lastname"))?></td>
          </tr>
          <tr>
                <th>Employer:</th>
                <td><input name="employer" id="employer"><?php echo(getRequestValue("employer"))?></td>
          </tr>
          <tr>
                <th>Graduation Date:</th>
                <td><input name="graduation_date" id="graduation_date"><?php echo(getRequestValue("graduation_date"))?></td>
          </tr>
          <tr>
                <th>Subscribe Newsletter:</th>
                <td><input name="subnewsletter" id="subnewsletter"><?php echo(getRequestValue("subnewsletter"))?></td>
          </tr>
        </table>
          <br>
          <input type="submit" name="Submit" value="Submit">
          <input type="reset" name="Submit2" value="Clear">
          <p align="center">&nbsp;</p>
        </form>
...
0
Comment
Question by:luna621
  • 31
  • 19
  • 16
66 Comments
 

Author Comment

by:luna621
ID: 16476321
>          $sql = "INSERT INTO person (NULL, username, password, firstname, middlename, lastname) VALUES ";

I put NULL because I didn't know what else to put for the person_id.  It's supposed to be auto incremented, but how does the PHP code know that?
0
 
LVL 40

Assisted Solution

by:Richard Quadling
Richard Quadling earned 200 total points
ID: 16476746
You don't need to put the "NULL,".

All default columns will default!

So ...

$sql = <<< END_SQL
INSERT INTO
      person
            (
            username,
            password,
            firstname,
            middlename,
            lastname
            )
      VALUES
            (
            ...,
            )
END_SQL;
0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16476751
Actually you can simply ignore those columns, i.e.,

  $sql = "INSERT INTO person (username, password, firstname, middlename, lastname) VALUES ('$username', '$password', '$firstname', '$middlename', '$lastname')";

And the second query, as it is set to the same variable as the first, will override the first query. Assign it to another variable, e.g.,

  $sql2 = "INSERT INTO member (employer, graduation_date, subnewsletter, firstname, middlename, lastname) VALUES ('$employer', '$graduation_date', '$subnewsletter', '$firstname', '$middlename', '$lastname')";

And of course you'll need to query twice:

  $conn = getConnection();
  if (mysql_query($sql, $conn)) {
    $last_id = mysql_insert_id ($conn);
    if (mysql_query ($sql2, $conn)) {
      $feedback = "Activity inserted into database successfully.";
    }

Note that, however, in rare cases the first query will work while the second will fail. In that case you should delete the row inserted in the first query:

    else {
      mysql_query ("DELETE FROM person WHERE person_id = $last_id", $conn);
      $feedback = "Error, unable to insert record into database.";
    }
  } else {
    $feedback = "Error, unable to insert record into database.";
  }

Hope these help.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:luna621
ID: 16477011
Thank you.  That helped a little, but it's still not letting me add to the database.  I need to sleep, so I'll work on this again tomorrow.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16477033
Ok. You SHOULD be getting an error.

At the top of the script put ...

<?php
error_reporting(E_ALL);
?>

Let's see ALL the errors/warnings/notices.

Today's a notices could become tomorrow's errors!
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16477037
OOI. How do you know it has not been added? Sounds daft, but you'd be surprised how often the tool looking at the data is looking at OLD data!!!
0
 

Author Comment

by:luna621
ID: 16486169
Ok, here are the warnings:

Warning: session_start() [function.session-start]: Cannot send session cookie - headers already sent by (output started at c:\Inetpub\wwwroot\lis\staffAddMembers.php:5) in c:\Inetpub\wwwroot\lis\staffAddMembers.php on line 6

Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at c:\Inetpub\wwwroot\lis\staffAddMembers.php:5) in c:\Inetpub\wwwroot\lis\staffAddMembers.php on line 6

Warning: Cannot modify header information - headers already sent by (output started at c:\Inetpub\wwwroot\lis\staffAddMembers.php:5) in c:\Inetpub\wwwroot\lis\staffAddMembers.php on line 7

---------------------------------------

> How do you know it has not been added?

1.  I checked through MySQL command line, and the member and person tables are not updated, and
2. When I clicked the 'Submit button', I get the error message.  Below is a link to the screenshot of the errors:

http://img106.imageshack.us/img106/9890/phperror15qh.png
0
 

Author Comment

by:luna621
ID: 16486184
Whoops!  Sorry, wrote the link wrong.  Should be: http://img126.imageshack.us/img126/7131/phperror18bs.png
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16486188
Can't see the image.
0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16486197
For the warning, remove everything, including <!-- comments -->, spaces, newlines, etc. before the first <?php. But this has nothing to do with MySQL, I think.

Also, the image http://img106.imageshack.us/img106/9890/phperror15qh.png returned 404 for me. Can you upload again?
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16486203
So.
----test.php---------
<?php
echo "stuff";
session_start();
?>

will produce that error.

as will

----test2.php
<html>
...
<?php
session_start();
?>


To use sessions, there must be NO output.

If you do a view source on the HTML page containing the errors, can you tell us EVERYTHING upto the Warning.

And can you show us the first 10 lines of staffAddMembers.php
0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16486205
OK, can see the pic now =)
0
 

Author Comment

by:luna621
ID: 16486222
Code:
---------------------------------------------------------------------------------------------------

<?php
error_reporting(E_ALL);
?>

<?php
  session_start();
  header("Pragma: no-cache");

  include("util.php");
      securityCheck();

      $locationFeedback = "&nbsp;";
      $feedback = "&nbsp;";

      $isPostback = FALSE;
  if (array_key_exists("Submit", $_REQUEST)) {
    $isPostback = TRUE;
  }

  if ($isPostback) {
        $username = getRequestValue("username");
        $password = getRequestValue("password");
        $firstname = getRequestValue("firstname");
        $middlename = getRequestValue("middlename");
        $lastname = getRequestValue("lastname");
        $employer = getRequestValue("employer");
        $graduation_date = getRequestValue("graduation_date");
        $subnewsletter = getRequestValue("subnewsletter");

            $validated = true;

            if ($validated) {
              // $staff_id = $_SESSION["LoggedOnPersonId"];
          $sql = "INSERT INTO person (username, password, firstname, middlename, lastname) VALUES ";
              $sql = $sql."('$username', '$password', '$firstname', '$middlename', '$lastname')";

          $sql2 = "INSERT INTO member (employer, graduation_date, NULL, subnewsletter, firstname, middlename, lastname, NULL,) VALUES ";
              $sql2 = $sql2."('$employer', '$graduation_date', '$subnewsletter', '$firstname', '$middlename', '$lastname')";

          $last_id = "&nbsp;";

              //echo($sql);
                  $conn = getConnection();
                  if (mysql_query($sql, $conn)) {
                    $last_id = mysql_insert_id($conn);
                    if (mysql_query($sql2, $conn)) {
                       $feedback = "Member inserted into database successfully.";
                    }
                  }
                    else {
                      mysql_query("DELETE FROM person WHERE person_id = $last_id", $conn);
                      $feedback = "Error, unable to insert record into database.";
                    }
                }
                else {
                  $feedback = "Error, unable to insert record into database.";
                }
          }
?>
...
        <form name="form1" method="post">
          <p><strong>Member Entry Form </strong></p>

            <table border="0">
            <!-- This gathers information for the person table. -->
          <tr>
            <th>UserName:</th>
            <td><input name="username" id="username"><?php echo(getRequestValue("username"))?></td>
          </tr>
          <tr>
            <th>Password:</th>
            <td><input name="password" id="password"><?php echo(getRequestValue("password"))?></td>
          </tr>
          <tr>
                <th>First Name:</th>
                <td><input name="firstname" id="firstname"><?php echo(getRequestValue("firstname"))?></td>
          </tr>
          <tr>
                <th>Middle Name:</th>
                <td><input name="middlename" id="middlename"><?php echo(getRequestValue("middlename"))?></td>
          </tr>
          <tr>
                <th>Last Name:</th>
                <td><input name="lastname" id="lastname"><?php echo(getRequestValue("lastname"))?></td>
          </tr>
          <tr>
                <th>Employer:</th>
                <td><input name="employer" id="employer"><?php echo(getRequestValue("employer"))?></td>
          </tr>
          <tr>
                <th>Graduation Date:</th>
                <td><input name="graduation_date" id="graduation_date"><?php echo(getRequestValue("graduation_date"))?></td>
          </tr>
          <tr>
                <th>Subscribe Newsletter:</th>
                <td><input name="subnewsletter" id="subnewsletter"><?php echo(getRequestValue("subnewsletter"))?></td>
          </tr>
        </table>
          <br>
          <input type="submit" name="Submit" value="Submit">
          <input type="reset" name="Submit2" value="Clear">
          <p align="center">&nbsp;</p>
        </form>
                        <p style="color:red"><?php echo($feedback); ?></p>
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16486228
<?php
error_reporting(E_ALL);
?>
  <<<<<<<<<<<<<<<<<<<<<< BLANK LINE!!!!!!!!!!!!!!!!!!!!
<?php
  session_start();
...

change to ...

<?php
error_reporting(E_ALL);
session_start();
...
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16486233
And is that a blank line BEFORE the first <?php line also?

NO OUTPUT AT ALL.

0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16486240
         $sql2 = "INSERT INTO member (employer, graduation_date, NULL, subnewsletter, firstname, middlename, lastname, NULL,) VALUES ";   <----

Please remove the two NULL's. Also there should be no commas before the ).
0
 

Author Comment

by:luna621
ID: 16486387
??  I thought I removed those NULLS.  Guess I forgot to save.  Let me try this again.
0
 

Author Comment

by:luna621
ID: 16486404
Updated Code:
-------------------------------------------------------------------------
<?php
error_reporting(E_ALL);
session_start();
  header("Pragma: no-cache");

  include("util.php");
      securityCheck();

      $locationFeedback = "&nbsp;";
      $feedback = "&nbsp;";

      $isPostback = FALSE;
  if (array_key_exists("Submit", $_REQUEST)) {
    $isPostback = TRUE;
  }

  if ($isPostback) {
        $username = getRequestValue("username");
        $password = getRequestValue("password");
        $firstname = getRequestValue("firstname");
        $middlename = getRequestValue("middlename");
        $lastname = getRequestValue("lastname");
        $employer = getRequestValue("employer");
        $graduation_date = getRequestValue("graduation_date");
        $subnewsletter = getRequestValue("subnewsletter");

            $validated = true;

            if ($validated) {
              // $staff_id = $_SESSION["LoggedOnPersonId"];
          $sql = "INSERT INTO person (username, password, firstname, middlename, lastname) VALUES ";
              $sql = $sql."('$username', '$password', '$firstname', '$middlename', '$lastname')";

          $sql2 = "INSERT INTO member (employer, graduation_date, subnewsletter, firstname, middlename, lastname) VALUES ";
              $sql2 = $sql2."('$employer', '$graduation_date', '$subnewsletter', '$firstname', '$middlename', '$lastname')";

          $last_id = "&nbsp;";

              //echo($sql);
                  $conn = getConnection();
                  if (mysql_query($sql, $conn)) {
                    $last_id = mysql_insert_id($conn);
                    if (mysql_query($sql2, $conn)) {
                       $feedback = "Member inserted into database successfully.";
                    }
                  }
                    else {
                      mysql_query("DELETE FROM person WHERE person_id = $last_id", $conn);
                      $feedback = "Error, unable to insert record into database.";
                    }
                }
                else {
                  $feedback = "Error, unable to insert record into database.";
                }
          }
?>
0
 

Author Comment

by:luna621
ID: 16486433
Arg!  Wrong link again!!  http://img126.imageshack.us/img126/7131/phperror18bs.png  Sorry.
0
 

Author Comment

by:luna621
ID: 16486444
I must be really tired.  This is the correct one: http://img87.imageshack.us/img87/6982/phperror28pd.png
0
 

Author Comment

by:luna621
ID: 16486450
Still says unable to insert the record.  I'm not sure why.
0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16486461
Try to replace the last few line with

                 else {
                   mysql_query("DELETE FROM person WHERE person_id = $last_id", $conn);
                   $feedback = "Error, unable to insert record into database." . mysql_error();
                 }
              }
              else {
                $feedback = "Error, unable to insert record into database." . mysql_error();
              }
         }

to see what's wrong?
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16486468
Change

  $feedback = "Error, unable to insert record into database.";

to

  $feedback = 'Error, unable to insert record into database.<br />Error : ' . mysql_errno($conn) . ' : ' . mysql_error($conn);
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16486476
KennyTM is NOT RQuadling! Just in case anyone is wondering. Same answers at the same time.
0
 

Author Comment

by:luna621
ID: 16486507
^ Haha, you guys are just too fast!  I'll try this.  Hold on. :-)
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16486522
Oh. And put in ...

echo $sql;

You commented it out. Also echo is not a function, so using () is not expected.
0
 

Author Comment

by:luna621
ID: 16486531
Error : 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ' at line 1

My line 1 is: <?php

I'm confused!!
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16486576
Line 1 of the SQL statement!!!

The error is generated by the mysql_error() function. SQL statements MAY be many 100 lines long!

So,

$feedback = 'Error, unable to insert record into database.<br />Error : ' . mysql_errno($conn) . ' : ' . mysql_error($conn);

becomes ...

$feedback = 'Error, unable to insert record into database.<br />Error : ' . mysql_errno($conn) . ' : ' . mysql_error($conn) . "<br />Statement : $sql<br />$sql2";
0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16486585
Line 1 is the line 1 of the query, not the line 1 of php.

Just a guess: maybe quoting the phrase password as `password` would help?
0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16486601
in mean, in the 1st SQL statement.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16486612
Ha! KennyTM! You're getting slow. A whole 7 minutes to generate the reply!
0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16486619
LOL i need to wait for the email to come, and Opera check email at a 5-min interval
(and I'm not a fast typist)
0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16486625
Because I do have other things to work on.
0
 

Author Comment

by:luna621
ID: 16486634
Both suggestions got this:

Error, unable to insert record into database.
Error : 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ' at line 1
Statement : INSERT INTO person (username, 'password', firstname, middlename, lastname) VALUES ('jdoe', '1', 'John', 'E', 'Doe')
INSERT INTO member (employer, graduation_date, subnewsletter, firstname, middlename, lastname) VALUES ('USA', '12/16/2006', '3', 'John', 'E', 'Doe')

Let me try screencap more pics.


0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16486655
I think password is a reservered keyword.

Try ...

INSERT INTO person (username, `password`, firstname, middlename, lastname)

(That is back tick)

or

INSERT INTO person (username, [password], firstname, middlename, lastname)

0
 

Author Comment

by:luna621
ID: 16486696
person table: http://img20.imageshack.us/img20/6533/person9ml.png
member table: http://img20.imageshack.us/img20/1279/member8pp.png

Does it matter that one is called firstname, but in the other it's called student_firstname, etc.?
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16486705
Oh. Back tick. Just like KennyTM said.

" = double quote (Shift+2 on my keyboard)
' = single quote (between ; and # on the right of my keyboard)
` = backtick (to the left of 1 on the top left of my keyboard)

Make sure you use ` within "" or '' as ` is also a PHP function.

e.g.

$s_program_output = `some_external_application.exe param1 param2`;

0
 

Author Comment

by:luna621
ID: 16486708
@RQuadling: Nope, still getting same error.

Thank you guys for all the help.  As it is 2:00 AM now, I need to get some sleep (need to wake up at 5:00 AM for work).  I'll be back again tomorrow.  I'm determined to get this stuff down!! :-)
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16486715
"matter". Depends.

I would call the same thing the same name. If you need it to be different then do so, if not, don't.
0
 

Author Comment

by:luna621
ID: 16486732
Well, the database I'm using was setup that way... so I don't want to screw around with anything there, yet.
0
 

Author Comment

by:luna621
ID: 16486739
Alright, I'll check back tomorrow.  Thank you again!
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16486746
Ah. I see. How can you insert firstname into a table without a column called firstname!!!!

$sql2 = "INSERT INTO member (employer, graduation_date, subnewsletter, student_firstname, student_middlename, student_lastname) VALUES ";

Though I would probably alter the DB structure so you only needed to store the data once in 1 table and then use a link to it in the other table.
0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16486753
BTW, There's no "username" column in the "person" table.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16486758
And going to sleep? Gee! What sort of programmer are you? Are you a girl? Get a couple of cans of cherry coke from the fridge and start again!
0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16486772
Well, she is a girl, if according to http://www.experts-exchange.com/M_872519.html :)
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16486787
Oops! Sorry Luna! (he he he)
0
 

Author Comment

by:luna621
ID: 16495105
^ Yes, girls exist on teh intranet and we need our beauty sleep too!!  Haha!!

See!  Told you I was terrible at programming!  I'll have to look at my tables again.  Hopefully, that'll solve the problem.  I'll post back in a few. :-)
0
 

Author Comment

by:luna621
ID: 16495188
Well, looks like it was able to add to the person table alright: http://img92.imageshack.us/img92/4170/submit0ic.png.  But, when I checked the member table and the phone table... nothing was inputted.  Is it because I need to link the person_id number from the person table to the member and the phone tables?

phone table -> http://img92.imageshack.us/img92/6695/phone8vw.png
0
 

Author Comment

by:luna621
ID: 16495193
Code:
-----------------------------------------------------

<?php
error_reporting(E_ALL);
session_start();
  header("Pragma: no-cache");

  include("util.php");
      securityCheck();
      $locationFeedback = "&nbsp;";
      $feedback = "&nbsp;";

      $isPostback = FALSE;
  if (array_key_exists("Submit", $_REQUEST)) {
    $isPostback = TRUE;
  }

  if ($isPostback) {
        $username = getRequestValue("username");
        $password = getRequestValue("password");
        $firstname = getRequestValue("firstname");
        $middlename = getRequestValue("middlename");
        $lastname = getRequestValue("lastname");
        $employer = getRequestValue("employer");
        $graduation_date = getRequestValue("graduation_date");
        $subnewsletter = getRequestValue("subnewsletter");
        $email_address = getRequestValue("email_address");
        $phone_number = getRequestValue("phone_number");

            $validated = true;

            if ($validated) {
              // $staff_id = $_SESSION["LoggedOnPersonId"];
          $sql = "INSERT INTO person (password, firstname, middlename, lastname) VALUES ";
              $sql = $sql."('$password', '$firstname', '$middlename', '$lastname')";

          $sql2 = "INSERT INTO member (employer, graduation_date, subnewsletter, student_firstname, student_middlename, student_lastname, email_address) VALUES ";
              $sql2 = $sql2."('$employer', '$graduation_date', '$subnewsletter', '$firstname', '$middlename', '$lastname', '$email_address')";

          $sql3 = "INSERT INTO phone (phone_number) VALUES ";
              $sql3 = $sql3."('$phone_number')";

          $last_id = "&nbsp;";

                echo($sql);
                  $conn = getConnection();
                  if (mysql_query($sql, $conn)) {
                    $last_id = mysql_insert_id($conn);
                    if (mysql_query($sql2, $conn)) {
                      $last_id = mysql_insert_id($conn);
                      if (mysql_query($sql3, $conn)) {
                        $feedback = "Member inserted into database successfully.";
                      }
                    }
                  }
                 else {
                   mysql_query("DELETE FROM person WHERE person_id = $last_id", $conn);
                   $feedback = 'Error, unable to insert record into database.<br />Error : ' . mysql_errno($conn) . ' : ' . mysql_error($conn) . "<br />Statement : $sql<br />$sql2";
                 }
              }
              else {
                $feedback = 'Error, unable to insert record into database.<br />Error : ' . mysql_errno($conn) . ' : ' . mysql_error($conn) . "<br />Statement : $sql<br />$sql2";
              }
         }
?>
0
 

Author Comment

by:luna621
ID: 16495202
Screencap of current command line queries: http://img20.imageshack.us/img20/4572/cmd2fx.png
0
 

Author Comment

by:luna621
ID: 16495209
person_id 7 & 8 are duplicate because I was testing the insert into table.  I guess I can use one of them for my delete (after I get the adding done, I want to work on deleting records).  So, you can see that adding to the person table is fine, but the member and phone tables are messed up.  I think it has to do with having the same person_id for all three tables.  Is there a way to do that?
0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16495223
The things after $sql3 are getting messy now...

Can you first echo all 3 statements by

echo("$sql<br />$sql2<br />$sql3"); ?
0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16495288
Hi. And maybe try this code for everything between "if ($validated) {" and "}". It won't solve anything but it will make debugging and later extensions easier.

----

$sql = Array();
$res = Array();
$last_ids = Array();
$delquery = Array();

$sql[0] = "INSERT INTO  person (password, firstname, middlename, lastname) VALUES ('$password', '$firstname', '$middlename', '$lastname')";
$sql[1] = "INSERT INTO member (employer, graduation_date, subnewsletter, student_firstname, student_middlename, student_lastname, email_address) VALUES ('$employer', '$graduation_date', '$subnewsletter', '$firstname', '$middlename', '$lastname', '$email_address')";
$sql[2] = "INSERT INTO phone (phone_number) VALUES ('$phone_number')";

$delquery[0] = "DELETE FROM person WHERE person_id = ";
$delquery[1] = "DELETE FROM member WHERE person_id = ";
$delquery[2] = "DELETE FROM phone WHERE phone_id = ";

// print_r ($sql);

$conn = getConnection();
$feedback = "Member inserted into database successfully.";

for ($i = 0; $i < count($sql); ++$i) {
  $res[$i] = mysql_query($sql[$i], $conn);
  $last_ids[$i] = mysql_insert_id($conn);
  if (!$res[$i]) {
    $err = mysql_error ($conn);
    for ($j = $i-1; $j >= 0; -- $j)
      mysql_query($delquery[$j] . $last_ids[$j]);
    $feedback = "Error, unable to insert record into database: <br />$err";
    break;
  }
}

echo $feedback;
0
 

Author Comment

by:luna621
ID: 16495340
Error, unable to insert record into database:
Incorrect datetime value: '03/14/2007' for column 'graduation_date' at row 1


What does is the datetime value format?  (mm/dd/yyyy)??
0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16495356
yyyy-mm-dd
0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16495362
In fact, for DATETIME it is "YYYY-MM-DD HH:MM:SS" as you can see from http://dev.mysql.com/doc/refman/5.0/en/datetime.html .
0
 

Author Comment

by:luna621
ID: 16495365
Ok, this format worked: 2007-03-14
0
 

Author Comment

by:luna621
ID: 16495376
Okay, I was able to add correctly to the person table, but the phone and member tables have different person_id...
0
 

Author Comment

by:luna621
ID: 16495383
So, the person I just added has a person_id of '12' in the person table, '1' in the phone table, and a 'NULL' in the member table.  Is there a way to make it '12' for all tables?  Do I have to query the tables when I do the insert to the phone and member tables for the person_id?
0
 

Author Comment

by:luna621
ID: 16495389
I'm 4 hours past my dinner.  I'll be back shortly.  :-)
0
 
LVL 8

Accepted Solution

by:
KennyTM earned 1800 total points
ID: 16495432
You can just use the last_insert_id() called before.
----

$sql = Array();
$res = Array();
$last_ids = Array();
$delquery = Array();

$sql[0] = "INSERT INTO  person (password, firstname, middlename, lastname) VALUES ('$password', '$firstname', '$middlename', '$lastname')";
$sql[1] = "INSERT INTO member (person_id, employer, graduation_date, subnewsletter, student_firstname, student_middlename, student_lastname, email_address) VALUES (LAST_INSERT_ID(), '$employer', '$graduation_date', '$subnewsletter', '$firstname', '$middlename', '$lastname', '$email_address')";
$sql[2] = "INSERT INTO phone (phone_id, phone_number) VALUES (LAST_INSERT_ID(), '$phone_number')";

$delquery[0] = "DELETE FROM person WHERE person_id = ";
$delquery[1] = "DELETE FROM member WHERE person_id = ";
$delquery[2] = "DELETE FROM phone WHERE phone_id = ";

// print_r ($sql);

$conn = getConnection();
$feedback = "Member inserted into database successfully.";

for ($i = 0; $i < count($sql); ++$i) {
  $res[$i] = mysql_query($sql[$i], $conn);
  $last_ids[$i] = mysql_insert_id($conn);
  if (!$res[$i]) {
    $err = mysql_error ($conn);
    for ($j = $i-1; $j >= 0; -- $j)
      mysql_query($delquery[$j] . $last_ids[$j]);
    $feedback = "Error, unable to insert record into database: <br />$err";
    break;
  }
}

echo $feedback;
0
 

Author Comment

by:luna621
ID: 16495591
^ last_insert_id(), I didn't know you could do that!  I thought I had to write some fancy sql query.  Thanks!  That worked!  I will now attempt to delete a person from the database... I'll probably make a new question some time tonight.  Thank you for all the help.  I'm slowly beginning to understand. :-)
0
 

Author Comment

by:luna621
ID: 16495596
Points time!  You two are now my favorite experts in this section!! :-)
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16495741
Gee. I go home for some sleep (Shhh - don't tell anyone) and a WHOLE load of answers.

So. Are we fixed yet?
0
 

Author Comment

by:luna621
ID: 16495770
Yup, for now.  The adding is A-OK!!  I will now attempt to delete.  :-)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question