0

I use MySQL (in NodeJS). I have a table with unknown data.

I present the data as an HTML table by looping out the items. Then I add a click event to the cells. When that happens I want to load the data from the current cell.

How can I do that?

What I could do (but don't want to)

I could let the user add a custom_identifier to specify that in this table, use the id as identifier.

/*
From click event. I click on the cell with `column slug` with `id 3`.

cell = {
  identifier: 3,
  column: slug,
};
*/
const custom_identifier = "id";
const sql = `SELECT ${cell.column} FROM WHERE ${custom_identifier} = ${cell.identifier}`;

Because this approach requires the user to specify the identifier for each table, I don't want to use this approach.

Problem

When making SQL queries and wanting a particular row, we need a WHERE statement and an identifier to get the correct row. Some tables don't have an id or even a unique identifier at all.

Question

How can I get the data from a row where I don't know an id?

Ideas

  • Is there a kind of index I could use that is not bound to a column name?
  • I can add more stuff to my data attributes in the HTML if needed, index or something else. I just don't want to put the load on the user.
Machavity
  • 30,841
  • 27
  • 92
  • 100
Jens Törnell
  • 23,180
  • 45
  • 124
  • 206
  • You can query the information schema – Strawberry Jun 18 '20 at 07:01
  • I'm thinking like me, I try every possible way to even create an artificial data to be used for the query first; if I can't use it in `WHERE`, I make it in `SELECT` using `CASE` then filter it in `HAVING` instead of `WHERE` etc. As long as I still think that it's possible to do with plain MySQL query, I'll try it.. so do you think it's possible to do it in plain MySQL query? – FanoFN Jun 18 '20 at 07:43
  • Use `SHOW CREATE TABLE tablename` to see what the table is like. (and show us) – Rick James Jun 30 '20 at 02:51

1 Answers1

2

You could use this technique to add a row number to each row. Here's an example of it being used to generate an ID column with incrementing integer values starting from zero:

SELECT @idvar := @idvar + 1 AS id,
       tbl.*
FROM tbl, 
     (SELECT @idvar := -1) alias;

As the order of results from a SELECT isn't guaranteed, you may need to use this to create either a temporary table or a non-temporary table that is later deleted - e.g:

CREATE TABLE new_tbl AS 
SELECT @idvar := @idvar + 1 AS id,
       tbl.*
FROM tbl, 
     (SELECT @idvar := -1) alias;

Note: You'll get a "Duplicate column name 'id'" error if id is already the name of an existing column in the table. So unless you know this can't be the case it could be worth going with a more obscure column alias name, e.g. id_zyxwv or some other gobbledygook.

See working demo here: https://rextester.com/DVRGNM12570

Steve Chambers
  • 37,270
  • 24
  • 156
  • 208
  • 1
    It looks like THE solution to my problem. Thanks! You mention temporary table and non-temporary table. I wonder why you use the non-temporary table in this case? – Jens Törnell Jun 30 '20 at 07:50
  • 1
    It just depends when you might need to read the data - if you can guarantee it will only be used within the same database connection (session) then by all means go for a temporary table. I went with a non-temporary table in the example in case this can't be guaranteed. – Steve Chambers Jun 30 '20 at 07:58