Permalänk

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>"; } } ?>

Permalänk
Hedersmedlem
Skrivet av naturaljoin:

Hejsan!

Jag har lite mycket trubbel just nu. Jag har problem att läga in data I min databas.

Hej,

Felmeddelandet "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" berättar egentligen allt.
Din query vill ha X antal parametrar, men får fel antal (för få eller för många).

Vad exakt har du på rad 111?

Skickades från m.sweclockers.com

Visa signatur

Dator, MOBO: Asus X99-A, CPU: Intel I7 6800k (3.4GHz), GPU: Geforce PNY 2070 Super, RAM: 4x8GB Corsair Vengeance LPX 2400MHz, OS-HDD: Intel 750 PCIe 400GB, PSU: EVGA SuperNOVA G2 850W