setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e) {
echo 'Attenzione errore: '.$e->getMessage();
}
?>
*********************************************************************
PDO queries:
1) DB connection:
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e) {
echo 'Attenzione errore: '.$e->getMessage();
}
?>
2) select query
con ciclo while:
$sql ='SELECT id, titolo, url, immagine, active, data_insert FROM skin WHERE 1 ORDER BY data_insert desc';
$stmt = $db->prepare($sql);
$stmt->execute();
$num_rows=$stmt->rowCount();
while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
}
solo 1 record:
$sql ='SELECT id, titolo, url, immagine, active, data_insert FROM skin WHERE 1 AND id = :id ORDER BY data_insert desc';
$stmt = $db->prepare($sql);
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();
$row=$stmt->fetch(PDO::FETCH_ASSOC);
3) insert query
$id=$_REQUEST['id'];
$titolo=$_REQUEST['titolo'];
$url=$_REQUEST['url'];
$padding=$_REQUEST['padding'];
$sfondo=$_REQUEST['sfondo'];
$active=$_REQUEST['active'];
$sql = 'INSERT INTO skin(titolo, url, padding, sfondo, active, data_insert) values(:titolo,:url,:padding,:sfondo:active,now())';
$stmt = $db->prepare($sql);
$stmt->bindParam(':titolo', $titolo, PDO::PARAM_STR);
$stmt->bindParam(':url', $url, PDO::PARAM_STR);
$stmt->bindParam(':padding', $padding, PDO::PARAM_INT);
$stmt->bindParam(':sfondo', $sfondo, PDO::PARAM_STR);
$stmt->bindParam(':active', $active, PDO::PARAM_INT);
$stmt->execute();
$id_skin_appena_aggiunto = $db->lastInsertId();
$sql = 'UPDATE skin SET immagine= :new_name WHERE id= :id_skin_appena_aggiunto';
$stmt = $db->prepare($sql);
$stmt->bindParam(':new_name', $new_name, PDO::PARAM_STR);
$stmt->bindParam(':id_skin_appena_aggiunto', $id_skin_appena_aggiunto, PDO::PARAM_STR);
$stmt->execute();
4) update query
$sql='UPDATE skin SET
titolo= :titolo,
url= :url,
padding= :padding,
sfondo= :sfondo,
active= :active,
updated_at= now()
WHERE id= :id LIMIT 1';
$stmt = $db->prepare($sql);
$stmt->bindParam(':titolo', $titolo, PDO::PARAM_STR);
$stmt->bindParam(':url', $url, PDO::PARAM_STR);
$stmt->bindParam(':padding', $padding, PDO::PARAM_INT);
$stmt->bindParam(':sfondo', $sfondo, PDO::PARAM_STR);
$stmt->bindParam(':active', $active, PDO::PARAM_INT);
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();
5) delete query
$sql='DELETE FROM skin WHERE id=:id LIMIT 1';
$stmt = $db->prepare($sql);
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();
------------------
*********************************************************************
//*******************round per season (parametro: id season)
function get_rounds(){
global $db,$path,$strAuth;
$oggi=date("Y-m-d");
//$sql='select id_season, inizio, fine, id_competition from ws_seasons where abilitato="1" AND inizio<="'.$oggi.'" AND fine>="'.$oggi.'"';
$sql='select id_season, inizio, fine, id_competition from ws_seasons where abilitato="1" ';
$stmt = $db->prepare($sql);
$stmt->execute();
while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
$file = $path.'get_rounds?season_id='.$row['id_season'].'&'.$strAuth;
$fp=file_get_contents($file);
$xml = simplexml_load_string($fp);
$itemRounds = $xml->xpath('//round');
foreach($itemRounds as $itemRound){
$sql='select id from ws_round where id_round='.$itemRound['round_id'].'';
$stmtC = $db->prepare($sql);
$stmtC->execute();
if($stmtC->rowCount()==0){
$sql='insert into ws_round(id_competition, id_season, id_round, nome, data_inizio, data_fine) values("'.$row['id_competition'].'", "'.$row['id_season'].'", "'.$itemRound['round_id'].'", "'.$itemRound['name'].'", "'.$itemRound['start_date'].'", "'.$itemRound['end_date'].'")';
$stmtx = $db->prepare($sql);
$stmtx->execute();
echo $sql.'
';
}
else{
$sql='update ws_round set id_competition="'.$row['id_competition'].'", id_season="'.$row['id_season'].'", nome="'.$itemRound['name'].'", data_inizio="'.$itemRound['start_date'].'", data_fine="'.$itemRound['end_date'].'" where id_round='.$itemRound['round_id'].' limit 1';
$stmty = $db->prepare($sql);
$stmty->execute();
echo $sql.'
';
}
}
}
}
—————————————————————————————————————————————
http://www.mrwebmaster.it/php/prepared-statements-pdo_11901.html
—————————————————————————————————————————————
db: babita11_pdo
u: babita11_pdo11
p = -_-.seMmokTeLlL011odigoh3h33..-_-
--------------------------------
- What is PDO? access layer
Why?
- secure against SQl injection attacks
- they are resonably fast
- support multiple database servers
- exceptions handling
- object oriented approach
dbname = babita11_pdo
user = babita11_pdo11
pass = _5sSwe.-.is5St1ger_11.2dDe3
Add User To Database
Manage User Privileges
User: babita11_pdo11
Database: babita11_pdo
User “babita11_pdo11” could not be added to the database “babita11_pdo”. (XID m4rv99) Database Error: Can't find any matching row in the user table
$con = new PDO("mysql:host=localhost;dbname=babita11_pdo","root","root");
if($con){
echo "We are connected";
}
—————————————————————————————————————————————
http://www.mrwebmaster.it/php/prepared-statements-pdo_11901.html
—————————————————————————
// definizione delle variabili per la query
$contatto_cognome = "Pellico";
$contatto_anni_vissuti = 64;
// preparazione della query SQL
$sql = $connessione->prepare("SELECT nome FROM contatti WHERE cognome = :contatto_cognome AND anni_vissuti = :contatto_anni_vissuti");
// bind dei parametri
$sql->bindParam(':contatto_cognome', $contatto_cognome, PDO::PARAM_STR, 7);
$sql->bindParam(':contatto_anni_vissuti', $contatto_anni_vissuti, PDO::PARAM_INT);
$sql->execute();