Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,566

  • Joined

  • Last visited

  • Days Won

    822

 Content Type 

Everything posted by Barand

  1. Barand
    @benanamen, the objective is to find the connections, not input the results just to print them out Data mysql> SELECT * FROM myflights; +----+-------------+-------------+---------+ | id | dep_airport | arr_airport | flt_num | +----+-------------+-------------+---------+ | 1 | LHR | JFK | 100 | | 2 | LHR | FRA | 200 | | 3 | JFK | MIA | 300 | | 4 | MCR | FRA | 400 | | 5 | MCR | MIA | 500 | | 6 | JFK | LHR | 150 | | 7 | FRA | LHR | 250 | | 8 | MIA | JFK | 350 | | 9 | FRA | MCR | 450 | | 10 | MIA | MCR | 550 | | 13 | MIA | FRA | 600 | | 13 | FRA | MIA | 650 | +----+-------------+-------------+---------+ Query -- DIRECT FLIGHTS SELECT f1.flt_num as flight1 , f1.dep_airport as depart1 , f1.arr_airport as arrive1 , NULL as flight2 , NULL as depart2 , NULL as arrive2 , NULL as flight3 , NULL as depart3 , NULL as arrive3 FROM myflights f1 WHERE f1.dep_airport = 'MIA' AND f1.arr_airport = 'FRA' UNION -- ONE CONNECTION SELECT f1.flt_num as flight1 , f1.dep_airport as depart1 , f1.arr_airport as arrive1 , f2.flt_num as flight2 , f2.dep_airport as depart2 , f2.arr_airport as arrive2 , NULL as flight3 , NULL as depart3 , NULL as arrive3 FROM myflights f1 JOIN myflights f2 ON f1.arr_airport = f2.dep_airport WHERE f1.dep_airport = 'MIA' AND f2.arr_airport = 'FRA' UNION -- TWO CONNECTIONS SELECT f1.flt_num as flight1 , f1.dep_airport as depart1 , f1.arr_airport as arrive1 , f2.flt_num as flight2 , f2.dep_airport as depart2 , f2.arr_airport as arrive2 , f3.flt_num as flight3 , f3.dep_airport as depart3 , f3.arr_airport as arrive3 FROM myflights f1 LEFT JOIN myflights f2 ON f1.arr_airport = f2.dep_airport LEFT JOIN myflights f3 ON f2.arr_airport = f3.dep_airport WHERE f1.dep_airport = 'MIA' AND f3.arr_airport = 'FRA' AND 'MIA' NOT IN (f2.dep_airport, f3.dep_airport) -- prevent loops AND 'FRA' NOT IN (f2.dep_airport, f3.dep_airport) -- prevent loops Result +---------+---------+---------+---------+---------+---------+---------+---------+---------+ | flight1 | depart1 | arrive1 | flight2 | depart2 | arrive2 | flight3 | depart3 | arrive3 | +---------+---------+---------+---------+---------+---------+---------+---------+---------+ | 600 | MIA | FRA | | | | | | | | 550 | MIA | MCR | 400 | MCR | FRA | | | | | 350 | MIA | JFK | 150 | JFK | LHR | 200 | LHR | FRA | +---------+---------+---------+---------+---------+---------+---------+---------+---------+
  2. Barand
    try using UTC_TIME() instead of CURTIME();
  3. Barand
    Are you sure your clock was right? This was my test mysql> select * from testtime; +----+------------+------------+---------------+ | id | start_date | end_date | schedule_time | +----+------------+------------+---------------+ | 1 | 2017-01-11 | 2017-01-18 | 18:55:00 | | 2 | 2017-01-11 | 2017-01-18 | 18:56:00 | | 3 | 2017-01-11 | 2017-01-18 | 18:57:00 | +----+------------+------------+---------------+ mysql> SELECT id -> , start_date -> , end_date -> , schedule_time -> , NOW() -> FROM testtime -> WHERE CURDATE() BETWEEN start_date AND end_date -> AND EXTRACT(HOUR_MINUTE FROM schedule_time) = EXTRACT(HOUR_MINUTE FROM CURTIME()); +----+------------+------------+---------------+---------------------+ | id | start_date | end_date | schedule_time | NOW() | +----+------------+------------+---------------+---------------------+ | 3 | 2017-01-11 | 2017-01-18 | 18:57:00 | 2017-01-14 18:57:39 | +----+------------+------------+---------------+---------------------+
  4. Barand
    Your code is mid-atlantic
  5. Barand
    you could use EXTRACT() ... AND EXTRACT(HOUR_MINUTE FROM CURTIME()) = EXTRACT(HOUR_MINUTE FROM schedule_date_time)
  6. Barand
    You have $store_code = $_GET['entity_number']; So where does your variable $entity_number get its value from?
  7. Barand
    Here's an example for you <?php $sql = "SELECT make , model , year FROM cars ORDER BY year DESC"; $res = $pdo->query($sql); $cardata = ''; foreach ($res as $car) { $cardata .= <<<CARDATA <div class="car"> <b>Make:</b> {$car['make']}<br /> <b>Model:</b> {$car['model']}<br /> <b>Year:</b> {$car['year']} </div> CARDATA; } ?> <!DOCTYPE html> <html> <head> <title>Cars</title> <style type="text/css"> body { font-family: sans-serif; font-size: 10pt; } #wrapper { width: 810px; min-height: 100px; margin-left: auto; margin-right: auto; overflow: auto; } .car { width: 140px; height: 50px; padding: 15px 25px; margin: 5px; float: left; border: 1px solid gray; border-radius: 5px; } </style> </head> <body> <div id="wrapper"> <h3>Cars</h3> <?=$cardata?> </div> </body> </html>
  8. Barand
    Easiest way is to put the results in divs with float:left and width ~= 25%
  9. Barand
    @benanamen: I have already requested that from her (via PM) but no response. Apparently she'd rather have us guessing.
  10. Barand
    Given that you are selecting for the prescriptions menu and the disease menu from the same table I would say your "database" (which I am guessing resembles a large spreadsheet) is in great need of normalization. For example, disease names and their ids should be in a separate table, with the disease id being stored in your medication table. This then the table you should be querying to build a menu of diseases.
  11. Barand
    Your posted code won't even execute without throwing syntax errors. Turn on error reporting in your php.ini file and set the error level to E_ALL. When you execute a query, check the return value. If false then it failed and you should check the content of mysql_error(). mysql_ functions have been obsolete for years and now removed from PHP. Change to mysqli_ functions or PDO. When you've done this, check the errors yourself and if you still cannot fix them come back.
  12. Barand
    Put into an array then join() $Q = "SELECT length, width FROM statxyx WHERE siteid='$siteid'"; $R = mysqli_query($DB,$Q); //start loop //while or foreach $results=[]; // define array while($row = mysqli_fetch_assoc($R)){ $results[] = "['7C6Buh',{$row['length']},{$row['width']}]"; // add to array } $final = join(",\r\n", $results);
  13. Barand
    Do not post multiple copies of the same problem.
  14. Barand
    A schema of this type (EAV model) is perfectly valid if implemented correctly. It is particularly useful for a products database where, say, a retailer sells washing machines and PCs. The sets of attributes for these items are very different (although some, like manufacturer and price, are common to all). It is also common in CMS systems so entities and attributes can easily be added on the fly. For the application that you have here, a straightforward relational model would be my choice.
  15. Barand
    Yesterday I thought it was a PIA. Now I have seen that RCA Manager is the third type 102 and ssm and status are the second and third type 4 records my view is that working with this database is a short path to insanity, and I am not paid enough for that. There should be an identifier to specify which field is which, not rely on a count, but neither the type nor the field id fulfill that function. (I hoped the metadata table might define the fields but no such luck). Ether there is something inherently wrong with the structure or the implementation is at fault. Anything you do with that data is going to be a hack. Good luck.
  16. Barand
    The attached table image show the incidence of the various field_ids for the incidents. For the purpose of the report you are attempting to produce, which field ids are you interested in?
  17. Barand
    Incident #232 (and others) doesn't have field_ids of 2665, 2666 and 2667 so that cannot be used. I would have expected the same for always to output the same - apparently not. I guess more understanding of the convoluted data structure is required.
  18. Barand
    It's 11pm (GMT) here so I'll have another look at it tomorrow morning.
  19. Barand
    What is the date format used in the database table? ie What do you get if you echo $departure_date immed after the get_field()? // MySQL DATE type $date = '2017-01-06'; $dt = new DateTime($date); echo $dt->format('j M Y') . '<br>'; // Unix epoch integer type $date = 1483660800; $dt = new DateTime("@$date"); echo $dt->format('j M Y') . '<br>';
  20. Barand
    or just fetch them as "quote" objects $sql = "SELECT id , price FROM quote"; $res = $db->query($sql); $quotes = []; while ($obj = $res->fetchObject('quote')) { // fetch as class 'quote' $quotes[] = $obj; }
  21. Barand
    Your desired output only shows 3 fields from wf_data table although your data has 9 for each object. So which of the 9 are you really interested in? For example, the description text in your sample above is from the v_text field of of field_id 2665. Your incident table has a status_id field - where does "resolved" text come from? What is SSM and where is it from? Some field ids are duplicated for incident 232 - do you only want the latest entry for a required field id? (Tables of this format are a real PIA)
  22. Barand
    That made my life easier!. Don't run queries inside loops, use joins to get the data with a single query. It's far more efficient. You can use coalesce to show the non-null value from the record. This will eliminate most of the nulls. If "object_id" is related to incident is filed, why is it confusingly called "object_id"? Example SELECT i.id as incident , i.title , i.date_ocurred , w.id as wid , w.field_id , w.type , CONVERT(COALESCE(v_boolean,v_integer,v_float,v_string,v_text,''), CHAR(50)) as value FROM incidents i JOIN wf_data w ON w.object_id = i.id ORDER BY i.id, w.field_id; Results +----------+------------------------------------------------------+--------------+------+----------+------+-------------------------------------------------+ | incident | title | date_ocurred | wid | field_id | type | value | +----------+------------------------------------------------------+--------------+------+----------+------+-------------------------------------------------+ | 206 | Root Cause Analysis | 1481719620 | 1606 | 2659 | 102 | | | 206 | Root Cause Analysis | 1481719620 | 1607 | 2660 | 2 | 206 | | 206 | Root Cause Analysis | 1481719620 | 1599 | 2661 | 6 | 1481414400 | | 206 | Root Cause Analysis | 1481719620 | 1600 | 2662 | 102 | | | 206 | Root Cause Analysis | 1481719620 | 1601 | 2663 | 102 | | | 206 | Root Cause Analysis | 1481719620 | 1602 | 2664 | 104 | | | 206 | Root Cause Analysis | 1481719620 | 1603 | 2665 | 5 | <p>Warehouse internet circuit down</p> | | 206 | Root Cause Analysis | 1481719620 | 1604 | 2666 | 4 | umm | | 206 | Root Cause Analysis | 1481719620 | 1605 | 2667 | 4 | resolved | | 231 | Lacey warehouse primary circuits down - Title - Test | 1482241980 | 1678 | 2659 | 102 | | | 231 | Lacey warehouse primary circuits down - Title - Test | 1482241980 | 1679 | 2660 | 2 | 231 | | 231 | Lacey warehouse primary circuits down - Title - Test | 1482241980 | 1671 | 2661 | 6 | 1482192000 | | 231 | Lacey warehouse primary circuits down - Title - Test | 1482241980 | 1672 | 2662 | 102 | | | 231 | Lacey warehouse primary circuits down - Title - Test | 1482241980 | 1673 | 2663 | 102 | | | 231 | Lacey warehouse primary circuits down - Title - Test | 1482241980 | 1674 | 2664 | 104 | | | 231 | Lacey warehouse primary circuits down - Title - Test | 1482241980 | 1675 | 2665 | 5 | <p>Lacey warehouse primary circuits down</p> | | 231 | Lacey warehouse primary circuits down - Title - Test | 1482241980 | 1676 | 2666 | 4 | SBM/SSM entry | | 231 | Lacey warehouse primary circuits down - Title - Test | 1482241980 | 1677 | 2667 | 4 | RCA Status Entry | | 232 | Testing RCA Workflow | 1482245760 | 1686 | 2668 | 102 | | | 232 | Testing RCA Workflow | 1482245760 | 1687 | 2669 | 2 | 232 | | 232 | Testing RCA Workflow | 1482245760 | 1690 | 2670 | 102 | | | 232 | Testing RCA Workflow | 1482245760 | 1680 | 2670 | 102 | | | 232 | Testing RCA Workflow | 1482245760 | 1691 | 2671 | 102 | | | 232 | Testing RCA Workflow | 1482245760 | 1681 | 2671 | 102 | | | 232 | Testing RCA Workflow | 1482245760 | 1682 | 2672 | 104 | | | 232 | Testing RCA Workflow | 1482245760 | 1692 | 2672 | 104 | | | 232 | Testing RCA Workflow | 1482245760 | 1693 | 2673 | 5 | <p>Testing RCA workflow - this is updatedp> | | 232 | Testing RCA Workflow | 1482245760 | 1683 | 2673 | 5 | <p>Testing RCA workflow</p> | | 232 | Testing RCA Workflow | 1482245760 | 1694 | 2674 | 4 | I dont know SBM/SSM - still dont know | | 232 | Testing RCA Workflow | 1482245760 | 1684 | 2674 | 4 | I dont know SBM/SSM | | 232 | Testing RCA Workflow | 1482245760 | 1695 | 2675 | 4 | Resolved | | 232 | Testing RCA Workflow | 1482245760 | 1685 | 2675 | 4 | Resolved | | 232 | Testing RCA Workflow | 1482245760 | 1688 | 2676 | 1 | 0 | | 232 | Testing RCA Workflow | 1482245760 | 1689 | 2677 | 4 | Update details as needed | | 232 | Testing RCA Workflow | 1482245760 | 1696 | 2678 | 1 | 1 | | 233 | Root Cause Analysis | 1482252180 | 1703 | 2690 | 102 | | | 233 | Root Cause Analysis | 1482252180 | 1704 | 2691 | 2 | 233 | | 233 | Root Cause Analysis | 1482252180 | 1697 | 2692 | 102 | | | 233 | Root Cause Analysis | 1482252180 | 1698 | 2693 | 102 | | | 233 | Root Cause Analysis | 1482252180 | 1699 | 2694 | 104 | | | 233 | Root Cause Analysis | 1482252180 | 1700 | 2695 | 5 | <p>new RCA</p> | | 233 | Root Cause Analysis | 1482252180 | 1701 | 2696 | 4 | SSM | | 233 | Root Cause Analysis | 1482252180 | 1702 | 2697 | 4 | Confirmed | | 233 | Root Cause Analysis | 1482252180 | 1705 | 2698 | 1 | 1 | | 234 | Root Cause Analysis | 1482323640 | 1712 | 2690 | 102 | | | 234 | Root Cause Analysis | 1482323640 | 1713 | 2691 | 2 | 234 | | 234 | Root Cause Analysis | 1482323640 | 1706 | 2692 | 102 | | | 234 | Root Cause Analysis | 1482323640 | 1707 | 2693 | 102 | | | 234 | Root Cause Analysis | 1482323640 | 1708 | 2694 | 104 | | | 234 | Root Cause Analysis | 1482323640 | 1709 | 2695 | 5 | <p>test description</p> | | 234 | Root Cause Analysis | 1482323640 | 1710 | 2696 | 4 | test ssm | | 234 | Root Cause Analysis | 1482323640 | 1711 | 2697 | 4 | test status | | 234 | Root Cause Analysis | 1482323640 | 1714 | 2698 | 1 | 1 | | 259 | This is the title | 1483468620 | 1721 | 2690 | 102 | | | 259 | This is the title | 1483468620 | 1722 | 2691 | 2 | 259 | | 259 | This is the title | 1483468620 | 1715 | 2692 | 102 | | | 259 | This is the title | 1483468620 | 1716 | 2693 | 102 | | | 259 | This is the title | 1483468620 | 1717 | 2694 | 104 | | | 259 | This is the title | 1483468620 | 1718 | 2695 | 5 | <p>this is a description</p> | | 259 | This is the title | 1483468620 | 1719 | 2696 | 4 | ssm | | 259 | This is the title | 1483468620 | 1720 | 2697 | 4 | status | | 259 | This is the title | 1483468620 | 1723 | 2698 | 1 | 1 | +----------+------------------------------------------------------+--------------+------+----------+------+-------------------------------------------------+
  23. Barand
    Can you let me have a copy of your software that lets you load a database table from an image. I can't find one. Then I could recreate your data on my pc to rewrite your queries. Alternatively, can you provide SQL dump of those tables with some sample data?
  24. Barand
    Your WHERE clause contains conditions on tables that are joined with a LEFT JOIN. This will make the query behave as though you were using INNER JOINS. If the query is functioning as you want (albeit slowly) try changing to INNER JOINS instead of LEFT JOINS, they are faster. If not there already, you could also try putting indexes on date of birth and country.
  25. Barand
    If that is a sample of your actual HTML markup, you have other problems that could hamper processing Your closing tag names do not match the opening tags <Tr> ... </ tr> and the space after the "</" is invalid. should be "</tr>" (no space).
×
×
  • 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.