SELECT DISTINCT error message

[AllenGarner]

I am using php 4.3.10 and mysql 4.1.9 on Windows Server 2003
I can SELECT Update insert delete just fine but when I use the DISTINCT keyword it craps out saying "table 'table_#SQL_43_34' is read only" I have a feeling it has somethig to do with making a temporary table or possibly permissions but dont know how to rectify can anyone help please?

 

[Bastien]

Depends on how you created the table. Windows permissions are a first check. Look at the folder perms for the table.

Also post your code, maybe there is something there

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[AllenGarner]

Windows perms and folder perms should be ok I created the table in phpmyadmin and the code is the following

function get_mysql_result($dbsql,$rstype)
{

$result = mysql_query($dbsql);

//-- initial value --
$iRows = 0;
$iCols = 0;

$iRows = mysql_num_rows($result);
$iCols = mysql_num_fields($result);
settype($arrContents,"array");

switch ($rstype) {
case "byindex":
for($row=0; $row<$iRows; $row++){
$rs = mysql_fetch_row($result);
for($col=0; $col<$iCols; $col++){
$arrContents[$row][$col] = $rs[$col];
};
};
return $arrContents;
break;
case "byname":
for($row=0; $row<$iRows; $row++){
$rs = mysql_fetch_row($result);
for($col=0; $col<$iCols; $col++){
$arrContents[$row][mysql_field_name($result,$col)] = $rs[$col];
};
};
return $arrContents;
break;
};

mysql_free_result($result);
}

$sql = "SELECT Distinct `group` FROM `link_table` WHERE `user` = '" . $user . "'"; // user is an integer

$result = get_mysql_result($sql, "byname") or die(mysql_error());

 

[ingresman]

Can you do an explain on the select please, I would agree that it's probabbly trying to do an intermediate sort. I wonder if you are getting a bad error message when in fact the real error may be lack of disc space (how much free do you have ?)

 

[r937]

be careful with column names that are actually reserved words

usually backticking them is okay, but it's better practice not to use a name like GROUP in the first place

and if user is an integer, don't put its values in single quotes, that makes them strings, and mysql only has to waste cycles turning the strings back into integers

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)