[Resolved] Perplexing Database Problem!

[chads2k2]

Ok ... I have tried to work on this problem for a few hours now and can't find an answer. See if this has happened to anyone else.

Here is the setup. I have a script that includes a database connection script. I know it works because I use it through the whole site. That is NOT the problem. Here IS the problem. When I run the code below it will work on some cases. But not work all the time. I tested the outputed SQL statements through Enterprise Manager (MSSQL admin suite) (phpmyadmin for MySQL) and ran the statement. It worked fine. So I know it is something with the code. Just can't find out what is causing it not to work!

   $build_ids[0] = "";
   $check_buildids = str_replace(",","",$build_ids);
   if(strlen(trim($check_buildids)) < 1)
   {
      // This is where it will build what SQL statement to send MSSQL.
      $update_wallplatea = "UPDATE smithch.wallplates SET wallplate_tele_id='1' WHERE wallplate_id='$wpid'";
      echo "$update_wallplatea<br>";
   }else{
      // This is where it will built what SQL statement to send MSSQL.
      $update_wallplatea = "UPDATE smithch.wallplates SET wallplate_tele_id='$build_ids' WHERE wallplate_id='$wpid'";
      echo "$update_wallplatea<br>";
   }
   $update_wallplate = "SELECT * FROM smithch.wallplates";

   $update_wallplate_result = mssql_query($update_wallplate);
   echo "Result = $update_wallplate_result<br>";
   exit();

Browser Output:
UPDATE smithch.wallplates SET wallplate_tele_id='843,' WHERE wallplate_id='180'
Result = Resource id #6

The top part works but this one doesn't!

   $build_ids[0] = "";
   $check_buildids = str_replace(",","",$build_ids);
   if(strlen(trim($check_buildids)) < 1)
   {
      // This is where it will build what SQL statement to send MSSQL.
      $update_wallplate = "UPDATE smithch.wallplates SET wallplate_tele_id='1' WHERE wallplate_id='$wpid'";
      echo "$update_wallplate<br>";
   }else{
      // This is where it will built what SQL statement to send MSSQL.
      $update_wallplate = "UPDATE smithch.wallplates SET wallplate_tele_id='$build_ids' WHERE wallplate_id='$wpid'";
      echo "$update_wallplate<br>";
   }
   $update_wallplate_result = mssql_query($update_wallplate);
   echo "Result = $update_wallplate_result<br>";
   exit();

Browser Output:
UPDATE smithch.wallplates SET wallplate_tele_id='843,' WHERE wallplate_id='180'
Result = 

I even did this and it didn't work:

   $build_ids[0] = "";
   $check_buildids = str_replace(",","",$build_ids);
   if(strlen(trim($check_buildids)) < 1)
   {
      $tele_id_value = "1";
   }else{
      $tele_id_value = "$build_ids";
   }
   $update_wallplate = "UPDATE smithch.wallplates SET wallplate_tele_id='$tele_id_value' WHERE wallplate_id='$wpid'";
   $update_wallplate_result = mssql_query($update_wallplate);
   echo "Result = $update_wallplate_result<br>";
   exit();
   echo "<meta http-equiv='refresh' content='0; URL=edit.wallplate.php?wpid=$wpid&option=2'>";
   exit();

Browser Output:
Result = 

Can anyone figure out why my update statement wouldn't do what it should! I have no idea. This is the first time this has ever happened to me in 7 years of work. Really quite frustrating. Thanks!

Chad R. Smith

[bastien]

what is the data type of the field "wallplate_tele_id"?

[Sxooter]

Toss some error checking in there. My guess is that there aren't any rows to update, or the update is failing in some other way that error messages will help.

[chads2k2]

Hey thanks for the replies.

I have used the outputed SQL statement and put it stright into the database. When I do I get a 1 row affected. That row being the change I was trying to make.

The database field is setup correctly. That I am 100% positive of.

Any other ideas? I have thrown just about all the error checking I can into this thing and have found nothing. How would you guys go about error checking it? Just to see if I am missing something. Thank you!

