Fetch last records added from multiple tables; Mysql & PHP.

[musnoure]

...I have multiple categories; auto, family, business &finance... and I set up one table for each category in Mysql. I'd like to retrieve the last two records added. ("SELECT * from table_name ORDER BY id DESC LIMIT 3");

This works but only for one table. What if I added new articles in different tables, let's say I added one article in *family*one in *auto* and another in *pets*.

 

Any help is appreciated, thank you!

[MarPlo]

Hy,

Try with Join, or Left Join:

SELECT * FROM `table1` LEFT JOIN `table2` ON `table2`.`id`>0 DESC LIMIT 2

[musnoure]

Hi, thanks for the reply!

I tried that but it did not work, ("SELECT * from `pets` LEFT JOIN `auto` ON `family`.`id`>0 DESC LIMIT 2");

 

--> error message: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in...

[musnoure]

What I really want to achieve is getting the last row inserted from each table.

[phpSensei]

Salam

 

I am going to comment on this post

 

What I really want to achieve is getting the last row inserted from each table.

 

It is squandering to create a new table for each category, however if the circumstances calls for it, I don't object. I don't know if there is a special statement in Mysql for this, but I do know if you are inserting something in your table, you can call mysql_insert_id() on the same page to get the id of the data you just inserted into your table.

 

edit: I thought you wanted to grab the last 2 rows. sorry, however read this post, i suggested a function.

[MarPlo]

Hy,

Here's an example that gets the las inserted id (I use MySQLi):

<?php
// connect to the "tests" database
$conn = new mysqli('localhost', 'root', 'pass', 'tests');

// check connection
if (mysqli_connect_errno()) {
  exit('Connect failed: '. mysqli_connect_error());
}

// sql query for INSERT INTO users
$sql = "INSERT INTO `users` (`name`, `pass`, `email`)
VALUES ('PloMar ', 'love_light', 'a_name@domain.net')"; 

// Performs the $sql query and get the last insert auto ID
if ($conn->query($sql) === TRUE) {
  echo 'The auto ID is: '. $conn->insert_id;
}
else {
echo 'Error: '. $conn->error;
}

$conn->close();
?>

[musnoure]

what if you have multiple tables?