Solved

Missing Array element

Posted on 2013-06-13
10
155 Views
Last Modified: 2013-06-19
Got an issue with a select PHP statment, and really dont understand.

Ive got my code, which executes
$resTbl = $mysqli->query("SELECT * FROM  `staff_DA` WHERE  `userID` LIKE  '" . $_GET['empID'] . "';");   
while($rowTbl = $resTbl->fetch_array()) {
}

Open in new window


It runs fine, and I can pull all my fields out, apart from 'fileID'.

When I add the code:-
foreach ($rowTbl as $key => $value) {
     $errBody = $errBody . "Post - " . $key . "=" . $value . "\n";
}

Open in new window


$errBody =

Post - 0=aab853a8-b8a4-11e2-aa8c-9abde5429d4b
Post - userID=aab853a8-b8a4-11e2-aa8c-9abde5429d4b
Post - 1=2013-06-13
Post - testDate=2013-06-13
Post - 2=Company 1
Post - tester=Company 1
Post - 3=
Post - TestScore=
Post - 4=
Post - Test2sScore=
Post - 5=
Post - comments=
Post - 6=f9c7ff18-d427-11e2-bdb1-9802434aa769
Post - fileID=
Post - 7=Apache2_Virtual_Hosts.pdf
Post - fileName=Apache2_Virtual_Hosts.pdf
Post - 8=application/pdf
Post - Mime=application/pdf

My problem is:-

Post - 6=f9c7ff18-d427-11e2-bdb1-9802434aa769
Post - fileID=

If I echo out $rowTbl[6] it works fine, but use the ID fileID ( $rowTbl['fileID']  )it doesnt pull anything out. I can pull the rest of the fields out by  $rowTbl[8] and $rowTbl['Mime']

Im totally confused :-S Any ideas?
0
Comment
Question by:tonelm54
10 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39244514
Please post the CREATE TABLE statement.  Fetch_array() returns too much data.  Try it like this and post the results back here:

while($rowTbl = $resTbl->fetch_object()) { var_dump($rowTbl); }

Also, you might want to read these links.  It looks like you may be using an external variable in a query.  That is a big No-No!
http://php.net/manual/en/security.php
http://php.net/manual/en/language.variables.external.php
http://php.net/manual/en/tutorial.forms.php

This article shows how to prepare external data for safe use in 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
0
 
LVL 15

Expert Comment

by:Jagadishwor Dulal
ID: 39244736
What's about select query:
$resTbl = $mysqli->query("SELECT * FROM  `staff_DA` WHERE  `userID` LIKE  '{$_GET['empID']}';");   

Open in new window

0
 
LVL 22

Expert Comment

by:Kim Walker
ID: 39244848
It appears that fetch_array is misbehaving. I'm not sure what you would call the result type that's being returned. Have you tried:
$resTbl = $mysqli->query("SELECT * FROM  `staff_DA` WHERE  `userID` LIKE  '" . $_GET['empID'] . "';");   
while($rowTbl = $resTbl->fetch_array(MYSQLI_BOTH)) {
}

Open in new window

or if you just want by column name without index numbers
$resTbl = $mysqli->query("SELECT * FROM  `staff_DA` WHERE  `userID` LIKE  '" . $_GET['empID'] . "';");   
while($rowTbl = $resTbl->fetch_assoc()) {
}

Open in new window

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39244879
@xmediaman: That's what it looked like to me, too.  But millions of web sites use fetch_array() every day and it works 100% of the time for them.  That's why I want to see the CREATE TABLE and the var_dump() from fetch_object().  I suspect there is something else at work here.  I would like to try to reproduce the issue in a controlled environment where I can try different things to compare outputs from the various fetch methods.
0
 

Author Comment

by:tonelm54
ID: 39248410
Good evening Ray_Paseur,

By using
while($rowTbl = $resTbl->fetch_object()) {
                      $strBody = $strBody . var_dump($rowTbl) . "\n\n\n\n";
                    }

Open in new window

I get:-

