• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

Merge two queries

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
kgp43
Asked:
kgp43
1 Solution
 
Cornelia YoderArtistCommented:
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
 
Ray PaseurCommented:
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
 
kgp43Author Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Ray PaseurCommented:
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
 
Cornelia YoderArtistCommented:
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
 
Ray PaseurCommented:
@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
 
Cornelia YoderArtistCommented:
@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
 
Cornelia YoderArtistCommented:
But my original statement stands:   There is no way to combine these queries into one that makes any sense.
0
 
Ray PaseurCommented:
@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
 
Cornelia YoderArtistCommented:
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
 
Ray PaseurCommented:
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
 
Eddie ShipmanAll-around developerCommented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now