Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,563

  • Joined

  • Last visited

  • Days Won

    822

 Content Type 

Everything posted by Barand

  1. Barand
    JOIN in the above query should be LEFT JOIN
  2. Barand
    Can't imagine why anyone would want a single, efficient query when they can kill server performance with dozens of single ones.
  3. Barand
    try $t1 = new DateTime('15:12:45'); $inc = new DateInterval('PT30M'); $t2 = $t1->add($inc); echo $t2->format('H:i:s'); //--> 15:42:45
  4. Barand
    So it started as find one record vs find one file Now its Find 1000's of records vs find one file If you are going to keep moving the goalposts perhaps you should experiment and do your own benchmark tests
  5. Barand
    Table with over 180,000 records. mysql> SELECT COUNT(*) FROM votes; +----------+ | COUNT(*) | +----------+ | 182685 | +----------+ Find 100,000th mysql> SELECT * FROM test.votes -> WHERE id = 100000; +--------+-------+------+ | id | votes | type | +--------+-------+------+ | 100000 | 647 | 7 | +--------+-------+------+ 1 row in set (0.00 sec) Less than 1/100th of a second, so the question is "how long to find and read your file?"
  6. Barand
    Keep the query simple, store data in an array by region then output from the array. Example code (sorry I couldn't rotate the table though) <?php $db = new mysqli(HOST,USERNAME,PASSWORD,'test'); // use your credentials error_reporting(-1); // // CREATE TEST DATA // $db->query("DROP TABLE IF EXISTS jayson"); $sql = "CREATE TABLE jayson ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f1 VARCHAR(20), f2 VARCHAR(20), f3 VARCHAR(20), f4 VARCHAR(20) )"; $db->query($sql); $sql = "INSERT INTO jayson (f1,f2,f3,f4) VALUES ('Region 1', 'Place 1', 'adadassd', 'cxxvxv'), ('Region 1', 'Place 2', 'kjlkl', 'hsdhsdfjsh'), ('Region 1', 'Place 3', 'lkjlklkklk', 'uooio'), ('Region 2', 'Place 4', 'qeqewq', 'vbnvbnvnvbv'), ('Region 2', 'Place 5', 'aaaaaaaa', 'fvrvrfv'), ('Region 2', 'Place 6', 'gdgdgdfgdf', 'hntntnhntn'), ('Region 2', 'Place 7', 'efefefe', 'jmjmmymym') "; $db->query($sql); // // QUERY THE TABLE AND STORE IN ARRAY BY REGION (f1) // $data = array(); $sql = "SELECT f1,f2,f3,f4 FROM jayson ORDER BY f1,f2"; $res = $db->query($sql); while ($row = $res->fetch_assoc()) { $data[$row['f1']][] = array_slice($row,1); } ?> <html> <head> <meta name="generator" content="PhpED 12.0 (Build 12010, 64bit)"> <title>Example</title> <meta name="author" content="Barand"> <meta name="creation-date" content="04/07/2014"> <style type='text/css'> th { font-family: sans-serif; font-size: 10pt; background-color: blue; color: white; } td { font-family: sans-serif; font-size: 8pt; background-color: #eee; color: black; } table { border-collapse: collapse; width: 400px; } </style> </head> <body> <table border='1' cellpadding='5'> <tr> <th>F1</th> <th>F2</th> <th>F3</th> <th>F4</th> </tr> <?php // OUTPUT THE DATA ARRAY foreach ($data as $reg => $recs) { $k = count($data[$reg]); // get rowspan for ($i=0; $i<$k; $i++) { echo "<tr>"; if ($i==0) { echo "<td rowspan='$k'>$reg</td>"; } echo "<td>" . join("</td><td>", $recs[$i]) . "</td></tr>\n"; } } ?> </table> </body> </html>
  7. Barand
    Just swap the fields SELECT enemy_id, COUNT(DISTINCT player_id) as enemies FROM tablename GROUP BY enemy_id ORDER BY enemies DESC LIMIT 5
  8. Barand
    Which is it?
  9. Barand
    SELECT date FROM tablename WHERE month = 4 AND temp = (SELECT MAX(temp) FROM tablename WHERE month = 4)
  10. Barand
    try SELECT player_id, COUNT(DISTINCT enemy_id) as enemies FROM tablename GROUP BY player_id ORDER BY enemies DESC
  11. Barand
    Round the number of seconds to nearest 900
  12. Barand
    <?php echo roundToQuarterHour('2014-04-03 10:06:33'); // 2014-04-03 10:00:00 echo roundToQuarterHour('2014-04-03 10:11:33'); // 2014-04-03 10:15:00 echo roundToQuarterHour('2014-04-03 10:18:33'); // 2014-04-03 10:15:00 echo roundToQuarterHour('2014-04-03 10:24:33'); // 2014-04-03 10:30:00 function roundToQuarterHour($tstr) { $t = round(strtotime($tstr)/900)*900; return date('Y-m-d H:i:s', $t); } ?>
  13. Barand
    points = 11 - position
  14. Barand
    You have a raceID and place. Why would you need more than 2, let alone more than 16?
  15. Barand
    Exactly. EG SELECT company_name FROM members INNER JOIN member_area_code USING (member_id) WHERE area_code = 'AB1'
  16. Barand
    Put the primary key on both fields. That will prevent duplicate entries. CREATE TABLE `member_areacode` ( `member_id` int(11) NOT NULL, `area_code` varchar( NOT NULL, PRIMARY KEY (`member_id`,`area_code`), KEY `idx_area` (`area_code`) )
  17. Barand
    NO! NORMALIZE! The member area code table should look like this | member_id | Code | +-----------+--------+ | 1 | AB1 | | 1 | AB2 | | 1 | AB3 | | 1 | AB4 | | 2 | AL1 | | 2 | AL2 | | 2 | AL3 | | 2 | AL4 | | 3 | AL1 | | 3 | AL2 | | 4 | B1 | | 4 | B2 | | 5 | AB1 | | 5 | AB2 | | 6 | AB4 | So now a search on the member column will tell you which areas they cover and a search on the area column tell you which members cover that area
  18. Barand
    The solution is to normalize your data and not use db tables like spreadsheets
  19. Barand
    SELECT ... FROM mytable WHERE date = (SELECT MAX(date) FROM mytable)
  20. Barand
    UPDATE queries don't like data being selected from the same table to do the update so I'd use a 3-step approach CREATE TEMPORARY TABLE wp_postmeta2 LIKE wp_postmeta; INSERT INTO wp_postmeta2 SELECT * FROM wp_postmeta WHERE meta_key = '_regular_price'; UPDATE wp_postmeta a JOIN wp_postmeta2 b USING (post_id) SET a.meta_value = b.meta_value WHERE a.meta_key = '_sale_price' AND (a.meta_value IS NULL OR a.meta_value=0); Giving: +---------+---------+----------------+------------+ | meta_id | post_id | meta_key | meta_value | +---------+---------+----------------+------------+ | 1054 | 434 | _price | NULL | | 1055 | 434 | _regular_price | 1379.95 | | 1056 | 434 | _sale_price | 1379.95 | | 3070 | 613 | _price | 2898.95 | | 3071 | 613 | _sale_price | NULL | +---------+---------+----------------+------------+
  21. Barand
    Ignace - the HAVING is ok and necessary.You cannot use an alias in a WHERE clause Ansego - this was my version to find postcodes within 200km of 5558 SELECT a.postcode as centre , a.latitude as ctr_lat , a.longitude as ctr_long , b.postcode , b.areaname , b.latitude , b.longitude , ATAN2(SQRT(POW(COS(RADIANS(a.latitude)) * SIN(RADIANS(b.longitude - a.longitude)), 2) + POW(COS(RADIANS(b.latitude)) * SIN(RADIANS(a.latitude)) - SIN(RADIANS(b.latitude)) * COS(RADIANS(a.latitude)) * COS(RADIANS(b.longitude - a.longitude)), 2)), (SIN(RADIANS(b.latitude)) * SIN(RADIANS(a.latitude)) + COS(RADIANS(b.latitude)) * COS(RADIANS(a.latitude)) * COS(RADIANS(b.longitude - a.longitude)))) * 6372.795 as distance FROM test1 a INNER JOIN test1 b WHERE a.postcode = 5558 AND b.postcode <> a.postcode HAVING distance <= 200; +--------+-------------+--------------+----------+------------+-------------+--------------+------------------+ | centre | ctr_lat | ctr_long | postcode | areaname | latitude | longitude | distance | +--------+-------------+--------------+----------+------------+-------------+--------------+------------------+ | 5558 | 45.57349396 | -73.65723419 | 15948 | Area 15948 | 46.59859085 | -72.83525085 | 130.462675168181 | | 5558 | 45.57349396 | -73.65723419 | 19254 | Area 19254 | 45.49897766 | -73.71706390 | 9.50901533415637 | | 5558 | 45.57349396 | -73.65723419 | 19827 | Area 19827 | 45.63068771 | -72.94931793 | 55.4545466390431 | | 5558 | 45.57349396 | -73.65723419 | 29110 | Area 29110 | 45.53290176 | -73.74340057 | 8.08850181505773 | | 5558 | 45.57349396 | -73.65723419 | 34732 | Area 34732 | 45.30852127 | -73.26404572 | 42.5458257195249 | | 5558 | 45.57349396 | -73.65723419 | 37368 | Area 37368 | 45.46603394 | -73.46455383 | 19.1921588559054 | | 5558 | 45.57349396 | -73.65723419 | 59852 | Area 59852 | 45.48648834 | -73.46222687 | 18.0145815911446 | +--------+-------------+--------------+----------+------------+-------------+--------------+------------------+
  22. Barand
    try $xml = simplexml_load_file('feefofeedtest.xml'); $data = $fields = array(); foreach ($xml->xpath('FEEDBACK') as $FEEDBACK) { $fields = array_keys((array)($FEEDBACK)); $data = join("', '", array_map(array($con, real_escape_string), (array)$FEEDBACK)); $sql = "INSERT IGNORE INTO feeforeviews (" . join(', ', $fields) . ") VALUES ('$data')" ; if (!mysqli_query($con,$sql)) { echo "Error creating database: " . mysqli_error($con); } }
  23. Barand
    You are not doing yourself any favours by using "SELECT * " especially when using joined table queries. Always specify the fields you need and apply column aliases when required eg SELECT book.id as bookid , cat.id as catid , etc...
  24. Barand
    ... WHERE datum BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() - INTERVAL 1 DAY ...
  25. Barand
    http://uk3.php.net/manual/en/mysqli.error.php
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.