PHP error!

[Lucie]

Hi,

I am trying to create a function where a owner review is added to a database but I am getting the following error:

Error adding new review: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' handlerName='Hay, Dan', startDate='2006-01-01'' at line 4

The code is:

<?php

$handlername = $_GET['handlerName'];
$startdate = $_GET['startDate'];
$animalname = $_GET['animalName'];
$ownerreview = $_GET['reviewtext'];

$sql1 ="UPDATE NK_Booking SET
reviewtext='$ownerreview'
WHERE
animalName='$animalname',
handlerName='$handlername',
startDate='$startdate'";

if (@mysql_query($sql1)) {
echo '<p>New review added</p>';
} else {
exit('<p>Error adding new review: ' .mysql_error() . '</p>');
}
?>

Can anyone help please?!!

Thanks.

[realwx]

Make sure the column handlerName exists in the database.

[Lucie]

I have changed the code to read:

<?php

$handlername = $_GET['handlerName'];
$startdate = $_GET['startDate'];
$animalname = $_GET['animalName'];
$ownerreview = $_GET['reviewtext'];

$sql1 ="UPDATE NK_Booking SET
reviewtext='$ownerreview'
WHERE
animalName='$animalname' AND
handlerName='$handlername' AND
startDate='$startdate'";

if (@mysql_query($sql1)) {
echo '<p>New review added</p>';
} else {
exit('<p>Error adding new review: ' .mysql_error() . '</p>');
}
?>

The error has now changed to: Error adding new review: Unknown column 'handlerName' in 'where clause'.

Is this because the handlerName field in not in the table NK_Booking? It is in Nk_BookingHandler.

Do i therefore need some kind of inner join? And how and where would I do this?!!

Thanks for your help,

Lucie

[mwinter]

$handlername = $_GET['handlerName'];
$startdate = $_GET['startDate'];
$animalname = $_GET['animalName'];
$ownerreview = $_GET['reviewtext'];

I really hope you aren't using that data directly within the query. That's asking for trouble.

$sql1 ="UPDATE NK_Booking SET
reviewtext='$ownerreview'
WHERE
animalName='$animalname' AND
handlerName='$handlername' AND
startDate='$startdate'";

Yes, that's better. The WHERE clause is an expression that must evaluate to a boolean; commas aren't legal.

The error has now changed to: Error adding new review: Unknown column 'handlerName' in 'where clause'.

Is this because the handlerName field in not in the table NK_Booking? It is in Nk_BookingHandler.

Yes.

Do i therefore need some kind of inner join?

Yes.

And how and where would I do this?!!

In the WHERE clause, using a subquery.

It's difficult to be specific because you haven't stated the relationship between NK_Booking and Nk_BookingHandler, but it would be something like:

"UPDATE NK_Booking"
    . " SET reviewtext=$ownerreview"
    . " WHERE animalName='$animalname'"
        . " AND startDate='$startdate'"
        . " AND <foreignKey>=(SELECT <primaryKey>"
            . " FROM Nk_BookingHandler"
            . " WHERE handlerName='$handlername')"

Subqueries as scalar operands must only return a single value: one attribute and one tuple.

Mike


SQL. It's been a while...

[Lucie]

Thanks Mike.

the relationship: the animalName field is a primary key in both the NK_Booking table and the NK_BookingHandler table.

Therefore I have tried the following code with an inner join.

<?php

$handlername = $_GET['handlerName'];
$startdate = $_GET['startDate'];
$animalname = $_GET['animalName'];
$ownerreview = $_GET['reviewtext'];

$sql1 ="UPDATE NK_Booking
SET reviewtext='$ownerreview'
WHERE
animalName='$animalname'
AND startDate='$startdate'
INNER JOIN NK_BoookingHandler ON
NK_Boooking.animalName=NK_BookingHandler.animalName
WHERE handlerName='$handlername'";

if (@mysql_query($sql1)) {
echo '<p>New review added</p>';
} else {
exit('<p>Error adding new review: ' .mysql_error() . '</p>');
}
?>

However, I am still not getting it right.

Error adding new review: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN NK_BoookingHandler ON NK_Boooking.animalName=NK_BookingHandler.an' at line 6

Can anybody help please.....

[djr33]

You might try backticks around the table names.

ex: ... JOIN `tablename` ...

[Lucie]

that didnt work...

[djr33]

Around other stuff in your query?

Not really sure. All I know is that I had the same error tonight, and it was fixed with proper backticks.

[Lucie]

I tried the following but it still didnt work:


$sql1 ="UPDATE NK_Booking
SET 'reviewtext'='$ownerreview'
WHERE
'animalName'='$animalname'
AND 'startDate'='$startdate'
AND 'ownerNo'='$ownerno' FROM NK_Booking
INNER JOIN NK_BookingHandler ON
NK_Booking.animalName=NK_BookingHandler.animalName
WHERE 'handlerName'='$handlername'";

So i have put it back to this:

<?php

$handlername = $_GET['handlerName'];
$ownerno = $_GET['ownerNo'];
$startdate = $_GET['startDate'];
$animalname = $_GET['animalName'];
$ownerreview = $_GET['reviewtext'];

$sql1 ="UPDATE NK_Booking
SET reviewtext='$ownerreview'
WHERE
animalName='$animalname'
AND startDate='$startdate'
AND ownerNo='$ownerno' FROM NK_Booking
INNER JOIN NK_BookingHandler ON
NK_Booking.animalName=NK_BookingHandler.animalName
WHERE handlerName='$handlername'";

if (@mysql_query($sql1)) {
echo '<p>New review added</p>';
} else {
exit('<p>Error adding new review: ' .mysql_error() . '</p>');
}
?>

I am so useless at php and sql - I dont have a clue! Thanks for your help.

[djr33]

Hmm... can't say. I haven't used any syntax aside from INSERT, UPDATE and SELECT... that stuff is new to me. I'm using the rest a lot, so I've got an idea what's going on... but.. yeah.
Probably someone else can help.

If not, trial and error... gets you somewhere, anyway. Heh.