Trying to retrieve data from SQL Server DB using PHP and only get 1 for rowcount when there are 10000 rows

Question

So, I'm trying to retrieve data from a data base on my own www.ionos.com server, which is dedicated and running SQL Server 2019.

I'm using PHP to retrieve the data. But, even though there are over 10K rows, I only ever see 1 row!

When I retrieve the data, I get 100 rows (restricting the select to TOP(100)) but all the data is coming back as NULL which is absolutely not true.

Here is my PHP code:

<?php

// require_once('../../PtcDebug.php');
// $_GET['debug'] = true;        // turn on the debug
// $_GET['debug_off'] = true;    // turn off debug
// PtcDebug::load();
// COMMENT when through testing....
// $error_reporting = error_reporting(E_ALL);
// $ini_set = ini_set('display_errors', 'on');

$return_arr = array();

$parameters = array();

$host = "tcp:12.345.67.890,1433";
$uid = "sa";
$pwd = "**********";
$db = "someDBName";
$query = "SELECT TOP(100) 
       [Product_Key]
      ,[Legacy_Product_Key]
      ,[Product_Code]
      ,[Date_Added]
      ,[Title]
      ,[Weight]
      ,[MSRP]
      ,[Wholesale]
      ,[Available_for_Web_Sale]
      ,[Detailed_Description]
      ,[Product_Type]
      ,[Manufacturer]
      ,[Artist_Magician]
      ,[Maintain_MSRP]
      ,[All_Star_Product]
      ,[On_Sale]
      ,[SkillLevels]
      ,[Image_URL]
      ,[Thumbnail_URL]
      ,[Alternate_Images]
      ,[Videos]
      ,[Christmas_Themed]
      ,[Halloween_Themed]
      ,[Card_Magic_and_Trick_Decks]
      ,[Decks_Custom_Standard]
      ,[Lectures_and_Conventions]
      ,[Magazines]
      ,[Money_Magic]
      ,[Posters_Gifts_and_Collectables]
      ,[Refills]
      ,[Silk_and_Silk_Magic]
      ,[Special_Effects_Fire_Smoke_Sound]
      ,[Sponge_and_Sponge_Magic]
      ,[Tables_and_Cases]
      ,[Theory_History_and_Business]
      ,[Toy_Magic_Toy_Kits_Puzzles]
      ,[Utility]
      ,[Close_Up_Performer]
      ,[Comedy_Performer]
      ,[Escape_Performer]
      ,[Gambling_Performer]
      ,[Illusionist]
      ,[Juggling_Performer]
      ,[Kids_Show_and_Balloon_Performer]
      ,[Mentalism_Bizarre_and_Psychokinesis_Perf]
      ,[Stage_Parlor_Performer]
      ,[Religious_and_Gospel_Performer]
      ,[Street_Performer]
      ,[Walk_Around_Performer]
      ,[Black_Label]
      ,[Limited_Edition] FROM murphys_product_list_new";

$querycnt = "SELECT COUNT(*) FROM a_database_table";
$dbconn = new PDO("sqlsrv:Server=12.345.67.890,1433;Database=" . $db . "", $uid, $pwd);
$dbconn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

if ($dbconn === false) {
    printf("There was an error opening the connection");
    die("Error opening connection: " . PDO::ERRMODE_EXCEPTION);
} else {



    /* Connect using SQL Server Authentication. */
    try {
        printf("Inside TRY<br>");

        $stmtcnt = $dbconn->prepare($querycnt);
        // $stmtcnt->execute();
        print_r($stmtcnt->execute() . "<br>");

        $stmtproducts = $dbconn->prepare($query);
        $stmtproducts->execute();

        // if ($row = $stmtproducts->fetch(PDO::FETCH_ASSOC)) {
        //     printf("Inside IF for rows - " . $row . "<br>");
        //     foreach ($row as $item) {
        //         $products_arr = array(
        //             "status" => true,
        //             "id" => $item['Product_Key'],
        //             "productcd" => $item['Product_Code']
        //         );
        //     }
        // } else {
        //     $products_arr = array(
        //         "status" => false,
        //         "message" => "Invalid Username and Password!",
        //     );
        // }
        print_r(json_encode($stmtproducts) . "<br>");

    } catch (Exception $e) {
        printf("Inside CATCH<br>");
        $e->getMessage();
        printf("ERROR CONNECTION: %s\n", $e);
        die('Error connecting to SQL Server: ' . $e . '.');
    }
}

