vinny42
-
Posts
411 -
Joined
-
Last visited
-
Days Won
3
Posts posted by vinny42
-
-
Did you have any indexes when you tried the JOIN solutions? Because that would make your previous popst irrelevant.
-
Hmm, it seems that the join solution does indeed not work too well here. Looking at the explain paths it's logical because the join has to basically create a cartesian productand flatten that out to get the counts.
Of course I'm going to annoy you by saying that PostgreSQL's windowing functions did the trick on 300.000 results in 0.4 seconds on an old 2GB dualcore laptop. :-)
-
On a table with 180,000+ rows both the dependent subquery and left join versions timed out. The version with the SQL variables took < 0.9 seconds.
What was the test setup?
-
You could if you want a highly inefficient solution. That solution uses a dependent subquery where every row has to run another query on the table.
True, if you have a few hundredthousand rows to examine you'd probably want to do it differently, using a left join and a count and a group-by;
SELECT scores.id, COUNT(betterscores.score)+1FROM scoresLEFT JOIN scores AS betterscores ON betterscores.score < scores.scoreGROUP BY scores.idORDER BY 2 ASC -
And, you've forgotten SELECT statements have an ORDER BY clause. So, what's your point? :]
I haven't forgotten anything, I've thought about what the actual problem is and the solution does not require an array, a loop, not even an ORDER BY clause.
All the OP wants is to know how many records come before a particular record. That's one single query that everybody here should be able to work out.
Actually, this problem is almost identical to this one: http://forums.phpfreaks.com/topic/283517-php-associative-array/?do=findComment&comment=1456585
-
Or you could just select the number of better scores per score:
SELECT scores.*, (SELECT COUNT(*)+1 FROM scores AS betterscores WHERE betterscores.score < scores.score) AS rankFROM scoresORDER BY rank;or if your database supports windowing, just ask it for the ranking:SELECTid,score,RANK() OVER (order by score ASC)FROM scores -
If there is a tie at number 2, the 3rd position is skipped
Does this data come from a database perhaps? Because databased have functions to give you the ranking, no need for messing about with PHP.
-
The code doesn't do any recursive scanning, though.
I'll need to find a workaround for that.You may want to read the original question; he's not looking far a value "anywhere in the array", he's forgotten that queries have a WHERE clause. :-)
-
so I'll get rid of these messages after the release candidates and maybe one day I'll have the patience to write an error logger and reviewer.
Removing messages before going live is a bad idea, experience shows that you never remove all the messages and once you have removed them, you have no way of debugging them because you have no messages.
Write a prioper logger *now* and save yourself a lot of problems.
It should not be much work anyway because if you have designed your code properly you should have a single function or class that executes queries and that's the only place where the logging needs to be implemented.
Even if you have spammed your code with mysqli_* statements you can just rename them to a custom function that does the logging and otherwise behaves like the mysqli_* functions.
-
Do you log all the errors separately and review them periodically?
You log everything. e-ve-ry-thing. We're talking errors here, unexpected behaviour, so the more information you have, the more likely it is that you can solve the problem.
Seems like a lot of work.
And that's why we don't do that :-)
The only thing users need to know is "do I call IT for this, or did I do something wrong myself?" A nice clear and short errormessage is often helpfull, if you are nice to your users they will look at the message and remember it, but anything beyond five words is simply forgotten if it's not clearly a user-error.
And actually, even in those cases - you have to review the errors and you're likely to read them in a browser or a mail client, both of which would be vulnerable to XSS. So even if I don't show the user the message, should I not always do the HTML escape on it?
I don't send logs as HTML emails and I don't think mailclients even do scripting in the first place (?), and I view the logs through SSH. If you do want to view it in HTML then I +1 on DavidAM; escape before you print, but log the original.
-
However, if the query contains HTML markup being written to the database, and the query failed, and you don't escape it using htmlspecialchars or htmlentities, then it could be difficult to read and interpret the error message. A
If you are viewing the error in a browser, yes. But we all agree that this does not happen in a professional environment :-)
-
The index is part of the record, and it's called asort().
Don't be a smartass when you're making a classic beginners mistake.
-
If you need to get specific record, why not just query the database for which record matches id of 4?
+234
The database can search for records *much* faster than PHP can.
unset id if you don't want it to be part of the array, we can do this since we'll have the indices set to the ID
The ID is part of the record and should never be separated from it. If you remove it and you decide to sort the array, chances are that the key's will point to different rows and you're done.
-
I think that there also needs to be done some recoding in the script, right?
Of course :-) You cannot just replace a string with an array and expect it to work.
You will have to loop through the array and add each word to the query.
And you newed to do something about your datamodel, right now you have keyword1, keyword2, keyword3, that's very inefficient and slow to work with, it's better to create a separate table that holds one record per keyword. That will also make this query a lot easier to do because you only have to search one column, instead of three or four (the keyword can occur in all colums so you have to search for each keyword three or four times... icky!)
-
You don't have to escape the error because you never display the error. The errormessage exists *only* for you, the programmer, so you can find out what went wrong. You never, ever, echo it to the browser. Never.
-
If you do it clientside you have the overhead of always downloading the original large image
You generaly don't resize on-demand anyway.
-
What's wrong with the quality you are getting?
-
My first question would be: what are you working on; why do you now know the names of the tables?
The code you posted makes no sense, did you read the PHP manual section about working with mssql?
-
...and I wanted to format my url to look like mysite.com/this-is-my-title
Which would be the only right way to do it. Allmost.
The reason why the ID is often spammed into the URL is because of a common database design fault: the surrogate primary key.
Instead of making a proper primary key that contains all that makes the record unique, they just spam an ID kolumn in the table and start using that.
A proper PK would be for example the title itself, but titles are usually re-used for multiple articles so a date or even a datetime would be a good addition. Serialnumbers are not.
News sites and even shops will have URL's like; www.news.com/Programming/PHP/2013/10/30/Breaking_news_in_the_morning.html
The categories, date and the title are the PK, no messy ID's required, the entire URL is pertinent to the article; it's an article in the PHP subcategory of Programming, it was posted on the 30th of the 10th month of 2013 and it's called "Breaking news in the morning". The only problem you can habe now is that two articles cannot have the same name during the same day, which would be confusing to the readers anyway.
The URL is also choppable; if you remove the title the website can list all articles of that day, if you also chop the day it can list the articles of that month, etc.
If you want to go this route then add an extra column in your table that holds the url for the post.
That's not required, and if you do do that, make sure the URL is filled automatically from the title, and use it as a cache. Never let humans write URL's because they will come up with things like "Breaking news: are we all going on holiday?? Say Smith & Clarkson" And boom goes your website.
-
What is if ($_FILES)
What it is, is "wrong" :-)
It tries to see if files were uploaded but it doesn't actually do that, it checks if the $_FILES array can be transformed to a boolean and if *that* results in "true".
This means that it wil return true whenever $_FILES contains somthing other than NULL or an empty array. It does *not* mean itwill return true only if files were actually uploaded.
So if your script contains a bug that assigns a value to $_FILES, perhaps something like "if ($_FILES['foo'[]='bar')" where you meanty to do "if ($_FILES['foo'[]=='bar')" then this statement will happily claim that files were uploaded.
So, always check for the actual data you need, never assume that PHP is magic and that converting anything to boolean will just make it work.
-
Don't do this. Not only is it a waste of resources, it also prevents the database from using indexes, so the query becomes as slow as it can possibly get.
A simple alternative is to create a separate table that holds just the searchable data. Then you can prepare that data once, store it in the new table, and search the new table using a simple "=" or even a LIKE or FULLTEXT. A small trigger can keep the searchtable uptodate when the original table is inserted/deleted or updated upon.
An added bonus of this method is that you can also separately store each word of the text and find texts that do not have an exact match, by counting the number of words that do match.
And although some people on this forum will hate me for it, I'm still going to say that in other databases you'd solve this problem using a functional index; an index built on an expression such as the triple-replace mentioned earlier, saving you the trouble of creating the separate table.
-
Just one more thing; executing a query can also fail and should also throw an exception. Queries can be eecuted anywhere in the code, so it's common practice to put the entire script inside one big try block, so that all exceptions (if they are not caught by some deeper nested try/catch) jump to the outer-most catch.
So quite literally this:
try
{
// do *EVERYTHING* here.
}
catch(your_database_exception $e)
{
// Catch your custom database exceptions here
}catch(Exception $e)
{
// Catch regular exceptions tht have not been caught yet
}
-
Uhm... yeah, in a way, that's what I meant. :-)
You said that I shouldn't send myself an e-mail if there's a MySQL error because, if my website's popular, I'll receive 1,000s of e-mails. Can't I prevent this by simply setting ignore_repeated_errors in my php.ini file to On?
No, firstly because that setting is for PHP's log function, which you are not using, and secondly because you don't want to completely ignore repeating errors, you just don't want to get separate emails for all of them.
-
Well, the major points still stand: don't die, don't redirect.
This die() business is only even remotely possible during the initial connection. You simply cannot die() on a query failure because that will completely mess up your page and if you are not extremely carefull; it will mess up your data too.
As for the redirect, stop doing that. It just adds a completely useless page into the user's browsing history and when they click "back" to back to the page before they got the error, they actually end up on the page that has the error, and are redirected back to the error message.
So again; throw an exception, catch it and print an error. no die(), no redirect, just one script that handles everything.
PHP associative array
in PHP Coding Help
Posted
Right... well, what can I say, except. Goodbye.
I'm done here, I'll leave you guys along in your happy little bubbles... I'm off to find intelligent life...