Best way to fetch a couple of columns from last (most recent) row?

[tfburges]

Is this possible to do without using the combination of mysql_num_rows and mysql_result?

[discomatt]

That depends on your database structure.

 

If you're using an auto increment column

 

SELECT `col1`, `col2` FROM `table` ORDER BY `id` DESC LIMIT 1

[rhodesa]

replace column5 and column6 with whatever columns you want, tablename with the name of the table, and id_column with your primary key if it's an auto increment field (if you don't have an auto increment, then please describe the fields in the table)

<?php
  $result = mysql_query("SELECT column5,column6 FROM tablename ORDER BY id_column DESC LIMIT 1")
    or die("Error: ".mysql_error());
  $row = mysql_fetch_assoc($result);
  print_r($row);
?>

 

the above will fetch the last row from the table

[tfburges]

I don't have it auto incremented but I'll do that.  I have a feeling it will make a lot of other things easier in the future as well.

 

Thanks!

[rhodesa]

you could also sort by a date column if you have that (or just add an auto_increment column)

[revraz]

How do you assign unique ID's now?

 

I don't have it auto incremented but I'll do that.  I have a feeling it will make a lot of other things easier in the future as well.

 

Thanks!

[discomatt]

Your best bet is to move all the data to a temp table, recreate the original table with an auto_increment, and re-add all the data to the new table.

[tfburges]

How do you assign unique ID's now?

There are a number of ways to do this, I'm sure.  The ALTER TABLE command MIGHT work; I'm not sure.

http://dev.mysql.com/doc/en/ALTER_TABLE.html

 

Then comes the CREATE TABLE command and its parameters (you probably want to use LIKE), although I'm not 100% sure if this method will work.

http://dev.mysql.com/doc/refman/5.0/en/create-table.html

 

Since I'm just in the developing stages of my design, I can simply drop the table and recreate it with the id column included.  Loss of data does not matter in my case.

 

If you want to use the exact same table as originally named, you could also do it (for sure) by first creating a temporary table from the original table...

CREATE TEMPORARY TABLE temptbl SELECT * FROM originaltbl;

...then drop the original table...

DROP TABLE originaltbl;

...then recreate the original table with the id column plus all of the old data that you stored in the temporary table...

CREATE TABLE originaltbl (id BIGINT auto_increment, col1 varchar(30 or whatever), etc (original table column layout) ..., PRIMARY KEY (id));
INSERT INTO originaltbl (col1,etc) VALUES SELECT * FROM temptbl;

I think the syntax is correct on that last step but you might want to look that up too.

http://dev.mysql.com/doc/refman/5.0/en/insert.html

 

 

 

Hehe discomatt beat me to it while I was typing!

[revraz]

I didn't ask how you CAN assign them, I asked how you ARE assigning them now.  So I take it from your reply, you are not.

 

How do you assign unique ID's now?

There are a number of ways to do this, I'm sure.  The ALTER TABLE command MIGHT work; I'm not sure.

http://dev.mysql.com/doc/en/ALTER_TABLE.html