wiki:sql
This is an old revision of the document!
Table of Contents
SQL
Create the SQL as a text variable, using in-line variable where required:
$sql = 'SELECT field FROM table WHERE 1';
DELETE or UPDATE
if ($mysqli->query($sql) === false) { $log = $_SERVER['PHP_SELF'].' SQL ERROR: '.$mysqli->error.' ** SQL: '.$sql; f_log($log); } else { $rows = $mysqli->affected_rows; }
INSERT
if ($mysqli->query($sql) === false) { $errmsg .= '<p class="bg-danger">TRX DESC: ' . $mysqli->error . '</p><p>' . $sql . '</p>'; } else { $newkey=$mysqli->insert_id; }
SELECT
if (($result = $mysqli->query($sql)) === false) { $errmsg .= '<p class=err><b>TRX DESC:</b> ' . $mysqli->error . '</p><p>' . $sql . '</p>'; echo $errmsg; } elseif (!$result->num_rows) { //no records } else { $row = $result->fetch_assoc(); while ($row = $result->fetch_assoc()) { } }
Bootstrap Version
if (($result = $mysqli->query($sql)) === false) { $log = $_SERVER['PHP_SELF'].' SQL ERROR: '.$mysqli->error.' ** SQL: '.$sql; f_log($log); } elseif (!$result->num_rows) { //no records } else { $row = $result->fetch_assoc(); while ($row = $result->fetch_assoc()) { } }
OLD Version
if (($result = mysql_query($sql)) == FALSE) { $error = mysql_error(); } elseif (!mysql_num_rows($result)) { } else { //$id = mysql_insert_id(); while ($row = mysql_fetch_assoc($result)) { } }
TRANSACTION
try { $mysqli->autocommit(false); //start the transaction //first SQL $sql = 'UPDATE tablename SET ' . 'field=1 ' . 'WHERE field=' . $target; if ($mysqli->query($sql) === false) { throw new Exception($mysqli->error); } //second SQL $sql = 'UPDATE tablename SET ' . 'field=2 ' . 'WHERE field=' . $target; if ($mysqli->query($sql) === false) { throw new Exception($mysqli->error); } //catch any errors } catch (Exception $e) { //roll back the changes thus far $mysqli->rollback(); //optionally terminate the page die($e); } //no errors found, so turn on autocommit which will also process all of the pending transactions $mysqli->autocommit(TRUE);
TRANSACTION (Alternate)
//first SQL $sql1 = 'UPDATE tablename SET ' . 'field=4 ' . 'WHERE field=' . $target; $sql2 = 'UPDATE tablename SET ' . 'field=5 ' . 'WHERE field=' . $target; try { $mysqli->autocommit(false); //start the transaction if ($mysqli->query($sql1) === false) { throw new Exception('<p class=err>TRX1: ' . $mysqli->error . '</p><p>' . $sql . '</p>'); } //a select transaction if (($result = $mysqli->query($sql)) === false) { throw new Exception('<p class=err>TRX2: ' . $mysqli->error . '</p><p>' . $sql . '</p>'); } //catch any errors } catch (Exception $e) { //roll back the changes thus far $mysqli->rollback(); //optionally terminate the page die($e); } //no errors found, so turn on autocommit which will also process all of the pending transactions $mysqli->autocommit(TRUE);
Copy Record
newtable also has an idfield that is auto-increment, primary key but different from the idfield of the original table
//copy the record to the deleted load table $sql = 'INSERT INTO newtable (`idfield`, `field2`, `field3`) '. 'SELECT `idfield`, `field2`, `field3` FROM originaltable WHERE idfield = '.$target;
Table Format
$page = '<div class="row">'.chr(13). '<div class="col-sm-9 col-sm-offset-1"></div>'.chr(13). '<div class="col-sm-2"><a href="admin_account_edit.php?id=0"><button class="btn btn-success btn-block">New</button></a></div>'.chr(13). '</div>'.chr(13). '<br />'; $page .= '<table name="A" id="A" class="table table-striped table-bordered table-hover table-responsive sortable" style="width: 100%">'.chr(13). '<thead>'.chr(13). '<tr>'.chr(13). '<th class="sorttable_nosort"></th>'.chr(13). '<th></th>'.chr(13). '<th></th>'.chr(13). '</tr>'.chr(13). '</thead>'.chr(13). '<tbody>'.chr(13); while ($row = $result->fetch_assoc()) { $page .= '<tr>'.chr(13). '<td sorttable_customkey="2">'.$row[''].'</td>' . chr(13) . '<td>'.$row[''].'</td>' . chr(13) . '<td>'.$row[''].'</td>' . chr(13) . '</tr>' . chr(13) . chr(13); } $page .= '</tbody>' . chr(13) . '</table>' . chr(13) . chr(13);
wiki/sql.1578347538.txt.gz · Last modified: 2024/03/05 12:58 (external edit)