Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,572

  • Joined

  • Last visited

  • Days Won

    824

 Content Type 

Everything posted by Barand

  1. Barand
    One option would be to take your approach - totally ignore the book then every day get on the phone to you saying "Hey, get your arse down to the court and coach these kids for me". And those needs would be what? Define "entirely". If the combination of (firstName, LastName) is defined as unique then it will only allow one record. If you have "Jon Doe" on the team it won't allow another "John Doe" to be added. With IGNORE it ignores the second John Doe and continues adding the rest of the players' records. Without the ignore the query would fail with an error.
  2. Barand
    If you use the ignore modifier, ignorable errors that occur while executing the insert statement are ignored. For example, without IGNORE, a row that duplicates an existing unique index or primary key value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs.
  3. Barand
    Javascript runs on the client, PHP on the server, so you can't incorporate PHP code inside JS. What you can do is submit an AJAX request from JS to the server, process it with PHP and send back the results as a response. In JQuery, look at $.ajax() $.get() $.post() Here's a very basic example <?php if (isset($_GET['ajax'])) { // I like to tell my script it's reciving AJAX $x = $_GET['x'] ?? 0; exit("$x squared is " . ($x**2)); // when process an AJAX request, anything that would normally be sent to the screen } // is sent back in a response message // rest of php code here ?> <html> <head> <title>Example</title> <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.0/jquery.min.js"></script> <script type='text/javascript'> $().ready( function() { $("#btnSub").click( function() { $.get ( "", // target script is "self" {"ajax":1, "x":$("#x").val() }, // data to send function(resp) { // process response $("#result").html(resp); }, "TEXT" // response type ) }) }) </script> </head> <body> Input a number <input type='text' id='x' value='0'> <br> <button id='btnSub'>Get Square</button> <hr> <div id='result'></div> </body> </html>
  4. Barand
    Just like it says in the manual. The query is a multiple insert like this, which insert 3 records with a single insert query, and is many times faster than several single record inserts. INSERT INTO tableX (colA, colB) VALUES (1, 'A'), (2, 'B'), (3, 'C'); Because it's a prepared query it looks a little different though - the $holders would be the array [ "(?,?)","(?,?)", "(?,?)" ] and the $vals contain [ 1, 'A', 2, 'B', 3, 'C' ] (ie a value for each of the placeholders)
  5. Barand
    I decided to get it working using your method and, as a comparison, created a separate pair of tables (player2, roster2) into which I add the data using my method. I timed the processing when adding 2, 10 and 20 records to the roster. Data results were identical in both cases. Processing times (as expected) were different. The code... <?php if ($_SERVER['REQUEST_METHOD']=='POST') { $schoolID = $_POST['school']; $varsity = $_POST['varsity']; $season = $_POST['season']; ## ## Jimr mysqli code ## $jimt1 = microtime(1); $player = $con->prepare("INSERT INTO player (schoolID,nameFirst,nameLast,feet,inches,grade,position) VALUES (?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE feet = VALUES(feet), inches = VALUES(inches), grade = VALUES(grade), position = VALUES(position) "); $player->bind_param('issiiis', $schoolID,$fname,$lname,$feet,$inches,$grade,$position); $pid = $con->prepare("SELECT id FROM player WHERE nameFirst = ? AND nameLast = ? AND schoolID = ?"); $pid->bind_param('ssi',$fname,$lname,$schoolID); $roster = $con->prepare("INSERT INTO roster (schoolID, playerID, uniform, varsity, season) VALUES (?,?,?,?,?) "); $roster->bind_param('sissi', $schoolID, $playerID, $uniform, $varsity, $season); foreach ($_POST['uniform'] as $k => $uniform) { $fname = $_POST['nameFirst'][$k]; $lname = $_POST['nameLast'][$k]; $feet = $_POST['feet'][$k]; $inches = $_POST['inches'][$k]; $grade = $_POST['grade'][$k]; $position = $_POST['position'][$k]; $player->execute(); $pid->execute(); $pid->bind_result($playerID); $pid->fetch(); $pid->reset(); $roster->execute(); } $jimt2 = microtime(1); printf("\nJIMR time to process: %0.3f seconds\n\n", $jimt2 - $jimt1); ## ## My PDO method ## $t1 = microtime(1); $db->exec("CREATE TEMPORARY TABLE temp_csv ( id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, schoolid int(11) DEFAULT NULL, varsity varchar(10) DEFAULT NULL, season int, uniform varchar(45) DEFAULT NULL, nameFirst varchar(45) DEFAULT NULL, nameLast varchar(45) DEFAULT NULL, feet tinyint(4) DEFAULT NULL, inches tinyint(4) DEFAULT NULL, grade int(11) DEFAULT NULL, position varchar(15) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 "); $sql = "INSERT INTO temp_csv (schoolid, varsity, season, uniform, nameFirst, nameLast, feet, inches, grade, position) VALUES \n"; $holders = $vals = []; foreach ($_POST['uniform'] as $k => $uniform) { $fname = $_POST['nameFirst'][$k]; $lname = $_POST['nameLast'][$k]; $feet = $_POST['feet'][$k]; $inches = $_POST['inches'][$k]; $grade = $_POST['grade'][$k]; $position = $_POST['position'][$k]; $holders[] = "(?,?,?,?,?,?,?,?,?,?)"; array_push($vals, $schoolID, $varsity, $season, $uniform, $fname, $lname, $feet, $inches, $grade, $position); } $stmtc = $db->prepare($sql . join(',', $holders)); $stmtc->execute($vals); $db->exec("INSERT IGNORE INTO player2 (schoolID,nameFirst,nameLast,feet,inches,grade,position) SELECT schoolid , nameFirst , nameLast , feet , inches , grade , position FROM temp_csv "); $db->exec("INSERT IGNORE INTO roster2 (schoolID, playerID, uniform, varsity, season) SELECT t.schoolid , p.id , t.uniform , t.varsity , t.season FROM temp_csv t JOIN player2 p ON t.nameFirst = p.nameFirst AND t.nameLast = p.nameLast; "); $t2 = microtime(1); printf("\nMy time to process: %0.3f seconds\n\n", $t2 - $t1); } ?> <!DOCTYPE html> <html> <head> <meta http-equiv="content-language" content="en"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.15.1/css/all.css"> <title>Example</title> <meta name="creation-date" content="11/23/2020"> <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.0/jquery.min.js"></script> <script type='text/javascript'> $().ready( function() { $("#add-player").click( function(e) { e.preventDefault() $("#myRepeatingFields").append('<div class="entry input-group col-xs-3">' + '<input class="form-control" name="uniform[]" type="text" placeholder="Uni #" />&nbsp;' + '<input class="form-control" name="nameFirst[]" type="text" placeholder="First Name" />&nbsp;' + '<input class="form-control" name="nameLast[]" type="text" placeholder="Last Name" />&nbsp;' + '<input class="form-control" name="feet[]" type="text" placeholder="Feet" />&nbsp;' + '<input class="form-control" name="inches[]" type="text" placeholder="Inches" />&nbsp;' + '<input class="form-control" name="grade[]" type="text" placeholder="Grade" />&nbsp;' + '<input class="form-control" name="position[]" type="text" placeholder="Position" />' + '</div>'); }) }) </script> </head> <body> <form action="" method="post"> <fieldset> <legend>Roster</legend> School ID <input type="text" name="school"><br> Varsity <input type="text" name="varsity"><br> Season <input type="text" name="season"><br> </fieldset> <fieldset><br> <legend>Players</legend> <div id="myRepeatingFields"> <div class="entry input-group col-xs-3"> <input class="form-control" name="uniform[]" type="text" placeholder="Uni #" /> <input class="form-control" name="nameFirst[]" type="text" placeholder="First Name" /> <input class="form-control" name="nameLast[]" type="text" placeholder="Last Name" /> <input class="form-control" name="feet[]" type="text" placeholder="Feet" /> <input class="form-control" name="inches[]" type="text" placeholder="Inches" /> <input class="form-control" name="grade[]" type="text" placeholder="Grade" /> <input class="form-control" name="position[]" type="text" placeholder="Position" /> </div> </div> </fieldset><br> <button id='add-player' title='add new player entry'><i class='fas fa-plus-circle'></i></button> <input type="submit" value="Send Roster" name="submit"> </form> </body> </html>
  6. Barand
    I use MySQL Workbench - it's free. I've also been known to us a And there's an SQL tutorial in my sig. which may help.
  7. Barand
    Then the sole reason for your making a dog's breakfast out of your DB design is purely laziness? I think I'll be lazy too and go and watch some TV.
  8. Barand
    1 ) So sometimes a player's varsity status in the roster table can be different from their varsity status in their player record? 2 ) Will there be a mix of players from different schools playing in a school's team?
  9. Barand
    Looking at your recent code you have schooID and varsity in both the player table and the roster table - why? Didn't you once have a "team" column in the roster table? Perhaps it might be an idea for you now to disclose the structures of those two tables.
  10. Barand
    Put us out of the our misery, the suspense is too much. Is that player_id correct or not?
  11. Barand
    According to the manual (your best friend)...
  12. Barand
    OK, now it's your turn again.
  13. Barand
    Sorry. This precisely why I hate mysqli - it's the result object that has the fetch_assoc() method but we are using a mysqli statement object with a different set of methods (in PDO they're the same animal). Try In the FOREACH loop $pid->execute(); $pid->bind_result($player_id); $row = $pid->fetch();
  14. Barand
    Try #In the FOREACH loop $pid->execute(); $row = $pid->fetch_assoc(); $player_id = $row['id'];
  15. Barand
    So I'm gussing either that's a load of bovine scatology or it simplifies down to the that you used in the table you posted.
  16. Barand
    Why are you using a reference to PDO when you have a mysqli connection? fetchColumn() is a PDO method, mysqli uses different methods. Again, I refer you to the manual. You should try it.
  17. Barand
    I'm curious - in what world does a total of temperature + humidity + pressure + power consumption make sense?
  18. Barand
    It's nothing to do with subqueries. When you do a SELECT query it returns a resultset. You need to process the result to get the values. (EG fetch a row ). $playerID value won't appear by magic.
  19. Barand
    You are allowed to read the manual https://www.php.net/manual/en/mysqli-stmt.bind-param.php
  20. Barand
    There are two ways to avoid sql injection by separating the values from the text of the sql query prepared statements stored procedures Both accomplish the same task. To prepare a stored procedure and bind the parameters seems like a "belt and braces" approach to me.
  21. Barand
    Perhaps $STHA = $DBH2->query("exec pEmployeeGetData '1','2020' "); $row = $STHA->fetch();
  22. Barand
    I don't have SQL Server but the MySQL equivalent using PDO is $result = $db->query("call average_result(1, 2015)"); $row = $result->fetch(); echo "{$row['name']}<br>{$row['avresult']}%<br>"; // Adam Simms // 77.0% procedure: CREATE PROCEDURE `average_result`(IN pupil INT, IN year INT) BEGIN SELECT concat(fname, ' ', lname) as name , ROUND(AVG(pcent),1) as avresult FROM result r JOIN pupil p ON r.pupilid = p.pupilid WHERE r.pupilid = pupil AND r.schoolyear = year; END
  23. Barand
    That is PDO code. Isn't your $con a mysqli connection?
  24. Barand
    Good, it seems you've realized that SQL code is not PHP code, but do you really think that defining a string variable will cause the query to be run? It needs to be processed by the DB server. EG $con->query($player); Stop just throwing code at the page and think.
  25. Barand
    ROFL I give up.
×
×
  • 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.