Question on "Check for an existing record"

Code Examples Form
,
#1

Hello,

I have the fallowing:

table with the next columns: id, order_nr, drawing_nr.

i want, when when i write in the field drawing_nr some text, to receive a message that tells - > the text was written and can be found on order_nr x, y, x

id order_nr drawing_nr
1 test1 12345
2 test2 234
3 test3 12345

this value “12345” can be found in order: test1, test3.

down you can find my script, can you tell me what i did wrong?

/**

  • Check for an existing record
    */

// SQL statement parameters
$check_table = ‘file’; // Table name
$check_where = “drawing_nr = ‘{drawing_nr}’”; // Where clause
$group_by = order_nr; //group by

// Check for record
$check_sql = ‘SELECT *’
. ’ FROM ’ . $check_table
. ’ WHERE ’ . $check_where
. ’ GROUP BY ’ . $group_by;

sc_select(dataset, $check_sql);

if (false == {dataset})
{
// Error while accessing database
}
elseif ({dataset}->EOF)
{
// IF no record found
$master_field = ‘duplicate’; // Field name on the master application
$master_value = ‘0’; // New value

// Set master value
sc_master_value($master_field, $master_value);
}
else
{
// IF record found
$javascript_title = ‘Attention - Record found !!!’; // Javascript message title
$javascript_message = “This part [ <font color=‘red’><b>{drawing_nr}</b></font> ] was made in the past.<br>You can take/compare the time values from there…<br> Orders: $group_by”; // Javascript message contents

// Display javascript message
sc_ajax_message($javascript_message, $javascript_title);

// Field parameters
$master_field = ‘duplicate’; // Field name on the master application
$master_value = ‘1’; // New value

// Set master value
sc_master_value($master_field, $master_value);
}

#2

First off, showing indented code makes this A LOT easier to read.

Aside from that - one possibility

// SQL statement parameters
$check_table = 'file'; // Table name
$check_where = "drawing_nr = '{drawing_nr}'"; // Where clause
$group_by = order_nr; //group by

// Check for record
$check_sql = 'SELECT *'
    . ' FROM ' . $check_table
    . ' WHERE ' . $check_where
    . ' GROUP BY ' . $group_by;

sc_select(dataset, $check_sql);

if (false == {dataset})       
{
    // Error while accessing database
}
// should be "$dataset->EOF" in this context.
elseif ($dataset->EOF)
{
    // IF no record found
    $master_field = 'duplicate'; // Field name on the master application
    $master_value = '0'; // New value

    // Set master value
    sc_master_value($master_field, $master_value);
}
else
{
    // IF record found
    $javascript_title = 'Attention - Record found !!!'; // Javascript message title
    $javascript_message = "This part [ <font color='red'><b>{drawing_nr}</b></font> ] was made in the past.<br>You can take/compare the time values from there...<br> Orders: $group_by"; // Javascript message contents

    // Display javascript message
    sc_ajax_message($javascript_message, $javascript_title);

    // Field parameters
    $master_field = 'duplicate'; // Field name on the master application
    $master_value = '1'; // New value

    // Set master value
    sc_master_value($master_field, $master_value);
}
#3

[QUOTE=adz1111;33243]First off, showing indented code makes this A LOT easier to read.

Aside from that - one possibility

// SQL statement parameters
$check_table = 'file'; // Table name
$check_where = "drawing_nr = '{drawing_nr}'"; // Where clause
$group_by = order_nr; //group by

// Check for record
$check_sql = 'SELECT *'
    . ' FROM ' . $check_table
    . ' WHERE ' . $check_where
    . ' GROUP BY ' . $group_by;

sc_select(dataset, $check_sql);

if (false == {dataset})       
{
    // Error while accessing database
}
// should be "$dataset->EOF" in this context.
elseif ($dataset->EOF)
{
    // IF no record found
    $master_field = 'duplicate'; // Field name on the master application
    $master_value = '0'; // New value

    // Set master value
    sc_master_value($master_field, $master_value);
}
else
{
    // IF record found
    $javascript_title = 'Attention - Record found !!!'; // Javascript message title
    $javascript_message = "This part [ <font color='red'><b>{drawing_nr}</b></font> ] was made in the past.<br>You can take/compare the time values from there...<br> Orders: $group_by"; // Javascript message contents

    // Display javascript message
    sc_ajax_message($javascript_message, $javascript_title);

    // Field parameters
    $master_field = 'duplicate'; // Field name on the master application
    $master_value = '1'; // New value

    // Set master value
    sc_master_value($master_field, $master_value);
}

