Where is this variable going wrong?

Anon

The following is the feature stripped version of a script I'm working on. It's simple to pick up on the idea, but what it should do is print out the correct article maching the id number if one is present on the url. That part works fine.

The else statement should cause the script to print out the latest article if no id is present on the url. This is done by doing a SELECT COUNT(*), then using that number as the basis for the $result select. For whatever reason though the $total_rows variable is being set to 2, when in fact it should be 18. Where is this messed up?

if ($id) {

$result = mysql_query("SELECT * FROM journal WHERE id=$id",$db);
$myrow = mysql_fetch_array($result);

printf("%s ", $myrow["title"]);
printf("%s ", $myrow["posted"]);
printf("%s ", $myrow["playing"]);
printf("%s ", $myrow["journal"]);

} else {

$total_rows = mysql_query("SELECT COUNT() FROM journal",$db);
$result = mysql_query("SELECT FROM journal WHERE id=$total_rows",$db);
$myrow = mysql_fetch_array($result);

printf("%s ", $myrow["title"]);
printf("%s ", $myrow["posted"]);
printf("%s ", $myrow["playing"]);
printf("%s ", $myrow["journal"]);

}

Anon

COUNT(*) seems to be a "GROUP BY" toy and doesn't work as I expected either!!

Try something like
"select * from journal order by posted DESC limit 1"

to get just one row containing the last item entered (postulating that "posted" is a timestamp field (INT))

Anon

Currently the posted field is just a php formatted date & time (yes yes, that sucks. I'm working on moving it to unix timestamp). Instead I tried ordering it by the id field, which is done with auto_increment, but the problem of $total_rows being set to a value of 2 still persists. I have no idea where this value of 2 is coming from. As a small test I put in a bit of code to print both the article id and the value of $total rows, both in the if block and the else block.

If no id is present on the url, both the article id and $total_rows is returned with a value of 2. If an id is present on the url, 18 for example since it's the latest, the id is returned as 18 but the value is also 2 there as well.

Anon

I added a mysql_num_rows count and that's fixed things up so it works now. Your suggestion got me going in the right direction, but I still say this should be something one variable should solve, not me having to create two; one to get the latest row and the other to count it.

Anon

I'd ignore the COUNT(*) stuff :⁾

your auto_inc field is perfect - select just the last entry based on that (no point in losing hair over more elegant ways to get the most recent post!!

I suppose MAX(id) might work as well if you have time to burn ....

Anon

Scott,

Try changing this:

$total_rows = mysql_query("SELECT COUNT() FROM journal",$db);
$result = mysql_query("SELECT FROM journal WHERE id=$total_rows",$db);
$myrow = mysql_fetch_array($result);

to this:

$max = mysql_query("SELECT MAX(id) FROM journal",$db);
$id = mysql_result($max, 0, 'MAX(id)');
$result = mysql_query("SELECT * FROM journal WHERE id=$id",$db);
$myrow = mysql_fetch_array($result);

assuming that the name of your id field is 'id'. The reason why you are getting a value of 2 is that 2 is the resource id # returned by the database. You need to use the mysql_result function to return the actual value from the database. Hope this helps!!

Geoff
www.mithril.com

Anon

I like this solution much better than my use of mysql_num_rows simply because it looks like it was designed for exactly this purpose. It would have been nice though had the count(*) worked like I had expected.

Anon

Hmmmm...... I can't see where mysql_num_rows() is used in your example, but count(*) could also be made to work. The only reason it didn't work in your sample was because you hadn't passed the resource id (result identifier) in to the mysql_result() function. Had you done so, it would have worked just as well, assuming that the id is an auto-increment field. The reason I used max(id) was in case your id numbering system wasn't incremental.

Cheers,

Geoff
www.mithril.com