I have this
`$categoryresult = mysql_query(“select * from categories where EXISTS (select * from site where site.category=categories.id and site.state=‘Public’ limit 1) and categories.cid=‘$catid’ order by views DESC limit $start $totalcatshow”);
this works very slow , is there any faster method to check if something exist in another table before showing results in first table.
Thank you
`
A simple JOIN should get you the results you desire without using the EXISTS
$categoryresult = mysql_query("SELECT categories.*
FROM categories
JOIN site ON site.category=categories.id AND site.state='Public'
WHERE categories.cid='$catid'
ORDER BY views DESC
LIMIT $start $totalcatshow");
Notes:
I’m no expert on database performance, but you should probably be using a JOIN rather than a query within a query, but Someone else can give you the details on that.
My concern, and the first thing you need to change is this:-
mysql_query()
Mysql is now obsolete, you should not be writing scripts using it. Use PDO or mysqli.
Edit Dave has the JOIN for you.
Thank you,
yes I know that, but the base of the script(huge script) still uses the old mysql etc, so that everything is connected with each other the old way etc, Once everything is ready, I will start changing it to mysqli.
JOIN “SELECT DISTINCT” WORKS, It’s much faster;)
Thanks DaveMaxwell, SamA74
You better get working on fixing it then - notice was given that it was to be removed way back in July 2013 and the interface was removed in December 2015. The only supported version of PHP that still supports it is PHP 5.6 which doesn’t have long to go now (3 months) before it will only be supported for security patches.
Well not really. It depends. Are you responsible for maintaining this code base? If so than you should start migrating to mysqli or PDO. However, if a client hired you to do a specific job than it isn’t your responsibility to fix. Perhaps make the client aware of the issue and leave it at that. The way most people will likely see it is it isn’t a priority until upgrading to a version of php that deprecates and/or removes the functions. So if your client just sits on their current version than it isn’t really an issue.
But mysql_ has been DEPRECATED for many many years now.
I see 2 potential problems with taking the opportunity to update the code while it’s being worked on.
Checking the documentation just now, I was surprised to notice that mysqli_ is the “preferred option” over PDO
mysqli was introduced in July 2004 (as was PDO later the same month).
PHP 4 end of life was 7 August 2008 so since then all supported versions of PHP have supported mysqli and PDO. Anyone still using PHP 4 is using a security hole instead of a programming language - there are certain to have been lots of never to be patched holes in it that have been found by now. There could even be never to be patched security holes that have been found already in PHP 5.4.
Between July 2013 and November 2015 all new versions of PHP labelled the mysql interface as obsolete and soon to be removed. By November 2015 all the versions released that did support mysql without it being obsolete have themselves reached end of life.
New versions of PHP released since December 2015 do not support the mysql interface at all as it has now passed its remove by date.
The only versions of PHP that are still supported have the mysql interface as obsolete or don’t support it at all. Those that have it flagged as obsolete are close to their end of life and will themselves be gone before very much longer.
At the very least you heed to make the client aweare that their code is long obsolete and unsupported and that you accept no responsibility for any security issues relating to their antiquated unsupported code.
History lesson aside I agree with felgall.
well, mysqli is specialised for MySQL, so it seems obvious that MySQL would recommend it over the general-purpose PDO extension.
Not wanting to drag things off-topic too much further, surely another potential problem is “employer or manager won’t allow the extra time to convert a currently-working product to use new libraries and will worry about it later”. I’ve been in situations where trying to get the higher-ups to accept that we have to convert things to new versions of languages or platforms is very difficult indeed. It’s not always down to the programmer to spend the extra time, much as they might like to, or see how sensible it is. And it’s not necessarily because the higher-ups are stupid or anything like it, they may well have knowledge of other matters that us lowly programmers don’t.
So while we’re all agreed that converting away from the old functions is A Good Thing, it’s not always down to the people just posting the questions on here.
There’s no need for so much urgency in replacing the old mysql extension. If a legacy system has grown large and has had no modular architecture then switching to a different extension is a lot of work and there are often more urgent things in a company. There are still a couple of options: stay with PHP 5.x, install mysql extension manually in PHP 7 (it is possible) or include a shim that will emulate mysql functions (those exist, too).
I’m wondering - why every mention of a person using the mysql extension almost always turns into several pages long and off-topic sermons why it should be abandoned now? A short reminder is enough, I think 
It’s often hard to tell whether someone posting on here is a new developer or not. If they’re new to PHP but learning from old sources it’s a worthwhile comment for them to not travel further down that road. But it can sometimes take over.
If you’re not the one who makes descisions about the codebase you need to impress on the person that does that as soon as the version of PHP that’s being used on the server gets upgraded to PHP 7 the whole script will break.
When you say it’s a “huge script”, what do you mean?
The server has many PHP versions supported, recent active are 5.6 to 7, as long as I am using one of them(older ones like PHP 5.4 etc), they will stay there, I like this approach.
It’s more easier to change entire script at once(once finished) than to start programming a new code with mysqli, that’s my idea of doing things. After-all I need to fix/update/write current and new coding (maybe some parts of it, I can’t actually start using mysqli in one part of the script, that will be changed later anyways to mysqli, it’s extra work), mysqli is the last job I should do right now.
Thank you
How much control do you have over what PHP versions are available on the server?
No vendor is just going to change the version of php from 5.4 to seven. They might offer seven on servers not not just replacing a working server with a new major version of php. It’s all a cost associated with upgrading. If you don’t need to do that than its all fine. Upgrading infrastructure can be a hard sell because it isn’t visual and the best upgrades should essentially go unnoticed. Depending on the scale of the website that can be a real hard sell.
Having multiple versions of PHP installed on the same server is trivial. The costs of making PHP 7 available is therefore close to zero.The cost for upgrading is actually borne by the hosting user as they are the one who needs to spend time retesting all their code to make sure it works on the new version and that will likely take a lot longer than the few minutes required to add another version of PHP to the list of available versions.
The shared hosting I use offers 7, 5.6, 5.5, 5.4, 5.3, 5.2, 5.1 and 4.4 with 5.5 set as the current default. So those with really antiquated PHP can switch back to using a long dead version such as 5.4 or 4.4 while those wanting to use a currently supported version and who have kept their code up to date can switch to 5.6 or 7.
That antiquated versions are still offered is a bit worrying though as it means I am dependent on the hosting provider having sufficient barriers between the different accounts so that when sites running 5.4 have a security hole exploited that it can’t spread to other accounts.
I personally would steer well clear of any host that offered any version of PHP older then 5.5. I can’t think of any valid reason why any site would use a version of PHP older then 5.5 provided whoever is responsible for the coding of a given site has kept its codebase up to date
Only three months to go before 5.5 joins the rest of the completely dead versions.,
I can’t control what versions my current provider offers (without changing hosts) but at least they ,have made 5.5 the default and also offer the still supported versions. Hopefully the only people who have switched back to older versions are those with a really good reason (also the location of the option isn’t exactly obvious so most probably use the default 5.5,