[RESOLVED] mysql_fetch_assoc result problem

system_critical

Hi,

I'm working on renaming a file on upload. My idea was to take the users name and add the primary key number for the name of their profile picture. I have already screened the file for size, extension and illegal characters in the name. I'm having trouble getting mysql_fetch_assoc to give me a value besides array. So here is the crux of the issue:

$query = 'SELECT \'index\' FROM mytable WHERE username =\''.$username.'\'';
$result = mysql_query($query);
$index = mysql_fetch_assoc($result);
// then we use it
$new_name = '../profile_pics/'.$username.$index['index'].'.'.$ext.'';

outputs for example:

'adminArray.jpg'

and when I use $new_name to rename the file uploaded you get this in the folder:

adminindex.jpg

From the query, index is the primary key, which is set to auto increment. I realize this seems like an arbitrary naming scheme, but it was simply an exercise for me, which has proven more difficult than I had hoped. I thought if in the returned assoc. array if I called for the index column, I could access it with $index['index'] and I would get the value. I appreciate any help tremendously. Thanks.

Weedpacket

SELECT `index`...

What you've done will select and return the literal string "index" in an arbitrarily-named field.

Suggestion: don't use keywords as identifiers 🙂

system_critical

Ahh, so index may be a reserved word. Because it gives the same result as before when I remove the quotes. I'll try renaming the column. Thank you very much.

bradgrafelman

You can either rename the column or surround it in backticks (for MySQL; mileage may/will differ with other DBMS's).

EDIT: Oops, just realized Weedpacket showed this already. :o Renaming the column, however, to avoid reserved words would be the more portable solution IMHO.

Weedpacket

system_critical wrote:
Ahh, so index may be a reserved word.

As in "CREATE INDEX ON some_table...". See http://dev.mysql.com/doc/mysql/en/reserved-words.html

bradgrafelman wrote:
Renaming the column, however, to avoid reserved words would be the more portable solution IMHO.

Especially since using backticks to quote identifiers is not standard SQL (the standard is to use double-quotes).