PHP & MySQL - how to find a NULL field within a row

  • ozzyosb100
  • Graduate
  • Graduate
  • User avatar
  • Posts: 191
  • Loc: FL

Post 3+ Months Ago

Hi all! Its been a while since I did PHP and MySQL and I've started practicing again. I've come upon an issue I'm not sure about. Here is my situation:
I've made a website with a login system and I've been able to get registration, email verification and loggin in working. Once I logged in I want users of my site to be able to store up to 20 strings in the database. By default I have a table with 21 columns with a row per each user. The first column is the user name and the other 20 are the data. By default they are NULL. I want to be able to make a script that can find which is the next NULL row and enter data into it. How can I do this? My users will be able to add and remove data from these rows, and they will not always necessarily be in order. For example:


Code: [ Select ]
Username | Data1 | Data2 | Data3 | Data4 | Data5 | Data6 |... etc
JohnDoe  | 'foo' | 'bar' |  NULL | 'abc' | '123' | NULL  | ...
  1. Username | Data1 | Data2 | Data3 | Data4 | Data5 | Data6 |... etc
  2. JohnDoe  | 'foo' | 'bar' |  NULL | 'abc' | '123' | NULL  | ...


In the above example, I'd like to be able to have a user save a string and have it stored in Data3(since thats the first NULL value). When users no longer need the data it would be erased and returned to NULL, so there would be different places that are free for new data. How can I check for this?

Thanks in advance.
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13502
  • Loc: Florida

Post 3+ Months Ago

I really don't know where to start about the reasons I don't like a one row per-user design.

I would abandon the one row per-user design and use multiple rows per-user with a column for the userID/username, one for data, and perhaps other columns for timestamps and other indexable tidbits.

SQL Code: [ Select ]
CREATE TABLE userdata (
  userID int(10) UNSIGNED NOT NULL,
  `data` text,
  modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  KEY userID (userID),
  KEY modified (modified),
  FULLTEXT KEY `data` (`data`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  1. CREATE TABLE userdata (
  2.   userID int(10) UNSIGNED NOT NULL,
  3.   `data` text,
  4.   modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  5.   KEY userID (userID),
  6.   KEY modified (modified),
  7.   FULLTEXT KEY `data` (`data`)
  8. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Post Information

  • Total Posts in this topic: 2 posts
  • Users browsing this forum: No registered users and 143 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.