Solved

php / MySQL problem

Posted on 2013-05-16
17
326 Views
Last Modified: 2013-05-16
We have a php application that manages database; it's been working for years.

Today I revised a program because it didn't account for a numeric field being larger than 2 digits (the program, NOT the database).

Now an update query DOES NOT work.

Here is the query & result:

qry = UPDATE schedule set request = '#76 has a cut zone line next to the first column in front of house/ bill to customer/ contact Bob Moore at #76 Justin with Raintree/As look at standing water by N gate by N pool. Between #10 and #11 by flower bed very wet. contact Charlie Freeman 634-0487', slots = 2 where ListID = 'C70000-988055540' and sn = 130
res=

these were produced by the following php statements:

$Qry = "UPDATE schedule set request = '$desc', slots = $slot where ListID = '$listid' and sn = $serno";
      $res = mysql_query ($Qry, $Link);
      if ($listid == "C70000-988055540") {
            echo "qry = " . $Qry . "<br>";
            echo "res=" . $res . "<br>";
      }      
Notice that the $res is null. The query does NOT update the database.

If I copy the query & use phpmysql to manually insert the update statement, it properly updates the table.

The structure of the table is attached,

What is wrong?
schedule-str.jpg
0
Comment
Question by:Richard Korts
  • 8
  • 4
  • 3
  • +1
17 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39172177
Do you have a copy of the previous version and the changed version?
0
 

Author Comment

by:Richard Korts
ID: 39172205
Dave,

I do but it's (of course) somewhat different.

Do you want me to post BOTH versions (full php program)?

The olde version uses depreciated php functions that some people yell at me about.

R
0
 
LVL 22

Expert Comment

by:Kim Walker
ID: 39172212
$res is the result of your query and is an object which cannot be echoed. It is the result of an UPDATE query and therefore contains no data. Try:
$Qry = "UPDATE schedule set request = '$desc', slots = $slot where ListID = '$listid' and sn = $serno";
      if ($res = mysql_query ($Qry, $Link) ) {
            if ($listid == "C70000-988055540") {
                  echo "qry = " . $Qry . "<br>";
                  echo "res=" . mysql_affected_rows($res) . "<br>";
            }
      } else {
            echo mysql_error($res)."<br>";
      }

Open in new window

This will echo the number of rows affected by the UPDATE query if successful or the error that is generated if not successful.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39172270
You said you changed something and now the query doesn't work.  Did you change the query too?
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39172331
Please see this article for examples of how to run MySQL queries and test for success or failure.  MySQL_Query() is not a black box -- it return a value that must be tested for success.  It can and will fail, sometimes for reasons that are not in your control, and it fails silently with respect to PHP.  Your script must use PHP function calls to test for success or failure of the queries.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

You cannot echo a resource, but you can use var_dump() to verify that your earlier call returned a resource.
0
 

Author Comment

by:Richard Korts
ID: 39172379
To xmediaman

I did yours; I get a COMPLETELY blank page.
0
 

Author Comment

by:Richard Korts
ID: 39172393
To DaveBaldwin

I changed the code that leads up to building the php variables included in the query. So the actual query is different; in particular the value of $slot.
0
 
LVL 22

Expert Comment

by:Kim Walker
ID: 39172402
If the query completed successfully and the listid was NOT "C70000-988055540", there would be nothing to echo. Let's try another variation that should echo something regardless of the outcome:
$Qry = "UPDATE schedule set request = '$desc', slots = $slot where ListID = '$listid' and sn = $serno";
      if ($res = mysql_query ($Qry, $Link) ) {
            if ($listid == "C70000-988055540") {
                  echo "qry = " . $Qry . "<br>";
                  echo "res=" . mysql_affected_rows($res) . "<br>";
            } else {
                  echo $listid . "<br>";
            }
      } else {
            echo mysql_error($res) . "<br>$Qry = " . $Qry . "<br>";
      }

Open in new window

0
 

Author Comment

by:Richard Korts
ID: 39172464
To xmediaman,

It produced exactly the attached (which seems impossible to me).
crazy.jpg
0
 

Author Comment

by:Richard Korts
ID: 39172485
To xmediaman,

FYI, it DID NOT update the database record on either of the last two tries. I looked at it in phpMyadmin.

R
0
 
LVL 22

Accepted Solution

by:
Kim Walker earned 500 total points
ID: 39172554
OK. That makes sense to me though I'm not sure why the error wasn't displayed. Try this:
$Qry = "UPDATE schedule set request = '$desc', slots = $slot where ListID = '$listid' and sn = $serno";
      if ($res = mysql_query ($Qry, $Link) ) {
            if ($listid == "C70000-988055540") {
                  echo "qry = " . $Qry . "<br>";
                  echo "res=" . mysql_affected_rows($res) . "<br>";
            } else {
                  echo $listid . "<br>";
            }
      } else {
            echo "Error = " . mysql_error() . "<br>Query = " . $Qry . "<br>";
      }

Open in new window

0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39172620
Why don't you try a SELECT to see if it returns what is supposed to be there?
$Qry = "SELECT * from schedule where ListID = '$listid' and sn = $serno";
$res = mysql_query ($Qry, $Link);
// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$res) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}

while ($row = mysql_fetch_assoc($res)) {
    echo $row['ListID'];
    echo $row['cust'];
    echo $row['slots'];
    echo $row['sn'];
}

Open in new window

0
 

Author Comment

by:Richard Korts
ID: 39172881
To: xmediaman

See following. This INSTANTLY solves the problem for me; I realized I was using the wrong version of the program hooked up to the OLD database. Ray Paseur & Dave Baldwin right too.

Error = Unknown MySQL server host 'db901.perfora.net' (1)
Query = UPDATE schedule set request = '#76 has a cut zone line next to the first column in front of house/ bill to customer/ contact Bob Moore at #76 Justin with Raintree/As look at standing water by N gate by N pool. Between #10 and #11 by flower bed very wet. contact Charlie Freeman 634-0487', slots = 2 where ListID = 'C70000-988055540' and sn = 130
0
 

Author Closing Comment

by:Richard Korts
ID: 39172884
Thanks for sticking with me & all those code changes.
0
 
LVL 22

Expert Comment

by:Kim Walker
ID: 39172951
You're welcome. Glad you got it figured out.
0
 

Author Comment

by:Richard Korts
ID: 39172975
It's amazing how when it all dawns on you; just one simple thing & I saw the WHOLE picture.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39173537
If you read the article and take some time to understand the examples, you can save yourself a lot of work and trouble in the future.  If you read the article and still have some questions, please be sure to post the questions at EE.  Best of luck with the project, ~Ray
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

806 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question