Storing array data in MySQL

PHP
1

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

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:

INSERT INTO `users`
(`USER`)
VALUES(:username)
ON DUPLICATE KEY UPDATE `LAST DATE` = NOW()

You create a prepared statement using PDO::prepare() 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 [url=http://php.net/manual/en/pdostatement.execute.php]PDOStatement::execute().

It might look something like this:

$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.

3

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

Hi,

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

ALTER TABLE `users` ADD UNIQUE KEY (`USER`);

Again, I don’t know what the name of your table is, so replace users if necessaryy.

5

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:

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()'
);
}