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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



You may be correct. Admittedly, I am stabbing in the dark here. I work on MySQL, not SQLite.
Just good at key word searches. :)  This link seems to be promising.

http://www.sqlite.org/c3ref/table_column_metadata.html

Sorry I can't help more.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com




> On Oct 10, 2016, at 7:48 PM, Ratin <ratin3@xxxxxxxxx> wrote:
> 
> Hi Karl, Isnt it the same ? I am doing the check  if ($result['pk'] == 1),
> and you are doing  if ($result['pk'] !== null). I wanted to make sure I
> dont get false positives, what if $result['pk'] is set to zero, in your
> case it will be a match but thats probably not what we want, cuz the pk
> field is not set to 1..
> 
> Regards,
> Ratin
> 
> 
> On Mon, Oct 10, 2016 at 5:38 PM, Karl DeSaulniers <karl@xxxxxxxxxxxxxxx>
> wrote:
> 
>> Hi Ratin,
>> I "think" you can just do this and not have to store the table name.
>> However, I have no way of testing this. You will have to test on your end.
>> 
>> while ($result = $query->fetchArray()) {
>>        if ($result['pk'] !== null) {
>>                $primary_key=$result['pk'];
>>        }
>> }
>> 
>> Best,
>> 
>> Karl DeSaulniers
>> Design Drumm
>> http://designdrumm.com <http://designdrumm.com/>
>> 
>> 
>> 
>> 
>>> On Oct 10, 2016, at 7:12 PM, Ratin <ratin3@xxxxxxxxx> 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 <karl@xxxxxxxxxxxxxxx
>> <mailto:karl@xxxxxxxxxxxxxxx>> 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 <http://stackoverflow.com/
>> questions/763516/information-schema-columns-on-sqlite>
>>> 
>>> HTH,
>>> 
>>> Best,
>>> 
>>> Karl DeSaulniers
>>> Design Drumm
>>> http://designdrumm.com <http://designdrumm.com/>
>>> 
>>> 
>>> 
>>> 
>>>> On Aug 20, 2016, at 5:30 AM, Karl DeSaulniers <karl@xxxxxxxxxxxxxxx
>> <mailto:karl@xxxxxxxxxxxxxxx>> 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/> <
>> http://designdrumm.com/ <http://designdrumm.com/>>
>>>> 
>>>> 
>>>> 
>>>> 
>>>>> On Aug 18, 2016, at 6:51 PM, Ratin <ratin3@xxxxxxxxx <mailto:
>> ratin3@xxxxxxxxx>> 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 <
>> karl@xxxxxxxxxxxxxxx <mailto:karl@xxxxxxxxxxxxxxx> <mailto:
>> karl@xxxxxxxxxxxxxxx <mailto:karl@xxxxxxxxxxxxxxx>>> 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/> <
>> http://designdrumm.com/ <http://designdrumm.com/>>
>>>>> 
>>>>> 
>>>>> 
>>>>> 
>>>>>> On Aug 18, 2016, at 1:27 PM, Ratin <ratin3@xxxxxxxxx <mailto:
>> ratin3@xxxxxxxxx> <mailto:ratin3@xxxxxxxxx <mailto:ratin3@xxxxxxxxx>>>
>> 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/>
>> <http://www.php.net/ <http://www.php.net/>>)
>>>>> To unsubscribe, visit: http://www.php.net/unsub.php <
>> http://www.php.net/unsub.php> <http://www.php.net/unsub.php <
>> http://www.php.net/unsub.php>>
>>>>> 
>>>>> 
>>>> 
>>> 
>>> 
>>> --
>>> 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





[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux