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

Missing Array element

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
tonelm54
Asked:
tonelm54
1 Solution
 
Ray PaseurCommented:
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
 
Jagadishwor DulalBraces MediaCommented:
What's about select query:
$resTbl = $mysqli->query("SELECT * FROM  `staff_DA` WHERE  `userID` LIKE  '{$_GET['empID']}';");   

Open in new window

0
 
Kim WalkerWeb Programmer/TechnicianCommented:
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
Ray PaseurCommented:
@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
 
tonelm54Author Commented:
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
 
tonelm54Author Commented:
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
 
tonelm54Author Commented:
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
 
Ray PaseurCommented:
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
 
tonelm54Author Commented:
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
 
Ray PaseurCommented:
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

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

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