drath

For posterity sake, here is the final query I used to get this working: WHERE STR_TO_DATE(lastlogin, '%M %D, %Y %l:%i %p') >= DATE_SUB(NOW(), INTERVAL 1 MONTH) I thought I used to be able to mark the thread as solved? Either way, it's SOLVED

Thanks, I was not even aware of STR_TO_DATE(). I will take a look at this and try to come up with something. Thanks!

I would have the date stored in DATETIME/TIMESTAMP as noted if I had a choice

MySQL Version: 5.0.32 Hello there guys, I've been stumped for awhile using DATE_FORMAT in a SELECT that I have been creating. Unfortunately the field is not in a timestamp, or a straightforward datetime format, and there is no ability to edit that, so it looks like it's DATE_FORMAT to the rescue... except it's not working (yet). The field (lastlogin) is formatted as: July 24th, 2010 2:03 PM or in PHP: date('F jS, Y g:i A'). As far as I can tell, the MySQL equivalent of that is %M %D, %Y %l:%i %p. This is my query for trying to select only the times within the last month: $sql = "SELECT snip FROM snip WHERE DATE_FORMAT('lastlogin', '%M %D, %Y %l:%i %p') >= '" . date("F jS, Y g:i A", strtotime("-1 month")) . "' ORDER BY snip DESC LIMIT 20"; Can anybody spot what I may be doing wrong here? It is not returning any error, so I assume it is at least executing, but nothing is showing up, which means I made a formatting mistake possible? Thanks! *EDIT* The PHP portion in there is confirmed working, it returns a date of the previous month in the proper format, "September 19th, 2011 6:39 PM" for example at the time of this post.

I'm working on a fairly ambitious project and have a HUGE set of array values to store, edit, and call from the database. This is a general question in regards to the best way to do this. To give you an example of the size: $tile[-33][423] = "green" $tile[-99][-300] = "green" $tile[1000][30] = "brown" $tile[9][-1] = "red" $tile[10939][-2] = "brown" and so on... There will possible be millions of variable array combinations here. I was first thinking about serializing the array to store in a single field on the database. But the problem comes in when I want to edit a single tile variable, I would have to grab all the data (I think?), change one thing, put it all back - that would be pretty resource intensive, especially when I would need to make these edit calls 10 times a minute for example. So that leaves me to the only other option I can think of, which is doing a column and row approach: x y tile 100 5000 red -211 7120 green 455 2 blue and so on... But then I thought, what is the performance going to be like if go to import 5000 variables into the database in one fell swoop? Or what about when I need to grab the data from 5000 of them to output? Maybe it won't be so bad? The SELECT, INSERT functions for these 5000 variables won't be happening near as much as the single UPDATE calls, but they could still be fairly frequent - perhaps once every 10 minutes for the INSERT, once every 2 minutes for the SELECT. Hopefully that made some sense. Also, am I going to be running into any PHP/MySQL limitations here on int values, or array limits?

Whether I was using IDs of fakeIDs as a column, I still need a way to shift them around, assuming I am using a numerical value. It wouldn't be a problem if there was 10 rows; however, with 10,000 it becomes more of an issue.

Okay, as I understand it, compressing the IDs is a worthless exercise... that's fine; however, in regards to my second question. I am using IDs as a sort method for new/old and I really need to shift some around.

I really had no idea what to call this... hopefully it works. These two questions are more logical than technical; however, feel free to put code in too. The first questions is about Primary IDs (keys) - the first column in my database which stores a number. My issue is the database has grown quite huge... we are getting up to 6 numbers. This normally couldn't be helped... except that many, many items have been deleted; however, with the way the auto-increment works, it goes after the last number instead of the next available. Is there an easy way to compress this down? I was generally just thinking of making a loop that would just assign new IDs in order; however, would that even work, or would it just keep adding new large numbers based on the auto-increment value? The second question is about inserting new rows in between IDs that already exist. I wish I could insert a row with the same ID as something already in the database and it would just shift everything automatically. Unfortunately, I don't think that's possible. What is the best way to do this? For example, I want to insert: 3 orange Into: 1 apple 2 banana 3 peach 4 grapes 5 cherry 6 purplezebra Thanks!

