check a variable for a fieldname

[jeff5656]

Here's my query:

$query2 = "SELECT * FROM telephone WHERE signoff_status = '".$so."' ";

 

How do I check to see if any of the field names (column names)  from that table equal a variable called $srt?

[ialsoagree]

Your question is a little bit confusing. Would this work for you?

 

<?php
$query2 = "SELECT $srt FROM telephone WHERE signoff_status = '$so'";
?>

[Mchl]

To few details. At least tell us which database you're using.

[jeff5656]

lets say we have this:

 

 

$query2 = "SELECT name, phone, state FROM telephone WHERE signoff_status = '".$so."' ";

 

and I have a session variable I assigned to $srt with a value of:

$srt = 'country';

 

I want to see if $srt matches any of the column names from the query.  In this case there is only name, phone and state.  There is no country so the comparison would be false.

In another instance, srt happens to be phone.  In that case it does match.

But what is the code to compare?  I am not looking for the CONTENT of these fields buit whether the names of these fields themselves exist.

 

I want to do something like this for every single fieldname selected in the query (I will be using * to select them all):

if ($srt !='any_of_the_field_names'){
then $srt = 'the first fieldname';
}
  

This way I wont get an error when I subsequently do ORDER BY $srt

[ialsoagree]

This seems like a check that would be better to do BEFORE you run an SQL query, here's why:

 

-If you do the check first, you can run 1 SQL query that orders everything for you.

-Having SQL order the data for you saves PHP from having to do it (SQL is going to be better at it anyway, with PHP it's going to take 2 loops).

 

You should know all the column names for all of your tables so my solution would be this:

 

Build an array with indexes with the name of each of your tables:

$database_array['table1']

$database_array['table2']

$database_array['tablex']

 

And then for each table, include the names of each column like so:

$database_array['table1'][] = 'colum_name1'

$database_array['table1'][] = 'colum_name2'

$database_array['table1'][] = 'colum_namex'

 

$database_array['table2'][] = 'colum_name1'

$database_array['table2'][] = 'colum_name2'

etc.

 

Then you can use the in_array function to determine if an order by criteria would work on a specific table, more on in_array here:

http://us2.php.net/manual/en/function.in-array.php

[jeff5656]

That seems like a lot of work.  I have like 10 tables with many columns.

Isn't there a way to do a query on the current table, and loop through the column names and check if any are equal to $sql, and if NONE are equal, the set $sql to be, say, the first column name.

 

Then I could put this code right before this:

 

$query2 = "SELECT * FROM telephone WHERE signoff_status = '".$so."' ORDER BY $srt";

 

and I would never get an error of a missing column name.

 

 

[ialsoagree]

That seems like a lot of work.  I have like 10 tables with many columns.

Isn't there a way to do a query on the current table, and loop through the column names and check if any are equal to $sql, and if NONE are equal, the set $sql to be, say, the first column name.

 

Then I could put this code right before this:

 

$query2 = "SELECT * FROM telephone WHERE signoff_status = '".$so."' ORDER BY $srt";

 

and I would never get an error of a missing column name.

 

It's going to be a fair amount of work no matter how you do it. There's no function in MySQL (that I've ever heard of anyway) or in PHP that handles this type of check.

 

It leaves you with 2 choices, one choice requires less processing each time the script runs, and the other requires more processing each time the script runs.

 

Any time that you can hard code a check (that is, define a set S, and then check if x is in set S) the check is going to have the minimum amount of processing necessary (that's what I've given you above).

 

Any time you have to generate the set S (by running a query, for example) you have to tell PHP to produce the set (this is going to be a decent amount of coding, and all that code has to be processed every time the script runs). But you're not done, all you've done is created the S set. Now you have to go through and see if x is in it.

 

In the end, my suggestion is (probably) the best. It might be more tedious, but the script will run faster and you really don't do any more coding.

 

In the end, no, there's no easy way to code this (at least that I know of).

 

If you are really unwilling to write out the list of tables and columns yourself, write a script to do this for you, then copy and paste it into your code. I'm not willing to do all the work for you, but I can give you the jist of how to do it:

 

<?php
$result = mysql_query('SELECT * FROM some_table LIMIT 1');
$array = mysql_fetch_assoc($result);
foreach ($array as $key => $value) {
echo "\n".' $database_array[\'some_table\'][] = $key;';
}
?>

 

I will admit, there is a small improvement you could make to my code. Instead of using numbered indexes and seeing if the column value is in that table (requiring the use of in_array), you could use the columns as indexes and check if it's defined:

 

<?php
$result = mysql_query('SELECT * FROM some_table LIMIT 1');
$array = mysql_fetch_assoc($result);
foreach ($array as $key => $value) {
echo "\n".' $database_array[\'some_table\'][\''.$key.'\'] = TRUE;";
}
?>

(there appears to be an error dispalying this, the 2nd index should be [\''.$key.'\'] but it does not appear to display this way inside the code box - this is an error with the forum and not with the way the code is written, make sure to apply this correction if you copy and paste this code).

 

...and then to check if a given column exists...

 

<?php
if ($database_array['table_to_search']['column_to_sort_by'] === TRUE)
?>

[Mchl]

So it's MySQL? Read about `information_schema` database or use SHOW COLUMNS