First Column

wilhud

Hello,

I was wondering if this is possible. I want to call the first column of a table in my code. So rather than having...

"SELECT * FROM $Table ORDER BY item_id ASC"

I wasnt something like...

"SELECT * FROM $Table ORDER BY " . COLUMN_1_ID . " ASC"

Obviously COLUMN_1_ID isn't it, but is there some code I can put there to achieve what I'm trying to do?

Thanks!
Wil

cgraz

I'm not sure if I understand you:

So if this is your db

id name address city state zip

Instead of doing

SELECT * FROM mytable

You want to select everything in the id field all the way down your table? So if there are 40 records, you want to select JUST the id fields for those forty records?

If that's what you want to do, the syntax for this is the same

SELECT [field(s)] FROM your_table [WHERE] [GROUP BY] etc

SELECT * selects all the fields - you'd want

SELECT id FROM your_table ORDER BY id ASC

and obviously the ORDER BY clause is optional.

Was that what you were looking for?

Cgraz

wilhud

Hello, sorry if the question was unclear but I am asking a serious question, not just screwing around. What I want to do is this...

Like you would run a query on your database and do $myrow[0] to display the first column of results in your database, I want to do something like that in my sql statement.

So I'd want something like...

"SELECT * FROM $Table ORDER BY ?? ASC"

?? Would be the first column, the one that holds the table ID. However, this page is dynamic so I won't know what the ID name is going to be. Otherwise I would just type in in. For one table it could be items_id and for another it could be users_id. Therefore, I was wondering if there was something I could type that would order it by the first column, rather than me having to specify a name. Or is there a way I could dynamically retrieve the column name?

I know that if I named my table and id similarly I could probably do something like...

"SELECT * FROM $Table ORDER BY " . $Table . "_id ASC"

But that would require everything in the database to be named properly and could encounter errors if it wasn't. I'll do this if there's no other solution, but it seems like there would be a better way. I just don't know what it is.

I'm not sure if this is going to be any more clear, sorry.

Wil

cgraz

YOu could pass the id variable in your url, so its up there whenever anyone accesses the page, then use that variable in your query

$id = $_GET['id'];
SELECT * FROM your_table WHERE id='$id'

and your url would look something like www.yourpage.php?id=7

and whatever the id was, your query would always be dynamic.

Does that help?

Cgraz

wilhud

Hi cgraz,

That's actually sort of what I'm doing right now. Let me write more of the code to see if I can show what want to do though.

The first page dynamically displays all of the tables in my database. Each Table has a link to a page that will display all of the information in that table. Here's a sample link.

This creates a link that looks like...

example: <a href="items.php?Table=user">user</a>

The results page then needs to display the results for that dynamic Table.

"SELECT * FROM $Table ORDER BY " . $Table . "_id ASC"

Which using the 'user' example would give me...

example: "SELECT * FROM user ORDER BY user_id ASC"

That works fine, but only if the 'user_id' column is named properly. Rather than use the name of the column, I was wondering if there is a way to just tell it to ORDER BY whatever the first column in the table happened to be. Seeing as the first column will always be the id. That way there won't be any naming issues.

Thanks,
Wil

cgraz

Seeing as the first column will always be the id. That way there won't be any naming issues.

If the first column is always id, then do

"SELECT * FROM $Table ORDER BY id ASC"

However I'm pretty sure that when you run a SELECT query, unless you actually specify ORDER BY, it's automatically ordered by your id field (assuming that's the primary key and set to auto increment). Now I alwasy get ASC and DESC mixed up 😉 but if asc is from least to greatest, then you don't even need the ORDER BY. if asc is greatest to least, then just do it how I wrote above.

Cgraz

wilhud

Hi cgraz,

You're right, but it won't always be called id, that's my problem. Sometimes it will be item_id, sometimes it will be user_id and so on. So depending on what table name is that is being displayed, the id name will change. Which is why I'm trying to find a way to just sort the first column, no matter what it is called. I wouldn't do it at all, because like you said it sorts that way anyhow, but I will need this later, when I go to update or delete a record. It's going to need to know which column has the id which needs modified or deleted.

Wil

wilhud

I think I found what I was looking for in the MySQL manual. Here's what I'm now using and it appears to be working...

"SELECT * FROM $Table WHERE _rowid = $ID"

_rowid is whatever column is the table's primary key or if there isn't one, it takes the first unique column. At least that's what the manual says. Which seems to work great for what I need. Hopefully I don't run into any problems.

Thanks for all the help,
Wil