[/QUOTE]

Hello,

thanks for the tip, i made the change in there…and also other changes…but it is not working…does anibody know where do i go rong?

//---------------------------------------------------------

/**

  • Check for an existing record
    */

// SQL statement parameters
$check_table = ‘file’; // Table name
$check_where = “drawing_nr = ‘{drawing_nr}’”; // Where clause

// Check for record
$check_sql = ‘SELECT *’
. ’ FROM ’ . $check_table
. ’ WHERE ’ . $check_where;

sc_select(dataset, $check_sql);

if (false == {dataset})
{
// Error while accessing database
}
// should be “$dataset->EOF” in this context. - scriptcase forum
elseif ($dataset->EOF)
// elseif ({dataset}->EOF) old sintax
{
// IF no record found
$master_field = ‘duplicate’; // Field name on the master application
$master_value = ‘0’; // New value

// Set master value
sc_master_value($master_field, $master_value);
}
else
{

$result = mysql_query($check_sql); //or die(mysql_error());

while($row = mysql_fetch_array($result)){
$order_n = $row[‘order_nr’];
}

// IF record found
$javascript_title = ‘Attention - Record found !!!’; // Javascript message title
$javascript_message = “This part [ <font color=‘red’><b>{drawing_nr}</b></font> ] was made in the past.<br>You can take/compare the time values from there…<br> Orders: $order_n”; // Javascript message contents

// Display javascript message
sc_ajax_message($javascript_message, $javascript_title);

// Field parameters
$master_field = ‘duplicate’; // Field name on the master application
$master_value = ‘1’; // New value

// Set master value
sc_master_value($master_field, $master_value);

}

#4

Also - noticed (from the manual):

Suggested code for checking if anything returned:

if ({my_data} === false)

// Note 3 “=” - also your operation is reversed, which I know should be okay - however as SC does substitution I would reverse it to as they have it, just to eliminate it as a potential issue.

#5

[QUOTE=adz1111;33247]Also - noticed (from the manual):

Suggested code for checking if anything returned:

if ({my_data} === false)

// Note 3 “=” - also your operation is reversed, which I know should be okay - however as SC does substitution I would reverse it to as they have it, just to eliminate it as a potential issue.[/QUOTE]

Thanks for your advice.

I solved the proablem. :slight_smile:

//------------------------------------------------------------
/**

  • Check for an existing record
    */

// SQL statement parameters
$check_table = ‘file’; // Table name
$check_where = “drawing_nr = ‘{drawing_nr}’”; // Where clause

// Check for record
$check_sql = ‘SELECT *’

. ’ FROM ’ . $check_table
. ’ WHERE ’ . $check_where;

sc_select(dataset, $check_sql);

if (false === {dataset})
{
// Error while accessing database
}
// should be “$dataset->EOF” in this context. - scriptcase forum
elseif ($dataset->EOF)
//elseif ({dataset}->EOF) //old sintax
{
// IF no record found
$master_field = ‘duplicate’; // Field name on the master application
$master_value = ‘0’; // New value

// Set master value
sc_master_value($master_field, $master_value);
}
else
{
//---------------------------------------------------------------------------
/* //--------------------------------------V1 (not working)--------------------------
$order_n = array();
$result = mysql_query($check_sql); //or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$order_n[] = $row[‘order_nr’];
}
$order_n = implode(", “, $order_n);
var_dump($order_n);*/
//-----------------------------------------------------------------------------
//--------------------------------------V2 (working)--------------------------
$order_n = array();
$check_sql = “SELECT DISTINCT order_nr”
. " FROM file”
. " WHERE drawing_nr = ‘" . {drawing_nr} . "’";
sc_lookup(rs, $check_sql);

if (isset({rs[0][0]})) // Row found
{
$order_n[] = {rs[0][0]};
}
$order_n = implode(", ", $order_n);
var_dump($order_n);
//-----------------------------------------------------------------------------

// IF record found
$javascript_title = ‘Attention - Record found !!!’; // Javascript message title
$javascript_message = “This part [ <font color=‘red’><b>{drawing_nr}</b></font> ] was made in the past.<br>You can take/compare the time values from there…<br> Check out this order(s): <font color=‘red’><b> $order_n </b></font>”; // Javascript message contents

// Display javascript message
sc_ajax_message($javascript_message, $javascript_title);

// Field parameters
$master_field = ‘duplicate’; // Field name on the master application
$master_value = ‘1’; // New value

// Set master value
sc_master_value($master_field, $master_value);

}