Solved

Merge two queries

Posted on 2013-01-12
12
220 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 27

Expert Comment

by:Cornelia Yoder
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 110

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 110

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:Cornelia Yoder
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 110

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
 
LVL 27

Expert Comment

by:Cornelia Yoder
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:Cornelia Yoder
ID: 38774066
But my original statement stands:   There is no way to combine these queries into one that makes any sense.
0
 
LVL 110

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:Cornelia Yoder
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 110

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

752 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