richei Posted September 9, 2012 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/ Share on other sites More sharing options...
trq Posted September 9, 2012 Share Posted September 9, 2012 Have you heard of an SQL JOIN ? Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/#findComment-1376413 Share on other sites More sharing options...
richei Posted September 9, 2012 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/#findComment-1376449 Share on other sites More sharing options...
premiso Posted September 9, 2012 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/#findComment-1376460 Share on other sites More sharing options...
richei Posted September 10, 2012 Author Share Posted 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? Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/#findComment-1376564 Share on other sites More sharing options...
Christian F. Posted September 10, 2012 Share Posted September 10, 2012 EXPLAIN that query, and post the results in the MySQL section. You'll get help there. Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/#findComment-1376585 Share on other sites More sharing options...
Monkuar Posted September 10, 2012 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/#findComment-1376590 Share on other sites More sharing options...
Christian F. Posted September 10, 2012 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/#findComment-1376593 Share on other sites More sharing options...
richei Posted September 10, 2012 Author Share Posted 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> Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/#findComment-1376625 Share on other sites More sharing options...
Christian F. Posted September 10, 2012 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/#findComment-1376628 Share on other sites More sharing options...
richei Posted September 10, 2012 Author Share Posted 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; } Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/#findComment-1376631 Share on other sites More sharing options...
Christian F. Posted September 10, 2012 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/#findComment-1376632 Share on other sites More sharing options...
richei Posted September 10, 2012 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/#findComment-1376675 Share on other sites More sharing options...
fenway Posted September 10, 2012 Share Posted September 10, 2012 Not explain, EXPLAIN. Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/#findComment-1376866 Share on other sites More sharing options...
richei Posted September 11, 2012 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/#findComment-1376887 Share on other sites More sharing options...
richei Posted September 11, 2012 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/#findComment-1376902 Share on other sites More sharing options...
kicken Posted September 11, 2012 Share Posted 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) Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/#findComment-1376956 Share on other sites More sharing options...
richei Posted September 11, 2012 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/#findComment-1376960 Share on other sites More sharing options...
richei Posted September 11, 2012 Author Share Posted 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) Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/#findComment-1376968 Share on other sites More sharing options...
fenway Posted September 13, 2012 Share Posted September 13, 2012 Well, you *do* recognize that issuing a query without examining the results will be 'nothing at all'. Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/#findComment-1377762 Share on other sites More sharing options...
richei Posted September 15, 2012 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/268178-using-an-array-in-a-mysql-where-clause/#findComment-1378244 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.