if (!empty($stmtproducts)) {
str_replace('T', ' ', $endtime) . "<p></p><ul>";
    foreach ($stmtproducts as $item) {
        $id = $item->Product_Key;
        $legacyid = $item->Legacy_Product_Key;
        $productcd = $item->Product_Code;
        $dateAdded = $item->Date_Added;
        $title = $item->Title;
        $weight = $item->Weight;
        $msrp = $item->MSRP;
        $wholesale = $item->Wholesale;
        $qty = $item->Available_for_Web_Sale;
        $detailed_description = $item->Detailed_Description;
        $productType = $item->Product_Type;
        $manufacturer = $item->Manufacturer;
        $artist_or_magician = $item->Artist_Magician;
        $allstar_product = $item->All_Star_Product;
        $onsale = $item->On_Sale;
        $skill_levels = $item->SkillLevels;
        $imageURL = $item->Image_URL;
        $thumbnailURL = $item->Thumbnail_URL;
        $alt_images = $item->Alternate_Images;
        $videos = $item->Videos;
        $christmas_Themed = $item->Christmas_Themed;
        $halloween_Themed = $item->Halloween_Themed;
        $card_magic_and_trick_decks = $item->Card_Magic_Trick_Decks;
        $decks_custom_standard = $item->Decks_Custom_Standard;
        $lectures_and_conventions = $item->Lectures_and_Conventions;
        $magazines = $item->Magazines;
        $money_magic = $item->Money_Magic;
        $posters_gifts_and_collectables = $item->Posters_Gifts_and_Collectables;
        $refills = $item->Refills;
        $silk_and_silk_magic = $item->Silk_and_Silk_Magic;
        $special_effects_fire_smoke_sound = $item->Special_Effects_Fire_Smoke_Sound;
        $sponge_and_Sponge_magic = $item->Sponge_and_Sponge_Magic;
        $tables_and_cases = $item->Tables_and_Cases;
        $theory_history_and_business = $item->Theory_History_and_Business;
        $toy_magic_toy_kits_puzzles = $item->Toy_Magic_Toy_Kits_Puzzles;
        $utility = $item->Utility;
        $close_up_performer = $item->Close_Up_Performer;
        $comedy_performer = $item->Comedy_Performer;
        $escape_performer = $item->Escape_Performer;
        $gambling_performer = $item->Gambling_Performer;
        $illusionist = $item->Illusionist;
        $juggling_performer = $item->Juggling_Performer;
        $kids_show_and_balloon_performer = $item->Kids_Show_and_Balloon_Performer;
        $mentalism_performer = $item->Mentalism_Bizarre_and_Psychokinesis_Perf;
        $stage_parlor_performer = $item->Stage_Parlor_Performer;
        $religious_performer = $item->Religious_and_Gospel_Performer;
        $street_performer = $item->Street_Performer;
        $walk_around_performer = $item->Walk_Around_Performer;
        $black_label = $item->Black_Label;
        $limited_edition = $item->Limited_Edition;

        // get rid of \\" and then \\""

        $title = str_replace('5\\"x8\\"', '5x8', $title);
        $title = str_replace('\\\\""Six\\\\""', 'Six', $title);
        $title = str_replace('3\\\\"X5\\\\"', '3x5', $title);

        stripslashes($title);

        $return_arr[] = array(
            "id" => $id,
            "legacyid" => $legacyid,
            "product_cd" => $productcd,
            "dateadded" => $dateAdded,
            "title" => $title,
            "weight" => $weight,
            "price" => $msrp,
            "wholesale" => $wholesale,
            "qty" => $qty,
            "htmldesc" => $detailed_description,
            "producttype" => $productType,
            "manufacturer" => $manufacturer,
            "artist_magician" => $artist_or_magician,
            "allstarproduct" => $allstar_product,
            "onsale" => $onsale,
            "skill_level" => $skill_levels,
            "imgfilename" => $imageURL,
            "imgthumbname" => $thumbnailURL,
            "altimages" => $alt_images,
            "videos" => $videos,
            "xmas" => $christmas_Themed,
            "halloween" => $halloween_Themed,
            "carddeckstricks" => $card_magic_and_trick_decks,
            "customstandarddecks" => $decks_custom_standard,
            "lecturesconventions" => $lectures_and_conventions,
            "magazines" => $magazines,
            "money" => $money_magic,
            "refills" => $refills,
            "silks" => $silk_and_silk_magic,
            "spfx" => $special_effects_fire_smoke_sound,
            "sponges" => $sponge_and_Sponge_magic,
            "tablescases" => $tables_and_cases,
            "theoryhistory" => $theory_history_and_business,
            "toyskitspuzzles" => $toy_magic_toy_kits_puzzles,
            "utility" => $utility,
            "closeup" => $close_up_performer,
            "comedy" => $comedy_performer,
            "escape" => $escape_performer,
            "gambling" => $gambling_performer,
            "illusionist" => $illusionist,
            "juggling" => $juggling_performer,
            "kidsballoons" => $kids_show_and_balloon_performer,
            "mentalism" => $mentalism_performer,
            "stageparlor" => $stage_parlor_performer,
            "religious" => $religious_performer,
            "street" => $street_performer,
            "walkaround" => $walk_around_performer,
            "blacklabel" => $black_label,
            "ltdedition" => $limited_edition

        );
    }

    $obj = (object) [
        "status" => 200,
        "statusText" => "Success",
        "data" => $return_arr
    ];

    // Save data to sessionStorage
    // sessionStorage . setItem('allmagictopresent', json_encode($obj, JSON_PRETTY_PRINT));

    echo json_encode($obj, JSON_PRETTY_PRINT);
} else {
    echo "No items found";
}