object(stdClass)#4 (9) { ["userID"]=> string(36) "aab853a8-b8a4-11e2-aa8c-9abde5429d4b" ["testDate"]=> string(10) "2013-06-14" ["tester"]=> string(9) "Company 1" ["alcoholScore"]=> string(0) "" ["drugsScore"]=> string(0) "" ["comments"]=> string(0) "" ["fileID"]=> string(36) "9a33a030-d511-11e2-bdb1-9802434aa769" ["fileName"]=> string(25) "Apache2_Virtual_Hosts.pdf" ["Mime"]=> string(15) "application/pdf" }

Although the select statement is pulling from an external variable ($_GET['empID']), it is properly escaped:-
      $resTbl = $mysqli->query("SELECT * FROM  `staff_DA` WHERE  `userID` LIKE  '" . $mysqli->real_escape_string($_GET['empID']) . "';");

Open in new window

0
 

Author Comment

by:tonelm54
ID: 39248411
xmediaman:-
I did start using 'fetch_object()' as opposed to 'fetch_array()' however ran into issues with field names with spaces (although I dont put spaces in field names, its hard to change old inherited tables withouth causing issues with existing apps).
0
 

Author Comment

by:tonelm54
ID: 39248437
Ray_Paseur:-
As requested, this is my table creation:-

