Jump to content

Barand

Moderators
  • Content Count

    19,007
  • Joined

  • Last visited

  • Days Won

    357

Everything posted by Barand

  1. The time column in your db table needs to be DATETIME type and not INT. You also need to change your query so you have ... WHERE `time` BETWEEN = '2019-03-20 00:00:00' AND '2019-03-20 23:59:00' instead of using 1553040000 and 1553126340
  2. Barand

    import csv in mysql db using ajax and php

    - Now you have impoerted your data you should nomalize the data, so you would have +---------------+ +---------------+ | student | | subject | +---------------+ +---------------+ | student_id |---+ +-----| subject_id | | name | | +---------------+ | | name | | etc | | | score | | +---------------+ +---------------+ | +---------------+ | +---<| student_id | | | subject_id |>---+ | score | | exam_date | +---------------| and the data in those tables would look like this +-----+----------+ +-------------+----------------+---------+-----------------+ +--------------+-----------------+ | Id | name | | student_id | subject_id | score | exam_date | | subject_id | name | +-----+----------+ +-------------+----------------+---------+-----------------+ +--------------+-----------------+ | 1 | Name1 | | 1 | 1 | 10 | 2019-03-01 | | 1 | Physics | | 2 | Name2 | | 1 | 2 | 25 | 2019-03-01 | | 1 | Maths | +-----+----------+ | 1 | 3 | 35 | 2019-03-01 | | 1 | Chemistry | | 2 | 1 | 80 | 2019-03-01 | | | | | 2 | 2 | 45 | 2019-03-01 | | 3 | 3 | 45 | 2019-03-01 | | ... | ... | ... | ... |
  3. Barand

    Do you have a pet?

    A good idea. You still get your exercise and the dog gets out and about to experience all the local scents (his daily newspaper)
  4. I see your philosophy is "Why use one function when the job can be done with four" Examples: $end = strtotime(date('M')." ".date('d').", ".date('Y')." 11:59pm"); // is equivalent to $end = mktime(23,59,0); if (cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y')) == 31) { $array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0,'24' => 0 ,'25' => 0 ,'26' => 0 ,'27' => 0 ,'28' => 0 ,'29' => 0 ,'30' => 0 ,'31' => 0); }elseif (cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y')) == 30) { $array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0,'24' => 0 ,'25' => 0 ,'26' => 0 ,'27' => 0 ,'28' => 0 ,'29' => 0 ,'30' => 0); }elseif (cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y')) == 29) { $array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0,'24' => 0 ,'25' => 0 ,'26' => 0 ,'27' => 0 ,'28' => 0 ,'29' => 0); }elseif (cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y')) == 28) { $array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0,'24' => 0 ,'25' => 0 ,'26' => 0 ,'27' => 0 ,'28' => 0); } // is equivalent to $array = array_fill_keys(range(1, date('t')), 0); Stop using unix style timestamps and use MySQL datetime formats. (Y-m-d H:i:s)
  5. Barand

    Creating Google Charts with dynamic data

    We've got out sync. My line 35 hasn't got one of those.
  6. Nobody noticed, honest!
  7. You have single quotes arond "time" so it is being treated as a string literal. Use backticks like you have around the previous one.
  8. Barand

    Creating Google Charts with dynamic data

    Try deleting line 36 and then reentering it in case there's a hidden character lurking.
  9. Barand

    Creating Google Charts with dynamic data

    Here's the relevant bit <?php define("HOST",'localhost'); define("USERNAME",'????'); define("PASSWORD",'????'); define("DATABASE", "????"); function pdoConnect() { $dsn = "mysql:dbname=".DATABASE."; host=".HOST."; charset=utf8"; $db = new pdo($dsn, USERNAME, PASSWORD, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, ]); return $db; }
  10. Barand

    stored procedure for php pdo login

    The single quotes were actually backticks, but as validateUser isn't a reserved SQL keyword they are optional. Also the IN appears to be optional with MySql but I cannot vouch for MariaDB
  11. Barand

    Creating Google Charts with dynamic data

    Post your nameOptions function that is giving the error.
  12. Barand

    Creating Google Charts with dynamic data

    Have you got a missing ";" at the end of line 36?
  13. Barand

    stored procedure for php pdo login

    Do you already have a "validateUser" procedure? If so, try DROP procedure IF EXISTS `validateUser`; DELIMITER $$ CREATE PROCEDURE `validateUser`(user varchar(30)) BEGIN SELECT id, username, password FROM user_account WHERE username = user; END$$ DELIMITER ;
  14. Copy that query and execute it in phpMyAdmin. I am sorry we can't do it for you.
  15. Barand

    stored procedure for php pdo login

    What is the whole error message?
  16. Barand

    Creating Google Charts with dynamic data

    It was working for me, except I didn't get any x-axis labels under the column (more reading required). Here's my full code which is vitually the same as yours except for the ajax bit. Note I use the same php script for the ajax processing too. <?php include("db_inc.php"); $connect = pdoConnect(); // // HAVE WE RECEIVED AN AJAX REQUEST? // if (isset($_GET['ajax'])) { if(isset($_GET["name"])) { $query = " SELECT physics , maths , chemistry , biology , sst FROM csv WHERE name = ? ORDER BY id ASC "; $statement = $connect->prepare($query); $statement->execute( [ $_GET['name'] ] ); $result = $statement->fetchAll(); exit(json_encode($result)); } else exit("Error"); } function nameOptions($connect) { $query = "SELECT DISTINCT name FROM zeta ORDER BY name DESC "; $statement = $connect->query($query); $opts = ''; foreach ($statement as $s) { $opts .= "<option>{$s['name']}</option>\n"; } return $opts; } ?> <!DOCTYPE html> <html> <head> <title>Google charts</title> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"> <script src="https://code.jquery.com/jquery-1.12.4.js"></script> </head> <body> <br /><br /> <div class="container"> <div class="panel panel-default"> <div class="panel-heading"> <div class="row"> <div class="col-md-9"> <h3 class="panel-title">Student Wise Marks Data</h3> </div> <div class="col-md-3"> <select name="name" class="form-control" id="name"> <option value="">Select Student</option> <?=nameOptions($connect)?> </select> </div> </div> </div> <div class="panel-body"> <div id="chart_area" style="width: 1000px; height: 620px;"></div> </div> </div> </div> </body> </html> <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script> <script type="text/javascript"> google.charts.load('current', {packages: ['corechart', 'bar']}); google.charts.setOnLoadCallback(); function load_student_data(name, title) { var temp_title = title + ' '+name+''; $.ajax({ url:"", method:"GET", data:{ajax:1, name:name}, dataType:"JSON", success:function(data) { drawStudentwiseChart(data, temp_title); } }); } function drawStudentwiseChart(chart_data, chart_main_title) { var jsonData = chart_data; var data = new google.visualization.DataTable(); data.addColumn('string', 'Name'); // ADD A LABEL COLUMN data.addColumn('number', 'Physics'); data.addColumn('number', 'Maths'); data.addColumn('number', 'Chemistry'); data.addColumn('number', 'Biology'); data.addColumn('number', 'SST'); $.each(jsonData, function(i, jsonData){ var Physics = jsonData['physics']; // USE ARRAY NOTATION INSTEAD OF OBJECT NOTATION TO GET VALUES var Maths = jsonData['maths']; // (Note: my table's field names were in lowercase) var Chemistry = jsonData['chemistry']; var Biology = jsonData['biology']; var SST = jsonData['sst']; data.addRows([["",Physics,Maths,Chemistry,Biology,SST]]); // ADD VALUE FOR LABEL }); var options = { title:chart_main_title, hAxis: { title: "Subjects" }, vAxis: { title: 'Percentage' } }; var chart = new google.visualization.ColumnChart(document.getElementById('chart_area')); chart.draw(data, options); } </script> <script> $(document).ready(function(){ $('#name').change(function(){ var name = $(this).val(); if(name != '') { load_student_data(name, 'Student wise marks data'); } }); }); </script> Gives
  17. Barand

    stored procedure for php pdo login

    try DELIMITER $$ CREATE PROCEDURE `validateUser`(user varchar(30)) BEGIN SELECT id, username, password FROM user_account WHERE username = user; END$$ DELIMITER ; then $user = $_POST['username']; $pwd = $_POST['password']; $res = $db->query("CALL validateUser('$user')"); $user_data = $res->fetch(); if (password_verify($pwd, $user_data['password']) { // password and user OK }
  18. Barand

    Creating Google Charts with dynamic data

    I had play with google charts and made a couple changes to your function function drawStudentwiseChart(chart_data, chart_main_title) { var jsonData = chart_data; var data = new google.visualization.DataTable(); data.addColumn('string', 'Name'); // ADD A LABEL COLUMN data.addColumn('number', 'Physics'); data.addColumn('number', 'Maths'); data.addColumn('number', 'Chemistry'); data.addColumn('number', 'Biology'); data.addColumn('number', 'SST'); $.each(jsonData, function(i, jsonData){ var Physics = jsonData['physics']; // USE ARRAY NOTATION INSTEAD OF OBJECT NOTATION TO GET VALUES var Maths = jsonData['maths']; // (Note: my table's field names were in lowercase) var Chemistry = jsonData['chemistry']; var Biology = jsonData['biology']; var SST = jsonData['sst']; data.addRows([["",Physics,Maths,Chemistry,Biology,SST]]); // ADD VALUE FOR LABEL }); var options = { title:chart_main_title, hAxis: { title: "Subjects" }, vAxis: { title: 'Percentage' } }; var chart = new google.visualization.ColumnChart(document.getElementById('chart_area')); chart.draw(data, options); }
  19. Barand

    Creating Google Charts with dynamic data

    Don't use "SELECT star", specify the columns you need. Now you can just encode the results as it contains the columns required. Also you don't seem to have the hang of prepared queries. You don't put post data directly into the query (ever) but use placeholders and pass the values as parameters. if(isset($_POST["name"])) { $query = " SELECT physics , maths , chemistry , biology , sst FROM zeta WHERE name = ? ORDER BY id ASC "; $statement = $connect->prepare($query); $statement->execute( [ $_POST['name'] ] ); $result = $statement->fetchAll(); echo json_encode($result); }
  20. You can still preserve normalization A campus has many names (over time) A campus has many addresses (over time) campus_name +-----------+------------------+----------------+-----------------+ | campus_id | name | valid_from | valid_until | +-----------+------------------+----------------+-----------------+ | 1 | Name 1 | 2000-01-01 | 2019-02-18 | | 1 | Name 2 | 2019-02-19 | 2019-03-03 | | 1 | Name 3 | 2019-03-04 | 9999-12-31 | current campus_address +-----------+------------------+----------------+-----------------+ | campus_id | address | valid_from | valid_until | +-----------+------------------+----------------+-----------------+ | 1 | Address 1 | 2000-01-01 | 2019-02-28 | | 1 | Address 2 | 2019-03-01 | 9999-12-31 | current
  21. You could add an auto_increment id field as primary key or you could use PRIMARY KEY(client_id, valid_from)
  22. That would be easier if you had "campus_id" in your data. The usual way for data that changes over time (eg product price, employee salary etc) is +-----------+------------------+----------------+-----------------+ | client_id | name | valid_from | valid_until | +-----------+------------------+----------------+-----------------+ | 1 | Name 1 | 2000-01-01 | 2019-03-18 | ) valid until yesterday | 1 | Name 2 | 2000-03-19 | 9999-12-31 | ) new name valid until further notice | 2 | Name 3 | 2000-01--1 | 9999-12-31 | Has not changed yet
  23. Barand

    import csv in mysql db using ajax and php

    @taquitosensei - "continue" means continue to the next row, not continue processing this row. (The exact opposite of the way you are using it )
  24. Barand

    import csv in mysql db using ajax and php

    I asked because the data you posted does not match your code ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,Fields,Physics~75,Maths~50,Chemistry~65,Bio~85,SST~100 ,,,Name1,10,25,35,42,62 ,,,Name2,80,45,45,45,25 ,,,Name3,63,25,63,36,36 ,,,Name4,82,36,75,48,42 ,,,Name5,45,45,78,25,24 ,,,Name6,36,36,15,75,36 ,,,Name7,99,45,24,24,45 ,,,Name8,45,85,85,85,96 | | $row[3] $row[8] The name is in index position 3, not 0. Also, use prepared statements and use PDO instead of mysqli (it makes life easier) $db = pdoConnect(); $db->exec("CREATE TABLE IF NOT EXISTS zeta ( id int not null auto_increment primary key, name varchar(30), physics int, maths int, chemistry int, biology int, sst int ) "); $stmt = $db->prepare("INSERT INTO zeta (name,physics,maths,chemistry,biology,sst) VALUES (?,?,?,?,?,?) "); $fp = fopen('zeta.csv', 'r'); while ($row = fgetcsv($fp)) { if ($row[3]=='Fields' || $row[3]=='') continue; $stmt->execute(array_slice($row,3)); } fclose($fp); Giving mysql> select * from zeta; +----+-------+---------+-------+-----------+---------+------+ | id | name | physics | maths | chemistry | biology | sst | +----+-------+---------+-------+-----------+---------+------+ | 1 | Name1 | 10 | 25 | 35 | 42 | 62 | | 2 | Name2 | 80 | 45 | 45 | 45 | 25 | | 3 | Name3 | 63 | 25 | 63 | 36 | 36 | | 4 | Name4 | 82 | 36 | 75 | 48 | 42 | | 5 | Name5 | 45 | 45 | 78 | 25 | 24 | | 6 | Name6 | 36 | 36 | 15 | 75 | 36 | | 7 | Name7 | 99 | 45 | 24 | 24 | 45 | | 8 | Name8 | 45 | 85 | 85 | 85 | 96 | +----+-------+---------+-------+-----------+---------+------+
  25. Barand

    import csv in mysql db using ajax and php

    Can you post the first few lines of your input CSV file so we can see what you are trying to process?
×

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.