SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Storing array data in MySQL

    Hi!

    I'm pretty new to programming/coding/whatever you want to call it! Currently, I'm working on my first database project. In short, I submit a string (in my case a username) through a html form. It gets stored into a 3 column table:

    -------------------------
    |ID - USER - LAST UPDATE|
    -------------------------
    | 1 - test - 2012/02/18 |
    -------------------------

    ID is primary key, user is the username, last update is the date at which time the certain username got added.

    I got all of this working, however I want to make my life a bit easier so that I can add multiple usernames at the same time. The string of usernames that I want to process looks like this:

    username1 - username2 - username3 - ...

    Each username is seperated by a '-' . I think it's easy to put all of these usernames in an array using explode().


    Now I got a few questions:
    1) How do I put these array elements in seperate rows in my MySQL database?
    2) Is it possible to make my form processing in such a way that it checks whether a username is already in the database, in which case only the DATE column has to be updated to the date of the last update. If the username is not in the database, a new row has to be created obviously.

    I hope it's clear, if not I'll add some more info. Thanks in advance!

  2. #2
    SitePoint Wizard bronze trophy devbanana's Avatar
    Join Date
    Apr 2006
    Location
    Pennsylvania
    Posts
    1,736
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you are using PDO, this is easy. It's not too much harder if you're using the mysql_X commands.

    It seems that the fastest thing might be to use 'on duplicate key update' at the end of insert. Make sure that the USER column is a unique key, though.

    So you'd use a syntax like:

    Code:
    INSERT INTO `users`
    (`USER`)
    VALUES(:username)
    ON DUPLICATE KEY UPDATE `LAST DATE` = NOW()
    You create a prepared statement using PDO:repare() with the above SQL (but replace the 'users' with the name of your table).

    Then you loop through your array of usernames, use PDOStatement::bindParam() to bind each username to the :username param. Then call PDOStatement::execute().

    It might look something like this:

    PHP Code:
    $stmt $db->prepare(
    'INSERT INTO `users` ' .
    '(`USER`) VALUES(' .
    ':username) ' .
    'ON DUPLICATE KEY UPDATE `LAST UPDATE` = NOW()'
    );

    foreach (
    $usernames as $username)
    {
        
    $stmt->bindParam(':username'$username);
        
    $stmt->execute();

    Again, if you're just using mysql_query, then you'll have to escape the data and insert them manually into the query.
    Laudetur Iesus Christus!
    Christ's Little Flock
    Jesus is the Good Shepherd

  3. #3
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your quick response. However, I'm totally demotivated at the moment haha. I have heard of PDO but it was so new to me that I didn't want to bother; basic php alone is more than enough. I'm using mysqli_query, though that's all I know about it.

    I checked my database using phpmyadmin, and I wasn't really able to set the USER column as a unique one. I'm probably way too nooby to be doing stuff like this anyway.

    Thanks for your help though.

  4. #4
    SitePoint Wizard bronze trophy devbanana's Avatar
    Join Date
    Apr 2006
    Location
    Pennsylvania
    Posts
    1,736
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I'm not sure how to do it in phpmyadmin. If you can run a raw query, enter this:

    Code:
    ALTER TABLE `users` ADD UNIQUE KEY (`USER`);
    Again, I don't know what the name of your table is, so replace users if necessaryy.
    Laudetur Iesus Christus!
    Christ's Little Flock
    Jesus is the Good Shepherd

  5. #5
    SitePoint Wizard bronze trophy devbanana's Avatar
    Join Date
    Apr 2006
    Location
    Pennsylvania
    Posts
    1,736
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I've not used mysqli for a while, but if you're using th procedural syntax, I think it would go something like this:

    PHP Code:
    foreach ($usernames as $username)
    {
    mysqli_query($db,
    'INSERT INTO `users` ' .
    '(`USER`) VALUES(' .
    '' mysqli_real_escape_string($db$username) . ') ' .
    'ON DUPLICATE KEY UPDATE `LAST UPDATE` = NOW()'
    );

    Laudetur Iesus Christus!
    Christ's Little Flock
    Jesus is the Good Shepherd


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •