Grow your CSS skills. Land your dream job.

quick mysql query

  • # November 27, 2009 at 3:45 pm

    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

    # November 27, 2009 at 6:22 pm

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

    Many Thanks
    Chris

    # November 27, 2009 at 7:02 pm

    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) ?

    # November 28, 2009 at 7:15 am

    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

    # November 28, 2009 at 1:31 pm

    Hmmm Okay.

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

    Try this:

    Code:
    < ?
    /*CONNECT TO DB HERE... then*/

    $lastid = mysql_insert_id();

    echo $lastid;
    ?>

    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…

    # November 29, 2009 at 6:32 am

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

    Many Thanks
    Chris

    # November 29, 2009 at 6:34 am

    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?

    # November 29, 2009 at 1:29 pm

    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

    … ?

    Rob
    # November 29, 2009 at 1:34 pm

    You could do it with

    query("SELECT LAST_INSERT_ID()");

    obviously attach the value to a var, then you have the ID number of the last inserted row, without worrying about too much searching. If you have the ID you can SELECT the data from that row.

    # November 29, 2009 at 4:38 pm

    Cool Rob.

    Do you think you could you do something likethis?:

    Code:
    query( “SELECT * FROM table WHERE ID = (SELECT LAST_INSERT_ID())” );
    Rob
    # November 30, 2009 at 4:12 am
    "AshtonSanders" wrote:
    Cool Rob.

    Do you think you could you do something likethis?:

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

    Yea I suppose so – I cant see why it wouldnt work…

    # November 30, 2009 at 11:55 am

    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

    # November 30, 2009 at 12:07 pm

    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.

    Rob
    # November 30, 2009 at 2:10 pm

    Well it will be fast coz it wont have to look for anything, because its just going to the last inserted ID… It knows where to go…

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic.

*May or may not contain any actual "CSS" or "Tricks".