Dealing with queries where fieldname may not exist for that table

[jeff5656]

I use this query for more than one table with ORDER by $srt for all the queries:

$query = "SELECT * FROM $tblname WHERE pulmonologist = '$curr_user' AND signoff_status = '$so' order by $srt  LIMIT $start, $limit";

 

I assign $srt to a session so it is maintained when a user clicks links to the various tables.

if(isset($_POST['srt'])) {
$_SESSION['srt'] = $_POST['srt'];
$srt = $_POST['srt'];
} elseif (isset($_SESSION['srt'])) {
$srt = $_SESSION['srt'];
} else {
$srt = 'patient';
}

The problem is sometimes a table will not have a field that $srt was assigned to and I get this:

Unknown column 'calldate' in 'order clause'

 

Is there a way to catch this error during the query, and then assign $srt to, say, the first field name in that table? Or just ignore the ORDER BY if the $srt doesn't exist and skip right to the LIMIT part of the query.

[mrMarcus]

without any real thought, try this:

 

$order_by = (isset ($srt) ? ' order by '.$srt : '');

$query = "SELECT * FROM $tblname WHERE pulmonologist = '$curr_user' AND signoff_status = '$so'{$order_by} LIMIT $start, $limit";

[jeff5656]

$order_by = (isset ($srt) ? ' order by '.$srt : '');		
$query = "SELECT * FROM telephone WHERE pulmonologist = '$curr_user' AND signoff_status = '$so' {$order_by}  LIMIT $start, $limit";

 

I still get this:

Unknown column 'location' in 'order clause'

 

That really looked like a creative way to do it to.  It looks like your solution should have worked...

[jeff5656]

actually tht doesnt work because srt is isset.  it's just set with a variable that does not exist in the current table (in this example, srt = "location" and location does not exist in this table. 

[mrMarcus]

please make sure there is a field by the name of `location` in the `telephone` table.

[mrMarcus]

so, create an array with field names that are allowed, ie.

 

$field_arr = array ('some_field','another','another');

$order_by = (isset ($srt) && (in_array ($srt, $field_arr)) ? ' order by '.$srt : '');

 

am i understanding correctly?

[jeff5656]

no let's say you visit table one and srt is set to location.  Then you click another link for table 2 that does not contain a fieldname called location.  But srt is still set to location because I store that as a session variable.  So when it gets to order by $srt it gives the error.

So is there a way to check if the table has a fieldname that srt is set to, and if not, set srt to any valid column name that exists in the table?

[mrMarcus]

you need to somehow differentiate which fieldnames are associated with which tables, either by passing values (via $_GET/$_POST, etc.), or something along those lines.  otherwise, it's a guessing game, isn't it?

 

or don't use a session var to store the location if possible (it's always possible) .. or, reset the session var when it's not needed.

 

i'm quite sure some simple IF/ELSE statements as per which table is in question would suffice.