1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

SQL INSERT INTO Table

Discussion in 'PHP' started by B6x, Feb 24, 2013.

  1. #1
    Hello everyone,

    I have learned from W3 School how to create a basic PHP code to store data from HTML Form which is as following:
    PHP:
    1. <?php
    2. $con = mysql_connect("localhost", "databaseuser", "password");
    3. if (!$con)
    4.   {
    5.   die('Could not connect: ' . mysql_error());
    6.   }
    7.  
    8. mysql_select_db("databasename", $con);
    9.  
    10. $date = date("Y-m-d G:i:s") ;
    11. $sql="INSERT INTO collect (submitdate, first_name, last_name, email)
    12. VALUES
    13. ('$date','$_POST[first_name]','$_POST[last_name]','$_POST[email]')";
    14.  
    15. if (!mysql_query($sql,$con))
    16.   {
    17.   die('Error: ' . mysql_error());
    18.   }
    19. echo "Thank You!";
    20.  
    21. ?>
    I was wondering if there any other way to achieve the same purpose. Could you please suggest whether or not it is possible to create it differently?

    Thanks in advance!
    B6x, Feb 24, 2013 IP
  2. Sano000

    Sano000 Member

    Messages:
    44
    Likes Received:
    0
    Best Answers:
    4
    Trophy Points:
    46
    #2
    http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers
    You should use PDO. See some examples on the link.
    Sano000, Feb 24, 2013 IP
  3. filali.zakariae

    filali.zakariae Greenhorn

    Messages:
    46
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    16
    #3
    Mysql_connect ... are the old method, and now from php5 because they work with OOP, it's preferable to use PDO ;)
    filali.zakariae, Feb 24, 2013 IP
  4. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,703
    Likes Received:
    48
    Best Answers:
    9
    Trophy Points:
    100
    #4
    Use this instead and always stick to current standards. The old style queries are outdated and incredibly insecure. Using PDO is my preference, alternatively you could use mySQLi. Using this method adds a layer of security to your queries and prevents injections.

    databaseconnect.php
    PHP:
    1.  
    2.  
    3. $db_myHost = "hostname";
    4. $dbmyDatabase = "database";
    5. $db_myUser = "username";
    6. $db_myPassword = "password";
    7. try
    8.    {
    9.    $dbPDO = new PDO('mysql:host='.$db_myHost.';dbname='.$db_myDatabase, $db_myUser, $db_myPassword);
    10.    $dbPDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    11.    }
    12. catch  (PDOException $e)
    13.    {
    14.     echo "Error!: " . $e->getMessage() . "
    15. ";
    16.     die();
    17.    }
    18.  
    19.  
    yourpage.php
    PHP:
    1. $sth = $dbconn->prepare("INSERT INTO collect (submitdate, first_name, last_name, email) VALUES (:date,:first_name,:last_name,:email)");
    2.  
    3. $params = array("date" => date(Y-m-d G:i:s), "first_name" => $_POST[first_name], "last_name" => $_POST[last_name], "email" => $_POST[email]);
    4. $sth->execute($params);
    5.  
    6.    if ($row = $sth->fetch()) {
    7.    
    8.    echo "Thank you";
    9.  
    10.    }
    scottlpool2003, Feb 25, 2013 IP
  5. deathshadow

    deathshadow Prominent Member

    Messages:
    5,980
    Likes Received:
    827
    Best Answers:
    144
    Trophy Points:
    395
    #5
    Uhm... no... not only is that ugly code it's also broken. Execute doesn't return a fetch set on insert other than an empty array.... so that test would ALWAYS return true... no reason to waste and extra variable on the params, and of course don't give the poor guy actually LEGIBLE code, no, stuff it all into one line -- that makes everything easier... as do the needlessly cryptic names . :D (gentle ribbing on the latter part)

    Of course the re-re use of double quotes and extra echo statement for nothing :p

    Database connection
    Code (Text):
    1. /*
    2.     LEARN about DSN's -- they're important when you want to connect via
    3.     sockets instead of ports, use SQL systems other than PDO, set up a
    4.     non-standard port, etc, etc.
    5. */
    6.  
    7. $dbDSN = 'mysql:host=localhost;dbname=database';
    8. $dbUser     = 'username';
    9. $dbPassword = 'password';
    10.  
    11. try {
    12.     $db = new PDO($dbDSN,$dbUser,$dbPassword);
    13. } catch  (PDOException $e) {
    14.     die('PDO Error: '.$e->getMessage());
    15. }
    doing the work.

    Code (Text):
    1. $statement = $db->prepare('
    2.     INSERT INTO collect (
    3.         submitdate, first_name, last_name, email
    4.     ) VALUES (
    5.         :date, :first_name, :last_name, :email
    6.     )
    7. ');
    8.  
    9. if ($statement->execute(array(
    10.     'date' => date(Y-m-d G:i:s),
    11.     'first_name' => $_POST[first_name],
    12.     'last_name' => $_POST[last_name],
    13.     'email' => $_POST[email]
    14. ))) {
    15.     echo 'Thank you';
    16. } else {
    17.     /* put an error handler here, something went wrong! */
    18. }
    deathshadow, Feb 25, 2013 IP
    scottlpool2003 likes this.
  6. deathshadow

    deathshadow Prominent Member

    Messages:
    5,980
    Likes Received:
    827
    Best Answers:
    144
    Trophy Points:
    395
    #6
    Oh, sidenote, since you're plugging in the PHP date... I'd be asking why isn't that field of type DATETIME? Then you could just plug in NOW() into the query instead of screwing around with PHP dates.
    deathshadow, Feb 25, 2013 IP
  7. B6x

    B6x Greenhorn

    Messages:
    10
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #7
    This is interesting. can you explain further what this can do better? Thanks
    B6x, Mar 10, 2013 IP
  8. B6x

    B6x Greenhorn

    Messages:
    10
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #8
    Thanks for your inputs to this. Well, I don't see this is working with date i.e. 'date' => date(Y-m-d G:i:s), can you explain what does execute(array( mean? Thanks
    B6x, Mar 10, 2013 IP
  9. deathshadow

    deathshadow Prominent Member

    Messages:
    5,980
    Likes Received:
    827
    Best Answers:
    144
    Trophy Points:
    395
    #9
    Oops, even more stuff that was broken in Scott's I failed to correct for -- like it missing the colons in the array indexes and the lack of proper quotes around string values.

    It should be:
    Code (Text):
    1. if ($statement->execute(array(
    2.    ':date' => date('Y-m-d G:i:s'),
    3.    ':first_name' => $_POST['first_name'],
    4.    ':last_name' => $_POST['last_name'],
    5.    ':email' => $_POST['email']
    6. ))) {
    What I get for not looking too close and doing a drive-by post.

    PDO's execute parameter can be passed an array to plug in values to a prepared query. This auto-sanitizes the values in the array so you can do things like plug-in $_POST directly without any extra escaping of values or worrying about script injections.

    Pretty much in the prepare where you see :date, the value indexed as :date in the array is plugged in there.

    One of the really nice features of this (that a LOT of people using prepared queries don't realize) is that you can actually perform more than one $statement->execute on a prepared query... so if for example you had ten or twelve records you were inserting with the same basic query, you could just prepare once and execute the array many times. You can see that in action in a demo I wrote for another user where I made a routine to just plug in 200 dummy records:

    http://www.cutcodedown.com/for_others/trenttdogg/sqlToMarkup/populate.phps

    I prepare once, but execute 200 times.

    Prepare/exec is how it SHOULD be done using PDO or mySQLi, if for no other reason than the auto-sanitizing of values. PDO makes it more convenient since you can use an array on it's execute method and can actually name your values-- though it can also use a method called 'bindParam' similar to how mySQLi handles it.
    deathshadow, Mar 10, 2013 IP
  10. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,703
    Likes Received:
    48
    Best Answers:
    9
    Trophy Points:
    100
    #10
    Fascinates me the depth and range of your knowledge. Normally you'd have somebody who's really good at programming and another person who's really good at designing but you seem to do both really well.

    Even minor things like not creating an extra variable using $params (where I see all the time when I've researched PDO, my bad I suppose for going for tutorial websites). Very minor thing but speeds things up that little bit extra.

    Comes with experience I suppose.
    scottlpool2003, Mar 11, 2013 IP
  11. solomon.flik

    solomon.flik Member

    Messages:
    23
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    33
    #11
    solomon.flik, May 15, 2013 IP
  12. kolryb29

    kolryb29 Greenhorn

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #12
    You've got a problem with superglobals vars.

    $_POST["first_name"]','$_POST["last_name"]','$_POST["email"]

    Try it...
    kolryb29, May 15, 2013 IP