Solved

Merge two queries

Posted on 2013-01-12
12
215 Views
Last Modified: 2013-01-17
Hi,

Is it possible to merge these two queries into one?

# Affiliate commission, only pay if 1 day unique
$ip_query = mysql_query("SELECT id FROM archive_views WHERE viewdate > (NOW() - INTERVAL 1 DAY) AND earnings!='0' AND userip='$userip' ") or die(mysql_error());
$ip_count = mysql_num_rows($ip_query);

if($ip_count == 0) {

	# Get earnings
	$ac_query = mysql_query("SELECT rate FROM affiliate_countries WHERE code='$cc' ") or die(mysql_error());
	$ac_fetch = mysql_fetch_array($ac_query);
	$ac_earnings = $ac_fetch['rate'];

}else{

	$ac_earnings = 0;
}

Open in new window

0
Comment
Question by:kgp43
12 Comments
 
LVL 27

Expert Comment

by:yodercm
ID: 38771887
Sorry, but I think it is not possible, because a joint query would have to first compute a number based on all rows in the database, and then do the selection you want based on that number.  This is inherently two queries.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38771995
I do not understand how these queries are related at all.

Table: archive_views
Data: id (unlimited number) - maybe you want a LIMIT clause?

Table: affiliate_countries
Data: rate (unlimited number) - maybe you want a LIMIT clause?

So, my answer would be, "Yes, you may be able to combine these queries."  But I am not sure why you would want to.  Maybe if you can explain the motivation behind the question we can offer better help.

Also, you might want to learn about the mysql_fetch_assoc() function.  It is much more efficient than mysql_fetch_array(), which has to transfer amounts of unused data.  

Going forward, you'll also want to learn about PDO or MySQLi because the MySQL extension is going away very soon.
0
 

Author Comment

by:kgp43
ID: 38772044
I just wanted to get rid of the extra query if it was not needed.

PDO or MySQLi, which one is better? Or is there an use for both?
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 38772089
PDO holds out the promise that you could change data base engines without needing to change the PHP script.  As an experienced programmer, I've heard that kind of horseshit many times before.  In my distant rear-view mirror there is a memory of a programming language called "COBOL" which was a semi-acronym for Common Business Oriented Language.  It was intended to be "self-documenting" and the means to that end was the use of very long variable names.  COBOL was held up as the last programming language anyone would need to know.  And COBOL choked to death on its own crud around the time of the death of the IBM mainframes.  I don't think PDO will last very long, but then, neither will MySQLi and the MySQL extension which has served us well for a decade is dying right now.

So choose whatever you're comfortable with, and use a set of your own classes as an abstraction layer.

With respect to the question of one vs two queries, I would only explore this issue if there was an observable efficiency to be gained.  Adding indexes on the right columns and using LIMIT clauses usually gets you more value than anything else.  And if what you're tuning is timed in milliseconds, then you need thousands of hits per second to make it worth the effort.

That's my $0.02.  YMMV.

Best regards, ~Ray
0
 
LVL 27

Expert Comment

by:yodercm
ID: 38772671
Keep in mind that mysql is currently being used in roughly 250 billion webpages, so I wouldn't worry too much that it will disappear overnight.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38773069
@Cornelia: This is a place where PHP could fall on its own sword, and if  the authors (and current maintainers) of PHP are more eager to be seen as "computer scientists" instead of human beings, they could get everything wrong very quickly.  Arrogance is a path to foolishness and destruction.  Here is my worst-case scenario:  PHP changes its default values; server farms abandon PHP upgrades because clients are screaming; clients look for alternatives.  That is the end of the popularity of PHP, full stop.

I'm a veteran of the collapse of many programming languages.  There is nothing magic or special about PHP.  It will die, too.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 27

Expert Comment

by:yodercm
ID: 38774061
@Ray: I've been in programming since RPG days, and I can tell you from experience that programming languages take about 50 years to collapse.  There is still plenty of COBOL and FORTRAN and APL around.
0
 
LVL 27

Expert Comment

by:yodercm
ID: 38774066
But my original statement stands:   There is no way to combine these queries into one that makes any sense.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38774082
@Cornelia: I take your point.  I think that the rate of technology change is accelerating.  It may take a generation for COBOL to die off, but I think the newer technologies may be more transient.  And even though there may still be COBOL programs still running today, I would not counsel a youngster to study COBOL or PHP3 (nor would I recommend the medical application of leeches :-)
0
 
LVL 27

Expert Comment

by:yodercm
ID: 38774111
I counsel all my web programming students to learn PHP.  It will be around for at least their lifetime.  250 BILLION webpages guarantee it.

By the way, today, doctors use leeches for treating abscesses, painful joints, glaucoma, myasthenia, and to heal venous diseases and thrombosis. Medical leeches are used in plastic surgery, for improving brain circulation and for curing infertility.

If you want to give up the best web programming language in existence, feel free, but don't give such terrible advice to others.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38774148
Well, I am not sure what you think might be "terrible advice," but you're entitled to your opinion.  I stand by the advice to use LIMIT clauses.  I stand by the advice to use mysql_fetch_assoc() instead of mysql_fetch_array().  I recommend that any PHP programmer who is currently using the MySQL extension, learn PDO and/or MySQLi.  I believe that an own-code abstraction layer may be wise, because if the programmer has 100 MySQL queries in 100 scripts it will be much more painful to upgrade the code to MySQLi.

Notwithstanding how many web pages may be using MySQL today, there is this, and we have to deal with it.  MySQL is deprecated as of PHP 5.5.0, and will be removed in the future.  Maybe if I get time I'll write an article about how to make the transition.  It is likely to be fairly easy in OOP, and fairly painful in procedural code.  If only PHP could let us overwrite function calls!
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 38774601
If you show how the tables are related and where the $cc value comes from, maybe we can help further.
Personally, I'd create a stored procedure in mySQL to do this work if it needs to create a cusrsor to loop through for values.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now