0

I have a table in a MySQL database that works as a course grade book. I have the table set up so that the columns are students names and the rows are the assignment name. The data that goes in is the grade.

However the problem that i am having is when i try to select a student (column named after a student), it only return the name of the column repeated. Here is the query:

SELECT 'Ryan Wans' FROM cachedb.stewart;

Where Ryan Wans is the student name and stewart is the name of the table. It returns the following:

+---------+
|Ryan Wans|
+---------+
|Ryan Wans|
|Ryan Wans|
|Ryan Wans|
|Ryan Wans|
+---------+

It Should Return:

+---------+
|Ryan Wans|
+---------+
|100      |
|87       |
|100      |
|96       |
+---------+

It returns 4 times because I have 4 assignments in the table. The numbers represent the grades. Any help is appreciated.

NOTE : I would also prefer to keep the student names as columns.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Ryan Wans
  • 28
  • 5
  • Try `Ryan Wans` - backtick instead of ' – Matthew Page Jan 27 '19 at 01:25
  • @MatthewPage Thanks very much, this worked!! Any idea as to why tho?? – Ryan Wans Jan 27 '19 at 01:29
  • Your column name should never be someones name, that's really bad database design. Post your tables and we'll clean them up :) – Matthew Page Jan 27 '19 at 01:30
  • The SELECT thinks anything in quotes is a text string , `SELECT 'hello world'` . Usually a column name has no spaces so you can just use the name with no quotes, the space forces you to use quotes, but in this case backticks – Matthew Page Jan 27 '19 at 01:31
  • @MatthewPage Actually: How would you make the table more efficient?? any help is needed :) – Ryan Wans Jan 27 '19 at 01:42
  • I would start a new question, How to optimise my tables.. Explain what the data is you are storing and show the tables you have. If you tag it MySQL I'll drop in before the admins kill it for a code review :) – Matthew Page Jan 27 '19 at 01:46
  • @MatthewPage Here is the table :) https://github.com/ryanrocket/tts-basic/blob/master/bin/testdb.txt – Ryan Wans Jan 27 '19 at 02:05
  • Great, give me a chance the practice my git commands, I'll try and edit it and you should get a notice... – Matthew Page Jan 27 '19 at 02:21

1 Answers1

-1

'Ryan Wans' should be your where condition.

Select * from cachedb.stewart where column_name = 'Ryan Wans'

Or instead of *, call whatever column name you wanna call after select i.e. amount, qty

Erik
  • 11
  • 1
  • 6