Chad R. Smith

[chads2k2]

Ok I tried this now a little bit of checking into the problem.

   $build_ids[0] = "";
   $check_buildids = str_replace(",","",$build_ids);
   if(strlen(trim($check_buildids)) < 1)
   {
      // This is where it will build what SQL statement to send MSSQL.
      $update_with_no_existance = "UPDATE smithch.wallplates SET wallplate_tele_id='1' WHERE wallplate_id='$wpid'";
      $resulta = mssql_query($update_with_no_existance);
      echo "A = $resulta";
   }else{
      // This is where it will built what SQL statement to send MSSQL.
      $update_wallplate_ids = "UPDATE smithch.wallplates SET wallplate_tele_id='". $build_ids . "' WHERE wallplate_id='" . $wpid . "'";
      echo "$update_wallplate_ids<br>";
      $resultb = mssql_query($update_wallplate_ids);
      $last_message = mssql_get_last_message();
      echo "Message: $last_message<br>";
      echo "Result = $resultb<br>";
   }
   exit();

Browser Output:
UPDATE smithch.wallplates SET wallplate_tele_id='1257,' WHERE wallplate_id='180'
Message: Line 1: Incorrect syntax near ''.
Result = 

Does anyone have an idea why this isn't executing? It seems like when you put a variable in there it wouldn't work. However, when it has a static SQL statement it works fine. Any ideas? I am going batty trying to figure this out. I have done this a hundred times in my life and never have had this problem. Thanks,

Chad R. Smith

[bastien]

try taking out the table name in the statement. Could it be the trailing comma causing some confusion to the db? Try either removing it if not needed or adding a trailing space after the comma

[chads2k2]

Thanks for the reply. I have tried both. When I just have the whole statement without any variables in it, it works fine.

That is what is causing the perplexion. With SQL Server you need to have the fully qualified name. Such as (owner.tbl_name). So that is not the issue. Any other ideas of what I could check? I will post the entire code here:

if(($phoneid > 0) && ($wpid > 0) && ($option == 2) && ($delete == 1))
{
   include_once("includes/db.connection.inc.php");
   include_once("includes/user.functions.inc.php");

   // Get current telephones
   $select_current_phones = "SELECT wallplate_tele_id FROM smithch.wallplates WHERE wallplate_id='$wpid'";
   $select_current_phones_result = mssql_query($select_current_phones);
   while($phones_array = mssql_fetch_array($select_current_phones_result))
   {
      // Returned as a , seperated list.
      $phone_ids = $phones_array['wallplate_tele_id'];
   }
   $connected_phone_array = explode(",",$phone_ids);
   foreach($connected_phone_array as $temp_phone_id)
   {
      if($temp_phone_id != $phoneid)
      {
         $build_ids = $temp_phone_id . "," . $build_ids;
      }
   }
   $build_ids[0] = "";
   $check_buildids = str_replace(",","",$build_ids);
   if(strlen(trim($check_buildids)) < 1)
   {
      // This is where it will build what SQL statement to send MSSQL.
      $update_with_no_existance = "UPDATE smithch.wallplates SET wallplate_tele_id='1' WHERE wallplate_id='$wpid'";
      $resulta = mssql_query($update_with_no_existance);
      echo "A = $resulta";
   }else{
      // This is where it will built what SQL statement to send MSSQL.
      $update_wallplate_ids = "UPDATE smithch.wallplates SET wallplate_tele_id='". $build_ids . "' WHERE wallplate_id='" . $wpid . "'";
      echo "$update_wallplate_ids<br>";
      $resultb = mssql_query($update_wallplate_ids);
      $last_message = mssql_get_last_message();
      echo "Message: $last_message<br>";
      echo "Result = $resultb<br>";
   }
   exit();
   echo "<meta http-equiv='refresh' content='0; URL=edit.wallplate.php?wpid=$wpid&option=2'>";
   exit();
}

Chad

[Sxooter]