I had no idea what to call the subject line here. I will try to describe as best as possible what I want to do. Here is how I have my table set up: Column 1 | Column 2 1 | apple 2 | orange 3 | banana 4 | pumpkin 5 | pear As you can see, the first column "ID" is a unique number that goes in sequence and order. Now say I wanted to add "3 | kiwi". What is the best way to do this? Meaning that I want "banana" to automatically change its ID to 4, as well as the rest after it to increment by 1. In theory, I would just loop through each item after the ID I was trying to add and update their IDs by 1. Is there a better way to do this? Maybe there is a special MYSQL way of doing this automatically? Intended end result: 1 | apple 2 | orange 3 | kiwi 4 | banana 5 | pumpkin 6 | pear

I figured out that I don't need to count if I just set the variable to mysql_num_rows($result) after the query. This seems to of fixed the issue; however, I did eventually get the count(*) working anyways. Thanks for the help on this. The query performance is a full second faster now (even on a small set) without the nested counting queries. My final pseudo code as follows: $setcount = "SELECT * FROM items WHERE gamename = '".$gamename."'"; $haveset = "SELECT DISTINCTROW iv.item_id FROM inventory iv INNER JOIN items i ON i.ID = iv.item_id WHERE iv.user_id = '".$ID."' AND i.gamename = '".$gamename."'"; SOLVED.

Using the count(*) seems to be slowing down the process, in fact, the whole page times out when the query is executed. Is there any alternate ways of counting?

Ken2k7, Sorry, I messed up, I meant to say: echo "User has $haveset out of $set items."; andrewgauger, Thanks for those queries, I will try to incorporate them and get back to you on my success/failure.

Bare with me here, it's going to take some explaining. I am using an INNER JOIN for two separate tables on a single value, but want to to do some counting in between then based on a grouping of a value. I have a working set of nested queries right now, but want to see if I can do it in a single query to improve performance (as including the nesting queries adds a full second on a small set). I will present my working query right now (tried to strip irrelevant items): $ID = 1; $sql = "SELECT iv.ID, iv.item_id, iv.user_id, i.ID, i.gamename FROM inventory iv INNER JOIN items i ON iv.item_id = i.ID WHERE iv.user_id = ".$ID.""; $result = mysql_query($sql) or die(sql_error($sql)); while($row = mysql_fetch_array($result)) { $gamename = $row['gamename']; //First Nested $sql2 = "SELECT ID, gamename FROM items WHERE gamename = '".$gamename."'"; $result2 = mysql_query($sql2) or die(sql_error($sql2)); $setcount = 0; while ($row2 = mysql_fetch_array($result2)) { $itemid = $row2['ID']; //Second Nested $sql3 = "SELECT DISTINCTROW item_id, user_id, auctioned FROM inventory WHERE user_id = '".$ID."' AND item_id = ".$itemid.""; $result3 = mysql_query($sql3) or die(sql_error($sql3)); while ($row3 = mysql_fetch_array($result3)) { $setcount = $setcount + 1; } $haveset = $setcount; } $set = mysql_num_rows($result2); } echo "User has $haveset out of $setcount items."; Basically I am loading all the items a user has in the first query. The first nested query is in the case to get the "gamename" from the main query, then the second nested query is to see how many items that user has from that gamename out of the full set that is actually available. Again, I have stripped a lot of information, but just left the foundation, please let me know if you want my full actual codebase and a working live model of this working to get a better idea.

Ah, yes, I see what you did there. This works perfectly as intended, thanks for the work and teaching me some new tricks.

That seems to throw the "Every derived table must have its own alias" error. I am not sure exactly what it wants me to change. Thanks for that though, I wasn't even aware that you could select within a select... very interesting.