Barand

Without your data I cannot do any timings but I have avoided the usual performance killers such as left joins and dependent subqueries. Make sure you have an index on idmember column. When using GROUP_CONCAT() there is a 1024 char limit on the grouped field size so inclusion of the notes using that method could be a problem depending on the number of other photos and the size of the notes. Will be OK just getting notes for the main photo. You can get the votes for the other photos using the same method as alternative 1 in #15 above.

Use the site's code tags or <> button in toolbar. The comparison operator is "==" and not "=" if (!$rst) { // handle the error eg die (mysqli_error($con)); } else { if ($row = mysqli_fetch_assoc($rst) { // process row } }

Plan B $sql = "SELECT p.idmember, p.CheminPhoto, p.TotalVote, max.CheminPhoto as other FROM photosprofils p INNER JOIN ( SELECT idmember , MAX(TotalVote) as maxVote , GROUP_CONCAT(CheminPhoto ORDER BY TotalVote DESC) as CheminPhoto FROM photosprofils GROUP BY idmember ) as max ON p.idmember = max.idmember AND p.TotalVote = max.maxVote ORDER BY p.TotalVote DESC, p.idmember"; /* gives these results +----------+-------------+-----------+-----------------------------+ | idmember | CheminPhoto | TotalVote | other | +----------+-------------+-----------+-----------------------------+ | 3 | Photo 3C* | 10 | Photo 3C*,Photo 3B,Photo 3A | | 1 | Photo 1B* | 9 | Photo 1B*,Photo 1A | | 2 | Photo 2B* | 8 | Photo 2B*,Photo 2A,Photo 2C | | 4 | Photo 4A* | 8 | Photo 4A*,Photo 4B | | 5 | Photo 5A* | 7 | Photo 5A* | +----------+-------------+-----------+-----------------------------+ */ $res = $mysqli->query($sql); while (list($mem, $mainphoto, $vote, $otherphoto) = $res->fetch_row()) { echo "<br>Member : $mem<br>Main Photo : $mainphoto<br>"; $photos = explode(',', $otherphoto); array_shift($photos); // remove main photo if (count($photos) > 0) { echo "Other photos :<br>"; foreach ($photos as $other) { echo " • $other<br>"; } } }

You refer to $item_no before that if(!isset($_POST['update']) && isset($_POST['submit'])) { $sql = "SELECT * FROM products WHERE item_no='".$item_no."'"; // line 82 $result = mysql_query($sql); if(result) {

A couple of alternatives SELECT cust_ID , SUM(quantity) as totQty , GROUP_CONCAT(order_name,' (', quantity,')' ORDER BY quantity DESC SEPARATOR ', ') as items FROM orders GROUP BY cust_ID ORDER BY totQty DESC; +---------+--------+-------------------------------+ | cust_ID | totQty | items | +---------+--------+-------------------------------+ | 2 | 6 | phone (3), DVD (2), Books (1) | | 1 | 5 | laptop (4), CD (1) | | 3 | 5 | CD (3), computer (2) | +---------+--------+-------------------------------+ or SELECT o.cust_ID, o2.totQty, o.order_name, o.quantity FROM orders o INNER JOIN ( SELECT cust_ID, SUM(quantity) as totQty FROM orders GROUP BY cust_ID ) as o2 USING (cust_ID) ORDER BY totQty DESC, cust_id; +---------+--------+------------+----------+ | cust_ID | totQty | order_name | quantity | +---------+--------+------------+----------+ | 2 | 6 | DVD | 2 | | 2 | 6 | phone | 3 | | 2 | 6 | Books | 1 | | 1 | 5 | CD | 1 | | 1 | 5 | laptop | 4 | | 3 | 5 | CD | 3 | | 3 | 5 | computer | 2 | +---------+--------+------------+----------+

Either load from string or load from file - not both! <?php $types = simplexml_load_file("data.xml"); foreach ($types->listcapacityresponse->capacity as $cap) { $results[] = (array)$cap; } echo '<pre>',print_r($results, true),'</pre>'; ?>

if you want the total call duration in each hour for each call number then (similar to Ch0cu3r) $lines = file('05-2012.txt'); $data = array(); foreach ($lines as $line) { list($cno,,$sdate,,$durn) = explode("\t", $line); $hr = date('m-d-H', strtotime($sdate)); if (!isset($data[$cno][$hr])) { $data[$cno][$hr] = 0; } $data[$cno][$hr] += $durn; } echo '<pre>',print_r($data, true),'</pre>'; /* results ***************************************** Array ( [19738424064] => Array ( [05-01-09] => 120 [05-01-12] => 1080 [05-01-14] => 120 [05-02-09] => 780 [05-02-14] => 420 ) [19738424065] => Array ( [05-01-14] => 420 [05-02-09] => 1080 ) ) ******************************************************/

this will put the data in an array $xml = simplexml_load_string($str); foreach ($xml->listcapacityresponse->capacity as $cap) { $results[] = (array)$cap; } echo '<pre>',print_r($results, true),'</pre>'; /* RESULTS *********************************************************** Array ( [0] => Array ( [type] => 8 [zoneid] => f26c2094-f2ca-4951-9265-a3f036e7f045 [zonename] => DBXCP1 [capacityused] => 6 [capacitytotal] => 18 [percentused] => 33.33 ) [1] => Array ( [type] => 0 [zoneid] => f26c2094-f2ca-4951-9265-a3f036e7f045 [zonename] => DBXCP1 [capacityused] => 27246198784 [capacitytotal] => 97078222080 [percentused] => 28.07 ) [2] => Array ( [type] => 1 [zoneid] => f26c2094-f2ca-4951-9265-a3f036e7f045 [zonename] => DBXCP1 [capacityused] => 13500 [capacitytotal] => 52800 [percentused] => 25.57 ) [3] => Array ( [type] => 5 [zoneid] => f26c2094-f2ca-4951-9265-a3f036e7f045 [zonename] => DBXCP1 [capacityused] => 3 [capacitytotal] => 12 [percentused] => 25 ) [4] => Array ( [type] => 6 [zoneid] => f26c2094-f2ca-4951-9265-a3f036e7f045 [zonename] => DBXCP1 [capacityused] => 14619246592 [capacitytotal] => 106308304896 [percentused] => 13.75 ) [5] => Array ( [type] => 3 [zoneid] => f26c2094-f2ca-4951-9265-a3f036e7f045 [zonename] => DBXCP1 [capacityused] => 92190801920 [capacitytotal] => 1099511627776 [percentused] => 8.38 ) ) *********************************************************************/

Where is $item_no being defined? $sql = "SELECT * FROM products WHERE item_no='".$item_no."'";

If you want speed and efficiency load the XML data into a database

To save me polishing my crystal ball, what results were you expecting?

Shouldn't data be string value ie in quotes var data = "<?php echo $array; ?>";

my test data The code $sql = "SELECT p.idmember, p.CheminPhoto, p.TotalVote, other.CheminPhoto as other FROM photosprofils p INNER JOIN ( SELECT idmember , MAX(TotalVote) as maxVote FROM photosprofils GROUP BY idmember ) as max ON p.idmember = max.idmember AND p.TotalVote = max.maxVote LEFT JOIN photosprofils other ON p.idmember = other.idmember AND p.id <> other.id ORDER BY p.TotalVote DESC, p.idmember"; $res = $mysqli->query($sql); $currMember = ''; while (list($mem, $mainphoto, $vote, $otherphoto) = $res->fetch_row()) { if ($currMember != $mem) { echo "<br>Member : $mem<br>Main Photo : $mainphoto<br>Other photos :<br>"; $currMember = $mem; } echo " • $otherphoto<br>"; } the results Member : 3 Main Photo : Photo 3C* Other photos : • Photo 3B • Photo 3A Member : 1 Main Photo : Photo 1B* Other photos : • Photo 1A Member : 2 Main Photo : Photo 2B* Other photos : • Photo 2A • Photo 2C Member : 4 Main Photo : Photo 4A* Other photos : • Photo 4B Member : 5 Main Photo : Photo 5A* Other photos : •

the error message is showing backticks in the DATE_FORMAT() parameters right syntax to use near '%d`, `%b` %Y')

Maybe SELECT IFNULL(birth_date, '') as birth_date FROM mytable

DON'T use global. Have your function return the found results. Also you want to avoid other side effects of calling your function. If you have to select another database (other than the default) inside the function then you should restore the database connection before returning. e.g. function getsupp($name) { // get the current database $result = mysql_query("SELECT DATABASE()"); list($currentDB) = mysql_fetch_row($result); mysql_select_db("picture_cards"); $records = array(); $result = mysql_query("SELECT name, address1, address2, address3, address4, pcode, email FROM supplier WHERE name LIKE('$name%')") or die(mysql_error()); if($result ){ while($row = mysql_fetch_assoc($result)) { $records[] = $row; }//endwhile }//endif // restore current database connection mysql_select_db($currentDB); // return array of found records return $records; }// endfunc getsupp() $res = getsupp('fred'); if (res) { // process the array of results returned in $res }

And use an editor that uses the correct " and ' characters instead of smartquotes

Single query INSERT INTO table2 (colA, colB) SELECT col1, col2 FROM table1

Your query would be something like this. The subquery finds the highest vote for each member. It then matches the records for each member to find the record matching this highest vote and uses the fields from that record SELECT p.idmember, p.CheminPhoto, p.TotalVote FROM photosprofils p INNER JOIN ( SELECT idmember, MAX(TotalVote) as maxVote FROM photosprofils GROUP BY idmember ) as max ON p.idmember = max.idmember AND p.TotalVote = max.maxVote GROUP BY p.idmember

phpinfo() will tell you which php.ini file is being used

For the benefit of those with a similar problem there should be no whitespace on the closing line of a heredoc statement ie <?php $mytext = <<<END_TEXT This is just a bunch of text I will write here so we can test a couple of things with text. I will add a few lines just so we have some. END_TEXT; ?>

You need to start a SQL TRANSACTION. If a query fails, ROLLBACK the transaction. If at the end there were no failures, COMMIT the transaction. http://dev.mysql.com/doc/refman/5.0/en/commit.html

you need to GROUP BY otherwise you'll get a single row with total of all notes SELECT j.jobID, j.name, COUNT(n.noteID) as totnotes FROM jobs j LEFT JOIN notes n USING (jobID) WHERE status='1' AND dateofenquiry>='$date' GROUP BY j.jobID You didn't tell us which tables contain "status" and "dateofenquiry". If either is in the notes table then the query will need amendment.

If you insert a new record in the database first you can grab the auto_increment id of that record (mysqli_insert_id() ) then save the image to the file system using that id as the image name

The second bit will not execute if it is greater than the later time then it is greater than the earlier time. Test for the later time first.