Checking Existence

[WarKirby]

I'm looking for a simple way to check if something exists in a table.

 

For example, my products table has articleNumber as the primary key. I want to check if a given product exists, so I'm trying to check if a supplied articlenumber is found in the database

 

What I'm thinking of doing:

 

$var = mysql_query("SELECT FROM tblProducts WHERE articleNumber=".$TestProduct);

if (isset($var))

...

 

Am I correct in thinking that isset($var) will return false if no matching record was found in the table? or will I need to do this some other way ?

[premiso]

$var = mysql_query("SELECT articleNumber FROM tblProducts WHERE articleNumber=".$TestProduct);
if (mysql_num_rows($var) > 0) {
    // field exists
}

 

Will tell you if more than 0 rows were returned (IE id number exists).

 

 

[WarKirby]

Fastest solve ever. hooray.

 

I would still like to know about the behaviour of isset in this situation, if only for curiosity's sake

[premiso]

For this situation isset does not work, cause either way $var is going to be set to a resource or a boolean. mysql_query will tell you what it will return. Returns false on error and Resource on a successful query.  Where isset is valid is usually in POST / GET data to test if you received a certain data item and allows you to assign it without causing a notice error.

[WarKirby]

Afraid I'm having  an issue with this solution. unsolving for now

 

This is the function I wrote, seems simple enough:

function isValidProduct($input)
{
$test = mysql_query("SELECT FROM ".$dbProductsTable." WHERE articleNumber=".QuoteSmart(trim($input)));
if (mysql_num_rows($test) > 0)
{
	return TRUE;
}
else
{
	return FALSE;
}
}

 

it's returning an error when it gets called though:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /usr/www/users/warkirby/testing/db.inc on line 41

 

any idea what might be wrong here ?

[premiso]

Change the query line to this:

 

$test = mysql_query("SELECT FROM ".$dbProductsTable." WHERE articleNumber=".QuoteSmart(trim($input))) or trigger_error("Query Failed: " . mysql_error());

 

The issue is going to be with $dbProductsTable, as it has not been defined within the scope of the function. Check out Variables Scope for more indepth information.

[WarKirby]

Change the query line to this:

 

$test = mysql_query("SELECT FROM ".$dbProductsTable." WHERE articleNumber=".QuoteSmart(trim($input))) or trigger_error("Query Failed: " . mysql_error());

 

The issue is going to be with $dbProductsTable, as it has not been defined within the scope of the function. Check out Variables Scope for more indepth information.

 

I'm not stupid 

Of course it's defined. This is just a function out of the whole script. That variable is defined at the top of the script,  not shown in this snippet.

 

See anything else that might be causing it ?

 

Edit: Oops, I missed the other part syou said. trying it now

[premiso]

I'm not stupid 

Of course it's defined. This is just a function out of the whole script. That variable is defined at the top of the script,  not shown in this snippet.

 

I never said you were.

 

Yes, it is a function, and I do not doubt you have it defined. But it is not defined in the scope of the function, read my whole line for more information on Variables Scope (note that was a link above).

[WarKirby]

Ok, gave that a try. the resulting extra error:

 

Notice: Query Failed: 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 'FROM WHERE articleNumber='\'4v3vc3y2c\''' at line 1 in /usr/www/users/warkirby/testing/db.inc on line 40

 

But I swear it's defined. I have this at the top of the file

 

$dbProductsTable	= 'tblProducts';

 

Possibly relevant, this function is in a file which I'm referencing from my main script, via require_once. As far as I understand from how it works, that shouldn't affect anything though.

[WarKirby]

Ok, I had a look, you were right.

 

I amended my query to this, but it still doesn't seem to work:

$test = mysql_query("SELECT FROM ".$GLOBALS['$dbProductsTable']." WHERE articleNumber=".QuoteSmart(trim($input))) or trigger_error("Query Failed: " . mysql_error());

 

[premiso]

Ok, read what I write please and look at the link.

 

Variables Scope means that you can only use variables that are:

A. Global

B. Super Global

C. Defined in the Function

D. Passed in as a parameter.

 

Given this, you do not have $dbProductsTable defined, passed in or set as a global. Thus it is not set in the function. To fix this error, you either have to define $dbProductsTable as a global or pass it in as a parameter. Here is an example of each:

 

function isValidProduct($input, $dbProductsTable) {
   $test = mysql_query("SELECT FROM ".$dbProductsTable." WHERE articleNumber=".QuoteSmart(trim($input))) or trigger_error("Query Failed: " . mysql_error());
   if (mysql_num_rows($test) > 0)  {
      return TRUE;
   }

   return FALSE;
}

 

 

// where the $dbProductsTable variable is defined:
global  $dbProductsTable;
$dbProductsTable = 'products';

function isValidProduct($input) {
   global  $dbProductsTable;
   $test = mysql_query("SELECT FROM ".$dbProductsTable." WHERE articleNumber=".QuoteSmart(trim($input))) or trigger_error("Query Failed: " . mysql_error());
   if (mysql_num_rows($test) > 0)  {
      return TRUE;
   }

   return FALSE;
}

 

Or a 3rd option, making it a CONSTANT with define

 

// where the $dbProductsTable variable is defined change to:
define('DB_PRODUCTS_TABLE', 'products');

function isValidProduct($input) {
   $test = mysql_query("SELECT FROM ". DB_PRODUCTS_TABLE ." WHERE articleNumber=".QuoteSmart(trim($input))) or trigger_error("Query Failed: " . mysql_error());
   if (mysql_num_rows($test) > 0)  {
      return TRUE;
   }

   return FALSE;
}

 

Please read this thoroughly: http://php.net/manual/en/language.variables.scope.php

[trq]

You need to pass $dbProductsTable as an argument to the function.

[WarKirby]

Does this apply to functions too?

 

Because I seem to have gotten that part working but I'm still getting an error from the same line, just apparently at a different part of it now.

 

I used the function exactly as you put it in the second example, as well as the global definition at the top.

 

global $dbProductsTable;
$dbProductsTable	= 'tblProducts';

//stuff in between

function isValidProduct($input) {
   global  $dbProductsTable;
   $test = mysql_query("SELECT FROM ".$dbProductsTable." WHERE articleNumber=".QuoteSmart(trim($input))) or trigger_error("Query Failed: " . mysql_error());
   if (mysql_num_rows($test) > 0)  {
      return TRUE;
   }

   return FALSE;
}

 

The errors I'm now getting:

 

Notice: Query Failed: 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 'FROM tblProducts WHERE articleNumber='\'aaaaaaaaa\''' at line 1 in /usr/www/users/warkirby/testing/db.inc on line 43

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /usr/www/users/warkirby/testing/db.inc on line 44

 

The string there is just 9 a's in a row, for testing.

I can see the table name is going in properly, do that's now working, but I'm not sure what else isn't. QuoteSmart is a function in the same file to protect against injection attacks, which reads:

 

// Quote variable to make safe
function QuoteSmart($value)
{
// Stripslashes
if (get_magic_quotes_gpc())
     {
     $value = stripslashes($value);
     }
// Quote if not integer
if (!is_numeric($value) || $value[0] == '0')
     {
     $value = "'" . mysql_real_escape_string($value) . "'";
     }
return $value;
}

 

any idea what's wrong now? Did I misunderstand something again ?

[premiso]

Nope, you notice your MySQL error now has the tblProducts in it?

 

The issue is you are not selecting any data from the table. If you change it to SELECT * FROM or put the columns you want in place of the star it should work.

[WarKirby]

Oh, I wasn't even aware the asterisk was necessary.

 

hooray, it works with no errors now.

 

Finally re-resolving. Thank you greatly