SQL query problems

[Ryan Govostes]

Eh, I was wondering if anyone could help me with a SQL query problem
I'm having. I'm a complete newbie to SQL and MySQL, so any help would
be greatly appreciated.

I am using PHP to allow users to submit ISBNs, prices, and item
conditions into a MySQL database. The table the values are inserted
into is declared with

CREATE TABLE prices (isbn VARCHAR(10), price FLOAT UNSIGNED,
conditions VARCHAR(5))

I use this SQL query to check whether or not the passed ISBN is
already in the database. If it does, it updates the columns with the
new price and conditions. If it does not, it inserts a fresh row. The
variables $isbn, $price, and $conditions are all PHP variables which
are automatically inserted into the query.

BEGIN
SELECT "true" INTO $exists FROM prices WHERE isbn = "$isbn";
IF $exists = "true" THEN
UPDATE prices SET price = $price, conditions = "$condition s" WHERE
isbn = "$isbn";
ELSE
INSERT INTO prices (isbn, price, conditions) VALUES ("$isbn",
$price, "$condition s");
ENDIF;
END

However, it doesn't understand my SELECT statement, and I'm sure it
has other problems.

Regards,
RG

[Jonck van der Kogel]

Hi,
First a question, you're coding in PHP? If so, then your entire syntax
is wrong. If by BEGIN you mean to start a transaction, the syntax
should be:
mysql_query("BE GIN");

To commit a transaction you would use
mysql_query("CO MMIT");

Next, if you want to check whether a ISBN number is already in your
table, and if so do an update and otherwise do an insert, you are
probably looking for some code that looks somewhat like this:

// First, connect to the server
if(!($connectio n = @ mysql_connect($ hostName, $username,$pass word)))
showerror();

// Select the database
if (!(@ mysql_selectdb( $databaseName, $connection)))
showerror();

// Check whether ISBN number is present
$query = "SELECT * FROM prices WHERE isbn = " . $isbn;

if (!($result = @ mysql_query ($query, $connection)))
showerror();

// if there is a row present the isbn needs to be updated, otherwise
it needs to be inserted
if (mysql_num_rows ($result) > 0) {
$query = "UPDATE prices SET price = " . $price . ", conditions = " .
$conditions . " WHERE isbn = " . $isbn;
}
else {
$query = "INSERT INTO prices (isbn, price, conditions) VALUES (" .
$isbn . ", " . $price . ", " . $conditions . ")";
}


if (!(@ mysql_query ($query, $connection)))
showerror();


Note that the variables $hostName, $username, etc... are stored in a
different file, which you must include at the top of your PHP file
that's calling the code I gave you above.
Also the function showerror() should be in a seperate file, which
should also be included.

The showerror() function looks like this:
<?php
function showerror() {
die("Error " . mysql_errno() . " : " . mysql_error());
}
?>

I hope this will help get you started, but it would probably be a good
idea you buy a book to get the hang of some basic stuff. I can suggest
"Web Database Applications with PHP & MySQL" by H.E. Williams and D.
Lane.

All the best, Jonck