Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,606

  • Joined

  • Last visited

  • Days Won

    831

 Content Type 

Everything posted by Barand

  1. Barand
    try $mysqli = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); $id = 75; $sql = "SELECT ci.collection_item_number , ci.collection_item_title , ci.collection_item_text , ci.username_id as ownerid , c.comment , c.username_id as userid FROM collection_items ci LEFT JOIN collection_item_comments c ON ci.collection_list_id = c.collections_list_id AND ci.collection_item_number = c.collection_items_id WHERE ci.collection_list_id = ?"; $data = []; $stmt = $mysqli->prepare($sql); $stmt->bind_param('i', $id); $stmt->execute(); $stmt->bind_result($number,$title,$text,$ownerid,$comment,$userid); while ($stmt->fetch()) { if (!isset($data[$number])) { $data[$number] = [ 'title' => $title, 'text' => $text, 'id' => $ownerid, 'comments' => [] ]; if ($comment) $data[$number]['comments'][] = [$comment, $userid]; } else { if ($comment) $data[$number]['comments'][] = [$comment, $userid]; } } ?> <html> <head><title>Sample</title> <style type="text/css"> table { border-collapse: collapse; } tr { vertical-align: top; } th,td { padding: 5px; } </style> </head> <body> <table border="1"> <tr><th>List Number</th><th>Item Title</th><th>Comments</th><th>Posted By</th></tr> <?php foreach ($data as $number => $item) { $span = count($item['comments'])+1; echo "<tr><td rowspan='$span'>$number</td> <td rowspan='$span'>{$item['title']}</td> <td>{$item['text']}</td> <td>{$item['id']}</td></tr>"; foreach ($item['comments'] as $comm) { echo "<tr><td>{$comm[0]}</td><td>{$comm[1]}</td></tr>"; } } ?> </table> </body> </html>
  2. Barand
    I would agree that if you have the order_item_id then you do not need order id. However, in the case of toppings it could be that item X has a choice of toppings and you want the choice applicable to the particular ordered item. Though I am guessing here about the actual relationship, without knowing the full schema.
  3. Barand
    Stop calling getTimeStamp() repeatedly. Call it once and store in a variable, then use the variable. That way you always have a consistent timestamp.
  4. Barand
    Details would be useful. What is the structure of the source table? What is the structure of the destination table? Are you inserting new records with the transferred data, or are you updating existing records? What is your current code?
  5. Barand
    Can't resist an interesting challenge. This is my interpretation of your problem and a possible solution (Note that the top lines of the two text files need removing prior to processing so they contain just the relevant data) $students= []; $limits = []; $totals = []; $results = json_decode(file_get_contents('result.txt'),1); /*************************************************************** * GET STUDENT DATA * (store classes in score order (desc) for each student) ****************************************************************/ foreach ($results as $sdata) { $tmp = array_slice($sdata,1); // get array of just classes and scores arsort($tmp); $s = $sdata['id_siswa']; $students[$s] = array_keys($tmp); // store classes in order of score $totals[$s] = array_sum($tmp); // get total score for each student } /*************************************************************** * SORT TOTALS ARRAY TO GIVE STUDENT PROCESSING ORDER * (students with highest total scores allocated first) ****************************************************************/ arsort($totals); /*************************************************************** * GET CLASS LIMITS ****************************************************************/ $ldata = file('limit.txt'); foreach($ldata as $line) { list($c,$l) = explode(':',$line); $limits[trim($c)] = trim($l); } /*************************************************************** * ALLOCATE THE STUDENTS TO CLASSES * if highest score class is full, try next until allocated ****************************************************************/ $classes = array_fill_keys(array_keys($limits), []); foreach ($totals as $sid=>$t) { foreach ($students[$sid] as $cls) { if (count($classes[$cls]) < $limits[$cls]) { $classes[$cls][] = $sid; break; // student allocated to class } } } /*************************************************************** * OUTPUT THE RESULTS ****************************************************************/ foreach ($classes as $cls => $studs) { echo "<strong>$cls</strong> " . count($studs) . " students (Limit : {$limits[$cls]})<br>"; echo join(', ', $studs). '<br><br>'; }
  6. Barand
    You have no closing ")" after the values in the query.
  7. Barand
    Then you are doing something wrong mysql> CREATE TABLE test_time (thetime TIME); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO test_time (thetime) VALUES ('01:30:10'),('00:05:00'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT TIME_TO_SEC(thetime) as sec FROM test_time; +------+ | sec | +------+ | 5410 | | 300 | +------+ 2 rows in set (0.00 sec)
  8. Barand
    Use a TIME type field (format hh:mm:ss) for maximum functionality. If you need it converted to seconds use TIME_TO_SEC() function mysql> SELECT TIME_TO_SEC('00:05:30'); +-------------------------+ | TIME_TO_SEC('00:05:30') | +-------------------------+ | 330 | +-------------------------+
  9. Barand
    If you want to use the current code (some DateTime stuff requires PHP5.3) then just change this line echo "$remainingDay days, $remainingHour hours, $remainingMinutes minutes, $remainingSeconds seconds";
  10. Barand
    Why don't you use DateTime object like I showed you in your other post? http://forums.phpfreaks.com/topic/298299-string-to-array-and-date/?do=findComment&comment=1521469 It saves you a shedload of code $targetDate = new DateTime('2015-12-25 12:00:00'); $remaining = $targetDate->diff(new DateTime()); echo $remaining->format('%a D - %H:%I:%S'); //--> 86 D - 19:44:24
  11. Barand
    So he has! Shouldn't it be "Anaemic Administrator" now?
  12. Barand
    Your solution shows another flaw, apart from the lack of validation, in your site. You should never put user input directly into a query. Always sanitize it against SQL injection first, or (better) use a prepared query. Although changing to prepared queries id probably a problem given your wp library. I had prepared a small working script for you so I may as well post it to demonstrate this <?php $mysqli = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); $gemeente = isset($_GET['gemeente']) ? $_GET['gemeente'] : ''; $top10 = ''; /****************************************************************************** *** *** GET LOCATION SELECT OPTIONS FOR MENU *** *******************************************************************************/ $sql = "SELECT DISTINCT value FROM wp_rg_lead_detail WHERE field_number = 11 ORDER BY value "; $loc_options = ''; $res = $mysqli->query($sql); while (list($locname) = $res->fetch_row()) { $sel = ($locname==$gemeente) ? 'selected="selected"' : ''; // select current gemeente $loc_options .= "<option $sel>$locname</option>\n"; } /****************************************************************************** *** *** CREATE TOP10 LIST FOR CHOSEN LOCATION *** *******************************************************************************/ if ($gemeente) { $gemeente = $mysqli->real_escape_string($gemeente); // sanitize user input $sql = "SELECT location , value , COUNT(*) as total FROM wp_rg_lead_detail INNER JOIN ( SELECT lead_id , value as location FROM wp_rg_lead_detail WHERE field_number = 11 ) loc USING (lead_id) WHERE field_number IN (8,16,20) AND value <> 'Selecteer uw lied!' AND location = '$gemeente' GROUP BY location, value ORDER BY location, total DESC LIMIT 10"; $top10 = "<ol>\n"; $res = $mysqli->query($sql); while (list($loc, $song, $total) = $res->fetch_row()) { $top10 .= "<li>$song ($total)</li>\n"; } $top10 .= "</ol>\n"; } ?> <html> <head> <meta name="generator" content="PhpED 14.0 (Build 14039, 64bit)"> <title>Example</title> <meta name="author" content="Barand"> <meta name="creation-date" content="09/29/2015"> </head> <body> <h1>Top 10</h1> <form method='get' action=''> Select location <select name="gemeente"> <option value=''> - location -</option> <?=$loc_options?> </select> <input type="submit" name="btnSubmit" value="Submit"> </form> <hr> <?=$top10?> </body> </html>
  13. Barand
    To close it, click the "Best Answer" button in the most helpful reply
  14. Barand
    Hi Newbie, If you are writing "Selecteer uw lied!" to the database then your validation is poor. For the location you need a subquery to pull the "11" records. I am assuming the field "Lead_id" is the one that groups the 8,11,16,20 records together. SELECT location , value , COUNT(*) as total FROM wp_rg_lead_detail INNER JOIN ( SELECT lead_id -- subquery to find location for the song choices , value as location FROM wp_rg_lead_detail WHERE field_number = 11 ) loc USING (lead_id) WHERE field_number IN (8,16,20) AND value <> 'Selecteer uw lied!' AND location = 'Zoetermeer ' -- remove this line for all locations GROUP BY location, value -- and remove location from ORDER and GROUP ORDER BY location, total DESC LIMIT 10
  15. Barand
    Instead of echoing "problem", try echoing something informative while you are debugging, like echo $stmt->error;
  16. Barand
    Can you give us some sample data, an example of the what the user might enter and the expected result? [EDIT] Beaten to the post, again.
  17. Barand
    you could try SELECT value , COUNT(*) as total FROM FROM wp_rg_lead_detail WHERE field_number=8 or field_number=16 or field_number=20 GROUP BY value ORDER BY total DESC LIMIT 10
  18. Barand
    1. Do not hijack old threads. I have moved your post to a new topic. 2. Use code tags around your code (use the <> button in the toolbar) I cannot see from your code how you know what answer the user gave, so that you can see if it is correct or not. In fact, with its confusing mix of html and php, lack of indentation plus repetitive code, it's not easy to see anything. What is the significance of "correct_answer" having the value "group"? And finally, what is your question?
  19. Barand
    DISTINCT is not a function that you can apply to a single field - it applies to the whole row. You haven't defined the join condition for the tables team_details and team_players. You haven't given us your table structure.
  20. Barand
    In this instance, $deletedCat = 1 UPDATE mytable SET cat = cat - 1 WHERE cat > $deletedCat But, as Mac_Gyver said, in a production environment, don't.
  21. Barand
    Then use your PDO object and PDO methods.
  22. Barand
    you age conditions should be if (trim($ageFrom) != '') { $where[] = sprintf ("(age >= '%s')", $mysqli->real_escape_string($ageFrom)); } if (trim($ageTo) != '') { $where[] = sprintf ("(age <= '%s')", $mysqli->real_escape_string($ageTo)); }
  23. Barand
    PHP does not require compiling. If it isn't already, put the directory containing php.exe in your path directive. At the command prompt, change to the directory containing your "index.php" invoke php.exe and pass it your arguments, which will be index.php, a, b, and c For example, to solve x^2 - 5x + 6 >php index.php 1 -5 6
  24. Barand
    Do not post multiple copies of the same question.
  25. Barand
    I use something like this $where = array(); $whereclause = ''; if (trim($gender) != '') { $where[] = sprintf ("(gender = '%s')", $mysqli->real_escape_string($gender)); } if (trim($mother_tongue) != '') { $where[] = sprintf ("(mother_tongue = '%s')", $mysqli->real_escape_string($mother_tongue)); } // etc if (count($where) > 0) { $whereclause = 'WHERE ' . join(' AND ', $where); } $sql = "SELECT * FROM tbluser " . $whereclause;
×
×
  • 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.