Are you sure the php library or something isn't adding in the tablename for you or something weird in there? Have you tried it (in the script) without the owner pre-pended on the query?

[chads2k2]

Yeah when I first started using MSSQL I tried that (too used to MySQL) but MSSQL server flipped out. I am pretty sure that the .dll isn't all messed up because the same UPDATE statement works in a different part of the code. Doing the same update statement for badem, just when a different option is selected.

Let me know I am about to go kick the SQL server when I get back to work.

Chad

[EDIT]
Line 1: Incorrect syntax near ''.
What the hell does that actually mean?
[/EDIT]

[Sxooter]

What kind of field are you trying to update? Is it a text type or a numeric? IF a numeric type, try dropping the comma in it.

[chads2k2]

Varchar(255). :-D It can handle the ,'s. It used to be something like 259,299,2999,.

Works fine. I am so confused.

Chad

[bastien]

Originally posted by chads2k2

That is what is causing the perplexion. With SQL Server you need to have the fully qualified name. Such as (owner.tbl_name).

Um, No. We use both sql server and mysql and have not qualified a table name yet other than when using a join. Never have mentioned the owner at all.

[chads2k2]

Sorry, but maybe it's our server or something. Anywhoo, nothing will work unless I do it like I have. Any other ideas?

Chad

[chads2k2]

I will pick up with this when I get back in the office tomorrow. I am just too tired right now and maybe I need some time to think about it. Night and thank you both so much for all your help! Till tomorrow, cya.

Chad

[chads2k2]

It hit me. Like a brick to the head just WHAM!. Here is the final code that works like a charm.

if(($phoneid > 0) && ($wpid > 0) && ($option == 2) && ($delete == 1))
{
   include_once("includes/db.connection.inc.php");
   include_once("includes/user.functions.inc.php");

   // Get current telephones
   $select_current_phones = "SELECT wallplate_tele_id FROM smithch.wallplates WHERE wallplate_id='$wpid'";
   $select_current_phones_result = mssql_query($select_current_phones);
   while($phones_array = mssql_fetch_array($select_current_phones_result))
   {
      // Returned as a , seperated list.
      $phone_ids = $phones_array['wallplate_tele_id'];
   }
   $connected_phone_array = explode(",",$phone_ids);
   foreach($connected_phone_array as $temp_phone_id)
   {
      if($temp_phone_id != $phoneid)
      {
         // CHANGED FROM:
         // $build_ids = $temp_phone_id . "," . $build_ids;
         // TO:
         $build_ids .= $temp_phone_id . ",";
      }
   }
   // TOOK OUT: $build_ids[0] = "";
   // That is what was giving the "Line 1: Incorrect syntax near ''." error.
   // The end result has two ,,'s so I just shorten it down to one ,.
   $build_ids = str_replace(",,",",",$build_ids);
   $check_buildids = str_replace(",","",$build_ids);
   if(strlen(trim($check_buildids)) < 1)
   {
      // This is where it will build what SQL statement to send MSSQL.
      $update_with_no_existance = "UPDATE smithch.wallplates SET wallplate_tele_id='1' WHERE wallplate_id='$wpid'";
      $resulta = mssql_query($update_with_no_existance);
   }else{
      // This is where it will built what SQL statement to send MSSQL.
      $update_wallplate_ids = "UPDATE smithch.wallplates SET wallplate_tele_id='$build_ids' WHERE wallplate_id='$wpid'";
      mssql_query($update_wallplate_ids);
   }
   echo "<meta http-equiv='refresh' content='0; URL=edit.wallplate.php?wpid=$wpid&option=2'>";
   exit();
}

Please note the changes that were made. They were subtle but damn made a big difference. Sorry for the snippy comment to you Bastien I didn't mean no harm. Just when I went to show it to my boss and it didn't work I got a little "worried". Glad it works now. :-D Thank you both for all your help and I wish you both the best of luck!

Chad R. Smith

[bastien]

No worries, mate. BTDT. Glad you got it sorted