Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>MySQL has a hard limit of 4,096 columns: <a href="http://dev.mysql.com/doc/refman/4.1/en/column-count-limit.html" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/4.1/en/column-count-limit.html</a></p> <p>That's not an ideal method of storing things. Arranging the data by date is better:</p> <ol> <li>It won't hit the column limit</li> <li>It supports optional relations. For instance, if you don't have <code>data1</code> for a given date - the record won't exist.</li> </ol> <h2><code>TABLE</code></h2> <ul> <li><code>RECORD_DATE</code>, [while you've stated varchar, I recommend DATETIME]</li> <li><code>RECORD_VALUE</code>, varchar(50)</li> </ul> <p>Pivoting/transposing the data requires using a CASE expression:</p> <pre><code>SELECT CASE WHEN t.record_date = '10-28-09' THEN t.record_value ELSE NULL END AS '10-28-09', CASE WHEN t.record_date = '10-29-09' THEN t.record_value ELSE NULL END AS '10-29-09', CASE WHEN t.record_date = '10-30-09' THEN t.record_value ELSE NULL END AS '10-30-09' FROM TABLE t </code></pre> <p>Reference: <a href="http://dev.mysql.com/doc/refman/5.0/en/case-statement.html" rel="nofollow noreferrer">CASE</a></p> <p>You might've noticed that the columns are hard coded - you'd want to consider using <a href="http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html" rel="nofollow noreferrer">MySQL's Prepared Statements</a> to dynamically create the statement.</p> <p>If you find that you're missing dates, you'd want to consider creating a numbers table so you can artificially construct dates by adding the appropriate value to a given date.</p>
    singulars
    1. This table or related slice is empty.
    1. PODisplay of Columns From Database
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USOMG Ponies
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHey rexem, thanks for the info on prepared statements and case. It seems the case conditional applies more-so for missing values. I tried to execute this: SELECT CASE WHEN t.date = '10-28-09' THEN t.name ELSE NULL END AS '10-28-09' FROM TABLE t which returned null values for the 9 rows in the test above. It seems mysql isn't best suited for the simple task i'm trying to accomplish. The dataset i'm working with will always be a strict set of 20 items and the only variable is the column name (date).
      singulars
      1. PO
      1. USrrrfusco
    2. COTo make a database as RJ suggested seems awkward, To display data horizontally seems beyond what mysql was intended for. My original solution seems like the simplest thing to do.
      singulars
      1. PO
      1. USrrrfusco
    3. CORexem, any advice? http://stackoverflow.com/questions/1658943/php-simulate-mysql-result-set
      singulars
      1. PO
      1. USrrrfusco
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload