MySQL-PHP Queries Notes

Select Data

$sql = "SELECT $cols FROM $table";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
	while($row = mysqli_fetch_assoc($result)) {
		echo $row['col_name'];
	}
} 
else {
	echo "0 results";
}

—only count rows of result

if ($result=mysqli_query($conn,$sql)){
	$rowcount=mysqli_num_rows($result);
	printf("Result set has %d rows.\n",$rowcount);
	mysqli_free_result($result);
}

—only count fields of result

if ($result=mysqli_query($cnon,$sql)){
	$fieldcount=mysqli_num_fields($result);
	printf("Result set has %d fields.\n",$fieldcount);
	mysqli_free_result($result);
}

—affected rows

mysqli_query( $conn, "SELECT * FROM $table");
echo "Affected rows: " . mysqli_affected_rows($conn);

—escape

mysqli_real_escape_string($conn, $_POST['field']);

Update data

$sql = "UPDATE $table SET column='Value' WHERE id=1";

if (mysqli_query($conn, $sql)) {
	echo "Record updated successfully";
} else {
	echo "Error updating record: " . mysqli_error($conn);
}

—append data

UPDATE Table SET Field=CONCAT(Field,'your extra html');

Insert data

$sql = "INSERT INTO MyGuests (column1, column2, column3)
VALUES ('$value1','$value2','$value3')";

if (mysqli_query($conn, $sql)) {
	//$last_id = mysqli_insert_id($conn); get last inserted id
	echo "New record created successfully";
} else {
	echo "Error: " . $sql . "
" . mysqli_error($conn);
}

Delete data

$sql = "DELETE FROM $table WHERE id=3";

if (mysqli_query($conn, $sql)) {
	echo "Record deleted successfully";
} else {
	echo "Error deleting record: " . mysqli_error($conn);
}