Jump to content


Photo

PHP/MySQL - How do I check for contents of a Slelct query


  • Please log in to reply
6 replies to this topic

#1 nitefall

nitefall
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 23 July 2006 - 12:01 AM

Hi,
I am building an asset tracking database and I have an issue.  When checking an asset in, I want to be able to query the asset table to see if the asset exists, before creating a row in the transaction table indicating the asset has been returned.

My issue is I don't know what functions to use, or the correct order to use the in.  As a result I didn't know what to type in when it was time to search for help.  So... Please forgive if this has already been answered.

Here is my code:
if ($HTTP_POST_VARS['submit'] == "Check-In") {

	// Create short variables from form data
		$asset_tag=$HTTP_POST_VARS['asset_tag'];
		$user_id=$HTTP_POST_VARS['user_id'];
		
	// Add Slashes to escape control characters
		$asset_tag = addslashes($asset_tag);
		$user_id = addslashes($user_id);
		
				
	// Check for asset in database
		db_connect();
		mysql_select_db('aws_assets');

		$lookup = "SELECT * FROM assets WHERE asset_tag='$aset_tag'";
		$lookup_result = mysql_query($lookup);
				
		if (!lookup) {
			echo('<p>Asset not in database!  Add Asset?</p>');
			exit();

		} else {

	// Write asset to Database
			$update = "UPDATE assets SET status_id='1' WHERE asset_tag='$asset_tag'";
			$update_result = mysql_query($update);
			
			if ($update_result) {
				$insert = "INSERT INTO transactions SET
					asset_id='$asset_tag',
					user_id='$user_id',
					status_id='1'";
				$insert_result = mysql_query($insert);
				if ($insert_result)
					echo('<p>Your transaction has been recorded</p>');
					
			}
		}



So... As it is currently written, if a valid asset tag is used at check in, all goes according to plan, the assets table is updated, and the transactions table has a new row added to it.

If however a bogus asset tag is entered, the assets table is not updated (as it should not be) but a new row is added to the transactions table with the bogus asset tag.

I am sure the issue lies in these lines... but I don't know what to do.

if (!lookup) {
			echo('<p>Asset not in database!  Add Asset?</p>');
			exit();

		}

Please Help.

Thank You

#2 rab

rab
  • Members
  • PipPipPip
  • Advanced Member
  • 155 posts

Posted 23 July 2006 - 12:14 AM

		if (!$lookup) {
			echo('<p>Asset not in database!  Add Asset?</p>');
			exit();

		}

you forgot the $

#3 nitefall

nitefall
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 23 July 2006 - 02:19 AM

So I did... So I did...  But this still does not seem to solve my issue.  I am still able to enter a bogus asset tag # when checking an asset in.

I need some method of looking at the assets table to confirm that the asset I am trying to check in is actually in the assets table.  If it is there, then the script should go on... If it is not however, there should be an error generated.

When the lookup is performed with a good asset tag, the query should return 1 row (asset tags are unique) and only one row.

How do I catch it if 0 rows are returned?  Wouldn't that be the result if the lookup was performed with a bogus asset_tag?



#4 nitefall

nitefall
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 23 July 2006 - 02:25 AM

And if I try this....

$lookup = "SELECT * FROM assets WHERE asset_tag='$aset_tag'";
		$lookup_result = mysql_query($lookup);
		mysql_num_rows($lookup_result);
		
				
		if (mysql_num_rows($lookup_result) <= 0) {
			echo('<p>Asset not in database!  Add Asset?</p>');
			exit();
		}

It fails all the time... even with good asset_tags...  so I am very confused.

#5 nitefall

nitefall
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 23 July 2006 - 09:38 PM

I have now gone through all of the above options again... I really am stuck on this one

#6 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 23 July 2006 - 10:02 PM

$lookup = "SELECT * FROM assets WHERE asset_tag='$aset_tag'";

But your posted value is retrieved as $asset_tag, so the code should be:
$lookup = "SELECT * FROM assets WHERE asset_tag='$asset_tag'";

Legend has it that reading the manual never killed anyone.
My site

#7 nitefall

nitefall
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 23 July 2006 - 10:51 PM

That is my answer!!!   

Thank you for catching that...  It works now!  Wooo Hoo...  The best part is learning that the way I thought it should be, is the way it is...  I just need to be better at spotting the little errors that I make.

Thank you very much.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users