Getting an integer from MySql using php

Question

I am quite new to php and I have some problems with my final year project at school. I have an issue retrieving an integer from a Select statement using php. I have 2 tables:

Table Camping :

capacity(int 11)
spot_nb (int 11)
location (varchar 20)
availability (varchar 1) //this can be 'Y' or 'N'

Table reservation :

reservation_nb (int 10 auto_increment)
spot_nb (int 11)
uin (varchar 8; null)
persons_nb (int 5)
price (int 11)
email (varchar 60)

And for php I have the following code:

$query5 = $conn->prepare("SELECT MAX(spot_nb) from camping where capacity=2 and availability='Y'");
$query5->execute();
$result = $query5->fetch(PDO::FETCH_ASSOC);
$id = (int) $result;

to get the max value from table camping and insert into reservation with this code

$query6 = $conn->prepare("INSERT INTO reservation (price, persons_nb, email, spot_nb) VALUES (:price, 2, :email, :id);");
$query6-> bindParam(':price', $campingNumber);
$query6-> bindParam(':email', $email);
$query6-> bindParam(':id', $id);

But every time query6 is executed, the spot_nb that is inserted is 1.

Thank you, Alex.

Have you checked the query manually, for instance, using MySQL workbench? — Rowland Shaw
– Rowland Shaw, Commented Jun 8, 2015 at 13:48
Yes, and for this query the returned result is 11, what i need, but in php it's not working, I have no idea why it's not working. — Alexandru
– Alexandru, Commented Jun 8, 2015 at 14:13

david strachan · Accepted Answer · 2015-06-08 14:57:51Z

1

Use fetchColumn() instead of fetch()

$query5 = $conn->prepare("SELECT MAX(spot_nb) from camping 
                        where capacity=2 and availability='Y'");
$query5->execute();
$result = $query5->fetchColumn();
$id = (int) $result;

answered Jun 8, 2015 at 14:57

david strachan

7,2282 gold badges25 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user4767190 · Accepted Answer · 2015-06-08 13:51:45Z

0

Update your query to

$query5 = $conn->prepare("SELECT MAX(spot_nb) as spot_nb from camping where capacity=2 and availability='Y'");
$query5->execute();
$result = $query5->fetch(PDO::FETCH_ASSOC);
$id = $result['spot_nb'];

answered Jun 8, 2015 at 13:51

user4767190

4 Comments

Alexandru Over a year ago

I already tried and I get this error every time: Undefined index: spot_nb in /Applications/XAMPP/xamppfiles/htdocs/website/register.php on line 79

user4767190 Over a year ago

print $result and check what you get in that array

Alexandru Over a year ago

if I print the result I get just a blank page

user4767190 Over a year ago

then there is issue with connection to database.check $conn

Collectives™ on Stack Overflow

Getting an integer from MySql using php

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related