• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

Help with MySQL - num_rows NOT returning proper value

In this php snipet I am creating a record in table itemhdr. $itemcount is a variable is loaded into this php from the calling html program. $itemcount represents the number of line items that should be contained in a line item table.

After the itemhdr record is created. I want to count the records in the itemdtl table and then populate a field on the itemhdr table. I will then compare these values to VERIFY that the number of items that were expected to be loaded in the table actually were.

The code below gives me very random results. For example I know that there should be 6 items that get loaded. If I view the itemdtl table I see the 6 items. When I view the itemhdr I see that the itemcount field equals 6 BUT the verifycount jumps all around, sometimes it's 2, 3, 5 it's NEVER the correct number.

I've checked the indexes on the mysql table and have played around with COUNT(*) and num_rows but am not finding the answer.

Any help would be greatly appreciated.


$conn = mysql_connect("localhost", "", "Y") or die(mysql_error());
    mysql_select_db("test");
                  
$result = mysql_query("INSERT INTO itemhdr (ukey, user, trxdate, trxtime, email_add, email_sent, itemcount, verifycount)
            VALUES ('$ukey', '$ser', '$vardate', '$vartime', '$email_add', 'N', '$itemcount', 0)");
if (!$result) {
    die('QUERY ERROR: sendcarthdr ' . mysql_error());
}
                                    
$query = mysql_query("SELECT * FROM itemdtl WHERE itemdtl.ukey = '$ukey'");
$vcount = mysql_num_rows($query);
                  
$sql = mysql_query("SELECT verifycount FROM itemhdr WHERE itemhdr.ukey = '$ukey'");
$sql = mysql_query("UPDATE itemhdr SET itemhdr.verifycount = '$vcount'");
                                    
mysql_close($conn);
0
kbios
Asked:
kbios
2 Solutions
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
THe following row may need to be modified if I am understanding you corretly.

$sql = mysql_query("UPDATE itemhdr SET itemhdr.verifycount = '$vcount'");

try this:
$sql = mysql_query("UPDATE itemhdr SET itemhdr.verifycount = '$vcount' where itemhdr.ukey = '$ukey'");
0
 
kbiosAuthor Commented:
Thanks, but the same result.

I'm new to php and perhaps you can help me in understanding something. Given the 'randomness' of the $vcount; I'm wondering if the first php, the one that builds the populates the itemdtl table is perhaps not done running when I launch the second program (?) Here is the html with the code that launches the php programs.

I was assuming that since the first program has a loop that the first program would be fully executed prior to the launch of the second program, Is that a safe assumption? Is it possible that program 2 is getting launched before program 1 is finished? If so, any suggestions as to how to wait?

for( var i = 1; i<= val; i++)
  {
  if ( localStorage["item" + i] != "X" )
  {
     itemctr=itemctr + 1;
                                
                          $.ajax({  
                            type: "POST",  
                            url: "sendcartitem.php",
                            data: "ukey="+ localStorage.ukey + "&item="+ localStorage["item" + i] +
                      "&showserver="+ localStorage.ShowServer,  
             success: function(){}
                          });
                        }
                    }
                   
localStorage.itemCtr = itemctr;

       $.ajax({  
                            type: "POST",  
                            url: "sendcarthdr.php",
                            data: "ukey="+ localStorage.ukey + "&user="+ localStorage.User +
                            "&email="+ localStorage.EMAdd+ "&itemcount="+ itemctr +
                            "&showserver="+ localStorage.ShowServer,
                            success: function(reconcile){
                                          $('form#sendcart').hide(function(){$('div.success').fadeIn();});
                                          }
                          });                  
0
 
nanharbisonCommented:
Have you tried echoing out results to see what is being returned?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Beverley PortlockCommented:
PHP will complete one query before starting the nexy, but if you are mixing this with AJAX (examples above) with is asynchronous then all bets are off. Looking at these queries

                           
$query = mysql_query("SELECT * FROM itemdtl WHERE itemdtl.ukey = '$ukey'");
$vcount = mysql_num_rows($query);
                  
$sql = mysql_query("SELECT verifycount FROM itemhdr WHERE itemhdr.ukey = '$ukey'");
$sql = mysql_query("UPDATE itemhdr SET itemhdr.verifycount = '$vcount'");

Open in new window


The the 3rd one $sql = mysql_query("SELECT verifycount FROM itemhdr WHERE itemhdr.ukey = '$ukey'"); does nothing because you do nothing with the result set which is overwritten by the following query. If you are trying to read verify count then you need this


                           
$query = mysql_query("SELECT * FROM itemdtl WHERE itemdtl.ukey = '$ukey'");
$vcount = mysql_num_rows($query);
                  
$sql = mysql_query("SELECT verifycount FROM itemhdr WHERE itemhdr.ukey = '$ukey'");
$row = mysql_fetch_assoc($sql);
echo "Verify count is {$row['verifycount']}<br/>";

$sql = mysql_query("UPDATE itemhdr SET itemhdr.verifycount = '$vcount'");

Open in new window

0
 
Beverley PortlockCommented:
Actually, given what you are attempting, this may be closer (UNTESTED)

                           
$query = mysql_query("SELECT * FROM itemdtl WHERE itemdtl.ukey = '$ukey'");
$vcount = mysql_num_rows($query);
                  
$sql = mysql_query("SELECT verifycount FROM itemhdr WHERE itemhdr.ukey = '$ukey'");
$row = mysql_fetch_assoc($sql);
$vcount = $row['verifycount'];

$sql = mysql_query("UPDATE itemhdr SET itemhdr.verifycount = '$vcount'");

Open in new window

0
 
kbiosAuthor Commented:
Thanks for the help. The more I work with it; the problem is that the ajax code for the second program is getting launched sooner than I need it to. Basically the line items are not always in the table ready for me. I'm trying some different approaches.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now