SQL Like without column name (using php)

Question

I need to find a result from all columns of a table starting with a specific value, regardless the column and assuming I don't know the columns's name, something like this:

$sql = "SELECT * FROM $myTable WHERE AllTheColumns LIKE '{$theValue}%'";

I know after the "WHERE" I have to specify the column name, but any other solution will be ok (if exists).

You could look for about column names in metadata information. — Shondeslitch, Nov 29 '15 at 01:01
@todd's answer should get you the required result. However I am wondering why you are trying to do it this way? I think you may have better success refining your process and maybe adjusting your table structure. — , Nov 29 '15 at 01:07
@Dijon I'm trying to optimice my code because i'm working with tables with 15 - 23 columns, using the @ яша method. — Roberto Sepúlveda Bravo, Nov 29 '15 at 02:51

score 2 · Answer 1 · answered Nov 29 '15 at 00:48

2

There's any other way you can do it.

SELECT * FROM $myTable 
    WHERE Column1 LIKE '%$search%' 
        OR Column2 LIKE '%$search%' 
        OR Column2 LIKE '%$search%'  
        OR Column3 LIKE '%$search%'

answered Nov 29 '15 at 00:48

Yehia Awad

2,898
1
20
31

yes I know that way, but if I don't know columns's name? – Roberto Sepúlveda Bravo Nov 29 '15 at 00:52
unfotunately it is not possible unless you dig into your DB metadata – Yehia Awad Nov 29 '15 at 01:00

score 2 · Answer 2 · answered Nov 29 '15 at 01:03

2

You could first select all the column names from the table:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';

// store resulting column names in $column_names var;

Then, with an array of column names, create a loop that adds each column name to the query:

$query = "SELECT * FROM $table_name WHERE ";

foreach($column_names as $i => $colname) {
  $query .= "$colname LIKE %$search_string% ";
  if ($i+1 < $column_names.length()) {
    $query .= " OR ";
  }
}

// do stuff with query

answered Nov 29 '15 at 01:03

Todd

2,824
2
29
39

I trying this way but I think I'm not creating correctly that array of column names, I have this: $sql="SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$db' AND TABLE_NAME = 'personal';"; $thenames = mysql_query($sql, $db); $column_names=array(); while($fila = mysql_fetch_object($thenames)) { array_push($column_names,$fila); } and the error: Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\ ... – Roberto Sepúlveda Bravo Nov 29 '15 at 02:38

score 1 · Answer 3 · edited May 23 '17 at 12:30

1

possibly, there are two options.

1) You can get all column names and perform operation accordingly.

Possible Solution: MySQL query to get column names?

2) You can reffere specific column by typing index.

Possible solution: Getting a Mysql Results without knowing a column name

edited May 23 '17 at 12:30

Community

1
1

answered Nov 29 '15 at 01:11

Ali Shan

334
3
11

SQL Like without column name (using php)

3 Answers3