0

I want to make a select statement but Its just not working Here's what Im trying to do

SELECT * 
from table 
WHERE FROM any ROW Pick that $value_id 
and indentify the column and display the column_name into a variable

Here's my attempted code:

$id = $_GET['id'];
$sql = mysql_query("SELECT * from table2 WHERE ANYROW_VALUE=$id");
$selected = mysql_fetch_assoc($sql);
$ROW_NAME=mysql_field_name($selected);

Also the id will come up in different COLUMNS so I would like to display all the columns with either do{ or WHILE ... But first I want to find out how can I get this far, thanks!

One thing not to get confused the $id is not a table id is just a numbered value from 1-8000 , also that number can be on the same row but not on the same column , so When I made the table I set each column to unique one more thing , The columns get bigger and bigger as development so it could be 1-50 columns it is why I asked this question it was ok if the column size didn't change but it does so... thanks !

Thankyou all for being here , This is how the table looks like rough sketch : https://i.stack.imgur.com/Rha4C.png

user4316754
  • 21
  • 5
  • Does nobody know how or is it hard to understand the question? – user4316754 May 12 '15 at 18:09
  • Sorry, but "the ID" of a row cannot be in different columns. You miss understood the idea of an ID. I assume what you are looking for is a "search index" that contains the value of all columns. – arkascha May 12 '15 at 18:10
  • where is `*` in your `SELECT` code? – Alive to die - Anant May 12 '15 at 18:10
  • Um Yeah but there are 40-50 columns and they increase in number dayly its why I asked the question this weirdly ! – user4316754 May 12 '15 at 18:10
  • 4
    Sounds like a really bad design of your application (data model). – arkascha May 12 '15 at 18:11
  • arkascha chan that is exactly what I ment sorry for missunderstanding ... Let me edit the question to make it more detailed ! – user4316754 May 12 '15 at 18:12
  • 1
    Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and consider using PDO, [it's not as hard as you think](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard May 12 '15 at 18:12
  • Whoa... "From any row pick that value" So you just want a [random record](http://stackoverflow.com/questions/1823306/mysql-alternatives-to-order-by-rand)? and then show that record? The link is to so you how someone's already done it in a previous question. – xQbert May 12 '15 at 18:12
  • No its not random it is all values ... read the UPDATE of the question while Ill try drawing a table to explain better ! – user4316754 May 12 '15 at 18:19
  • As far as I know there is no "any value in the row" feature in MySQL, and I'd like to think I am fairly well versed. You can do `WHERE $id IN (col1, col2, col3....)` (where the .... just indicates more field names, NOT actual syntax), but you would have to update the query every time you added another column to be checked. I would 2nd arkascha's statement about your data model and suggest you rework that if you can. (Also, the above suggestion won't tell you the column the match was found in; that would be a MUCH longer query.) – Uueerdo May 12 '15 at 18:25
  • Um Uuerdo I asked this because col1, col2 , col3 increase in number ... anyway maybe its clearer with the picture now? Cristik I am new here so 3 minutes is a lifetime ^-^ – user4316754 May 12 '15 at 18:35

1 Answers1

0

You should really reconsider your database design if you are adding more and more columns to your database.

To solve this problem you will want to run two queries mixed in with php

your first query

mysql_query("SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' 
    AND `TABLE_NAME`='yourtablename'");

You will then loop though the column names to build your WHERE for your second query I would recommend inserting the column names into an array so you can use implode like so

foreach($result as $columnName){
    $columnArray[] = '`' . $columnName . '` = ' . $id;
}

then you would run your query

mysql_query("SELECT * FROM `yourtablename` WHERE " . implode(' OR ', $columnArray));
cmorrissey
  • 8,493
  • 2
  • 23
  • 27