CREATE TABLE IF NOT EXISTS `staff_DA` (
  `userID` varchar(36) NOT NULL,
  `testDate` date NOT NULL,
  `tester` varchar(55) NOT NULL,
  `alcoholScore` varchar(25) default NULL,
  `drugsScore` varchar(25) default NULL,
  `comments` text,
  `fileID` varchar(45) default NULL,
  `fileName` varchar(55) default NULL,
  `Mime` varchar(55) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Open in new window


My entire test code, which replicates the issue is:-
<?php
    $dbHost = "localhost";
    $dbUser = "usr-965";
    $dbPass = "bannana27";
    $dbName = "db-763321";

    if (!class_exists(DB)) {
	    class DB extends mysqli {
	        public function uuid() {
	            $resUUID = $this->query("SELECT uuid() as 'uid';");
	            $rowUUID  = $resUUID->fetch_assoc();
	            return $rowUUID["uid"];
	        }
        }
    }
    $mysqli = new DB($dbHost, $dbUser, $dbPass, $dbName);
?>


<div id="DNContents">
  <div id="divResults">
    <table border="1">
      <tr>
          <thead>
              <td style="width:65px;">Date</td>
              <td style="width:65px;">Tester</td>
              <td style="width:65px;">Alcohol</td>
              <td style="width:65px;">Drugs</td>
              <td style="width:115px;">Comments</td>
              <td style="width:65px;">Attachments</td>
          </thead>
      </tr>
      <?php
      $resTbl = $mysqli->query("SELECT * FROM  `staff_DA` WHERE  `userID` LIKE  'aab853a8-b8a4-11e2-aa8c-9abde5429d4b';");

      if ($resTbl->num_rows == 0) {
          ?>
          <tr>
            <td colspan="6" style="text-align: center;">No Rows returned</td>
          </tr>
          <?php
      }
      else {
        while($rowTbl = $resTbl->fetch_array()) {
          ?>
          <tr>
            <td><?php echo $rowTbl['testDate']; ?></td>
            <td><?php echo $rowTbl['tester']; ?></td>
            <td><?php echo $rowTbl['alcholScore']; ?></td>
            <td><?php echo $rowTbl['drugsScore']; ?></td>
            <td><?php echo $rowTbl['comments']; ?></td>
            <td><?php
                if (!$rowTbl['fileID'] = "") {
                    ?><a href="#" alt="<?php echo $rowTbl['fileName']; ?>" onClick="window.open('downloadFile.php?Type=DA&fileID=<?php echo $rowTbl['fileID']; ?>');">1-<?php echo $rowTbl['fileName']; ?></a><?php
                    ?><a href="#" alt="<?php echo $rowTbl['fileName']; ?>" onClick="window.open('downloadFile.php?Type=DA&fileID=<?php echo $rowTbl[6]; ?>');">2-<?php echo $rowTbl['fileName']; ?></a><?php
                }
                else { echo "&nbsp;"; }  ?>
            </td>
          </tr>
          <?php
          }
      }
      ?>
      <?php
      if ($rowPriv['allowUpdate']=='true') {
        ?><tr><td colspan="6" style="text-align: center;"><input id="cmdAddNew" type="button" value="Upload new result" onClick="$('#divResults').css('display','none'); $('#divNew').css('display','block'); "></td></tr><?php
      }
      ?>
    </table>

Open in new window


Which produces:-
<div id="DNContents">
  <div id="divResults">
    <table border="1">
      <tr>
          <thead>
              <td style="width:65px;">Date</td>
              <td style="width:65px;">Tester</td>
              <td style="width:65px;">Alcohol</td>
              <td style="width:65px;">Drugs</td>
              <td style="width:115px;">Comments</td>
              <td style="width:65px;">Attachments</td>
          </thead>
      </tr>
                <tr>
            <td>2013-06-13</td>
            <td>Company 1</td>
            <td></td>
            <td></td>
            <td></td>
            <td><a href="#" alt="Apache2_Virtual_Hosts.pdf" onClick="window.open('downloadFile.php?Type=DA&fileID=');">1-Apache2_Virtual_Hosts.pdf</a><a href="#" alt="Apache2_Virtual_Hosts.pdf" onClick="window.open('downloadFile.php?Type=DA&fileID=f9c7ff18-d427-11e2-bdb1-9802434aa769');">2-Apache2_Virtual_Hosts.pdf</a>            </td>
          </tr>
                    <tr>
            <td>2013-06-14</td>
            <td>Company 1</td>
            <td></td>
            <td></td>
            <td></td>
            <td><a href="#" alt="Apache2_Virtual_Hosts.pdf" onClick="window.open('downloadFile.php?Type=DA&fileID=');">1-Apache2_Virtual_Hosts.pdf</a><a href="#" alt="Apache2_Virtual_Hosts.pdf" onClick="window.open('downloadFile.php?Type=DA&fileID=9a33a030-d511-11e2-bdb1-9802434aa769');">2-Apache2_Virtual_Hosts.pdf</a>            </td>
          </tr>
                    </table>

Open in new window



As you can see
echo $rowTbl['fileID'];     produces ""
echo $rowTbl[6];             produces "9a33a030-d511-11e2-bdb1-9802434aa769"
0
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39248970
object(stdClass)#4 (9) {
["userID"]=> string(36) "aab853a8-b8a4-11e2-aa8c-9abde5429d4b"
["testDate"]=> string(10) "2013-06-14"
["tester"]=> string(9) "Company 1"
["alcoholScore"]=> string(0) ""
["drugsScore"]=> string(0) ""
["comments"]=> string(0) ""
["fileID"]=> string(36) "9a33a030-d511-11e2-bdb1-9802434aa769"
["fileName"]=> string(25) "Apache2_Virtual_Hosts.pdf"
["Mime"]=> string(15) "application/pdf" }

Open in new window

I don't see any column names with blanks or dashes, and I see that the fetch_object() method got the data for fileID.

The problem may be on line 53 of the test code where there is this statement:

if (!$rowTbl['fileID'] = "") {

Open in new window

In PHP (and many other languages) the single equal sign is the assignment operator. The double equal sign is the comparison operator.  The effect of this statement is to set $rowTbl['fileID'] to an empty string.  Hence the difference between the numbered array position and the indexed array position.
0
 

Author Closing Comment

by:tonelm54
ID: 39258736
I cant put the language I used when I read this comment, and how dumb line 53 mistake was :-S

Stupid mistake that no matter how long I was looking at it I didnt see.

Thank you
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39259341
Thanks for the points.  You're only about the 1,000,000th person, myself included, to make this mistake!  Welcome to our club ;-)
0

Featured Post

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

Suggested Solutions

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

832 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