Extract primary key from MySQL in PHP

Question

I have created a PHP script and I am lacking to extract the primary key, I have given flow below, please help me in how can i modify to get primary key

I am using MySQL DB, working for Joomla, My requirement is tracking the activity like insert/update/delete on any table and store it in another audit table using triggers, i.e. I am doing Auditing. DB's table structure: Few tables dont have any PK nor auto increment key

Flow of my script is :

1. I fetch out all table from DB.
2. I check whether the table have any trigger or not.
3. If yes then it moves to check for next table and so on.

4. If it does'nt find any trigger then it creates the triggers for the table, such that,

   • it first checks if the table has any primary key or not(for inserting in Tracking audit table for every change made)
   • if it has the primary key then it uses it further in creation of trigger.
   • if it doesnt find any PK then it proceeds further in creating the trigger without inserting any id in audit table

Now here, My problem is I need the PK every time so that I can record the id of any particular table in which the insert/update/delete is performed, so that further i can use this audit track table to replicate in production DB.. Now as I haave mentioned earlier that I am not available with PK/auto-incremented in some table, then what should I do get the particular id in which change is done?

please guide me...GEEKS!!!

Answer 1

If I understand your question right, you need a unique identifier for table rows that have no primary key and no other kind of unique identifier. That's not easy to do as far as I can see. Other databases have unique Row IDs, but mySQL does not. You could use the value of every column to try and identify the row, but that is far from duplicate-safe - there could be two or more rows containing the exact same values. So I'd say, without a unique identifier, this is something that simply cannot be done.

Some ideas in this SO question:

MySQL: is there something like an internal record identifier for every record in a MySQL table?