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.

That doesn't seem to be working in my implementation, it doesn't seem like it is getting the "gamename" properly: $sql = "SELECT count(gamename) as theCount FROM highscores ORDER BY theCount DESC LIMIT 2"; $result = mysql_query($sql) or die(sql_error($sql)); while ($row = mysql_fetch_array($result)) { $gamename = $row['gamename']; echo "<div class=\"game\"><a href=\"$gamename.php\"><img src=\"$gamename.gif\"</a>$gamename</div>"; } Shows one empty DIV. Your other method of "GROUP BY" doesn't seem to work either, giving me the error that you can't GROUP BY theCount. I tried to play around with it some more and came up with: $sql = "SELECT gamename, count(gamename) as theCount FROM highscores ORDER BY theCount LIMIT 2"; This one at least displays some items in the DIV, but they seem to be just the two first entries in the column, not the most popular.

I was wondering if this type of thing was possible. Basically I want to be able to order by results by the highest row count of a column of a value. Basically, the most "popular" in a column. To give a better visual: 1 | apple 2 | apple 3 | orange 4 | apple 5 | orange 6 | banana 7 | apple Because apple has the highest row count value, I would want to order result to be: apple, orange, banana using a single (or multiple) PHP MYSQL queries. Here's my pseudo code (that wouldn't anyways, since you can't order by DISTINCT, let alone count a column): SELECT column1, column2 FROM table ORDER BY DISTINCT count(column2)

Users loads flash.html which loads flash.swf, which then loads flashinclude.swf (inside flash.swf itself using the method linked). I don't want users to be able to go to "domain.com/flashinclude.swf" directly.

I want to deny all outside traffic to a specific flash file on my website using .htaccess, except it doesn't seem to work. I think the problem is with flash itself, because I am trying to include the flash file from another flash file using this method: http://www.ilike2flash.com/2009/11/load-external-swf-in-as3.html. Here's what I am doing: <Files flash.swf> order allow,deny deny from all allow from domain.com allow from 127.0.0.1 allow from 55.55.55.55 allow from 55.55.55.0/17 </Files> I've tried allowing from the domain name, localhost, the actual domain IP, and then even the DNS IP, all of them do not allow the file to be included. When I use the statement, the other flash file is not getting loaded and continues to try to "read" from the domain, rather than a normal 404 that would be thrown on a normal file using this method. And yes, it does get loaded properly without the .htaccess statement used above. Maybe there's a PHP solution to get around this .htaccess issue, but still keep the functionality that I need.

I am already using a unique value for the variable (refer to my semi-visual diagram that I added while you posted). Unless you mean set a unique variable name AND variable value... hmmm, would there be any way to get the variable name?

I'm having a rough time sending a variable to three different pages on my site by having the first page (apple.php) send data a long to the next two pages. My original solution of using SESSION to pass the variable a long failed because users could go the first page (apple.php), then go to another version of the first page (orange.php) to spoof the session data and use it for the first version of the first page (apple.php) so it would read as though the variables are coming from (orange.php). Confused yet? The same issue applies to cookies and even writing to a database for that matter. One solution I thought of is to not set the session if the session was already open the variable was set. This failed, because I can't unset the variables until the third page, and sometimes the user stops right at the first page (apple.php or orange.php). I would be locking them out completely? HTTP_REFERER worked as well, because I could directly see if they were coming from orange.php or apple.php - but HTTP_REFERER can be spoofed even easier as well as anti-virus apps block it like crazy so it's useless. Any idea? Hopefully I didn't miss anything or was too confusing. If you need a visual of what I am explaining: orange.php (set session $variable = orange) --> secondpage.php (read $variable) --> thirdpage.php (read $variable, unset session) apple.php (set session $variable = apple) --> secondpage.php (read $variable) --> thirdpage.php (read $variable, unset session)

Wow, that means perfect sense to me now. Thanks a lot. A kind of related question that just came up though: Using that same statement, is it possible to get a DISTINCT using only a certain field? In the case of this query, I want to make sure the a.gamename is DISTINCT. I noticed SELECT DISTINCT does not seem to work because it is taking all three selections to compare. Also, do you think using your query over my original one would increase performance, as in, would it execute faster?

For some reason, I can handle every aspect of MYSQL, except JOIN/GROUP BY/LEFT JOIN - they just completely escape me - all the examples I look at don't seem relevant. So I thought a good way to learn would be using a real example that I would normally need to use one of them. So far, I've been getting away with doing nested statements to get by, but I would like to optimize my site a bit better. So I ask you fine people, how would I optimize the following: As you can see I'm using a nested SQL statement to compare user_id and ID from two different tables rather than using some of the more complex MYSQL statements - simply because I don't know how.

Thank you kindly. I think I got a handle on this stuff now.

I'm trying to change the following: http://www.sitename.com/page.php?id=pagename http://www.sitename.com/page.php?id=otherpagename to: http://www.sitename.com/pagename.php http://www.sitename.com/otherpagename.php I tried the following: RewriteRule ^/(.*)\.php$ /page.php?id=$1 (no effect) RewriteRule ^(.*)\.php$ /page.php?id=$1 (500/404) Anybody see my error(s)?