Update one table from another, uses an implicit join 

UPDATE table1 AS A, table2 AS B 
    SET A.fieldx = B.fieldz
    WHERE A.field7 = B.field3 and A.field2 = 'not me'  --  etc...
 

Make the first character of the value of a field upper-case 

UPDATE Valor.Table
SET `size` = CONCAT(UCASE(LEFT(`size`, 1)), SUBSTRING(`size`, 2));

 

PHP, update a table. No return values. 

$updatequery = "[DELETE FROM | INSERT INTO | UPDATE] Valor.Table [fieldx fieldz VALUES (?, ?) | SET fieldx = ?] WHERE field1 = ?;";
$stmt_update = $conn->prepare($updatequery);
$stmt_update->bind_param("s[sss]", $arg1[, $var2, $var3, $var4]);
--------------------
$stmt_update->execute();
$stmt_update->close();
 


Stack Overflow on calling Stored Procs in PHP multiple times


 
PHP, all possibilities wrapped in functions
  1. function to run a query or call a stored proc from which we do not need the return sets (insert, update, delete)
  2. function to run a query or call a stored proc which returns a single value
  3. function to run a query or call a stored proc which returns a full result set
Resources are free/destroyed when the function exits

There are two benefits to using prepared queries
  1. Varaible substitution into the query is automatically properly formatted
  2. Performance improvement when the same prepared statement is used multiple times
      The second benefit is lost when the "prepare" is done in a function. So if the same query is called many times, don't use these function wrappers
      Also, if very large sets are returned, might also want to not use a function wrapper, and use fetch_assoc() in loop control
            instead of the fetch_all before the loop, which gets the the remaining (if fetch_assoc was already used on it) result set all at once 
function modifyDB1($query, $arg1){
    //  $query = "[DELETE FROM | INSERT INTO | UPDATE] Valor.Table [fieldx fieldz VALUES (?, ?) | SET fieldx = ?] WHERE field1 = ?;";
    //   - OR -
    //  $query = "CALL Valor.SP(?);"
    global $conn;
    $stmt = $conn->prepare($query);
    $stmt->bind_param("s", $arg1);
    $stmt->execute();
}

function getValueFromDB1($query, $arg1){
    //  $query = "SELECT field1 FROM Valor.Table WHERE field2 = ? LIMIT 1;"   ||   "CALL Valor.SP(?);"
    global $conn;
    $stmt = $conn->prepare($query);
    $stmt->bind_param("s", $arg1);
    $stmt->execute();
    return $stmt->get_result()->fetch_all()[0][0];
}
TENTATIVE: 
function getResultSet1($query, $arg1){
    $stmt = $conn->prepare($query);  
    $stmt->bind_param("s", $arg1); 
    $stmt->execute();
    $currresult = $stmt->get_result();  // i.e. the current (first) result set we are on in $stmt
    return $currresult->fetch_all(); // fetch all rows
    //  These are now not necessary, because done by object destruction when function exits
        //  $currresult->free();
        //  $stmt->next_result(); //  Will get an error on the next call if we don't advance to the final result. 
        //  $stmt->close();
}
$fetchedset = getResultSet($query, $arg1);
foreach($fetchedset as $row){
    $val1 = $row[0];
    $val2 = $row[1];
}
 

PHP, prepared query calling a stored procedure. Simplist case: One row with one field is returned.
Stored procedures always return at least two sets:

  1. One result set for each SELECT statement
  2. One empty(?) set for the CALL's return value/set
The [0][0] subscripts in fetchedset reference the row and the field 

$sp_getvalue = $conn->prepare("CALL Valor.GetValue(?);");  
$sp_getvalue->bind_param("s", $arg1); 
--------------------
$sp_getvalue->execute();
$currresult = $sp_getvalue->get_result();  // i.e. the current (first) result set we are on in $sp_getvalue
$fetchedset = $currresult->fetch_all();
$value      = $fetchedset[0][0];  // "ASSOCIATIVE ARRAY" MY ASS                           
$currresult->free();
$sp_getvalue->next_result(); //  Will get an error on the next call if we don't advance to the final result. 
--------------------
$sp_getvalue->close();
 

PHP, prepared query calling a stored procedure which returns multiple rows.
Stored procedures always return at least two sets:

  1. One result set for each SELECT statement
  2. One empty(?) set for the CALL's return value/set

$sp_getresults = $conn->prepare("CALL Valor.SP(?);");  
$sp_getresults->bind_param("s", $arg1); 
--------------------
$sp_getresults->execute();
$currresult = $sp_getresults->get_result();  // i.e. the current (first) result set we are on in $sp_getresults
$fetchedset = $currresult->fetch_all(); // fetch all rows
// can now free some resources
$currresult->free();
$sp_getresults->next_result(); //  Will get an error on the next call if we don't advance to the final result. 
foreach($fetchedset as $row){
    $val1 = $row[0];
}
--------------------
$sp_getresults->close();
 

TENTATIVE:
PHP, a FUNCTION for prepared query calling a stored procedure which returns multiple rows.
Stored procedures always return at least two sets:

  1. One result set for each SELECT statement
  2. One empty(?) set for the CALL's return value/set

function runstoredprocedure($arg1){
    $sp_getresults = $conn->prepare("CALL Valor.SP(?);");  
    $sp_getresults->bind_param("s", $arg1); 
    $sp_getresults->execute();
    $currresult = $sp_getresults->get_result();  // i.e. the current (first) result set we are on in $sp_getresults
    return $currresult->fetch_all(); // fetch all rows
    //  These are now not necessary, because done by object destruction when function exits
        //  $currresult->free();
        //  $sp_getresults->next_result(); //  Will get an error on the next call if we don't advance to the final result. 
        //  $sp_getresults->close();
}
$fetchedset = runstoredprocedure($arg1);
foreach($fetchedset as $row){
    $val1 = $row[0];
}


 
 
 

PHP, get a count from a table. Uses bind_result, which I don't use anywhere else. Should I start using this? 

$query = "SELECT COUNT(*) FROM Valor.Table WHERE field1 = ? AND field2 = ?;";
$stmt = $conn->prepare($query);
$stmt->bind_param("ss", $arg1, $var2);
--------------------
$stmt->execute();
$stmt->bind_result($count);
$stmt->fetch();
$stmt->close();