problem in searching record in a database

[MadTechie]

However it outputs the message that " record cannot be found" when supervisor tries to search for a customer_id which is not in the database.

 

Erm.. what was you expecting ?

If you don't want the message just comment it out

//initilize variables
    $var_nic = '';
    $var_full_name = '';
    $var_name_with_initials = '';
    $var_address = '';
    $var_contact_number = '';
    $var_gender = '';
    if(isset($_POST['customer_id'])) { 
      $customer_id=$_POST['customer_id']; 
      $query = "select * from customer where customer_id=" .$customer_id;
      $result = mysql_query($query) or die(mysql_error());
      if(mysql_num_rows($result) < 1) {
	//echo 'The record could not be found.'; <-- remove this line
	while ($row=mysql_fetch_array($result)) {
	  while ($row=mysql_fetch_array($result)) {
	    // replace blank variables with variables from the database
	    $var_nic = $row['nic'];
	    $var_full_name = $row['full_name'];
	    $var_name_with_initials = $row['name_with_initials'];
	    $var_address = $row['address'];
	    $var_contact_number = $row['contact_number'];
	    $var_gender = $row['gender'];
	  }
	}
      }
    }

[heshan]

@ MadTechie,

 

I want that error message too. That is coming with no problems..

 

But most importantly when supervisor looks up on the records, i want to display relevant customer details to him.

 

For an example if searches for customer ID 4 and wants to verify details the relevant record should display in a form. But it results an empty form......

[MadTechie]

But you said

when supervisor tries to search for a customer_id which is not in the database.

However it outputs the message that " record cannot be found" when supervisor tries to search for a customer_id which is not in the database.

 

As it seams your $customer_id would be a number and would relate to only 1 record would so this

//initilize variables
$var_nic = '';
$var_full_name = '';
$var_name_with_initials = '';
$var_address = '';
$var_contact_number = '';
$var_gender = '';
if(isset($_POST['customer_id'])) {
  $customer_id=(int)$_POST['customer_id'];
  $query = "select * from customer where customer_id=$customer_id LIMIT 1";
  $result = mysql_query($query) or die(mysql_error());
  if(mysql_num_rows($result) < 1) {
    echo 'The record could not be found.';
  }else {
    $row=mysql_fetch_array($result);
    $var_nic = $row['nic'];
    $var_full_name = $row['full_name'];
    $var_name_with_initials = $row['name_with_initials'];
    $var_address = $row['address'];
    $var_contact_number = $row['contact_number'];
    $var_gender = $row['gender'];
  }
}

[heshan]

@ MadTechie, Thanks for you help. But still it returns an empty form....

 

I entered a customer_id as 1 and clicks on search button. The relevant customer details relating to this id is already in the customer table. Therefore relevant results should come. But it returns an empty form....

[MadTechie]

okay try this and post back the debug info

 

ie

DEBUG:~----------------------------

POST:array(0) { }

----------------------------

<?php
//initilize variables
$var_nic = '';
$var_full_name = '';
$var_name_with_initials = '';
$var_address = '';
$var_contact_number = '';
$var_gender = '';
echo "DEBUG:~";
error_reporting(E_ALL);
echo "----------------------------<BR />\n";
echo "POST:";var_dump($_POST);echo "<BR />";
if(isset($_POST['customer_id'])) {
  $customer_id=(int)$_POST['customer_id'];
  $query = "SELECT * FROM customer WHERE customer_id=$customer_id LIMIT 1";
  echo "\$query = $query<BR />\n";
  $result = mysql_query($query) or die(mysql_error());
  if(mysql_num_rows($result) < 1) {
    echo 'The record could not be found.';
  }else {
    $row=mysql_fetch_array($result);
    echo "\$row = $row<BR />\n";
    $var_nic = $row['nic'];
    $var_full_name = $row['full_name'];
    $var_name_with_initials = $row['name_with_initials'];
    $var_address = $row['address'];
    $var_contact_number = $row['contact_number'];
    $var_gender = $row['gender'];
  }
}
echo "----------------------------<BR />\n";

[kickstart]

Hi

 

Not sure whether you just did a minor mod to your script of pasted in MadTechies code, however the bit highlighted in red in the following is the critical change from the code you previously pasted.

 

//initilize variables

$var_nic = '';

$var_full_name = '';

$var_name_with_initials = '';

$var_address = '';

$var_contact_number = '';

$var_gender = '';

if(isset($_POST['customer_id'])) {

  $customer_id=(int)$_POST['customer_id'];

  $query = "select * from customer where customer_id=$customer_id LIMIT 1";

  $result = mysql_query($query) or die(mysql_error());

  if(mysql_num_rows($result) < 1) {

    echo 'The record could not be found.';

  }else {

    $row=mysql_fetch_array($result);

    $var_nic = $row['nic'];

    $var_full_name = $row['full_name'];

    $var_name_with_initials = $row['name_with_initials'];

    $var_address = $row['address'];

    $var_contact_number = $row['contact_number'];

    $var_gender = $row['gender'];

  }

}

 

All the best

 

Keith

[heshan]

Yes, finally it works.....

 

@MadTechie. thank you for your earlier coding. It worked. I made a mistake.

@ Kickstart, thank you very much for showing me the mistake.

 

Thanks everyone who helped in this topic.

 

@MadTechie. Atlast can you expalin me what these lines of your coding does?

 

$customer_id=(int)$_POST['customer_id']; 

 

$query = "select * from customer where customer_id=$customer_id LIMIT 1";

 

if(mysql_num_rows($result) [b]< 1[/b]) {
               echo 'The record could not be found.';
	   
	     

 

What is refer to as "1" there?

[MadTechie]

Sure thing

 

$customer_id=(int)$_POST['customer_id']; 

(int) means get as an integer, so 0 = 0, 1=1 but HELLO = 0

I used it to stop SQL injection on a Int

 

$query = "select * from customer where customer_id=$customer_id LIMIT 1";

LIMIT means once you found that limit (1) stop searching,

this is just a little optimization

 

if(mysql_num_rows($result) < 1) {
               echo 'The record could not be found.';

1 refs to the records found, (if less than 1 records then none are found

 

I hope it all makes sense

 

also remember to mark as solved