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.

[mwinter]

the relationship: the animalName field is a primary key in both the NK_Booking table and the NK_BookingHandler table.What's the foreign key in NK_Booking, though?

There's something bizarre about all this. If animalName is the primary key for NK_Booking and that's part of the condition, why do you need anything else to select a unique tuple? Is animalName part of a compound key? Are your primary keys not unique (if not, that's a serious flaw)?


Therefore I have tried the following code with an inner join.A literal JOIN isn't possible in an UPDATE query. That's why I used a scalar subquery: it produces the same effect.

Mike

[djr33]

^told ya someone would know.

Ha :)

[snowbydave]

Hi Lucie,

Is ur problem solved ? Else check below step & it will defeneatly works.

1. Check animalName, handlerName, startDate all this field exist in NK_Booking table. Also fields names r case sensitive so write as u had written it in mySQL. If possible then copy & paste field name from mySQL to ur PHP page query.

2. Also copy below query & check it.

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

3. If this will not solve or solve ur problem then email me on snowbydave1@hotmail.com