Acá tenés un par de ejemplos con PDO de un sistema que hice hace rato.
PHP Code:
define("DB_HOST", "localhost");
define("DB_USER", "root");
define("DB_PASS", "root");
define("DB_NAME", "facturacion");
$odb = new PDO( "mysql:host=".DB_HOST.";dbname=".DB_NAME, DB_USER, DB_PASS );
function getHash($pass){
return hash('sha512', substr(hash('sha512', $pass.SALT), 0, 500));
}
function getIP(){
return $_SERVER['REMOTE_ADDR'];
}
function createLoginLog($user, $success){
global $odb;
$query = $odb->prepare("INSERT INTO login_logs (user, fecha, ip, success) VALUES (:user, :fecha, :ip, :success)");
$query->execute(Array(":user" => $user, ":fecha" => time(), ":ip" => getIP(), ":success" => $success));
}
function doLogin($user, $pass){
global $odb;
$user = substr($user, 0, 16);
$user = preg_replace("/[^0-9a-zA-Z ]/m", "", $user);
$user = preg_replace("/ /", "-", $user);
$pass = getHash($pass);
$query = $odb->prepare("SELECT id FROM usuarios WHERE user = :user AND password = :password");
$query->execute(Array(":user" => $user, ":password" => $pass));
if ($query->rowCount()){
$row = $query->fetch(PDO::FETCH_ASSOC);
}
return $query->rowCount() ? 1 : 0;
}
function cargarTurno(){
global $odb;
$query = $odb->prepare("SELECT * FROM turnos WHERE fin = '0' ORDER BY inicio DESC LIMIT 1");
$query->execute();
if ($query->rowCount()){
$row = $query->fetch(PDO::FETCH_ASSOC);
$_SESSION['userid'] = $row['userid'];
$_SESSION['turnoid'] = $row['id'];
} else {
unset($_SESSION['userid']);
unset($_SESSION['turnoid']);
return false;
}
return true;
}
function actualizarTurno($turnoid, $virtual, $sube, $proveedores, $caja){
global $odb;
$query = $odb->prepare("UPDATE turnos SET fin = :fin, virtual = :virtual, sube = :sube, proveedores = :proveedores, caja = :caja");
$query->execute(Array(":fin" => time(), ":virtual" => $virtual, ":sube" => $sube, ":proveedores" => $proveedores, ":caja" => $caja));
unset($_SESSION['userid']);
unset($_SESSION['turnoid']);
return "success";
}
function nuevoTurno($empID){
global $odb;
$query = $odb->prepare("INSERT INTO turnos (userid, inicio) VALUES (:userid, :inicio)");
$query->execute(Array(":userid" => $empID, ":inicio" => time()));
$query = $odb->prepare("SELECT * FROM turnos WHERE fin = '0' ORDER BY inicio DESC LIMIT 1");
$query->execute();
$row = $query->fetch(PDO::FETCH_ASSOC);
$_SESSION['turnoid'] = $row['id'];
$_SESSION['userid'] = $empID;
return "success";
}
function getEmpleados(){
global $odb;
$empleados = Array();
$query = $odb->prepare("SELECT * FROM encargados WHERE activo='1'");
$query->execute();
while($row = $query->fetch(PDO::FETCH_ASSOC)){
$empleados[$row['id']] = $row['nombre'];
}
return $empleados;
}
function getTipoProductos(){
global $odb;
$tipos = Array();
$query = $odb->prepare("SELECT * FROM tipo_productos");
$query->execute();
while($row = $query->fetch(PDO::FETCH_ASSOC)){
$tipos[$row['id']] = $row['nombre'];
}
return $tipos;
}
function getDataProductos(){
global $odb;
$cod = Array();
$desc = Array();
$tipo = Array();
$stock = Array();
$precio = Array();
$noCod = Array();
$query = $odb->prepare("SELECT * FROM productos");
$query->execute();
while($row = $query->fetch(PDO::FETCH_ASSOC)){
$cod[$row['id']] = $row['cod'];
$desc[$row['id']] = $row['descripcion'];
$tipo[$row['id']] = intval($row['tipo']);
$stock[$row['id']] = intval($row['stock']);
$precio[$row['id']] = floatval($row['precio']);
$noCod[$row['id']] = intval($row['nocod']);
}
return Array($cod, $desc, $tipo, $stock, $precio, $noCod);
}
function getVentas(){
global $odb;
$itemid = Array();
$cant = Array();
$precio = Array();
$index = Array();
$query = $odb->prepare("SELECT * FROM ventas WHERE actual='1'");
$query->execute();
$contador = 0;
while($row = $query->fetch(PDO::FETCH_ASSOC)){
$itemid[$contador] = intval($row['itemid']);
$cant[$contador] = intval($row['cantidad']);
$precio[$contador] = floatval($row['precio']);
$index[$contador] = intval($row['id']);
$contador++;
}
return $query->rowCount() ? Array($itemid, $cant, $precio, $index) : false;
}
function getTotalVentas(){
global $odb;
$total = 0.00;
$query = $odb->prepare("SELECT * FROM ventas WHERE turnoid = :turnoid");
$query->execute(Array(":turnoid" => $_SESSION['turnoid']));
while($row = $query->fetch(PDO::FETCH_ASSOC)){
$total += ($row['precio']*intval($row['cantidad']));
}
return $total;
}
function nuevaVenta($itemid, $cantidad, $precio){
global $odb;
$query = $odb->prepare("INSERT INTO ventas (turnoid, itemid, cantidad, precio, hora, actual) VALUE (:turnoid, :itemid, :cantidad, :precio, :hora, :actual)");
$query->execute(Array(':turnoid' => $_SESSION['turnoid'], ':itemid' => $itemid, ':cantidad' => $cantidad, ':precio' => $precio, ':hora' => time(), ':actual' => '1'));
$query = $odb->prepare("SELECT id FROM ventas WHERE itemid = :itemid AND actual = '1'");
$query->execute(Array(':itemid' => $itemid));
$row = $query->fetch(PDO::FETCH_ASSOC);
return intval($row['id']) ? intval($row['id']) : 0;
}
function updateVenta($id, $cantidad){
global $odb;
$query = $odb->prepare("UPDATE ventas SET cantidad = :cantidad WHERE id = :id");
return $query->execute(Array('id' => $id, ':cantidad' => $cantidad)) ? "success" : "fail";
}
function existeVenta($itemid){
global $odb;
$query = $odb->prepare("SELECT id FROM ventas WHERE itemid = :itemid AND actual = '1'");
$query->execute(Array(':itemid' => $itemid));
$row = $query->fetch(PDO::FETCH_ASSOC);
return intval($row['id']) ? intval($row['id']) : 0;
}
function borrarTodo(){
global $odb;
$query = $odb->prepare("SELECT * FROM ventas WHERE actual='1'");
$query->execute();
$result = "success";
$temp = "";
while($row = $query->fetch(PDO::FETCH_ASSOC)){
$query2 = $odb->prepare("DELETE FROM ventas WHERE id = :id");
$temp = $query2->execute(Array(':id' => intval($row['id']))) ? "success" : "fail";
if ($result == "success" && $temp == "fail"){
$result = $temp;
}
}
return $result;
}
function guardarVenta(){
global $odb;
$query = $odb->prepare("UPDATE ventas SET actual='0' WHERE actual='1'");
$query->execute();
$result = "success";
return $result;
}
function updateDesc($cod, $desc){
global $odb;
$query = $odb->prepare("UPDATE productos SET descripcion=:descripcion WHERE cod=:cod LIMIT 1");
$query->execute(Array(":descripcion" => $desc, ":cod" => $cod));
$error = $query->errorInfo();
return $error[2];
}
function updatePrecio($cod, $precio){
global $odb;
$query = $odb->prepare("UPDATE productos SET precio=:precio WHERE cod=:cod LIMIT 1");
$query->execute(Array(":precio" => $precio, ":cod" => $cod));
$error = $query->errorInfo();
return $error[2];
}
function insertProducto($cod, $name, $tipo, $precio){
global $odb;
$query = $odb->prepare("INSERT INTO productos (cod, descripcion, tipo, stock, precio, nocod) VALUES (:cod, :desc, :tipo, '0', :precio, '0')");
$query->execute(Array(":precio" => $precio, ":cod" => $cod, ":desc" => $name, ":tipo" => $tipo));
$error = $query->errorInfo();
return $error[2];
}
function getNombreEmpelado($userid){
global $odb;
$query = $odb->prepare("SELECT nombre FROM encargados WHERE id = :id");
$query->execute(Array(":id" => $userid));
$row = $query->fetch(PDO::FETCH_ASSOC);
return $row['nombre'];
}