query error PHP mysql
Hejsan!
Jag har lite mycket trubbel just nu. Jag har problem att läga in data I min databas.
Jag har query:
$query = "INSERT INTO loan(item, user, returndate) VALUES (:item, :user, :returndate)";
och jag försöker att lägga in data I den som jag hämtat upp från API
Som följande:
$xml = simplexml_load_file('https://www.yr.no/sted/Norge/Buskerud/Ringerike/Hønefoss/fore...');
$city1temp = $xml->forecast->tabular->time[0]->temperature->attributes()['value'];
$query = "INSERT INTO loan(item, user, returndate) VALUES ($city1temp, :user, :returndate)"
Men jag får error hela tiden som lyder:
Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in C:\Xampp\htdocs\Webb\API.php on line 111
Här är hela min kod och skulle behöver få reda på hur man gör detta:
<?php /* dblabb4.php */
include 'dblabb2.php';
$db = new DB(); ?>
<?php
//Här har jag genererat en klass kallad 'Foo'
class Template {
public $item = '<table border="1"><tr><th>item id</th><th>item type</th><th>description</th>' .'<th>user</th><th>loan date</th><th>return date</th><th>return item</th></tr>';
public $aFuncName = '<label>User: </label>';
}
class City1 {
public $city1 = '<h2>Display and save todays weather in*****666</h2>';
}
?>
<h4>Weatherpanel</h4>
<form action="API.php" method="post">
<label>Item: </label><select name="item">
<?php
/* Vi använder en SQL JOIN på items, itemtypes och loan för
* att hämta data för select fältet.
* Vi vill inte visa föremål som redan är utlånade så vi
* hämtar active fältet från loan.
*/
$query = "SELECT loan.id AS lid, items.id, itemtypes.name, loan.active FROM items LEFT JOIN loan ON loan.item = items.id LEFT JOIN itemtypes ON itemtypes.id = items.type";
if ($result = $db->query($query))
{ while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
// kontrollera att active inte är 1
if ($row['active'] != 1) {
echo '<option value="' . $row['id'] . '">' .$row['name'] .'</option>';}}}
?>
</select>
<label>User: </label><select name="user">
<?php
// Query för att hämta användare och skriva ut dem.
$query = "SELECT id, username FROM user ORDER BY username ASC";
if ($result = $db->query($query)) {
while ($row = $result->fetch(PDO::FETCH_NUM)) {
echo '<option value="' . $row['0'] . '">' . $row['1'] . '</option>';
}}
// echo '<a href="dblabb4.php?returnItem=' . $row['lid'] . '">Return item</a>';
?>
</select><label>Return before: </label><input type="date" name="returndate" placeholder="yyyy-mm-dd"><input type="submit" name="addLoan" value="AddLoan"></form>
<?php
require_once 'APIClassSet.php';
$xml = simplexml_load_file('https://www.yr.no/sted/Norge/Buskerud/Ringerike/Hønefoss/fore...');
$city1temp = $xml->forecast->tabular->time[0]->temperature->attributes()['value'];
$city1unit = $xml->forecast->tabular->time[0]->temperature->attributes()['unit'];
$Datefrom1= $xml->forecast->tabular->time[0]->attributes()['from'];
$Dateto1 = $xml->forecast->tabular->time[0]->attributes()['to'];
$rain1 = $xml->forecast->tabular->time[0]->symbol->attributes()['name'];
$wind = $xml->forecast->tabular->time[0]->windSpeed->attributes()['mps'];
$rainvalue = $xml->forecast->tabular->time[0]->precipitation->attributes()['value'];
$windDirection = $xml->forecast->tabular->time[0]->windDirection->attributes()['deg'];
$windDirection2 = $xml->forecast->tabular->time[0]->windDirection->attributes()['name'];
$Datefrom2= $xml->forecast->tabular->time[1]->attributes()['from'];
$Dateto2= $xml->forecast->tabular->time[1]->attributes()['to'];
$city2temp = $xml->forecast->tabular->time[1]->temperature->attributes()['value'];
$city2unit = $xml->forecast->tabular->time[1]->temperature->attributes()['unit'];
$rain2 = $xml->forecast->tabular->time[1]->symbol->attributes()['name'];
$wind2 = $xml->forecast->tabular->time[1]->windSpeed->attributes()['mps'];
$rainvalue2 = $xml->forecast->tabular->time[1]->precipitation->attributes()['value'];
$windDirection3 = $xml->forecast->tabular->time[1]->windDirection->attributes()['deg'];
$windDirection4 = $xml->forecast->tabular->time[1]->windDirection->attributes()['name'];
$Datefrom3= $xml->forecast->tabular->time[2]->attributes()['from'];
$Dateto3= $xml->forecast->tabular->time[2]->attributes()['to'];
$city3temp = $xml->forecast->tabular->time[2]->temperature->attributes()['value'];
$city3unit = $xml->forecast->tabular->time[2]->temperature->attributes()['unit'];
$rain3 = $xml->forecast->tabular->time[2]->symbol->attributes()['name'];
$wind3 = $xml->forecast->tabular->time[2]->windSpeed->attributes()['mps'];
$rainvalue3 = $xml->forecast->tabular->time[2]->precipitation->attributes()['value'];
$windDirection5 = $xml->forecast->tabular->time[2]->windDirection->attributes()['deg'];
$windDirection6 = $xml->forecast->tabular->time[2]->windDirection->attributes()['name'];
// Låna ett föremål och lägg till rad för detta i loan
if(isset($_POST['addLoan'])) { // filtrera input, float för datum fältet tillåter - och .
$now = new DateTime();
$return = filter_input(INPUT_POST, 'returndate', FILTER_SANITIZE_NUMBER_FLOAT);
$nextYear = new DateTime("$return");
$diff = $now->diff($nextYear);
$tid = $diff->format("%r%a");
$max =370;
$min = 1;
if (filter_var($tid, FILTER_VALIDATE_INT, array("options" => array("min_range"=>$min, "max_range"=>$max))) === false) {
echo("Loan date value is not within the legal range of 1 year, pleas re-enter the date");
} else {
$item = filter_input(INPUT_POST, 'item', FILTER_SANITIZE_NUMBER_INT);
$user = filter_input(INPUT_POST, 'user', FILTER_SANITIZE_NUMBER_INT);
/* Eftersom det är möjligt att föremål ska lånas innan de finns i
* låntabellen så behöver vi kontrollera om active är 0 eller
* inte satt. */
$query = "SELECT active FROM loan WHERE item = :id AND active = 0 ";
$sth = $db->prepare($query); if ($sth->execute(array(':id' => $item))) {
// lägg till lånet, vi anger inte active då det automatisk sätts till 1
$query = "INSERT INTO loan(item, user, returndate) VALUES (:item, :user, :returndate)";
$values = array(':item' => $item, ':user' => $user, ':returndate' => $return);
$sth = $db->prepare($query);
if ($sth->execute($values)) {
echo "Loan for item with id " . $item . " registered.";}
else {
// om något går fel skriv ut PDO felmeddelande
echo "<h4>Error</h4>";
echo "<pre>" . print_r($sth->errorInfo(), 1) . "</pre>";}}
else {
// det fanns redan ett aktivt lån för det föremålet
echo "A loan for item with id: " . $item . " is already active.";
} }
}
///////
$query = "SELECT loan.id AS lid, active, item, loandate, returndate, description, name AS itemtype, loan.user AS loaner FROM loan LEFT JOIN items ON items.id = loan.item LEFT JOIN itemtypes ON itemtypes.id = items.type LEFT JOIN user AS loaner ON loaner.id = loan.user ORDER BY loandate DESC";
$local = new APIClassSet("",$Datefrom1,$Dateto1,"",$Datefrom2,$Dateto2,"",$Datefrom3,$Dateto3);
$local->displayCity1();
echo <<<_END
<form method="post">
<input type="submit" name="time1" value="Display">
</form>
_END;
if(isset($_POST['time1']))
{
echo $rain1 . ' ' . $city1temp . ' ' . $city1unit . ' windspeed: ' . $wind . ' mps with ' . $rainvalue . ' mm rainfall wind direction ' . $windDirection . ' ' . $windDirection2;
}
$local->displayCity2();
echo <<<_END
<form method="post">
<input type="submit" name="time2" value="Display">
</form>
_END;
if(isset($_POST['time2']))
{
echo $rain2 . ' ' . $city2temp . ' ' . $city2unit . ' windspeed: ' . $wind2 . ' mps with ' . $rainvalue2 . ' mm rainfall wind direction ' . $windDirection3 . ' ' . $windDirection4;
}
$local->displayCity3();
echo <<<_END
<form method="post">
<input type="submit" name="time3" value="Display">
</form>
_END;
if(isset($_POST['time3']))
{
echo $rain3 . ' ' . $city3temp . ' ' . $city3unit . ' windspeed: ' . $wind3 . ' mps with ' . $rainvalue3 . ' mm rainfall wind direction ' . $windDirection5 . ' ' . $windDirection6;
}
if ($result = $db->query($query))
{
echo "<h2>Registered weather</h2>";
$template = new Template;
$element = 'item';
print $template->$element;
while ($row = $result->fetch(PDO::FETCH_ASSOC))
{
echo "<tr>";
echo "<td>" . $row['item'] . "</td>";
echo "<td>" . $row['itemtype'] . "</td>";
echo "<td>" . $row['description'] . "</td>";
echo "<td>" . $row['loaner'] . "</td>";
echo "<td>" . $row['loandate'] . "</td>";
echo "<td>" . $row['returndate'] . "</td>"; // Fält för att visa länk för återlämning av föremål
echo "<td>";
if($row['active'] == '1') {
echo '<a href="API.php?returnItem=' . $row['lid'] . '">Return item</a>'; }
else {
echo "No active loan";
}
echo "</td></tr>";
}
echo "</table>";
}
if (isset($_POST['addLoan'])) {
$id = filter_input(INPUT_GET, 'addLoan', FILTER_SANITIZE_NUMBER_INT);
// query för att uppdatera active fältet
$query = "UPDATE loan SET active='1' WHERE id=(:id)";
$sth = $db->prepare($query);
if ($sth->execute(array(':id' => $id))) {
echo "<h4>Item type Loaned."; }
else {
echo "<h4>Error</h4>";
echo "<pre>" . print_r($sth->errorInfo(), 1) . "</pre>"; } }
if (isset($_GET['returnItem'])) {
$id = filter_input(INPUT_GET, 'returnItem', FILTER_SANITIZE_NUMBER_INT);
// query för att uppdatera active fältet
$query = "UPDATE loan SET active='0' WHERE id=(:id)";
$query = "DELETE FROM loan WHERE id=(:id)";
$sth = $db->prepare($query);
if ($sth->execute(array(':id' => $id))) {
echo "<h4>Item type with id: " . $id . " returned."; }
else { echo "<h4>Error</h4>";
echo "<pre>" . print_r($sth->errorInfo(), 1) . "</pre>";
} }
?>