Search the Community
Showing results for tags 'sql'.
-
Hi everyone, I am trying to make it simply, but seem error come repeatedly, I could not figure why it went wrong. I am trying to get print (echo) to visible the print before I build other script. but resilt said "Notice: Array to strong conversion in" what did I do wrong? Thanks, Gary <!doctype html><html><body><table><?php require("require2.php"); $show = "show tables from XXXX";$table = mysqli_query($GaryDB, $show);$array = array();while($array = mysqli_fetch_array($table)) { echo $array;}?></table></body></html>
-
Hi I am trying to demonstrate a random number college project by code. I have a query which works fine getting data from table: SELECT id,Date, GROUP_CONCAT(ball ORDER BY ball) balls FROM ( SELECT id,Date,Ball1 ball FROM history eh UNION SELECT id,Date,Ball2 ball FROM history eh UNION SELECT id,Date,Ball3 ball FROM history eh UNION SELECT id,Date,Ball4 ball FROM history eh UNION SELECT id,Date,Ball5 ball FROM history eh ) x WHERE ball IN (6, 24, 32, 48, 50) GROUP BY id HAVING COUNT(*) >= 2 This will give 3 columns result: id,Date,balls which match any 2 or more of above numbers I want to join the result with another table starballs which has the same id as above and stores two colums of data Star1 Star2 i.e. id,Star1,Star2 I assume this will be inner join? to keep the data of top query and join it with starballs table. I have tried all sorts and cannot get it working. Can someone help me with this please. Thanks
-
Hi, Here is my SQL, its build with a loop, and looks correct to what I think it should be. I believe the output "$data" below should only be 4 records.. though its a lot more, and some data is duplicated and shown twice in the array. Lot of stuff removed for simplicity. SELECT usr.usr_login, app.app_name, acs.acs_read, acs.acs_add, acs.acs_edit, acs.acs_delete, acs.acs_admin FROM acs LEFT JOIN usr ON usr.ID = usr.ID LEFT JOIN app ON app.ID = app.ID if it matters here's the code that generated the statement above from an array. <?php // ===================================================================================================== // preperation // ===================================================================================================== foreach($fldarray as $fld){ // get readable names and stick em into an array $nm[] = $fld['human']; // Field name, and joins (if the join is using the option table) if($fld['opt'] == 1 and ($fld['opt_table'] == "" or $fld['opt_field'] == "")){ $jn[] = array('table' => "opt", 'field' => "opt_value"); $fd[] = "opt.opt_value"; // Field name, and joins (if the join is using a table other than options) }elseif($fld['opt'] == 1 and ($fld['opt_table'] <> "" and $fld['opt_field'] <> "")){ $jn[] = array('table' => $fld['opt_table'], 'field' => $fld['opt_field']); $fd[] = $fld['opt_table'].".".$fld['opt_field']; // a field directly written no foreign keys.. }else{ $fd[] = $ap.".".$fld['fieldname']; } $sa[] = array('human' => $fld['human'], 'field' => $fld['fieldname'], 'opt_table' => $fld['opt_table'], 'opt_field' => $fld['opt_field']); } // ===================================================================================================== // Start building the SQL // ===================================================================================================== $sql = "SELECT ".implode(", ", $fd)." FROM ".$ap; // ===================================================================================================== // Add the joins if any.. // ===================================================================================================== if (isset($jn)){ foreach($jn as $j){ $sql .= " LEFT JOIN ".$j['table']." ON ".$j['table'].".ID = ".$j['table'].".ID"; } } // ===================================================================================================== $stmt = $db->query($sql); $data = $stmt->fetchAll(); echo $sql; echo "<pre>"; print_r($data); echo "</pre>"; // ===================================================================================================== ?> Tables acs ID | acs_usr | acs_app | acs_read | acs_add | acs_edit | acs_delete | acs_admin ======================================================================================== 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 1 | 1 | 1 | 1 | 0 | 1 | 1 | 0 app ID | app_name ============= 1 | Applications 2 | Users usr ID | login ============= 1 | joe 2 | fred 3 | carlie 4 | lisa outputs this array.. Array ( [0] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [1] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [2] => Array ( [usr_login] => fred [0] => fred [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [3] => Array ( [usr_login] => fred [0] => fred [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [4] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [5] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [6] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [7] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [8] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [9] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [10] => Array ( [usr_login] => fred [0] => fred [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [11] => Array ( [usr_login] => fred [0] => fred [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [12] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [13] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [14] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [15] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [16] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [17] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [18] => Array ( [usr_login] => fred [0] => fred [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [19] => Array ( [usr_login] => fred [0] => fred [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [20] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [21] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [22] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [23] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [24] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [25] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [26] => Array ( [usr_login] => fred [0] => fred [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [27] => Array ( [usr_login] => fred [0] => fred [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [28] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [29] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [30] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [31] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [32] => Array ( [usr_login] => Joe [0] => Joe [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [33] => Array ( [usr_login] => Joe [0] => Joe [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [34] => Array ( [usr_login] => fred [0] => fred [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [35] => Array ( [usr_login] => fred [0] => fred [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [36] => Array ( [usr_login] => carlie [0] => carlie [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [37] => Array ( [usr_login] => carlie [0] => carlie [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [38] => Array ( [usr_login] => lisa [0] => lisa [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [39] => Array ( [usr_login] => lisa [0] => lisa [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [40] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [41] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [42] => Array ( [usr_login] => fred [0] => fred [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [43] => Array ( [usr_login] => fred [0] => fred [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [44] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [45] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [46] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [47] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [48] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [49] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [50] => Array ( [usr_login] => fred [0] => fred [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [51] => Array ( [usr_login] => fred [0] => fred [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [52] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [53] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [54] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [55] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [56] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [57] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [58] => Array ( [usr_login] => fred [0] => fred [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [59] => Array ( [usr_login] => fred [0] => fred [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [60] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [61] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [62] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [63] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) )
-
I am trying to insert data from my mother language (Sinhala) into my mysql table. But MySQL displays my chracters as question marks. This is how I test it: CREATE DATABASE sinhala_test; USE kindheart; ALTER DATABASE sinhala_test CHARACTER SET utf8 COLLATE utf8_general_ci CREATE TABLE IF NOT EXISTS `category` ( `category_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `name_si` VARCHAR(100) COLLATE utf8_unicode_ci NOT NULL, `description_si` TEXT COLLATE utf8_unicode_ci NOT NULL, `last_update` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `category` VALUES (1,'පොත් පත් සහ පාසල් උපකරණ','නවතම පොත් පත් අවශ්යතා ඇතුළු සියළුම පාසැල් හා අධ්යාපන මෙවලම්.',NOW()); This is what can I get from MySQL command line: mysql> select * from category\G *************************** 1. row *************************** category_id: 1 name_si: ???? ??? ?? ????? ????? description_si: ???? ??????? ??????? ????????. ????/????????? ????? ?????? ?????? ?? ???????? ??????. last_update: 2018-05-20 11:11:20 Can anybody tell me how can I fix this problem? NOTE: When inserting from phpMyAdmin its correctly work.
-
Hello everyone, I am a total newbie in PHP and so do here. I am trying to build a table consisting foreign key and primary key and it is showing error like "Parse error: syntax error, unexpected 'cities' (T_STRING) in line number 29". I have marked up my lines according to sublime text 3 editor. I am trying to create a database. 1 <?php 2 $servername = "localhost"; 3 $username = "username"; 4 $password = "password"; 5 $dbname = "newDB"; 7 // Create connection 8 $conn = new mysqli($servername, $username, $password, $dbname); 10 // Check connection 11 if ($conn->connect_error) { 12 die("Connection failed: " . $conn->connect_error);} 14 $firstname = $conn->real_escape_string($_REQUEST['firstname']); 15 $lastname = $conn->real_escape_string($_REQUEST['lastname']); 16 $address = $conn->real_escape_string($_REQUEST['address']); 17 $city = $conn->real_escape_string($_REQUEST['city']); 18 $country = $conn->real_escape_string($_REQUEST['country']); 19 $phone_number = $conn->real_escape_string($_REQUEST['phone_number']); 20 $email = $conn->real_escape_string($_REQUEST['email']); 22 // Attempt insert query execution 23 $sql1 = "INSERT INTO cities VALUES ('$city')"; 25 $sql2 = "INSERT INTO countries VALUES ('$country')"; 27 $sql3 = "INSERT INTO Contacts (firstname, lastname, address, city, country, phone, email) VALUES ('$firstname', '$lastname', '$address', $city, $country, '$phone_number','$email')"; 29 SELECT * FROM cities; SELECT * FROM countries; SELECT * FROM Contacts; if($conn->query($sql1) === true){ echo "City added successfully."; } else{ echo "ERROR: Could not able to execute " . $conn->error; } if($conn->query($sql2) === true){ echo "Çountry added successfully."; } else{ echo "ERROR: Could not able to execute " . $conn->error; } if($conn->query($sql3) === true){ echo "Çontact added successfully."; } else{ echo "ERROR: Could not able to execute " . $conn->error; } // Close connection $conn->close(); ?> -
Hello Everyone - I am playing around with some MYSQL and PHP project I have. I ran into a complex problem getting a PHP table filled with data from MYSQL. I will try to explain what I am trying to do: I am trying to do something like this but in PHP. This is my data from MYSQL database. The Table is called Children This is a quick explanation of how each column on the first screenshot should be filled from the database. This code is what I have so far... to be honest i am not sure how to get the totals of rest of the columns. Maybe use I can use subqueries or if statements... not sure! Can you please help me out? $r = mysqli_query($dbc,"SELECT Classrooms.ClassroomName, COUNT(*) AS TotalChildren FROM Children JOIN Classrooms ON Children.classroomID = Classrooms.classroomID GROUP BY Classrooms.ClassroomName");
-
I would like to have this +3 apply when the paymentterm if is less than 45. So the current code is using case statement. SELECT SUM(sblPOAmount) AS totalPOAmt, case when paymentTerm = '45 Days' then DATENAME(Day, DATEADD(day, SUBSTRING(paymentTerm, 1, 2) + 3, SBLInvoiceDate)) else DATENAME(Day, DATEADD(day, SUBSTRING(paymentTerm, 1, 2) + 0, SBLInvoiceDate)) end FROM [A_Sys].[dbo].[Eventtbl] WHERE DATENAME(MONTH, DATEADD(day,SUBSTRING(paymentTerm, 1, 2)+3,SBLInvoiceDate))='June' AND DATENAME(YEAR, DATEADD(day,SUBSTRING(paymentTerm, 1, 2)+3,SBLInvoiceDate))='2017' Group By paymentTerm,SBLInvoiceDate The Sum having multiple result due to "Group By", but if i run it without Group By , it will have error message. Please help
-
I have a login session where it checks the user name and displays it to the form (it displays FirstName LastName). That username is also being used as a variable to pull up data in db. Now I also have another page where user's can update the db, I dont have a problem if the user will update it with a complete FirstName LastName entry because it will just be the same as the one's being used by the login session, but sometimes they just update it with FirstName. The problem starts when I have values in TEST db under Tester column and in USERS db under User column that is of different values. It would have two diff values when a user did not enter the full FirstName LastName. For example, FirstName1 LastName1 is the value in USERS db User column - this is fixed and is being used in a login session User updated Tester column in TEST db with just FirstName1 - this is different from the User column above Here is what I am trying to do, Getting list of tickets from the TEST db where datefrom and dateto and using a variable for the values that is in User column under USER DB If User column under USERS db = Tester column under Test db which is FirstName1 LastName1 - it will be good as I will be able to get tickets under FirstName1 LastName1. But I will not be able to get ticket which is still assigned to that same person because the value in Tester column under Test db is just FirstName1. If User column under USERS db (FirstName1 LastName1) is not equal to Tester column under Test db which is just FirstName1 - I will not get tickets assigned to FirstName1 as my variable is equal to FirstName1 LastName1. I hope that I explained it clearly, here is my code, Here is the variable that I am posting, $uid = false; if(isset($_POST['uid'])){ $uid = $_POST['uid']; } And here is the query, $sql = 'SELECT `id`, `date_implemented`, `tester`, `comments` FROM `tracker` WHERE `tester` = :uid AND `scheduled_start_date` BETWEEN :d1 AND :d2'; $stmt = $conn->prepare($sql); $stmt->bindParam(':uid', $uid, PDO::PARAM_STR); $stmt->bindParam(':d1', $date['from'], PDO::PARAM_STR); $stmt->bindParam(':d2', $date['to'], PDO::PARAM_STR); $stmt->execute(); $result = $stmt->fetchAll(PDO::FETCH_ASSOC); I would need help in passing that variable (uid) such that I can use `WHERE tester LIKE uid`. In that case whether the tester column just contains FIRSTNAME1 or a complete FIRSTNAME1 LASTNAME1, I would be able to get all tickets assigned to FIRSTNAME1.
-
I have a php script which runs a sql query and writes it to a CSV, which works perfectly. However, I created a second modified version of the script because I want to write the same CSV, but with an additional row beneath each group of data for totals. Currently, the results are ordered by User Extension (CSRs and phone numbers). So for extension 7200 I want, let's say, all 10 of their calls and then another row to total the calls and the individual columns. I currently have the query returning an 'x' for indicators. So one user might have 10 calls and 4 'x's in the 'inbound' column. I would want that to say 4. Anyway, I started the second version of the script to use an array to do this. The first problem is it's telling my fputcsv() expects parameter 1 to be resource. It looks like this could be an error for the type of array I'm using, but I'm not sure. I feel like I'm on the right track but I'm not familiar with using arrays and CSV files together like this. Any help is much appreciated. ```$result = mysqli_query($conn2, "SELECT DISTINCT firstn , lastn , extension , Recieved , RecievedKnown , Outbound , outboundKnown , Missed , MissedKnown , CallingNumber , CalledNumber , starttime , endtime , duration , HOLDTIMESECS , TERMINATIONREASONCODE FROM ( SELECT u.firstn , u.lastn , c.extension , CASE WHEN LEGTYPE1 = 2 AND ANSWERED = 1 THEN 'x' ELSE '' END AS Recieved , CASE WHEN LEGTYPE1 = 2 AND answered = 1 AND CALLINGPARTYNO = k.phone_number THEN 'x' ELSE '' END AS RecievedKnown , CASE WHEN ANSWERED = 1 AND LEGTYPE1 = 1 THEN 'x' ELSE '' END AS Outbound , CASE WHEN LEGTYPE1 = 1 AND FINALLYCALLEDPARTYNO = k.phone_number THEN 'x' ELSE '' END AS outboundKnown , CASE WHEN Answered = 0 THEN 'x' ELSE '' END AS Missed , CASE WHEN ANSWERED = 0 AND CALLINGPARTYNO = k.phone_number THEN 'x' ELSE '' END AS MissedKnown , a.CALLINGPARTYNO AS CallingNumber , a.FINALLYCALLEDPARTYNO AS CalledNumber , b.starttime AS starttime , b.endtime AS endtime , b.duration , a.holdtimesecs , a.terminationreasoncode FROM ambition.session a INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID INNER JOIN ambition.mxuser c ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID INNER JOIN jackson_id.users u ON c.extension = u.extension LEFT JOIN ambition.known_numbers k ON a.callingpartyno = k.phone_number WHERE date(b.ts) >= curdate() AND LEGTYPE1 <> 12 -- This keeps the report from having blank spaces due to the 12 legtype. AND c.extension IN (7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312) ) x ORDER BY lastn") or die(mysqli_error( $conn2)); $userDetails = array(); while ($row = mysqli_fetch_assoc($result)){ $userDetails[] = $row; $extension = $row['extension']; if(!isset($userDetails[$extension])){ $userDetails[$extension]['missedCallCounts'] = 1; /* First time count */ }else{ $userDetails[$extension]['missedCallCounts'] += 1; /* Sum up the count */ } } echo $userDetails; $fp = fopen('TESTCSV.csv', 'w'); foreach($userDetails as $userDetail){ /* In the following line dump the respective userdetails to csv which will show summary */ fputcsv($fp, array_values($userDetails)); }```
-
This seems like something I've done several times a year for years, but I can't seem to get it. I can't find a solution on Google that works, although the question was asked many times and answered, too. Also, I'm low on sleep right now. Let's say I have two tables: USERS USERS_LOGINS I want to join the tables and get one row per user, with only the latest login row. Assume "UserId" is the pk/fk and there's some sort of LoginTime in the USERS_LOGINS table. So I will get results like: - Brian 8/4 - Mitch 8/3 - Jerry 8/2 I appreciate the help.
-
Insert into test.ambition_test(Extension, ExtID, Total_Talk_Time_seconds, Total_Talk_Time_minutes,Total_Outbound, Total_Inbound, Missed_Calls, Total_Calls, Date_of_report, Time_of_report) SELECT c.extension as Extension ,RESPONSIBLEUSEREXTENSIONID as ExtID , sum(Duration) as Total_Talk_Time_seconds , round(sum(Duration) / 60,2) as Total_Talk_Time_minutes , sum(if(LEGTYPE1 = 1,1,0)) as Total_Outbound , sum(if(LEGTYPE1 = 2,1,0)) as Total_Inboundambition_test , sum(if(Answered = 1,0,1)) as Missed_Calls , count(DISTINCT b.NOTABLECALLID) as Total_Calls , DATE(NOW()) , NOW() FROM cdrdb.session a LEFT JOIN cdrdb.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID LEFT join cdrdb.mxuser c ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID WHERE b.ts >= curdate() AND c.extension IN (7295,7306,7218,7247,7330,7000,7358) group by c.extension ON duplicate key update Total_Talk_Time_seconds =values(Total_Talk_Time_seconds), Total_Talk_Time_minutes =values(Total_Talk_Time_minutes), Total_Outbound = values(Total_Outbound), Total_Inbound = values(Total_Inbound), Missed_calls = values(Missed_Calls), Total_Calls = values(Total_Calls), Date_of_report = values(Date_of_report), Time_of_report = values(Time_of_report); The above query is what I have running in MySQL workbench currently. It's pulling and joining several tables to get call data and form metrics for 7 CSR agents on our phone system. IT pulls the data and forms the metrics correctly and it also updates throughout the day properly, but I have a huge problem: It will not insert new records on each new day, it still overwrites per day whereas I want to keep a history. I created a unique index on the Primary key Extension as well as the Date_of_report column as well. To specify, this query runs every 15 minutes to add/aggregate the metric totals based on the extension, however, the next day we should be inserting 7 new records and then have those aggregate through the day. Basically by the end of 3 days, we would have 21 records. However, this just continues to overwrite every day. Any ideas or tips here?
-
I am trying to retrieved data from database and update a single column. Here is a sample of how my page would look like, first part is when we initially upload it, this is during the approval process column1 | column2 | value1 | value1 | value2 | value2 | Now the second part is when we try to retrieved the data from the db with the added column3 because we want to assign it to someone, thus adding their name before we re-upload it to the db column1 | column2 | column3 value1 | value1 | ------- value2 | value2 | ------- <=== this column3 rows does not have value in database yet as it is not part of the ... page I used when we initially uploaded the data, so it will be blank when I retrieved the data I am trying to retrieved multiple rows here with column3 being editable so we can add different values to it. Then I want to re-upload those different values in column3 alone. Here is how I'm fetching the data, <?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "sample_db"; // check data before use it and convert from string to expected type, use try, not like here: $date = $_POST['date']; $date1 = $_POST['date1']; // use valid data to select rows try { //1. connect to MySQL database $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); //2. set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //3. create query string (here is answer on your question) $sql = 'SELECT column1, column2, column3 FROM sample_table WHERE scheduled_start_date BETWEEN :d1 AND :d2'; //4. prepare statement from query string $stmt = $conn->prepare($sql); //5. bind optional parameters //if ($status != 'All') $stmt->bindParam(':st', $status); //6. bind parameters $stmt->bindParam(':d1', $date); $stmt->bindParam(':d2', $date1); //7. execute statement $stmt->execute(); //8. returns an array containing all of the result set rows $result = $stmt->fetchAll(PDO::FETCH_ASSOC); //get count of rows $numrow = count($result); //print array - there is many solution to print array, //to debug you can do: //print_r($result); } catch(PDOException $e) { echo "Error: " . $e->getMessage(); } $conn = null; if($numrow == 0) echo "No results found."; else echo "Count: $numrow</br>"; { echo "<table width='auto' cellpadding='1px' cellspacing='0px' border=1 align='center'> <tr> <!--<th align='center'><input id=checkall name=checkall id=checkall type=checkbox value='' /></th>--> <th align='center'><strong>Column1</strong></th> <th align='center'><strong>Column2</strong></th> <th align='center'><strong>Column3</strong></th> </tr>"; foreach ($result as $row => $info) { echo "<form action='crqretrieve_status.php' method='post'>"; echo"<tr>"; echo "<td align='center'>" . $info['column1'] . "<input type=hidden name=column1 value=" . $info['column1'] . " </td>"; echo "<td align='center'>" . $info['column2'] . "<input type=hidden name=column2 value=" . $info['column2'] . " </td>"; echo "<td align='center'>" . "<input name=column3 value='' </td>"; echo "</tr>"; echo "</form>"; } } echo "</table>"; ?> Now, I have a code here which I am using to update status but it is only applicable for single row update which is based on the "ID". How can i use this for multiple row update? I've read about using a "case" but i dont know how to implement it using the code below. Here is the code to retrieved data from db and update row but only for single ID, i cant make it to work when retrieving multiple ID's and updating multiple ID's after clicking a Submit button. <?php /* Allows the user to both create new records and edit existing records */ // connect to the database include('include/connect-db.php'); // creates the new/edit record form // since this form is used multiple times in this file, I have made it a function that is easily reusable function renderForm($column1 = '', $column2 = '', $column3 = '', $column4 = '', $error = '', $id = '') { ?> <html xmlns="http://www.w3.org/1999/xhtml"> <body class="oneColFixCtrHdr"> <div id="container"> <?php if ($error != '') { echo "<div style='padding:4px; border:1px solid red; color:red'>" . $error . "</div>"; } ?> <div id="mainContent"> <form action="" method="post"> <table width="450" border="0" align="center"> <?php if ($id != '') { ?> <input type="hidden" name="id" value="<?php echo $id; ?>" /> <p>ID: <?php echo $id; ?></p> <?php } ?> <tr> <td>A1:<span style="color: #ff0000;"><strong>*</strong></span></td> <td colspan="2"><input type="text" name="column1" value="<?php echo $column1;?>"/></td> </tr> <tr> <td>A2:<span style="color: #ff0000;"><strong>*</strong></span></td> <td colspan="2"><input type="text" name="column2" value="<?php echo $column2; ?>" readonly="readonly"/></td> </tr> <tr colspan="3"> <td>A3:<span style="color: #ff0000;"><strong>*</strong></span></td> <td colspan="2"><input type="text" name="column3" id="column3" value="<?php echo $column3; ?>"/></td> </tr> <tr> <td><input type="submit" name="submit" value="Submit" /></td> </tr> </table> </form> <!-- end #mainContent --></div> <!-- end #container --></div> </body> </html> <?php } /* EDIT RECORD */ // if the 'id' variable is set in the URL, we know that we need to edit a record if (isset($_GET['id'])) { // if the form's submit button is clicked, we need to process the form if (isset($_POST['submit'])) { // make sure the 'id' in the URL is valid if (is_numeric($_POST['id'])) { // get variables from the URL/form $id = $_POST['id']; $column1 = $_POST['column1']; $column2 = htmlentities($_POST['column2'], ENT_QUOTES); $column3 = htmlentities($_POST['column3'], ENT_QUOTES); // check that fields are not empty if ($column1 == '' || $column2 == '' || $column3 == '') { // if they are empty, show an error message and display the form $error = 'ERROR: Please fill in all required fields!'; renderForm($column1, $column2, $column3, $error, $id); } else { // if everything is fine, update the record in the database if ($stmt = $mysqli->prepare("UPDATE sample_table SET column1 = ?, column2 = ?, column3 = ?, WHERE id=?")) { $stmt->bind_param("sssi", $column1, $column2, $column3, $id); $stmt->execute(); $stmt->close(); } // show an error message if the query has an error else { echo "ERROR: could not prepare SQL statement."; } // redirect the user once the form is updated header("Location: list.php"); } } // if the 'id' variable is not valid, show an error message else { echo "Error!"; } } // if the form hasn't been submitted yet, get the info from the database and show the form else { // make sure the 'id' value is valid if (is_numeric($_GET['id']) && $_GET['id'] > 0) { // get 'id' from URL $id = $_GET['id']; // get the record from the database if($stmt = $mysqli->prepare("SELECT column1, column2, column3 FROM sample_table WHERE id=?")) { $stmt->bind_param("i", $id); $stmt->execute(); $stmt->bind_result($column1, $column2, $column3); $stmt->fetch(); // show the form renderForm($column1, $column2, $column3, NULL, $id); $stmt->close(); } // show an error if the query has an error else { echo "Error: could not prepare SQL statement"; } } // if the 'id' value is not valid, redirect the user back to the view.php page else { header("Location: list.php"); } } } // close the mysqli connection $mysqli->close(); ?>
-
i have this table A +---------+---------------+----------+ | MatchID | CompetitionID | SeasonID | +---------+---------------+----------+ | 27 | 8 | 12 | | 28 | 10 | 12 | +---------+---------------+----------+ 2 rows in set (0.00 sec) and this table B +------+-------------------------------+----------+---------------+------------------------+ | Yr | Achievement | SeasonID | CompetitionID | CompetitionName | +------+-------------------------------+----------+---------------+------------------------+ | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | | 2016 | UN Peace Cup Winners | 12 | 9 | UN Peace Cup | | 2016 | Kyanja U-14 Cup Winners | 12 | 10 | KYANJA U-14 Cup | | 2016 | NDIV V Winners | 12 | 7 | Nakawa Fifth division | | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | | 2015 | UN Peace Cup Winners | 13 | 9 | UN Peace Cup | | 2015 | Kyanja U-14 Cup Winners | 13 | 10 | KYANJA U-14 Cup | | 2015 | NDIV V Winners | 13 | 7 | Nakawa Fifth division | +------+-------------------------------+----------+---------------+------------------------+ 8 rows in set (0.02 sec) how can i select a competitionId from table B corresponding to a group of matches with same competitionID and seasonID in table A
-
declare l_team_id int; DECLARE cur1 CURSOR FOR SELECT * FROM t_summ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row_fetched=1; SET l_last_row_fetched=0; OPEN cur1; cat_loop: LOOP FETCH cur1 INTO l_team_id,l_teamcat_id; /*work with the data*/ IF l_last_row_fetched=1 THEN /* No more rows*/ LEAVE cat_loop; END IF; if l_team_id = inHometeamId || l_team_id = inAwayteamId THEN insert into mvenue (MatchID ,match_venueID,stadium_LocationID,LeagueSeasonID,CompetitionID,Team_catId)values(inm_id,inVenueId,instadiumId,inseasonId,inCompId,l_teamcat_id); elseif l_team_id = inAwayteamId THEN insert into mvenue (MatchID ,match_venueID,stadium_LocationID,LeagueSeasonID,CompetitionID,Team_catId)values(inm_id,inVenueId,instadiumId,inseasonId,inCompId,l_teamcat_id); else insert into mvenue (MatchID ,match_venueID,stadium_LocationID,LeagueSeasonID,CompetitionID,Team_catId)values(inm_id,inVenueId,instadiumId,inseasonId,inCompId,'0'); end if; END LOOP cat_loop; CLOSE cur1; SET l_last_row_fetched=0; how can i improve on the above to make it database friendli and efficient? how can i modify it to use case statementand leave out the cursor?
-
iam trying to insert a summary into a table.Is it possible to write a case condition inside an insert statement like so: insert into match_summ(MatchID ,match_venueID,stadium_LocationID,SeasonID,CompetitionID,Team_catId)values('5','6','7','8','9', CASE WHEN homeid = l_team_id THEN teamcat_id=1 END , CASE WHEN l_team_id = awayid THEN teamcat_id=2 ELSE teamcat_id= 0 END ); Is what iam trying to do possible??What is the best way to do it???
-
how can i get a table like so: from a database table with teams and goals scored Season 2016/17 Premier League 2016/17 Team Information Home Away Total Points Information P W D L F A W D L F A W D L F A +/- Pts Manchester City 7 3 0 0 9 2 3 0 1 9 5 6 0 1 18 7 11 18
-
<?php $host= "localhost"; $username="root"; $password="root"; $db_name= "test2.0"; $tbl_name="permohonan"; mysql_connect("$host","$username","$password")or die ("cannot connect"); mysql_select_db ("$db_name")or die("cannot select DB"); if(isset($_POST['submit'])) { $Jabatan = mysql_real_escape_string($_POST['Jabatan']); $unit = mysql_real_escape_string($_POST['unit']); $lain2 = mysql_real_escape_string($_POST['lain2']); $nama_pemohon = mysql_real_escape_string($_POST['nama_pemohon']); $destinasi = mysql_real_escape_string($_POST['destinasi']); $tujuan = mysql_real_escape_string($_POST['tujuan']); $maklumat_ = mysql_real_escape_string($_POST['maklumat_']); $datedepart_= mysql_real_escape_string($_POST['datedepart_']); $timedepart_= mysql_real_escape_string($_POST['timedepart_']); $datearrive_ = mysql_real_escape_string($_POST['datearrive_']); $timearrive_ = mysql_real_escape_string($_POST['timearrive_']); $query1 = mysql_query("INSERT INTO permohonan VALUES(NULL,'$Jabatan','$unit','$lain2','$nama_pemohon','$destinasi','$tujuan','$maklumat_','$datedepart_','$timedepart_,'$datearrive_',$timearrive_')"); if($query1) { header("location : reservationform.php"); } } ?>
-
I've got two tables user table - id - username - fname - lname - group_id event Table - id - user_id - event_time - event_date - event ('Arrival','Departure','Break',etc..) My problem is getting all user by group and joining other table even date, but it should be base on 'event_date' I use `LEFT JOIN` and `LEFT OUTER JOIN` but because I have to specify the `event_date` not all user can be shown because some are absent. I made this SELECt * FROM user as u LEFT OUTER JOIN event as e ON u.id = e.user_id WHERE u.group_id = 6 AND ( e.event_date = '2016-07-05' AND e.event = 'Arrival' ) GROUP BY u.id but it only show the user who has an event on the date specified. I tried to make an VIEW table but it still has the condition base on event_date. Although I made this on PHP by condition but it takes time to load as I select event on every user. is there a way to get it using SQL? it should be like this:
-
Hey guys: I have a database that is full of items. Each item lives in a box, with various accessories. I'm trying to loop through this DB to print out a label for each case that shows the amount of items that are in the case, along with the accessories that belong to it. I currently have a rather convoluted bit of code, that kind of works, but doesn't have the order that I want. My result is currently this: 4 X Generic lighting hanging clamp 4 X Generic lighting hanging clamp 4 X Generic lighting hanging clamp 4 X Generic lighting hanging clamp 4 X Generic lighting hanging clamp 2 X 25kg Safety Bond 2 X 25kg Safety Bond 2 X 25kg Safety Bond 2 X 25kg Safety Bond 2 X 25kg Safety Bond 2 X 16a to Powercon 2 X 16a to Powercon 2 X 16a to Powercon 2 X 16a to Powercon 2 X 16a to Powercon ********** 12 X Generic lighting hanging clamp 6 X 25kg Safety Bond ********** All the information is there, but due to the way that it loops through it's in the wrong order. I'd like it to be grouped so it looks like this so that it is grouped by box: 4 X Generic lighting hanging clamp 2 X 25kg Safety Bond 2 X 16a to Powercon 4 X Generic lighting hanging clamp 2 X 25kg Safety Bond 4 X Generic lighting hanging clamp 2 X 16a to Powercon 4 X Generic lighting hanging clamp 2 X 25kg Safety Bond 2 X 16a to Powercon 4 X Generic lighting hanging clamp 2 X 25kg Safety Bond 2 X 16a to Powercon ********** 12 X Generic lighting hanging clamp 6 X 25kg Safety Bond ********** Here is the code that I'm using. At the moment I'm just concentrating on the if ($item_type == '2') part, but have shown the whole lot so you can see what I'm doing. foreach ($distinct_path as $path) { $sql = "SELECT * FROM current_items WHERE path = '$path'"; $result = $con->query($sql); if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { $item_name = $row['item_name']; $item_type = $row['item_type']; $item_qty = $row['qty']; $case_qty = $row['case_qty']; if ($item_type == '1') // THESE ARE MAIN ITEMS { $total_main_qty = $row['qty']; //main item QTY; $main_item_name = $item_name; } // This code will tell us number of full boxes, and number of extra lights left over $amtoffull = $total_main_qty/$case_qty; $amtoffull = floor($amtoffull); // we round down to the nearest whole number $total_left = $total_main_qty - ($case_qty * $amtoffull); if ($item_type == '2') // THESE ARE ACCESSORIES { $asc_qty = $row['qty']; //main item QTY; $asc_qty = $asc_qty/$total_main_qty; // THIS GIVES US THE TOTAL OF ONE Item $asc_qty = $asc_qty * $case_qty; // This is a full case amount //echo $asc_qty ." x ". $item_name ."</p>"; while ($i < $amtoffull) { echo $asc_qty ." X ". $item_name ."</p>"; ++$i; } } $i = 0; } echo "**********"; echo "<p>"; } I think I need to either shift the loops around so they work in a different order, or store everything into an array and then loop through that. Can any one help?
-
Hello! I need help making a query for this statement: Drinkers who frequent exactly one of the bars which Joe frequents The table looks like this: --------------------------------------------- | drinker | bar | --------------------------------------------- | Mike |A.P. Stump's | | Bob |Blue Angel | | Erik |Blue Angel | | Herb |Blue Angel | | Jesse |Blue Angel | | Joe |Blue Angel | | John |Blue Angel | | Justin |Blue Angel | | Mike |Blue Angel | | Rebecca |Blue Angel | | Tom |Blue Angel | | Vince |Blue Angel | | John |Cabana | | Mike |Cabana | | Vince |Cabana | | Joe |Caravan | | John |Caravan | | Tom |Caravan | | Bob |Coconut Willie's Cocktail Lounge | | Joe |Coconut Willie's Cocktail Lounge | | Rebecca |Coconut Willie's Cocktail Lounge | | Justin |Gecko Grill | | Rebecca |Gecko Grill | | Herb |Seven Bamboo | | Vince |Seven Bamboo | | Mike |The Shark and Rose | --------------------------------------------- I can't seem to understand the logic that is needed to build the query. I started with this: SELECT DISTINCT * FROM frequents F1 WHERE NOT EXISTS (SELECT * FROM frequents F2 WHERE F1.drinker = 'Joe' AND /* Something here */); The way I'm reading is that my NOT EXISTS sub query should have Drinkers who like SOME of the bars which Joe frequents then somehow turn that into only one. How should I proceed? Any help would be appreciated! Thanks in advance!
-
I have a page of html tables that are filled by database values through PHP, and there are no problems there. However, I have an issue on one of the tables. I have a pricing table, attached below. The $575 is currently filled from a field in the database that is set from a CSV upload, but the new CSV template is not doing this because it's missing the price field. This is the SQL statement that I'm using in the database, since the price is based off of meterSize, meterType and workOrderType2: UPDATE staging INNER JOIN pricing ON staging.meterType = pricing.meterType AND staging.meterSize = pricing.meterSize AND staging.workOrderType2 = pricing.workOrderType SET staging.onsiteTestSurveyPrice = pricing.price This works, but only with the original template, so I'm looking into doing this strictly in PHP with the table/row values, if possible. An example of what I'm looking for, using the html table and PHP: If $row['meterSize'] contains 3", $row['meterType'] contains COMPOUND and $row['workOrderType2'] contains ONSITE SURVEY AND TEST then $575 should go in <td><? echo $row['onsiteSurveyTestCost'];?> </td> Here's the code for those 3 conditional table rows: <tr style="border: none;"> <td style="border: none; text-align: left;">Meter Type:</td> <td style="border: none; text-align: right;"><? echo $row['meterType'];?> </td> </tr> <tr style="border: none;"> <td style="border: none; text-align: left;">Meter Size:</td> <td style="border: none; text-align: right;"><? echo $row['meterSize'];?> </td> </tr> <tr style="border: none;"> <td style="border: none; text-align: left;">Service Preformed:</td> <td style="border: none; text-align: right;"><? echo $row['workOrderType2'];?> </td> </tr> Is there a way to do this? I have multiple prices that are based on multiple conditions, but If I could figure out how to do this once then I can alter the values accordingly. Basically I want to mirror the db/SQL statement only with strictly PHP and the values in each table row. How can I do that, if possible?
-
I have a search function that works perfectly by matching database values and displaying the records on my page. However, I want to make my serial number field in the output a "href " link to open a new page that uses the serial number in the code. I had it working previously with dropdowns but I'm trying to make it work with the search function. Here's the working code: $search = $connect->real_escape_string($_POST['search']); $resultSet = $connect->query("SELECT * FROM staging WHERE serialNumber LIKE '%$search%'"); if($resultSet->num_rows > 0){ while($rows = $resultSet->fetch_assoc()) { $date = $rows['date']; $utility = $rows['utility']; $address = $rows['address']; $sn = $rows['serialNumber']; $output .= "Date: $date <br />Utility: $utility<br />Address: $address<br />Serial Number: $sn<br /><br />"; } }else{ $output = "No Results"; } } Now, when I try to add the link to the serial number variable, like this: $output .= "Date: $date <br />Utility: $utility<br />Address: $address<br />Serial Number: <a href=\\'/dashboard-display?id='.$row['serialNumber'].''>$sn</a><br /><br />"; The page doesn't load. I'm using a PHP reference but I think I may have syntax wrong in the href section. How can I get this to display the info with the link tag around $sn?
-
I have an html table with some numerical values in each data row. There are 8 columns representing 8 tests. At the bottom of each column is a corrected accuracy cell which represents a math formula from the three rows above it. Basically, it divides the meter volume by the tester volume, multiplies that result by the tester accuracy, and divides that result by 100. I have attempted something with the following code: <table style="width:100%; border:none; border-collapse:collapse;"> <? php $test1FormA = $row['test1MeterVol'] / $row['test1TesterVol']; $test1FormB = $test1FormA * $row['test1Accuracy']; $test1FinalForm = $test1FormB / 100; ?> <tr> <td style="border:none; text-align: left;">Meter Volume: </td> <td><? echo $row['test1MeterVol'];?> </td> </tr> <tr> <td style="border:none; text-align: left;">Tester Volume: </td> <td><? echo $row['test1TesterVol'];?> </td> </tr> <tr> <td style="border:none; text-align: left;">Tester Accuracy: </td> <td><? echo $row['test1Accuracy'];?> </td> </tr> <tr> <td style="border:none; text-align: left;">Corrected Accuracy: </td> <td><? echo $test1FinalForm;?> </td> </tr> </table> However, my page no longer loads with this so I'm assuming I'm doing something wrong with my variables in the top, maybe with syntax. I just need it to take the values for those three data rows and use them in the formulas so that I can put the final result in the corrected accuracy field.
-
I have a page that displays about 12 tables that are filled from database values. These values correspond with a single record but some of these fields are based off of queries I'm making so that if something's wrong in the query, it will display red text. I'm only doing this for a few different fields but I want an option to edit and fix those fields and then save them to the database with an UPDATE. Basically, I don't want to wrap the whole page in a form since only a few fields in a few of the tables will need to be editable. Is there a way to do this so that I can edit only the few fields that need to be fixed? Here's a table as example: <div class="testResults" style=" width: 30%; clear:both; float: left;"> <!--Test Results Table--> <table style="width: 100%;"> <tr> <th colspan="2">Test Results</th> </tr> <tr> <td style="text-align: left;">Tested Within AWWA Standards</td> <td><? echo $row['standards'];?> </td> </tr> <tr> <td style="text-align: left;">Meter Failed on Low Side</td> <td><? echo $row['lowFail'];?> </td> </tr> <tr> <td style="text-align: left;">Meter Failed on High Side</td> <td><? echo $row['highFail'];?> </td> </tr> <tr> <td style="text-align: left;">Meter Failed Low and High Side</td> <td><? echo $row['bothFail'];?> </td> </tr> <tr> <td style="text-align: left;">Unable To Test</td> <td><? echo $row['unableTest'];?> </td> </tr> </table> So, for example, say I want to edit the data for just standards row and lowFail row. I tried this but it didn't work: <td><input type="text" name="test8TRGPM" value="<? echo $row['test8TestRateGPM'];?>"> </td> I know once this is done I'll need to have a submit button that attaches to a query to update with the input name, but right NOw i'm trying to find the best way to edit. Is it easiest to just wrap the whole page in a form and just create the few inputs where I need? -
Hi I have been working on my OOP and have put together some class files to aid my test application ( photo album ) on the upload page I have the browse box, a caption text box and an upload button this page posts to self, Once you click on upload it is also supposed to insert a database entry to allow tracking of the file's attributes, once I click the upload button I get this error message back, " Database Query Failed: Incorrect integer value ' ' for column 'id' at row 1 " so I have re looked over my codes in regards to uploading files and just can not seem to put my mouse on the spot that's causing me an issue so here is the codes that matter to the file uploads... this one is from my database.php class file, public function insert_id() { // get the last id inserted over the current db connection return mysql_insert_id($this->connection); } and this one is one comes from my photograph class file, public function create() { global $database; $attributes = $this->sanitized_attributes(); $sql = "INSERT INTO ".self::$table_name." ("; $sql .= join(", ", array_keys($attributes)); $sql .= ") VALUES ('"; $sql .= join("', '", array_values($attributes)); $sql .= "')"; if($database->query($sql)) { $this->id = $database->insert_id(); return true; } else { return false; } } Looking at my error and the information in those functions I can guess that's where the issue is coming from just don't get why any ideas please?
