User Tools

Site Tools


wiki:sql

This is an old revision of the document!


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)