e_i_pi's Profile User Rating: *****

Reputation: 799 Master
Group:
Mentors
Active Posts:
1,682 (0.8 per day)
Joined:
30-January 09
Profile Views:
48,188
Last Active:
User is offline Aug 26 2014 07:48 PM
Currently:
Offline

Previous Fields

Country:
AU
OS Preference:
Windows
Favorite Browser:
FireFox
Favorite Processor:
AMD
Favorite Gaming Platform:
Playstation
Your Car:
Mitsubishi
Dream Kudos:
75
Expert In:
Databases

Latest Visitors

Icon   e_i_pi has set their status

Posts I've Made

  1. In Topic: Combining Select and Update

    Posted 20 Aug 2014

    @CTphpnwb He's using PDOs in his original code, maybe he's transitioning over from the deprecated functions.

    @EdNolan In answer to your points above:

    1). how do I call it if its on the same php page?
    Pass another get/post parameter called 'mode' where you set the value to "edit" during your form postback

    2). if I call it to execute while on the same page as other classes that reference other update prepared statements would those others execute as well?[/b]
    Depends on how you do your postback to server. Wrap it all up in a JSON object and spit it back, let the server do a bulk update if need be.

    3). if I execute a class that has one value that will update one field and that class was on a different php page would the other fields in the database table be updated to empty?
    You're going about it the wrong way. If the update fields from the form are empty strings, cast them to null on server side then use SQL like this:
    UPDATE banners SET
    lnk5 = COALESCE(:lnk5, lnk5),
    etc...
    
    


    4). Can I reference $pdo the way I have it in this class since $pdo is my object for connection to the database?
    Yes. PDOs can be used for any sort of database query.

    5). Which is better; onclick_button and have this execute, or most likely within a form action=?
    Depends on your coding style, either way works.

    [u]6). Banners is my database table name (do I have it coded correctly) database name is players?
    Not sure what you mean by this :(
  2. In Topic: Combining Select and Update

    Posted 20 Aug 2014

    @CTphpnwb He's using PDOs in his original code, maybe he's transitioning over from the deprecated functions.

    @EdNolan In answer to your points above:

    1). how do I call it if its on the same php page?
    Pass another get/post parameter called 'mode' where you set the value to "edit" during your form postback

    2). if I call it to execute while on the same page as other classes that reference other update prepared statements would those others execute as well?[/b]
    Depends on how you do your postback to server. Wrap it all up in a JSON object and spit it back, let the server do a bulk update if need be.

    3). if I execute a class that has one value that will update one field and that class was on a different php page would the other fields in the database table be updated to empty?
    You're going about it the wrong way. If the update fields from the form are empty strings, cast them to null on server side then use SQL like this:
    UPDATE banners SET
    lnk5 = COALESCE(:lnk5, lnk5),
    etc...
    
    


    4). Can I reference $pdo the way I have it in this class since $pdo is my object for connection to the database?
    Yes. PDOs can be used for any sort of database query.

    5). Which is better; onclick_button and have this execute, or most likely within a form action=?
    Depends on your coding style, either way works.

    [u]6). Banners is my database table name (do I have it coded correctly) database name is players?
    Not sure what you mean by this :(
  3. In Topic: Combining Select and Update

    Posted 18 Aug 2014

    Ah righto. You're not adding in quotes on your href and src attributes, and you're not escaping any quotes in the string, so the HTML is malformed. You have this:
    echo "<td>" .$row['userid'] . "<br /><a href=" .$row['lnk'] . "><img src=" . $row['gfx'] . "></a></td><td>This Banner Links To: <input type="text" maxlength="125" name="lnk" placeholder="http://iCardCounting.com"/></td>";
    
    

    ...but you need this...
    echo "<td>" .$row['userid'] . "<br /><a href=\"" .$row['lnk'] . "\"><img src=\"" . $row['gfx'] . "\"></a></td><td>This Banner Links To: <input type=\"text\" maxlength=\"125\" name=\"lnk\" placeholder=\"http://iCardCounting.com\"/></td>";
    
    
  4. In Topic: Combining Select and Update

    Posted 18 Aug 2014

    View PostEdNolan, on 19 August 2014 - 08:53 AM, said:

    I would actually like to be able to allow someone the opportunity to update maybe one of the graphics if they need too, but I wasn't going to address that issue until I was able to figure out how to style the select and update to work properly this way first. Thank you for the close connection info, I wasn't aware of it closing. Is it best to insert it between?
    }
    33 catch
    

    I am not aware yet of how to do a rollback. I did read something last week about it, but will have to research.

    Well, let's cut to the chase here. When you begin a transaction, you are telling the database that you want to keep track of everything that happens from that point. At some stage, you do a commit or a rollback. A commit says to the database "okay, I'm good with those changes, write them for me", whereas a rollback says to the database "hold on, I'm not comfortable with those changes, reverse them all for me". Rollback is kind of like an undo function.

    Now, to answer your entire quote above, this is the sort of thing you want to be doing as a general design pattern when working with databases:
    PHP 5 - 5.4
    	$stmt->execute();
    	$stmt->commit();
    	$pdo = null;  /*** close the database connection ***/
    }
    catch(PDOException $e)
    {
    	$stmt->rollback();
    	$pdo = null;  /*** close the database connection ***/
    	echo $e->getMessage();
    }
    
    

    PHP 5.5+
    	$stmt->execute();
    	$stmt->commit();
    }
    catch(PDOException $e)
    {
    	$stmt->rollback();
    	echo $e->getMessage();
    }
    finally
    {
    	$pdo = null;  /*** close the database connection ***/
    }
    
    

    Once you get into OOP you can make improvements to this pattern by having all this commit/rollback code in an exception wrapper around a more generic PDO object, but it is the same principle - commit the transaction if there's no errors, rollback the transaction if there's a problem.

    As further reading (getting deeper into the database space), you might want to look into race conditions and row level locking. Interesting topics that anyone using a database should be at least aware of.

    Quote

    So I don't actually want to update every row in the table, good catch. If I add the code below will it just update the row for that user, or do I need to reference the userid since the username is in a different table but connected to the userid, I don't believe I set the primary key yet for this table either since I was sort of rushed when I was trying to get part of this page going; userid is the only other field within this banner table.
    session_start();
    try {
    if ($_SESSION['userid'])
    

    My sessions on my other pages are usually ...
    session_start();
    try {
    if ($_SESSION['username'])
    

    Maybe I should just add username to this banner table?

    As a rule of thumb, I would use userid as the primary key, for a number of reasons..
    • userid is likely already a primary key in your "users" table. It should therefore be your foreign key in related tables. This also achieves consistency across your database (i.e. all tables use userid as the primary identified rather than some using userid and others using username)
    • userid is likely an integer, making lookups and indexes on it more efficient than something like username which is a varchar (string).
    • userid is something that is set by the application (i.e. - an internal key), whereas username is something that is set by the user (i.e. - a display value only)
  5. In Topic: Combining Select and Update

    Posted 18 Aug 2014

    A few things to note:
    • Your UPDATE statement will update every row in the table banners. Does that table have just one row, or are you missing a WHERE clause?
    • You're also failing to commit the transaction before closing the connection, which means that the database engine would automatically rollback at connection close. I would suggest doing a commit just before you null (close) the connection, and also have a rollback in your catch block.

My Information

Member Title:
= -1
Age:
40 years old
Birthday:
June 24, 1974
Gender:
Location:
Australia
Years Programming:
4
Programming Languages:
HTML, XML, jQuery, PHP, SQL, MySQL, VBA, VBScript, PostgreSQL

Contact Information

E-mail:
Private
Website URL:
Website URL  http://www.artofwar.cc/

Comments

Page 1 of 1
  1. Photo

    cupidvogel Icon

    09 Jun 2012 - 11:30
    Ha ha, I really liked your "if(navigator.appName == 'Microsoft Internet Explorer')" theme!
  2. Photo

    Duckington Icon

    04 Apr 2012 - 00:57
    It's not live yet, still developing and then going into beta test. I haven't actually decided on a name yet, so no domain for it.
  3. Photo

    modi123_1 Icon

    13 Mar 2012 - 06:56
    Ha.. well good luck with it.. it's always a good butt of a joke.
  4. Photo

    RudiVisser Icon

    08 Mar 2012 - 15:20
    You know, I only just worked out what your avatar is. Love it!
  5. Photo

    RudiVisser Icon

    06 Sep 2011 - 00:37
    Thank you very much :-D
  6. Photo

    RudiVisser Icon

    06 Sep 2011 - 00:37
    Thank you very much :-D
  7. Photo

    e_i_pi Icon

    05 Sep 2011 - 22:15
    Ah thank you :) I'm a fan of minimalism, so I wwanted something nice and simple, plus you just can't go past red and black!
  8. Photo

    no2pencil Icon

    05 Sep 2011 - 19:52
    Love your avatar!
  9. Photo

    Sayid Ahmed Icon

    13 Jul 2011 - 14:02
    thanks for the add.
  10. Photo

    Dormilich Icon

    17 Feb 2011 - 13:25
    just wanted to congratulate for the funny thread title "OOP, PDOs, pain, tears".
Page 1 of 1