10 Replies Latest reply: Mar 1, 2013 3:29 PM by Tony290 RSS

    Advanced Recordsets

    Tony290

      I cannot get a $_SESSION variable to work in the advanced recordset below in DW CS5.5. I've sucessfully used this technique quite a bit in SQL "=" statements but cannot get it to work in a SQL "IN" statement. I've create the session variable $_SESSION['coachnos]in program A as "000062,000063" and 000062,000063 and pass it to program B. Neither format works. If I substitue the colname variable with "000062,000063" or 000063,000063 it works. I've spent 2 days of this with no luck.What am I missing? I'd appreciated any advice anyone has. Here's what my advanced recordset looks like.

       

      rsCoach.png

        • 1. Re: Advanced Recordsets
          bregent CommunityMVP

          When using the IN predicate, each text item in the list must be quoted individually.

           

          SELECT * from myTable WHERE myColumn IN ('000062', '000063')

           

          Not sure why it works when hardcoded. Can you show us the entire recordset code that is not working?

          • 2. Re: Advanced Recordsets
            Tony290 Community Member

            Thanks for your speedy response. I've passed the session variable as both '000062', '000063 and 000062,000063 and it still does not work. I display the session variable in program A before passing it to program B. Here's the recordset in program B:

             

            <? session_start();

            ob_start();

            ?>

            <?php require_once('Connections/tenniscare.php'); ?>

            <?php

            if (!function_exists("GetSQLValueString")) {

            function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

            {

              if (PHP_VERSION < 6) {

                $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

              }

             

              $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

             

              switch ($theType) {

                case "text":

                  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

                  break;   

                case "long":

                case "int":

                  $theValue = ($theValue != "") ? intval($theValue) : "NULL";

                  break;

                case "double":

                  $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

                  break;

                case "date":

                  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

                  break;

                case "defined":

                  $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

                  break;

              }

              return $theValue;

            }

            }

             

            $colname_rsCoach = "000000";

            if (isset($_SESSION[coachnos])) {

              $colname_rsCoach = $_SESSION[coachnos];

            }

            mysql_select_db($database_tenniscare, $tenniscare);

            $query_rsCoach = sprintf("SELECT coaches.ch_clnt_no, coaches.ch_private_fee, coaches.ch_group_fee, coaches.ch_racquet_stringer, clients.clnt_no FROM coaches, clients WHERE clients.clnt_no IN(%s) AND  clients.clnt_no = coaches.ch_clnt_no", GetSQLValueString($colname_rsCoach, "text"));

            $rsCoach = mysql_query($query_rsCoach, $tenniscare) or die(mysql_error());

            $row_rsCoach = mysql_fetch_assoc($rsCoach);

            $totalRows_rsCoach = mysql_num_rows($rsCoach);

            ?>

            <?php

            $startt = $_SESSION[coachnos];

            echo "start session" . $startt;

            ?>

            • 3. Re: Advanced Recordsets
              bregent CommunityMVP

              Echo $query_rsCoach so we can see what the SQL statement looks like after GetSQLValueString processes the parameter.

              • 4. Re: Advanced Recordsets
                Tony290 Community Member

                Here's what $query_rsCoach looks like: query string= SELECT coaches.ch_clnt_no, coaches.ch_private_fee, coaches.ch_group_fee, coaches.ch_racquet_stringer, clients.clnt_no FROM coaches, clients WHERE clients.clnt_no IN('000000') AND  clients.clnt_no = coaches.ch_clnt_no

                 

                It appears as though my $_SESSION variables are not being passed from program A to program B. I tried displaying them in program B and they're blank!  I've done this many times in other programs and I'm not sure why its not working now. Here's code from program A and program B.

                 

                Program A

                 

                //Check for coaches based on user's criteria

                $sql = "SELECT clients.clnt_no, clients.clnt_market,clients.clnt_coach,

                                coaches.ch_status,coaches.ch_type,coaches.ch_level,coaches.ch_lesson_location,coaches.ch_ clnt_no

                        FROM clients,coaches

                                WHERE clients.clnt_coach = 'Y' AND

                                    clients.clnt_market = '$market' AND

                                    coaches.ch_status = 'A' AND

                                    coaches.ch_type = '$coach_type' AND

                                    coaches.ch_level = '$coach_lvl' AND

                                    coaches.ch_lesson_location = '$lesson_loc' AND

                                    clients.clnt_no = coaches.ch_clnt_no";

                       

                $result = mysql_query($sql,$conn) or die(mysql_error());

                $num_rows = mysql_num_rows($result);

                $row_num = 0;

                $cnt = 1;

                $sw = "1";

                 

                //use this code in match query "IN" command

                if ($num_rows > 0) {

                    do    {

                   

                        if ($sw == "1") { //make first entry i.e. '000001'

                        $pronos = '\'' . mysql_result($result,$row_num,'clients.clnt_no') . '\''; //use for strings

                            //$pronos = mysql_result($result,$row_num,'clients.clnt_no');

                            $sw = "2";

                        }else {   

                        $pronos = $pronos . ',\'' . mysql_result($result,$row_num,'clients.clnt_no') . '\''; //use for strings

                            //$pronos = $pronos . "," . mysql_result($result,$row_num,'clients.clnt_no');

                }

                        $row_num = $row_num + 1;

                        $cnt = $cnt + 1;   

                    }while ($cnt <= $num_rows);

                }

                $_SESSION['coachnos'] = $pronos;

                 

                //get market description

                $sql = "SELECT mkt_name FROM markets WHERE mkt_no = '$market'";

                $result = mysql_query($sql,$conn) or die(mysql_error());

                $_SESSION['coachmkt'] = mysql_result($result,'mkt_name');

                 

                //get coach type description

                $sql = "SELECT cht_desc FROM coach_type WHERE cht_code = '$coach_type'";

                $result = mysql_query($sql,$conn) or die(mysql_error());

                $_SESSION['coachtype'] = mysql_result($result,'cht_desc');

                 

                //get coach level description

                $sql = "SELECT chl_desc FROM coach_level WHERE chl_code = '$coach_lvl'";

                $result = mysql_query($sql,$conn) or die(mysql_error());

                $_SESSION['coachlvl'] = mysql_result($result,'chl_desc');

                 

                //get lesson location

                if ($lesson_loc == "C") {

                    $_SESSION['coachloc'] = "Coach Location";

                }else {

                    $_SESSION['coachloc'] = "Flexible";

                }

                echo "coachnos= " . $_SESSION[coachnos] . "  ";

                echo "coachmkt= " . $_SESSION[coachmkt] . " ";

                echo "coachtype= " . $_SESSION[coachtype] . " ";

                echo "coachlvl= " . $_SESSION[coachlvl] . " ";

                echo "coachloc= " . $_SESSION[coachloc] . " ";

                header("Location: coachselect.php?" . SID);

                            exit;

                }

                 

                ?>

                <?php

                mysql_free_result($rsMrkt);

                 

                mysql_free_result($rsCoachType);

                 

                mysql_free_result($rsCoachLvl);

                 

                mysql_free_result($rsCoachLoc);

                ob_end_flush();

                ?>

                  <p> </p>

                  <p> </p>

                  <script type="text/javascript">

                var spryradio1 = new Spry.Widget.ValidationRadio("spryradio1");

                var spryselect1 = new Spry.Widget.ValidationSelect("spryselect1", {invalidValue:"000", validateOn:["blur"]});

                var spryselect2 = new Spry.Widget.ValidationSelect("spryselect2", {invalidValue:"0", validateOn:["blur"]});

                var spryselect3 = new Spry.Widget.ValidationSelect("spryselect3", {invalidValue:"0", validateOn:["blur"]});

                var spryradio2 = new Spry.Widget.ValidationRadio("spryradio2");

                  </script>

                  <!-- InstanceEndEditable --></div>

                <script type="text/javascript">

                var MenuBar1 = new Spry.Widget.MenuBar("MenuBar1", {imgDown:"../SpryAssets/SpryMenuBarDownHover.gif", imgRight:"../SpryAssets/SpryMenuBarRightHover.gif"});

                </script>

                </body>

                <!-- InstanceEnd --></html>

                 

                Program B

                 

                <? session_start();

                ob_start();

                ?>

                <?php require_once('Connections/tenniscare.php'); ?>

                <?php

                if (!function_exists("GetSQLValueString")) {

                function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

                {

                  if (PHP_VERSION < 6) {

                    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

                  }

                 

                  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

                 

                  switch ($theType) {

                    case "text":

                      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

                      break;   

                    case "long":

                    case "int":

                      $theValue = ($theValue != "") ? intval($theValue) : "NULL";

                      break;

                    case "double":

                      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

                      break;

                    case "date":

                      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

                      break;

                    case "defined":

                      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

                      break;

                  }

                  return $theValue;

                }

                }

                 

                $colname_rsCoach = "000000";

                if (isset($_SESSION[coachnos])) {

                  $colname_rsCoach = $_SESSION[coachnos];

                }

                mysql_select_db($database_tenniscare, $tenniscare);

                $query_rsCoach = sprintf("SELECT coaches.ch_clnt_no, coaches.ch_private_fee, coaches.ch_group_fee, coaches.ch_racquet_stringer, clients.clnt_no FROM coaches, clients WHERE clients.clnt_no IN(%s) AND  clients.clnt_no = coaches.ch_clnt_no", GetSQLValueString($colname_rsCoach, "text"));

                $rsCoach = mysql_query($query_rsCoach, $tenniscare) or die(mysql_error());

                $row_rsCoach = mysql_fetch_assoc($rsCoach);

                $totalRows_rsCoach = mysql_num_rows($rsCoach);

                ?>

                <?php

                $startt = $_SESSION[coachnos];

                echo "query string= " . $query_rsCoach;

                echo "start session" . $startt;

                ?>

                • 5. Re: Advanced Recordsets
                  bregent CommunityMVP

                  I don't use PHP, so I don't know - but shouldn't the session variable be enclosed in quotes?

                  • 6. Re: Advanced Recordsets
                    Tony290 Community Member

                    The session variable is enclosed in single quotes when you create it i.e. $_SESSION['name']. To access it you write $_SESSION[name]. I have a feeling my problem may be with the Javascript Spry validation code in program A. I've only recently started using them.

                     

                    Anyone else have any ideas?

                    • 7. Re: Advanced Recordsets
                      bregent CommunityMVP

                      >The session variable is enclosed in single quotes when you create it i.e.

                      >$_SESSION['name']. To access it you write $_SESSION[name].

                       

                      Really? I don't see that in the PHP manual or in any examples. In any case, I know PHP is pretty forgiving (too much if you ask me) about rules, but it's worth a shot if you haven't tried it yet.

                       

                      Have you verified that your browser is set to allow session cookies?

                      • 8. Re: Advanced Recordsets
                        Tony290 Community Member

                        Here's what I found when passing my session variable with single quotes and without any quotes.

                         

                        1. $_SESSION[coachnos] = 000062,000063 (Type is Text in Advance Recordset Dialog)

                        query string= SELECT coaches.ch_clnt_no, coaches.ch_private_fee, coaches.ch_group_fee, coaches.ch_racquet_stringer, clients.clnt_no FROM coaches, clients WHERE clients.clnt_no IN ('000062,000063') AND clients.clnt_no = coaches.ch_clnt_no

                         

                        Dreamweaver substitued my values with '000062,000062') which is incorrect!

                        ----------------------------------------------------------

                        2. $_SESSION [coachnos] =  '000062','000063' (Type is Text in Advance Recordset Dialog)

                        query string= SELECT coaches.ch_clnt_no, coaches.ch_private_fee, coaches.ch_group_fee, coaches.ch_racquet_stringer, clients.clnt_no FROM coaches, clients WHERE clients.clnt_no IN ('\'000062\',\'000063\'') AND clients.clnt_no = coaches.ch_clnt_no

                        ----------------------------------------------------------

                         

                        3. $_SESSION[coachnos] = 000062,000063  (Type is Integer in Advance Query Dialog)

                        query string= SELECT coaches.ch_clnt_no, coaches.ch_private_fee, coaches.ch_group_fee, coaches.ch_racquet_stringer, clients.clnt_no FROM coaches, clients WHERE clients.clnt_no IN (62) AND clients.clnt_no = coaches.ch_clnt_no

                         

                        Dreamweaver substitued my values with 62 with is incorrect!

                         

                        4. $_SESSION[coachnos] = 000062,000063 (Type is Floating Point in Advance Query Dialog)

                        query string= SELECT coaches.ch_clnt_no, coaches.ch_private_fee, coaches.ch_group_fee, coaches.ch_racquet_stringer, clients.clnt_no FROM coaches, clients WHERE clients.clnt_no IN (62) AND clients.clnt_no = coaches.ch_clnt_no

                         

                        Dreamweaver substitued my values with 62 with this method as well. Which is incorrect!

                         

                        I'm seriously beginning to think there's a bug in Dreamweaver version CS 5.5. Is there anyway to contact Adobde's Dreamweaver development team about this? I'm wasting a lot of time on this issue. I've used this technique successfully plenty of times with SQL WHERE statemtments. This is the first time I've used them with an IN statement.

                         

                        Help someone!

                        • 9. Re: Advanced Recordsets
                          bregent CommunityMVP

                          >I've used this technique successfully plenty of times with SQL WHERE statemtments.

                           

                          That's because the IN predicate requires that each comma separated value is wrapped in quotes and it is up to you to construct the variable this way -  but it looks like GetSQLValueString() does not handle this properly and tries to escape the quotes. You don't need GetSQLValueString for this - just pass the $colname_rsCoach to sprintf() directly and it should work.

                          • 10. Re: Advanced Recordsets
                            Tony290 Community Member

                            It worked the first time I tried your suggestion! I lost about a week trying to solve this problem. This is the first time I tried this forum. You can bet I'll be visiting here more often whether I have a problem or not. Just seeing the issues others have and the solutions are very helpful.

                             

                            Your expertise is much appreciated!