quick mysql query

[AlCapone]

Hi, i have a table that is going to have a lot or rows, i don’t want to have to search the whole table for a value with a query i want to check what the last row added was.
So each day a new row will be added, but i want a script to check if today’s date is a new row or not so i only want to check the last entry to the table as if its not equal to today’s date then it will create a new row.

Any Ideas?
Many Thanks
Chris

[AshtonSanders]

I’d try something like this:
http://php.net/manual/en/function.mysql-insert-id.php
http://forums.mysql.com/read.php?147,189341,189341

[AlCapone]

Mmmmm, Thanks, do you feel like helping me use them? I cant see how they work =-)

Many Thanks
Chris

[AshtonSanders]

I could try.

I’m assuming you’re using PHP to run the mysql queries?

at what point do you need to know the "last ID" (before or after you run a query) ?

[AlCapone]

Hi, Thanks

Yeah i’m using php.

Ok. so it should go like this,

Connect to DB – Which I can do.
Check what the last row entered to the db was – it gets the last row entered and stores its value .
It then compares that value with one that has just been created by using the date function.
If they are the same everything is fine.
If they are different then it enters a new row in the table – this i should be able to do.

Many Thanks
Chris

[AshtonSanders]

Hmmm Okay.

Well, mysql_insert_id(); should return the ID of the last entry.

Try this:

Code:

Hopefully, tat will return the last… but looking at the function details again, it looks like it may only work on Queries previously run in the same DB connection.

Anyway, let me know if that works…

[AlCapone]

Hi, it returned ‘0’ which has nothing to do with my database.

Many Thanks
Chris

[AlCapone]

Quick question,
If i just run a query to match my variable with one in the db and stop searching when it has found one, which side of the table does it start from?

[AshtonSanders]

Okay, it looks like that’s not going to work then…

"AlCapone" wrote:

Quick question, …

No idea.

But it sounds like the best bet is to just do a query like:

SELECT * FROM table WHERE Date = $today OR Date = $yesterday

… ?

[AshtonSanders]

Cool Rob.

Do you think you could you do something likethis?:

Code:

query( “SELECT * FROM table WHERE ID = (SELECT LAST_INSERT_ID())” );

[AlCapone]

Ok, ill try this thanks – Question though is this query going to search through the entire table? That was the main thing i was after – i only wanted it to check the last entry not the whole table.

Many Thanks
Chris

[AshtonSanders]

Hi Chris,

I don’t know how the MySQL search mechanism works, but I doubt you’ll be able to do better than this. The biggest tax on your SQL server is the amount of information returned; not how much is searched through. And that’s only noticable if you’re returning thousands of rows of info.

SQL also has a memory function where it will remember a similar query.

I have a friend who created a CMS where every tag, attribute and value was stored separately in the database, and the page still loaded as fast as an HTML page… we have yet to test this on a hundred-visits-a-minute site, but you get the idea.

Hope that helps.

[Author]

Posts