problem with mysql_fetch_row() vs mysql_fetch_assoc()

Not getting data with fetch_assoc()

[neophyte]

Initially I set up some code to display table data using fetch_row() and then used $name = result[0]; $date = result[1]; $time = result[2]; - and so on - to assign the different values of the array into the specific variables noted above.

That worked great.

Then I stumbled upon fetch_assoc(). Cool. Now I can refer to the result by the name of the db field - it was always hard for me to remember what [0], [1], etc. were referring to.

However, when I use fetch_assoc(), I AM getting result['name']; but I'M NOT getting result['postDate']; or result['postTime'];.

Hummm. Interesting. Irritating.

When I switch back to fetch_row() and use [0] (these are called numberic indices, correct?) etc., everythings okay again - I'm able to get the date and time as well as the name.

I've triple-checked my db field names (they're ok: postDate and postTime) as well as my query strong (okay as well).

I can't see where I'm going wrong. Anyone have any idea?

Neophyte.

[dreamcatcher]

Can you post us some code so we can see how you are using mysql_fetch_assoc?

dc

[neophyte]

Here ya go:

while ($viewRows = mysql_fetch_assoc($recordSet)) {

$name=$viewRows['name'];// put name into variable $name
$city=$viewRows['city'];// put city into variable $city
$country=$viewRows['country'];// put country into variable $country
$rating=$viewRows['rating'];// put rating into variable $rating
$comment=$viewRows['comment'];// put comment into variable $comment
$date=$viewRows['postDate'];// put postDate into variable $date
$time=$viewRows['postTime'];// put postTime into variable $time

...display the variables
}

DC - as I had said, everything comes out fine except the postDate and postTime.

The only thing different about postDate and postTime from the other fields is that the datatype for postDate is date and for postTime is time, and I'm using a mySQL function on them in the initial select query to alter the display formatting (see below)

-----------------------------
$query=("SELECT
name, city, country, rating, comment, date_format(postDate,'%W, %M %D, %Y.'), time_format(postTime,'%l:%i %p')
FROM
guest
ORDER BY
id DESC");

$recordSet = getRecords($query);
------------------------------

I don't know why (or if) using the date_format/time_format function is making fetch_assoc() inoperable for these two fields, as it doesn't cause any problem for fetch_row().

Hope you (or someone) can see what's happening. I'm baffled.

Neophyte

[dreamcatcher]

date_format and time_format are missing some arguments:

Try this:

$query=("SELECT
name, city, country, rating, comment, date_format(postDate,'%W, %M %D, %Y.') as date_string, time_format(postTime,'%l:%i %p') as time_string
FROM
guest
ORDER BY
id DESC");

Then use $viewRows['date_string'] & $viewRows['time_string']

Hopefully that should do the trick.

dc

[neophyte]

dc -

Perfect, works like a charm now.

What is happening when you put the "as date_string" and "as time_string"? Is the function now creating a new array key called "date_string" and "time_string" instead of the real field names of postDate and postTime?

Neophyte

[dcrombie]

When you select "date_format(postDate)" you're not selecting the actual column called "postDate". That's why you get an 'anonymous' column.

'AS <colname>' is used to assign a name to that column, although the actual 'AS' is optional ;)

[dreamcatcher]

Thanks dcrombie.

Glad it worked neophyte. For more info on the various date syntaxes, check out the MySQL website:

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html [dev.mysql.com]

dc