5

I would like to SELECT * FROM table where the first column is equal to a variable. It supposed that I don't know the column name.

I know I can do something like 

SELECT * FROM table WHERE column_id = 1

But I can't compare the data.

How can I do that?

I found some solution with T-SQL but it doesn't interest me.

To be more accurate :

I'm developing an administration panel in my website where the "super" admin can directly modify the database. For that I can select a table and edit this table. But to do that, I'm using an only PHP script which showing all tables, we can select one and the script show all rows in the selected table. After that you select a row and you are redirected to a page where the problem is. This page can receive any table with only one row, so I want to SELECT the data contained in this row.

Images to understand:
The first one shows the tables.
The second shows the rows of a selected table.
The third shows (normally) the data of 1 row but in this picture we can see data of many rows.
selecto http://imageshack.us/g/135/selecto.png

I found a solution :

Try to explain: First : I selected all form the specific table which was posted 

 $query="SELECT * FROM ".$_POST['table']."";
    $result=mysql_query($query);

Second: I attributed to a variable the column name (which I didn't know)

while($fields=mysql_fetch_array($result))
    {
        $col =  mysql_field_name($result,0);
        $nb++;
    }

Third: I selected data from the table where $col = id of the row

$sql = "SELECT * FROM ".$_POST['table']." WHERE ".$col."=".$_GET['idRow']."";
$result1=mysql_query($sql);
shA.t
  • 16,580
  • 5
  • 54
  • 111
Alexandre Loctin
  • 129
  • 1
  • 2
  • 9
  • 8
    It is not clear what you are trying to accomplish and how you are failing. Consider rewording your question. – lanzz Jun 07 '12 at 08:59
  • 7
    If T-SQL doesn't interest you - **what** database and SQL dialect are you interested in!?!? – marc_s Jun 07 '12 at 08:59
  • not quite clear what you're asking, but understanding to your question title, you can do this SELECT * FROM table WHERE column = value AND column2 != value – evilone Jun 07 '12 at 09:01
  • Well, i got 19 tables in my SQL database. The first column of every tables is the id of the table. I would like, in PHP, to SELECT * FROM every table WHERE the id (which name is different between every tables) is equal to a variable. – Alexandre Loctin Jun 07 '12 at 09:03
  • 3
    Its odd that you know the name of the table, and not the name of its PK. With a sensible naming convention it could even be inferred. – Jon Egerton Jun 07 '12 at 09:07
  • 1
    You cannot do `SELECT * FROM every table` in a single statement, and when you have multiple statements you can also have the different column names specified in them. – lanzz Jun 07 '12 at 09:07
  • Ok i got an idea, Can i do a $fields = mysql_num_fields($result); and after this a $query = "SELECT * FROM $mytable WHERE $fields[0] = $var "; – Alexandre Loctin Jun 07 '12 at 09:12
  • Not working because mysql_num_fields returns the number of fields – Alexandre Loctin Jun 07 '12 at 09:30
  • I hope you're not using string concatenation elsewhere in your app to build queries. It leaves you crazy-vulnerable to sql injection attacks, and you **will** get hacked. – Joel Coehoorn Dec 14 '14 at 19:20
  • Maybe this other question could be helpful if you want to do it all on the DB Side : http://stackoverflow.com/questions/4165195/mysql-query-to-get-column-names – Benoittr Jun 15 '15 at 13:51
  • 1
    Just pointing out that this statement here... `$query="SELECT * FROM ".$_POST['table']."";` ...leaves you *wide* open to all sorts of SQL injection. – haslo Jun 15 '15 at 15:00
  • You know, you can answer your own questions with an actual *answer* on stack overflow, rather than editing your question – Lukas Eder Mar 25 '16 at 08:49

3 Answers3

1

If you know how many columns there are, you could use this little trick here:

SELECT *
FROM (
  SELECT null x1, null x2, ..., null xn
  WHERE 1 = 0
  UNION ALL
  SELECT * FROM my_table
) t
WHERE t.x1 = something

In other databases than MySQL, renaming "unknown" columns would be even simpler, e.g. in PostgreSQL you could rename only the first column like this:

SELECT * FROM my_table t(x) WHERE x = something

If you don't know anything about the table

... you can quickly query the information_schema first:

SELECT column_name
FROM information_schema.columns
WHERE table_name = :my_table
AND ordinal_position = 1

A note on SQL injection

Please don't, DON'T do this. EVER:

$query="SELECT * FROM ".$_POST['table']."";

I've recently written an article about SQL injection. Every single vulnerability like yours will allow any script kiddie to dump your database, or worse.

The solution is to sanitize your input first. Ideally, you'll maintain a catalog of allowed table strings, compare your $_POST variable with those, and then concatenate the pre-defined table string into your SQL statement, NOT the user input.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
0

In PHP you could do something like:

$col = 'users';
mysql_query("SELECT * FROM table WHERE $col = $something");
shA.t
  • 16,580
  • 5
  • 54
  • 111
dibs
  • 1,018
  • 2
  • 23
  • 35
0

I think you can use SHOW CREATE TABLE table_name to fetch the schema of the table. After that, you should already know every column.

Clarence
  • 721
  • 1
  • 5
  • 9