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

Reputation: 793 Master
Group:
Mentors
Active Posts:
1,679 (0.83 per day)
Joined:
30-January 09
Profile Views:
47,426
Last Active:
User is offline Yesterday, 03:50 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 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>";
    
    
  2. 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)
  3. 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.
  4. In Topic: Update multiple tables in one query

    Posted 23 Jul 2014

    View Postjimzcoder, on 24 July 2014 - 10:40 AM, said:

    no emp_id is not a primary key i have. i would consider it as a foreign key since i have an auto increment column(unsigned int) in my table which serves as my primary key. there are just instances that the client needs to change the employee`s id for some reason. and emp_id column as a varchar datatype that contains atleast 10 characters example(2014-07-43). 2014- is the year of employment and 07 is the month of employment and 43 is the sequence number derived from auto increment id i have in my table.

    I don't know why you'd have an auto-increment field (1, 2, 3...) and yet use a natural key (2014-07-43,...) for your foreign keys. Surely it would make more sense to store the natural key on the employee table, and then foreign key constrain your child tables against the auto-increment field. That would save you all these painful UPDATE statements, which incur unnecessary load on the database. What happens if you miss one of the UPDATE statements for some reason? Bye bye data integrity :(
  5. In Topic: Best way of choosng frm Hndred thousands of options on a select field.

    Posted 1 Jul 2014

    Have you tried using jQueryUI Autocomplete? For 100k records, you'll need to use a remote data source otherwise you might cause browsers to hang.

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