finding primary key

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Da_Elf
Forum Commoner
Posts: 81
Joined: Mon Dec 29, 2008 12:31 pm

finding primary key

Post by Da_Elf »

where am i going wrong with this?

Code: Select all

$key = mysql_fetch_array(mysql_query("SHOW KEYS FROM (table) WHERE Key_name = 'PRIMARY'"), MYSQL_ASSOC);
echo $key['KEYS'];
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: finding primary key

Post by twinedev »

1. Why do mysql_fetch_array($resource,MYSQL_ASSOC) when you can just use mysql_fetch_assoc($resource)?

2. You have no checking to make sure that the query will actually return something before trying to use it.

3. If you have more than one Primary Key, you won't get past the first row, as you are not looping through results.

4. $key['KEYS'] (at least on my server) is not a valid field. When I tried the SHOW KEYS statement on one of my tables, I received the following fields:
| Table
| Non_unique
| Key_name
| Seq_in_index
| Column_name
| Collation
| Cardinality
| Sub_part
| Packed
| Null
| Index_type
| Comment
Da_Elf
Forum Commoner
Posts: 81
Joined: Mon Dec 29, 2008 12:31 pm

Re: finding primary key

Post by Da_Elf »

here is the full code i got working now incase anyone finds it useful. if you find a way to shorten it please feel free to share for everyone's benifit

Code: Select all

function purgeTab($table,$sid,$bdConn){
	$rows = mysql_result(mysql_query('SELECT COUNT(*) FROM $table'), 0); 
	if (!$rows) { mysql_query("ALTER TABLE $table auto_increment = 1", $bdConn); }
	else {$siz = mysql_fetch_array(mysql_query('SELECT $sid FROM $table ORDER BY $sid DESC LIMIT 1',$bdConn), MYSQL_ASSOC);
	$news = $siz['$sid']+1; mysql_query("ALTER TABLE $table auto_increment = $news", $bdConn);} 	
}
function getPrimaryKeyOf($table) { 
  	$keys = Array();
  	$kresult = mysql_query(sprintf("SHOW KEYS FROM `%s`", $table)) or die(mysql_error());
  	while ($krow = mysql_fetch_assoc($kresult)) {if ( $krow['Key_name'] == 'PRIMARY' ){$keys[$krow['Seq_in_index'] - 1] = $krow['Column_name'];}}
	return $keys;
}
$tresult = mysql_query("SHOW TABLES FROM $dbName");
while ($trow = mysql_fetch_row($tresult)) {
	$pname = getPrimaryKeyOf($trow[0]);		
	purgeTab($trow[0],$pname[0],$dbConn);
}
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: finding primary key

Post by twinedev »

If you are working with your own database, an easier method would be to handle filed names such as, Table `account`, PK `account_id` or `accountID`.

also, for the purge you could just do one query:

ALTER TABLE `$table` AUTO_INCREMENT = (SELECT MAX(`$sid`)+1 FROM `$table`)

On second thought, it may complain that you are selecting from the table you are altering, but even so you could do

$news to be: SELECT MAX(`$sid`) FROM `$table`
$news++;
ALTER TABLE `$table` AUTO_INCREMENT = $news
Post Reply