Re: Any method to get primary key matching a given value ?

Hi Ratin,

check out this FAQ : https://sqlite.org/faq.html#q7

SQlite has a read-only table that holds the creation query for each table.

By doing a text analysis of this query, you can find out which field is 
declared as primary key.

Regards,

Bert

On 11/10/16 02:12, Ratin wrote:
> Sorry about my late reply but this was my function - a bit of a hack but
> works properly on my version of php-sqlite3:
>
>
>       function get_primary_key_name($table)
>       {
>            $primary_key='';
>            $db = new MyDB();
>            if(!$db)
>            {
>                echo $db->lastErrorMsg();
>            }
>            else
>            {
>                $qstr = "PRAGMA table_info(" . $table . ");" ;
>                $query = $db->query($qstr);
>                while ($result = $query->fetchArray())
>                {
>                   if ($result['pk'] == 1)
>                   {
>                         $primary_key=$result['name'];
>                    }
>                 }
>            }
>            $db->close();
>            return $primary_key;
>        }
>
>
> On Sat, Aug 20, 2016 at 3:35 AM, Karl DeSaulniers <[email protected]>
> wrote:
>
>> This may also shed some light for you.
>> The accepted answer and possibly the one below it if you are on .NET
>>
>> http://stackoverflow.com/questions/763516/information-
>> schema-columns-on-sqlite
>>
>> HTH,
>>
>> Best,
>>
>> Karl DeSaulniers
>> Design Drumm
>> http://designdrumm.com
>>
>>
>>
>>
>>> On Aug 20, 2016, at 5:30 AM, Karl DeSaulniers <[email protected]>
>> wrote:
>>>
>>> Hey Ratin,
>>> Have you looked into the table column named 'pk' inside table_info?
>>> That is where a column is indicated to be a primary key or not.
>>>
>>> Best,
>>>
>>> Karl DeSaulniers
>>> Design Drumm
>>> http://designdrumm.com <http://designdrumm.com/>
>>>
>>>
>>>
>>>
>>>> On Aug 18, 2016, at 6:51 PM, Ratin <[email protected]> wrote:
>>>>
>>>> Hi Karl, Thanks a lot for your response, I think  INFORMATION_SCHEMA is
>> not available for sqlite database. I had to built up the whole query with
>> php using PRAGMA table_info(tablename), looking at the pk entry, when its
>> 1, get the column name, and then update the sql statement based on that. A
>> bit of work, wouldve been much simpler if a method was provided, but oh
>> well ..
>>>>
>>>> Thanks again
>>>>
>>>> Ratin
>>>>
>>>> On Thu, Aug 18, 2016 at 2:53 PM, Karl DeSaulniers <[email protected]
>> <mailto:[email protected]>> wrote:
>>>> Hi Ratin,
>>>> Going to take a stab at this one.
>>>> Have you looked into INFORMATION_SCHEMA.COLUMNS for your query?
>>>> Might be where you want to look for what you are trying.
>>>> Sorry can't help more.
>>>>
>>>> Best,
>>>>
>>>> Karl DeSaulniers
>>>> Design Drumm
>>>> http://designdrumm.com <http://designdrumm.com/>
>>>>
>>>>
>>>>
>>>>
>>>>> On Aug 18, 2016, at 1:27 PM, Ratin <[email protected] <mailto:
>> [email protected]>> wrote:
>>>>>
>>>>> I'm writing the generic get that works on different tables having
>> different
>>>>> primary keys but the argument of get is always the primary key , i.e.
>> get
>>>>> request is -
>>>>>
>>>>> get (column name, value)
>>>>>
>>>>> the value is always the primary key value.
>>>>>
>>>>> It looks like it would be a pretty standard method but I cant find a
>> method
>>>>> like that. Anybody have any clue?
>>>>>
>>>>> Thanks
>>>>>
>>>>> Ratin
>>>>
>>>>
>>>> --
>>>> PHP Database Mailing List (http://www.php.net/ <http://www.php.net/>)
>>>> To unsubscribe, visit: http://www.php.net/unsub.php <
>> http://www.php.net/unsub.php>
>>>>
>>>>
>>>
>>
>>
>> --
>> PHP Database Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
>


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php