[SOLVED] MYSQL_BOTH - column name identification

[ann]

Hi

 

I'm working on a 'quick', 'key word' based search for my database.  Say the key word is 'pool' and the sql search returns an array like...

id : image_desc    : location        : photographer

2  : mountain pool  : lake district  : Mr Swan

8  : tower ballroom : blackpool    : Mrs Pen

9  : beach ball        : skegness    : James Pool

 

The code below will print...

2 mountain pool

8 blackpool

9 James Pool

 

What I'd like is...

2 mountain pool (image_desc)

8 blackpool (location)

9 James Pool (photographer)

 

I can do this by creating an array of all my column names and using $i to pull out the relative column id but is there a better way to do it?  I'm using MYSQL_BOTH so I already have the column name information I just don't know how to get at it.

 

Thanks for your time.

Ann

 

		
while($row = mysql_fetch_array($sql, MYSQL_BOTH)){ 
     //work out which field matched and print that field
     for ($i=1; $i<sizeof($row); $i++) {
        if (preg_match('/'.$_REQUEST['search_key'].'/', $row[$i])){
           $row[$i]=preg_replace('/'.$_REQUEST['search_key'].'/', '<b>'.$_REQUEST['search_key'].'</b>', $row[$i]);  #make the search term bold
           echo $row[0]." ".$row[$i]."<br>";
        }
     }
}

[Barand]

One way

SELECT id, image_desc, '(IMAGE_DESCRIPTION)' as col FROM mytable WHERE image_desc LIKE '%pool%'
UNION
SELECT id, location, '(LOCATION)' as col FROM mytable WHERE location LIKE '%pool%'
UNION
SELECT id, photographer, '(PHOTOGRAPHER)' as col FROM mytable WHERE photographer LIKE '%pool%'
ORDER BY id 

[ann]

Thanks Barand.  Don't think I asked the question very well and I probably should have asked under php. 

 

What I was after is the array_keys function which gives an array of the column names from a query.  My code now looks like...

 

$sql = mysql_query($query);
$columns= array_keys(mysql_fetch_array($sql, MYSQL_ASSOC)); 	

$sql = mysql_query($query);
while($row = mysql_fetch_array($sql, MYSQL_BOTH)){ 
     //work out which field matched and print that field
     for ($i=1; $i<sizeof($row); $i++) {
        if (preg_match('/'.$_REQUEST['search_key'].'/', $row[$i])){
           $row[$i]=preg_replace('/'.$_REQUEST['search_key'].'/', '<b>'.$_REQUEST['search_key'].'</b>', $row[$i]);  #make the search term bold
           echo $columns[$i]." ".$row[$i]."<br>";
        }
     }
}

 

NOTE: if you're joining tables and those tables have columns with the same name (I had edit_date columns in two tables) the array_keys function will only give you the first instance of the name so the $columns array and the $row array end up different lengths.