Weird Query Needed!

[iPixel]

Im not even sure if this is possible. My MySQL query abilities are amateur at best.

 

What i need it to query a single table and return all fields where any record may contain a text string " .jpg ".

 

So...

 

Table name  = sometablename

 

The structure...

 

field_1 | field_2 | field_3 | field_4 | field_5

 

values could be like...

 

Hello | img.jpg | what's up | nothing | bye.jpg

 

 

SO this query should either return the column names that have .jpg within it's records

so... field_2, field_5.

 

Best case scenario a result would look like

 

field_2 | field_5

img.jpg | bye.jpg

abc.jpg | yay.jpg

etc.jpg | got.jpg

 

So is this possible?

 

Also if i may add... i do not want to compare each column using 'column'.contains(string). Because there's like a bazillion columns and i dont want to type it all up. I'm looking for a query like

 

SELECT * FROM sometable WHERE  any column's values contain a .jpg.

[requinix]

Because there's like a bazillion columns and i dont want to type it all up.

Which should be the first indication that there's something wrong with your table layout. You need another table.

 

The table you have now includes some kind of identifier, right? Create a second table with two columns: one for the identifier in the first table, one for a single "field".

 

Before:

id | ... | field_1 | field_2 | field_3 | field_4 | field_5
---+-----+---------+---------+---------+---------+--------
1 |     | img.jpg | bye.jpg |         |         |
2 |     | abc.jpg |         | yay.jpg |         |
3 |     |         |         |         | etc.jpg | got.jpg

After:

id | ...
---+----
1 |
2 |
3 |

id | field
---+--------
1 | img.jpg
1 | bye.jpg
2 | abc.jpg
2 | yay.jpg
3 | etc.jpg
3 | got.jpg

[iPixel]

Actually i have no control over the table structure nor can i add anything to it. I'm using a CMS which is responsible for the database architecture, i just need to do some custom data mining type stuff.

[requinix]

Then no, there's no shortcut you can use. You'll have to type them all out.

[Zane]

Well if you have no say so in the construction, you could always grab the fields with DESCRIBE

and use the LIKE keyword to grab them all.

$describe = mysql_query("DESCRIBE yourtable");
while($r = mysql_fetch_array ($describe)) {
     $fields[] = $r['Field'];
}
// Create SQL statement
$sql = "SELECT * FROM yourtable WHERE ";
$c = 0;
foreach($fields as $field) {
    if($c >= count($fields)) $sql .= "(`$field` LIKE '%.jpg')";
    else $sql .= "(`$field` LIKE '%.jpg') OR \n";
    $c++;
}
$query = mysql_query($sql) or die("SQL: $sql 
\nError:" . mysql_error());