Page 1 of 1

finding primary key

Posted: Thu Nov 01, 2012 7:34 am
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'];

Re: finding primary key

Posted: Thu Nov 01, 2012 11:49 am
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

Re: finding primary key

Posted: Fri Nov 02, 2012 6:20 am
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);
}

Re: finding primary key

Posted: Fri Nov 02, 2012 1:52 pm
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