php ms sql update statement help

kat35601 · October 12, 2015, 3:03pm

I want to first check to see if a row is returned and if so update it. There will only be one row per serial number. I think i am close. And is the the best way to do it???

[php]

Status Screen

Open RedTags

<?php $serial=$_POST["serial"]; $connect =odbc_connect("removed"); if(!$connect) { exit("Connection Failed: " . $connect); } $query= " Select serial from WIP_Master where serial ='$serial' "; $sql=" Update WIP_Master set redtagclosed='N' where serial ='$serial' "; $result =odbc_exec($connect,$query); if (odbc_num_rows($query)){ $result =odbc_exec($connect,$sql); } if(!$result){ trigger_error("Error in SQL".odbc_errormsg()); } odbc_close($connect); sleep(5); $url = 'http://theintranet/kk_update_redtag_lk.php'; header('Location: ' . $url); ?> [/php]

Topcoder · October 12, 2015, 5:35pm

Why do you want to check to see if it exist first?

Just run the update, if it exists then it will tell you how many rows were updated (1 or 0 in your case) .

There’s no reason to check to see if it exist first.

kat35601 · October 12, 2015, 5:47pm

I want to validate that it is a good serial number and if it is not I will give the user a message of where to look for the correct serial number as they may have more than one option.

ErnieAlex · October 12, 2015, 7:20pm

Just use mysqli_num_rows fuction and it will tell you how many rows you selected. If zero, did not exist.

http://www.w3schools.com/php/func_mysqli_num_rows.asp

kat35601 · October 12, 2015, 8:47pm

I am using the MS sql version of that but it does not work correctly for some reason

from my code above
[php]$result =odbc_exec($connect,$query);
if (odbc_num_rows($query)){
$result =odbc_exec($connect,$sql);
[/php]

astonecipher · October 12, 2015, 8:52pm

You are using sql server? So why are you using odbc?

This is what you are after,http://php.net/manual/en/function.sqlsrv-num-rows.php

ErnieAlex · October 12, 2015, 8:58pm

Sorry, I did not catch the ODBC part… This is how is is handled start to finish…
From a StackOverflow post, not tested…
[php]

<?PHP //PHP Code to connect to a certain database using ODBC and getting information from it //Determining The Database Connection Parameters $database = 'DatabaseName'; $username = 'UserName'; $password = 'Password'; //Opening the Connection $conn = odbc_connect($database,$username,$password); //Checking The Connection if (!$conn) { exit("Connection Failed: " . $conn); } //Preparing The Query $sql = "SELECT * FROM Table1 WHERE Field1='$v_Field1'"; //Executing The Query $rs = odbc_exec($conn,$sql); //Checking The Result Set if (!$rs) { exit("Error in SQL"); } echo "

The Results

"; while ( odbc_fetch_row($rs) ) { $field1 = odbc_result($rs,1); $field2 = odbc_result($rs,2); $field3 = odbc_result($rs,3); echo "field1 : " . $field1 ; echo "field2 : " . $field2 ; echo "field3 : " . $field3 ; } $RowNumber = odbc_num_rows($rs); echo "The Number of Selected Rows = " . $RowsNumber ; //Closing The Connection odbc_close($conn); ?>

[/php]
Hope that helps…

Topcoder · October 13, 2015, 3:07am

odbc_num_rows() is not reliable

From the manual

Note: Using odbc_num_rows() to determine the number of rows available after a SELECT will return -1 with many drivers.

MS SQL it doesn’t work with.

You’re just going to have to change the SQL to something like this:

[php]
$query= " Select count(*) as counter from WIP_Master where serial =’$serial’ ";
$result =odbc_exec($connect,$query);
$arr = odbc_fetch_array( $result );
if ($arr[‘counter’] < 1 ) { echo ‘doesn’t exist’ };
[/php]