Jump to content

Dealing with queries where fieldname may not exist for that table


jeff5656

Recommended Posts

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.

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";

$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...

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?

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?

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.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.