The Little Guy
-
Posts
6,675 -
Joined
-
Last visited
Posts posted by The Little Guy
-
-
it is for my library, and some people may want to know the current pointer position, so I want to add it to my library.
-
Is there a way to get the mysql's internal pointer position? I have searched Google, found this:
<?php function mysql_pointer_position($result_set) { $num_rows = mysql_num_rows($result_set); $i = 0; while($result = mysql_fetch_array($result_set)) { $i++; } $pointer_position = $num_rows - $i; //Return pointer to original position if($pointer_position <= $num_rows - 1) { mysql_data_seek($result_set, $pointer_position); } return $pointer_position; } ?>
But I don't really like it. Is there a better way to find the current position of the internal pointer?
-
There really isn't. The good news is, the page doesn't get accessed much, and most members don't have that many people in their down-line. The page that returns 100k results only runs 20 queries and each one takes less than a second to return results. It builds the data in about 7 seconds, and then takes the browser 30ish seconds to download the data and render it. My code builds a 17MB javascript file for that member.
-
NOW()
in MySQL Help
I'm sorry, but which one then supports the invalid dates?
-
is there any reason you need everything?
If you could select less items, you might be able to remove that file sort
-
Your problem is that you should add a where clause, using datetime.
http://venublog.com/2007/11/29/mysql-how-to-avoid-filesort/
So, the conclusion:
- You can avoid the filesort by making order by column appear in the where clause
- When using join, make sure the left side join table column is used in the ORDER BY clause or change the join type
-
I don't know if you are doing this or not, but what you really want to do when doing a join, is start with your "main" table and join onto that. Another thing; I see your doing this, but try to remove your having, its like a second query it looks like you can get the same results if it is in your where clause instead.
Having is usually only good if you get results back that are not stored in the table. for example:
select member_id, sum(amount) as amt, count(*) as total from purchases group by member_id having total > 4 and amt = 1000
Next try indexing both of the "constant" columns if they are not.
-
Run this and post your results:
EXPLAIN SELECT translations.translation AS translation, translations.translation_id AS translation_id, originals.translation AS original, originals.translation_id AS original_id, translations.locked FROM test2_common_translations translations JOIN test2_common_translations originals ON BINARY translations.constant = BINARY originals.constant WHERE translations.lang_id = 1 AND originals.lang_id = 3 HAVING translation LIKE "%test%" OR original LIKE "%test%" ORDER BY original DESC LIMIT 0, 20
-
my members complained because they couldn't see their down-line, and their is a range from 1-100k members in someones down-line and they all complained because they couldn't see everyone in it. So we gave them the ability boom! 100k rows! you can then use javascript to filter what your looking for almost instantly by searching for either "member name" or by "member id number"!
-
I have put 100k+ rows into a javascript array, so what is wrong with displaying 30k rows... If your table is optimized it should run fast. My page takes longer to render the page than it does to select the rows. We then use the javascript to search the items in the array, which searches even faster the the mysql database.
The only problem we have is that our template system doesn't work well with it because the template system is very shitty.
-
I think your looking for this:
select * from table_name where date_format(from_unixtime(timestamp_col), '%m-%d') = '08-12';
-
NOW()
in MySQL Help
fenway, is that the same as this?
SELECT id FROM idtrack WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 30 SECOND);
if so, which is faster?
-
-
you might need to get a data recovery tool:
http://www.google.com/search?sourceid=chrome&ie=UTF-8&q=data+recovery+tool
I have never used one, so you will have to find one that works for you
-
This will display each table on the page:
$sql = mysql_query("show tables"); while($row = mysql_fetch_array($sql)){ echo $row[0]."<br>"; }
-
<?php $user_id = (int)$_SESSION['user_id']; $sql = mysql_query("select * from myimg where loginid = $user_id"); while($row = mysql_fetch_assoc($sql)){ $image = $row['link']; echo "<img src='$image' />"; } ?>
Why are you storing the entire url of a local file? What will you do if the domain name changes, or the image path? You will now have to do some fancy regex to fix it.
I feel you should just store the file name.
What I usually do is store the file name in one column and the extension in a second column. But I don't recommend storing the whole file path.
-
you need to assign a variable to mysql_query, not the string.
Pass that to variable mysql_fetch_assoc instead.
-
You usually want to beak up the data as much as possible, this usually creates faster queries. You can then join tables in select statements to make a final table.
Here are two example tables, the data could be in one but that could slow other things down:
members:
member_id
email
password
member_info:
member_info_id
member_id
first_name
last_name
display_name
age
notice that the two tables both have a column that stores the same value, we will use that to join the two tables together, like so:
select * from members m left join member_info mi using(member_id) where member_id = 123 limit 1;
Your output then might look something like this:
member_id : email : password : member_info_id : first_name : last_name : display_name : age 123 : me@site.com : 324gff8fj48gfj : 1234 : Joe : Awesome : Super_Fly : 15
Breaking up data will help speed up queries, because you don't always want all the data from one table, and just because you use column names instead of star won't always speed it up, it still has to look through those columns even if it doesn't use it. When I say look through those columns I mean it has to find the start and end of the column just to ignore it. So if you don't even have those items in the table it can search even faster.
The best thing to do is:
http://en.wikipedia.org/wiki/Database_normalization
That is the process of making your tables so you will have the least amount of redundant data as possible. This makes for smaller tables and faster queries. Small tables with good indexes can search over 1 billion records in less than 1 second or even less than 1/2 a second or faster. If you want to know where I got those numbers, my company has tables like that, with over 1 billion records, and when I do a query I can search one of them and get results in about 0.02 seconds.
I watched a video from Facebook, and they were talking about their table structure, and they use key, value tables meaning that they have only a few columns in each table kinda like a php variable 1 key one value.
Another thing I would like to mention, is don't use comma separated values in a MySQL table when you need to search for certain things and it is in a MySQL comma separated list it will be as hard as hell to find anything. As I mentioned above MySQL can hold over 1 billion records and still query amazingly fast, so don't think that because you have a lot of records MySQL will get slower, that usually isn't the case.
Hope this helps, I know I wrote more than what you asked for but too much info never hurt anyone right?
-
I have created the following code to do this task, it can be found here:
http://phpsnips.com/snippet.php?id=62
Note: You will need to place the code in the domain, NOT in the sub-domain.
-
fenway's method:
select Date, TempMax from (SELECT Date, TempMax FROM wx_daily LIMIT 10) as temps order by temps.Date desc limit 1
-
That won't get the newest record from the returned result set, that will get the newest record from the table.
-
of course I did... just not all of it
-
./ means the current directory from which php is executing from.
So, if this script is in the root directory of the web server "A", "B", "C" etc would be child directories according to your glob.
Also, what happened when my code ran? no results? if that is the case, your glob path is wrong.
-
Get one of these bad boys!
It can hold a large hard drive and lots of ram!
you could also get bluetooth:
htmlentites
in PHP Coding Help
Posted
is there a function similar to htmlentities(), that I can pass anything into and it will do what htmlentites does, BUT if passing the returned result from htmlentites back into it it will not convert that.
Kinda confusing, I know, so here is an example:
Here is the result if I pass html to htmlentities 1 time:
Here is the result if I pass the above to htmlentities again:
What I would like is this:
Passed to somefunction 1 time:
Passed to somefunction the second time:
As you can see the result didn't change, like it did in the first example. Any way for me to get my second example two work?
Thanks!