mrsquash2

Ah, what a lovely case of the Friday morning brain farts!

I have a query that selects some data from a table based on the current ID
selected.

If the query does not return any results, I want it to continue to another
query that will insert a record into the table.

Below is what I have...but it will not insert anything if the first query
does not find a match.


<?php
$request_id = $_GET['id'];
$current_user = substr($_SERVER['AUTH_USER'], 13);

$lock_query = "SELECT id, locked_by_user FROM locked_payments WHERE id =
'$request_id'";
$lock_result = mssql_query($lock_query) or die(mssql_get_last_message());

if (empty($lock_result)) {
$set_lock = "INSERT into locked_payments (
id,
locked_by_user)
VALUES
('$request_id',
'$current_user')";
mssql_query($set_lock) or die ("Insert failed: <br
/>".mssql_get_last_message());
}
?>

Any ideas on what I'm doing wrong? My guess is that (empty($lock_result))
is probably not the correct way to check if an array is empty?

muadib

I think the $lock_result is just a resource #id you haven't fetched any data yet. True?

Aleksander

mrsquash2

Okay, gotcha!

I changed it to this and it works:


<?php
$request_id = $_GET['id'];
$current_user = substr($_SERVER['AUTH_USER'], 13);

$lock_query = "SELECT id, locked_by_user FROM locked_payments WHERE id =
'$request_id'";
$lock_result = mssql_query($lock_query) or die(mssql_get_last_message());
$lock_row = mssql_fetch_array($lock_result);
$lock_id = $lock_row['id'];
$lock_user = $lock_row['locked_by_user'];

if (empty($lock_row)) {
$set_lock = "INSERT into locked_payments (
id,
locked_by_user)
VALUES
('$request_id',
'$current_user')";
mssql_query($set_lock) or die ("Query failed: <br
/>".mssql_get_last_message());
}
?>

Thanks!

tularis

I'll just put my comments inline for you...


You can't trust this info.

WARNING :: SQL INJECTION :: WARNING


You don't know if these 2 exist, so you'll get E_NOTICEs when you get 0 rows in your result


And now you check if it actually HAS data, why didn't you do this 2 lines earlier ?


WARNING :: SQL INJECTION :: WARNING


you're welcome. - Tul

muadib

$request_id = $_GET['id']; <--- I suppose this would be an int. True? If
so then add:

<?php
$request_id = intval($_GET['id']);
?>

Aleksandar

marek

Even better:

<?php
$request_id = $_GET['id'];
$current_user = substr($_SERVER['AUTH_USER'], 13);

$lock_query = "SELECT id, locked_by_user FROM locked_payments WHERE id =
'$request_id'";
$lock_result = mssql_query($lock_query) or die(mssql_get_last_message());
$lock_row = mssql_fetch_array($lock_result);

if (empty($lock_row)) {
$lock_id = $lock_row['id'];
$lock_user = $lock_row['locked_by_user'];
$set_lock = "INSERT into locked_payments (
id,
locked_by_user)
VALUES
('$request_id',
'$current_user')";
mssql_query($set_lock) or die ("Query failed: <br
/>".mssql_get_last_message());
}
?>

marek

I would advise against using intval if the sql data type is anything
greater and/or equal to "int unsigned".
PHP on 32 bit systems: intval can only handle values up to 2147483647.
sql unsigned int can go up to |4294967295 and bigints even higher....

This could cause serious problems ...

Marek
|

lists

I won't say anything about what others have already warned you about, but here is what I would do.

<?php
$request_id = intval($_GET['id']);
$current_user = substr($_SERVER['AUTH_USER'], 13);

$lock_query = " SELECT id, locked_by_user
FROM locked_payments
WHERE id = '{$request_id}'"; $lock_result = mssql_query($lock_query) or
die('MSSQL ERROR: Lock Query Failed<br />'.
mssql_get_last_message());

#
## here is the key to making this work...
# checking to make sure that the query returned 0 (zero) results
# http://us3.php.net/mssql_num_rows
if ( mssql_num_rows($lock_result) == 0 ) {
$set_lock = "INSERT INTO locked_payments
(id,locked_by_user) VALUES ('{$request_id}','{$current_user}')"; mssql_query($set_lock) or
die ('MSSQL ERROR: Insert failed:<br />'. mssql_get_last_message()); }
?>

--
Jim Lucas

"Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them."

Twelfth Night, Act II, Scene V
by William Shakespeare

tedd

I wouldn't check for an empty array, but rather if affected rows > 0
-- like so:

// check to see if there is a record for this page
$query = "SELECT * FROM pages WHERE page_id = $page_id";
$result = mysql_query($query) or die('Error, query 1 failed ' .
mysql_error(). $query); $seg = mysql_real_escape_string($segment);
if (mysql_affected_rows() > 0) // if record exist then add data to it
{
$query = "UPDATE pages SET segment_id= '$seg' WHERE page_id=
'$page_id' ";
$result = mysql_query($query) or die('Error, query 2 failed '
.. $query);
}
else // else create new record and then add data to it
{
$query = "INSERT INTO pages ($page_id, segment_id) VALUES (
'$page_id' , '$seg' )";
$result = mysql_query($query) or die('Error, query 3 failed '
.. mysql_error());
}

Cheers,

tedd
--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

mike503

if you don't actually need to know inside of PHP whether or not it
exists, you can skip the PHP work and have mysql do it for you:

http://dev.mysql.com/doc/refman/5.0/...duplicate.html