What I get back from this is the following for this URL: http://magicdjs.com/webservices/vendor/murphs/murphs-services-mssql.php

Inside TRY
1
{"queryString":"SELECT TOP(100) \n [Product_Key]\n ,[Legacy_Product_Key]\n ,[Product_Code]\n ,[Date_Added]\n ,[Title]\n ,[Weight]\n ,[MSRP]\n ,[Wholesale]\n ,[Available_for_Web_Sale]\n ,[Detailed_Description]\n ,[Product_Type]\n ,[Manufacturer]\n ,[Artist_Magician]\n ,[Maintain_MSRP]\n ,[All_Star_Product]\n ,[On_Sale]\n ,[SkillLevels]\n ,[Image_URL]\n ,[Thumbnail_URL]\n ,[Alternate_Images]\n ,[Videos]\n ,[Christmas_Themed]\n ,[Halloween_Themed]\n ,[Card_Magic_and_Trick_Decks]\n ,[Decks_Custom_Standard]\n ,[Lectures_and_Conventions]\n ,[Magazines]\n ,[Money_Magic]\n ,[Posters_Gifts_and_Collectables]\n ,[Refills]\n ,[Silk_and_Silk_Magic]\n ,[Special_Effects_Fire_Smoke_Sound]\n ,[Sponge_and_Sponge_Magic]\n ,[Tables_and_Cases]\n ,[Theory_History_and_Business]\n ,[Toy_Magic_Toy_Kits_Puzzles]\n ,[Utility]\n ,[Close_Up_Performer]\n ,[Comedy_Performer]\n ,[Escape_Performer]\n ,[Gambling_Performer]\n ,[Illusionist]\n ,[Juggling_Performer]\n ,[Kids_Show_and_Balloon_Performer]\n ,[Mentalism_Bizarre_and_Psychokinesis_Perf]\n ,[Stage_Parlor_Performer]\n ,[Religious_and_Gospel_Performer]\n ,[Street_Performer]\n ,[Walk_Around_Performer]\n ,[Black_Label]\n ,[Limited_Edition] FROM murphys_product_list_new"}
{ "status": 200, "statusText": "Success", "data": [ { "id": null, "legacyid": null, "product_cd": null, "dateadded": null, "title": "", "weight": null, "price": null, "wholesale": null, "qty": null, "htmldesc": null, "producttype": null, "manufacturer": null, "artist_magician": null, "allstarproduct": null, "onsale": null, "skill_level": null, "imgfilename": null, "imgthumbname": null, "altimages": null, "videos": null, "xmas": null, "halloween": null, "carddeckstricks": null, "customstandarddecks": null, "lecturesconventions": null, "magazines": null, "money": null, "refills": null, "silks": null, "spfx": null, "sponges": null, "tablescases": null, "theoryhistory": null, "toyskitspuzzles": null, "utility": null, "closeup": null, "comedy": null, "escape": null, "gambling": null, "illusionist": null, "juggling": null, "kidsballoons": null, "mentalism": null, "stageparlor": null, "religious": null, "street": null, "walkaround": null, "blacklabel": null, "ltdedition": null },.... and so on

Question: Why are all NULLS coming back when in SQL Server Mgmt Studio, I see this...

UPDATE BECAUSE OF DALE

        if ($stmtproducts->fetch(PDO::FETCH_ASSOC)) {
            printf("Inside IF for rows - " . $row . "<br>");
            while ($row = $stmtproducts->fetch(PDO::FETCH_ASSOC)) {
                $products_arr = array(
                    "status" => true,
                    "id" => $row['Product_Key'],
                    "productcd" => $row['Product_Code'],
                    "title" => $row['Title']
                );
                print_r(json_encode($products_arr) . "<br>");

            }
            $obj = (object) [
                "status" => 200,
                "statusText" => "Success",
                "data" => $products_arr
            ];
            echo json_encode($obj, JSON_PRETTY_PRINT);
        } else {
            $products_arr = array(
                "status" => false,
                "message" => "Invalid Username and Password!",
            );
        }

Re-run this to see the different results:

http://magicdjs.com/webservices/vendor/murphs/murphs-services-mssql.php

Now I need to put ALL those records BACK into the

            $obj = (object) [
                "status" => 200,
                "statusText" => "Success",
                "data" => $products_arr
            ];

But I'm only putting "1" object vice 100. I'm close Dale. Thank you! A wee bit more help please, sir and make it a solution and I'll vote for you.

UPDATE FOR ROWCOUNT:

This works in MSSQL Server Mgmt Studio but not my code

SELECT COUNT(*) FROM murphys_product_list_new

You're outputting the contents of $stmtproducts, which is not the data you are looking for. Why is this commented out: $row = $stmtproducts->fetch(PDO::FETCH_ASSOC)? Also, it should be in a loop to repeatedly fetch all the rows, e.g. while ($row = $stmtproducts->fetch(PDO::FETCH_ASSOC)) { ... }. — kmoser
– kmoser, Commented Feb 3, 2022 at 19:03
You're 100% CORRECT! OMG! Now, I just need to rebuild the ARRAY and I'm only getting 1 ROW, not the rows you see being retrieved, Dale. See my UPDATE of the code under UPDATE. I think you'll be pleased. — Peter The Angular Dude
– Peter The Angular Dude, Commented Feb 6, 2022 at 18:09

kmoser · Accepted Answer · 2022-02-06 20:23:40Z

1

You're creating $products_arr multiple times in a loop but you are only ever ending up with one element in the array.

Instead of $products_array = array(...) (which will replace the contents of $products_array every time with one single element), you need to use $products_array[] = array(...), which will push an element to the end of the $products_array:

This should give you an array of elements:

   if ($stmtproducts->fetch(PDO::FETCH_ASSOC)) {
        printf("Inside IF for rows - " . $row . "<br>");
        while ($row = $stmtproducts->fetch(PDO::FETCH_ASSOC)) {
            $products_arr[] = array(
                "status" => true,
                "id" => $row['Product_Key'],
                "productcd" => $row['Product_Code'],
                "title" => $row['Title']
            );
            print_r(json_encode($products_arr) . "<br>");

        }
        $obj = (object) [
            "status" => 200,
            "statusText" => "Success",
            "data" => $products_arr
        ];
        echo json_encode($obj, JSON_PRETTY_PRINT);
    } else {
        $products_arr = array(
            "status" => false,
            "message" => "Invalid Username and Password!",
        );
    }

answered Feb 6, 2022 at 20:23

kmoser

9,3693 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Peter The Angular Dude Over a year ago

This is perfect, thank you Dale! Last bit of business, sir. The ROWCOUNT still only returns 1 but there are 10,000+ $stmtcnt = $dbconn->prepare($querycnt); $numRows = $stmtcnt->execute(); print_r("Number Rows Returned: " . $numRows . "<br>"); This is the SQL $querycnt = "SELECT COUNT(*) FROM murphys_product_list_new"; In MSSQL Server Mgmt Studio, I get back all 10K+ rows in a rowcount. Why still only 1?

Peter The Angular Dude Over a year ago

I got it to work, Dale. It's great! Thank you for all your help!

kmoser Over a year ago

You're welcome, but I'm not Dale :)

Peter The Angular Dude Over a year ago

Oops....no worries.

Collectives™ on Stack Overflow

Trying to retrieve data from SQL Server DB using PHP and only get 1 for rowcount when there are 10000 rows

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related