using an array in a mysql where clause

richei · September 9, 2012

I've been at this tidbit for a couple days now and now i'm just tired of it, so i'm here again needing help. Basically, i'm using an array of id's generated from 1 query that puts the associated labels (music labels) into another array for use in getting the sales data from another table. I've been able to work out up to using the label array to get the sales data. Depending on the code, i either get the info the very last label or none at all. As i have it now, i'm trying to get it implode but its not doing it right (or i'm not echoing it right) so i know its not pulling the right info out. The code is below and i'm really hoping someone can help me out here.

As basic run down is this

as an example, user id 7 referred users 62, 349 and 79. The script then goes out, runs a query to get the labels that belong to those 3 users. after that happens, it goes out, grabs the sales information for those 3 users and totals it and then displays.

code

<?php
ob_start();

require_once 'config.php';
require_once 'functions.php';

$qry = mysql_query("SELECT c.id, c.fullname, c.label, c.email, c.country_code, r.reward_id, r.user_id FROM clients c
			   LEFT JOIN rewards r ON c.id = r.user_id 
			   WHERE c.in_aff = 1") or die(mysql_error()); // this works
?>
<div style="background-color: #36C; color:#FFF; font-size: 24px; font-family: Arial, Helvetica, sans-serif; text-align: center; padding: 10px;">Reward Members</div>
<table width="100%" style="font-size: 10pt;">
<tr style="text-align:center; font-weight: bold; background-color:#0FF;">
	<td>Client ID</td>
	<td>Client Name</td>
	<td>Label</td>
	<td>Email</td>
	<td>Country</td>
	<td>No. of <br /> Referred Clients</td>
	<td>Total Rewards <br /> Sales (To Date)</td>
</tr>
<?php 

while($r = mysql_fetch_assoc($qry)) {

if($r['reward_id'] !== NULL) {
	$tmp = explode(",", $r['reward_id']);
	$ct = count($tmp);

	$find_label = mysql_query("SELECT label FROM clients WHERE id IN ($r[reward_id])") or die(mysql_error()); // this works finally

	if(mysql_num_rows($find_label) != 0) {

		while($lab = mysql_fetch_assoc($find_label)) { // this is the part i'm having fits about
			for($i=0; $i < count($lab); $i++) {
				$label = implode("', '", $lab);

				echo $label;  // this outputs label1label2label3 instead of label1,label2,label3
			}

			$qrys = mysql_query("SELECT label, partner_share_currency, extended_partner_share_currency AS psc FROM itunes_sales WHERE label IN ($label) AND sales_or_returns = 'S'");  // this is the query that gets the sales info using the label and label is the only link i have to the client.  The rest of the code below works as it took it from the itunes sales script.
		}
		while($row = mysql_fetch_assoc($qrys)) {

			switch($row['partner_share_currency']) {
				case 'CNY':
					$row['psc'] = $row['psc'] * 0.158328;
					break;
				case 'EUR':
					$row['psc'] = $row['psc'] * 0.813460;
					break;
				case 'USD':
					$row['psc'] = $row['psc'];
					break;
				case 'CAD':
					$row['psc'] = $row['psc'] * 0.964782;
					break;
				case 'GBP':
					$row['psc'] = $row['psc'] * 1.46867;
					break;
				case 'JPY':
					$row['psc'] = $row['psc'] * 0.0108447;
					break;
				case 'AUD':
					$row['psc'] = $row['psc'] * 0.847417;
					break;
				case 'NZD':
					$row['psc'] = $row['psc'] * 0.684454;
					break;
				case 'MXN':
					$row['psc'] = $row['psc'] * 0.0783349;
					break;
				case 'CHF':
					$row['psc'] = $row['psc'] * 1.11174;
					break;
				case 'NOK':
					$row['psc'] = $row['psc'] * 0.177831;
					break;
				case 'DKK':
					$row['psc'] = $row['psc'] * 0.18229;
					break;
				case 'SEK':
					$row['psc'] = $row['psc'] * 0.14949;
					break;
			}
			$rows[$row['label']][] = $row;

			foreach($rows as $key => $row) {
				for($i = 0 ; $i < count($row); $i++) {
					$rows[$key]['total'] += $row[$i]['psc'];
					$total += $rows[$key]['total'];
				}
			}
		}
	}
} else {
	$ct = 0;
}
?>
<tr>
	<td><?=$r['id']?></td>
	<td><?=$r['fullname']?></td>
	<td><?=$r['label']?></td>
	<td><?=$r['email']?></td>
	<td><?=$r['country_code']?></td>
	<td><?=$ct?></td>
	<td><?=number_format($total, 2)?></td> <!-- This is the total sales from the last query -->
</tr>
<?php } ob_flush();?>
</table>

If there's a better or simpler way of getting it done, i'm game, but i really need to get this done

trq · September 9, 2012

Have you heard of an SQL JOIN ?

richei · September 9, 2012

yep, i use it in the first query and it won't work for the 2nd one, it just saids Column 'id' in where clause is ambiguous

premiso · September 9, 2012

Column 'id' in where clause is ambiguous

Which is right, but you can get around that by either specifying, tablename.id, or with an aliased tablename. Something like this:

SELECT tn2.id FROM tablename1 tn1 JOIN tablename2 tn2 ON tn2.some_id = tn1.id WHERE ....

Which will get rid of the ambiguous error. You will just need to make sure to prefix the doubled column names with the respected tablename you want to use.

richei · September 10, 2012

Well, now i just need to figure out a way to do it without exceeding the max execution time when it does the total. Is there a more effecient way of doing it?

Christian F. · September 10, 2012

EXPLAIN that query, and post the results in the MySQL section. You'll get help there.

Monkuar · September 10, 2012

USING a IN Clause you need to seperate the id's by commas

your $label does not do that.

echo implode(",", $label);

this is what is needed if you want to select stuff in a WHERE IN Clause, or separate them by commas somehow. AFAIK.

Christian F. · September 10, 2012

monkuar: That's not his problem, and in any case he's only inserting one ID at a time into that query.

richei · September 10, 2012

Actually, it was the problem, but now i'm running into really long execution times. So either something is wrong (which is probably the case) or i just can't do it like i wanted. I keep getting Fatal error: Maximum execution time of 30 seconds exceeded in /home/content/67/9435167/html/admin/rewards_view.php on line 88, line 88 is $rows[$key]['total'] += $row[$i]['psc'];. The adjusted code is below.

<?php
ob_start();
// Require config.php to connect to venzo MySQL server, and other functions
require_once 'config.php';
require_once 'functions.php';

$qry = mysql_query("SELECT c.id, c.fullname, c.label, c.email, c.country_code, r.reward_id, r.user_id FROM clients c
	   LEFT JOIN rewards r ON c.id = r.user_id 
	   WHERE c.in_aff = 1") or die(mysql_error());
?>
<div style="background-color: #36C; color:#FFF; font-size: 24px; font-family: Arial, Helvetica, sans-serif; text-align: center; padding: 10px;">Reward Members</div>
<table width="100%" style="font-size: 10pt;">
<tr style="text-align:center; font-weight: bold; background-color:#0FF;">
	<td>Client ID</td>
	<td>Client Name</td>
	<td>Label</td>
	<td>Email</td>
	<td>Country</td>
	<td>No. of <br /> Referred Clients</td>
	<td>Total Rewards <br /> Sales (To Date)</td>
</tr>
<?php 

while($r = mysql_fetch_assoc($qry)) {

if($r['reward_id'] !== NULL) {
	$tmp = explode(",", $r['reward_id']);
	$ct = count($tmp);

	$find_label = mysql_query("SELECT label FROM clients WHERE id IN ($r[reward_id])") or die(mysql_error());

	if(mysql_num_rows($find_label) != 0) {

		while($lab = mysql_fetch_assoc($find_label)) {
			$labels[] = $lab['label'];
		}
		$label = '"'.implode('", "', $labels).'"';
		$build_qry = "SELECT label, partner_share_currency, extended_partner_share_currency AS psc FROM itunes_sales WHERE label IN ($label) AND sales_or_returns = 'S'";
		//echo $build_qry;
		$qrys = mysql_query($build_qry) or exit(mysql_error());		
		while($row = mysql_fetch_assoc($qrys)) {

			switch($row['partner_share_currency']) {
				case 'CNY':
					$row['psc'] = $row['psc'] * 0.158328;
					break;
				case 'EUR':
					$row['psc'] = $row['psc'] * 0.813460;
					break;
				case 'USD':
					$row['psc'] = $row['psc'];
					break;
				case 'CAD':
					$row['psc'] = $row['psc'] * 0.964782;
					break;
				case 'GBP':
					$row['psc'] = $row['psc'] * 1.46867;
					break;
				case 'JPY':
					$row['psc'] = $row['psc'] * 0.0108447;
					break;
				case 'AUD':
					$row['psc'] = $row['psc'] * 0.847417;
					break;
				case 'NZD':
					$row['psc'] = $row['psc'] * 0.684454;
					break;
				case 'MXN':
					$row['psc'] = $row['psc'] * 0.0783349;
					break;
				case 'CHF':
					$row['psc'] = $row['psc'] * 1.11174;
					break;
				case 'NOK':
					$row['psc'] = $row['psc'] * 0.177831;
					break;
				case 'DKK':
					$row['psc'] = $row['psc'] * 0.18229;
					break;
				case 'SEK':
					$row['psc'] = $row['psc'] * 0.14949;
					break;
			}
			$rows[$row['label']][] = $row;

			foreach($rows as $key => $row) {
				for($i = 0 ; $i < count($row); $i++) {
					$rows[$key]['total'] += $row[$i]['psc'];
					$total += $rows[$key]['total'];
				}
			}
		}
	}
} else {
	$ct = 0;
}
?>
<tr>
	<td><?=$r['id']?></td>
	<td><?=$r['fullname']?></td>
	<td><?=$r['label']?></td>
	<td><?=$r['email']?></td>
	<td><?=$r['country_code']?></td>
	<td><?=$ct?></td>
	<td><?=number_format(($total * .20) * .30, 2)?></td>
</tr>
<?php } ob_flush();?>
</table>

Christian F. · September 10, 2012

You are still running queries inside loops, which is a major resource waste. As previously stated you need to move all of the data fetching into one query, using JOINs to well.. Join the tables you want to fetch it from.

richei · September 10, 2012

Still timing out, i commented out the middle while loop, and the other stuff, so now the middle part looks like

while($r = mysql_fetch_assoc($qry)) {

if($r['reward_id'] !== NULL) {
	$tmp = explode(",", $r['reward_id']);
	$ct = count($tmp);

	$find_label = mysql_query("SELECT cl.label, i.partner_share_currency, i.extended_partner_share_currency AS psc FROM clients cl 
				LEFT JOIN itunes_sales i ON (cl.label = i.label)
				WHERE cl.id IN ($r[reward_id]) AND i.sales_or_returns = 'S'") or die(mysql_error());

	if(mysql_num_rows($find_label) != 0) {

		//while($lab = mysql_fetch_assoc($find_label)) {
		//	$labels[] = $lab['label'];
		//}
		//$label = '"'.implode('", "', $labels).'"';
		//$build_qry = "SELECT label, partner_share_currency, extended_partner_share_currency AS psc FROM itunes_sales WHERE label IN ($label) AND sales_or_returns = 'S'";
		//echo $build_qry;
		//$qrys = mysql_query($build_qry) or exit(mysql_error());		
		while($row = mysql_fetch_assoc($find_label)) {

			switch($row['partner_share_currency']) {
				case 'CNY':
					$row['psc'] = $row['psc'] * 0.158328;
					break;
				case 'EUR':
					$row['psc'] = $row['psc'] * 0.813460;
					break;
				case 'USD':
					$row['psc'] = $row['psc'];
					break;
				case 'CAD':
					$row['psc'] = $row['psc'] * 0.964782;
					break;
				case 'GBP':
					$row['psc'] = $row['psc'] * 1.46867;
					break;
				case 'JPY':
					$row['psc'] = $row['psc'] * 0.0108447;
					break;
				case 'AUD':
					$row['psc'] = $row['psc'] * 0.847417;
					break;
				case 'NZD':
					$row['psc'] = $row['psc'] * 0.684454;
					break;
				case 'MXN':
					$row['psc'] = $row['psc'] * 0.0783349;
					break;
				case 'CHF':
					$row['psc'] = $row['psc'] * 1.11174;
					break;
				case 'NOK':
					$row['psc'] = $row['psc'] * 0.177831;
					break;
				case 'DKK':
					$row['psc'] = $row['psc'] * 0.18229;
					break;
				case 'SEK':
					$row['psc'] = $row['psc'] * 0.14949;
					break;
			}
			$rows[$row['label']][] = $row;

			foreach($rows as $key => $row) {
				for($i = 0 ; $i < count($row); $i++) {
					$rows[$key]['total'] += $row[$i]['psc'];
					$total += $rows[$key]['total'];
				}
			}
		}
	}
} else {
	$ct = 0;
}

Christian F. · September 10, 2012

Still one level of loop-nested queries to remove. Once you've done that, and the script still times out, then see my instructions in the first post I made in this thread. Almost certain that you're missing some indices in your tables, and by EXPLAINing the select it'll tell you where.

richei · September 10, 2012

Still getting the execution error message. Not really sure what you need me to explain, what i'm trying to accomplish with all this mess is in my initial post.

fenway · September 10, 2012

Not explain, EXPLAIN.

richei · September 11, 2012

ahhh, lol ok.

Just tried it with

$find_label = mysql_query("EXPLAIN SELECT i.partner_share_currency, i.extended_partner_share_currency AS psc FROM venzo_itunes_sales i 
  LEFT JOIN venzo_clients cl ON (cl.label = i.label)
  WHERE cl.id IN ($r[reward_id]) AND i.sales_or_returns = 'S'") or die(mysql_error());

The result was nothing at all, just my normal page minus the sales results.

richei · September 11, 2012

ok, i ran the query using phpmyadmin and this is the result

id    select_type    table    type     possible_keys     key           key_len     ref      rows       Extra 
1     SIMPLE         cl       const    PRIMARY           PRIMARY       4           const    1
1     SIMPLE         i        ALL      NULL              NULL          NULL        NULL     33310      Using where

Hopefully it means something to someone here.

kicken · September 11, 2012

1     SIMPLE         i        ALL      NULL              NULL          NULL        NULL     33310      Using where

That line means that there is no index on your venzo_itunes_sales table, which means mysql has to do a full table scan (checking every individual row) each time you run that query.

You'll want to create indexes on both the venzo_itunes_sales.label field (for the JOIN condition) and the venzo_itunes_sales.sales_or_returns field (for the WHERE clause)

richei · September 11, 2012

There is an index though, but its not on the label, its on the id. i'll create one on the label and see what happens.

richei · September 11, 2012

Well, i wasn't able to really use a join in there since it wasn't giving me the right information (i ran the query through phpmyadmin to see what it would give me), so i went back to my old code and got some promising results, not anywhere near correct, but its not timing out either. This is what i'm getting for a result

412 terst mine compfnatic80@hotmail.com US 1 95,792,085,326,317.50

(id, client name, label, email, country # of referred clients, total sales to date)

fenway · September 13, 2012

Well, you *do* recognize that issuing a query without examining the results will be 'nothing at all'.

richei · September 15, 2012

we got this fixed, ill post the code when I get home. I ended up having to hire some on, but the fix wasn't to far off from what I had already.

Sign In

using an array in a mysql where clause

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information