14 Replies Latest reply on Feb 18, 2012 7:48 AM by DonMitchinson

    Flex & PHP - Insert Query in Php Service

    Rpgccv

      I have a MySql DB with 2 tables:
      1. category - categoryID / category / description
      2. photos - photoID / categoryID / photodescription / photo

      And i have a service (CategoryService.php) automatically created by Flash Builder, but i cannot get the second table!!

      <?php

      class CategoryService {

      var $username = "root";
      var $password = "";
      var $server = "localhost";
      var $port = "3306";
      var $databasename = "teste";
      var $tablename = "category";

      var $connection;

      /**
      * The constructor initializes the connection to database. Everytime a request is
      * received by Zend AMF, an instance of the service class is created and then the
      * requested method is invoked.
      */

      public function __construct() {
          $this
      ->connection = mysqli_connect(
                                  $this
      ->server, 
                                  $this
      ->username, 
                                  $this
      ->password,
                                  $this
      ->databasename,
                                  $this
      ->port
                             
      );

          $this
      ->throwExceptionOnError($this->connection);
      }

      /**
      * Returns all the rows from the table.
      *
      * Add authroization or any logical checks for secure access to your data
      *
      * @return array
      */

      public function getAllCategory() {

          $stmt
      = mysqli_prepare($this->connection, "SELECT * FROM $this->tablename");       
          $this
      ->throwExceptionOnError();

          mysqli_stmt_execute
      ($stmt);
          $this
      ->throwExceptionOnError();

          $rows
      = array();

          mysqli_stmt_bind_result
      ($stmt, $row->categoryId, $row->category, $row->description);

         
      while (mysqli_stmt_fetch($stmt)) {
            $rows
      [] = $row;
            $row
      = new stdClass();
            mysqli_stmt_bind_result
      ($stmt, $row->categoryId, $row->category, $row->description);
         
      }

          mysqli_stmt_free_result
      ($stmt);
          mysqli_close
      ($this->connection);

         
      return $rows;
      }

      /**
      * Returns the item corresponding to the value specified for the primary key.
      *
      * Add authorization or any logical checks for secure access to your data
      *
      *
      * @return stdClass
      */

      public function getCategoryByID($itemID) {

          $stmt
      = mysqli_prepare($this->connection, "SELECT * FROM $this->tablename where categoryId=?");
          $this
      ->throwExceptionOnError();

          mysqli_stmt_bind_param
      ($stmt, 'i', $itemID);       
          $this
      ->throwExceptionOnError();

          mysqli_stmt_execute
      ($stmt);
          $this
      ->throwExceptionOnError();

          mysqli_stmt_bind_result
      ($stmt, $row->categoryId, $row->category, $row->description);

         
      if(mysqli_stmt_fetch($stmt)) {
           
      return $row;
         
      } else {
           
      return null;
         
      }
      }

      /**
      * Returns the item corresponding to the value specified for the primary key.
      *
      * Add authorization or any logical checks for secure access to your data
      *
      *
      * @return stdClass
      */

      public function createCategory($item) {

          $stmt
      = mysqli_prepare($this->connection, "INSERT INTO $this->tablename (category, description) VALUES (?, ?)");
          $this
      ->throwExceptionOnError();

          mysqli_stmt_bind_param
      ($stmt, 'ss', $item->category, $item->description);
          $this
      ->throwExceptionOnError();

          mysqli_stmt_execute
      ($stmt);    
          $this
      ->throwExceptionOnError();

          $autoid
      = mysqli_stmt_insert_id($stmt);

          mysqli_stmt_free_result
      ($stmt);    
          mysqli_close
      ($this->connection);

         
      return $autoid;
      }

      /**
      * Updates the passed item in the table.
      *
      * Add authorization or any logical checks for secure access to your data
      *
      * @param stdClass $item
      * @return void
      */

      public function updateCategory($item) {

          $stmt
      = mysqli_prepare($this->connection, "UPDATE $this->tablename SET category=?, description=? WHERE categoryId=?");     
          $this
      ->throwExceptionOnError();

          mysqli_stmt_bind_param
      ($stmt, 'ssi', $item->category, $item->description, $item->categoryId);      
          $this
      ->throwExceptionOnError();

          mysqli_stmt_execute
      ($stmt);    
          $this
      ->throwExceptionOnError();

          mysqli_stmt_free_result
      ($stmt);    
          mysqli_close
      ($this->connection);
      }

      /**
      * Deletes the item corresponding to the passed primary key value from
      * the table.
      *
      * Add authorization or any logical checks for secure access to your data
      *
      *
      * @return void
      */

      public function deleteCategory($itemID) {

          $stmt
      = mysqli_prepare($this->connection, "DELETE FROM $this->tablename WHERE categoryId = ?");
          $this
      ->throwExceptionOnError();

          mysqli_stmt_bind_param
      ($stmt, 'i', $itemID);
          mysqli_stmt_execute
      ($stmt);
          $this
      ->throwExceptionOnError();

          mysqli_stmt_free_result
      ($stmt);    
          mysqli_close
      ($this->connection);
      }


      /**
      * Returns the number of rows in the table.
      *
      * Add authorization or any logical checks for secure access to your data
      *
      *
      */

      public function count() {
          $stmt
      = mysqli_prepare($this->connection, "SELECT COUNT(*) AS COUNT FROM $this->tablename");
          $this
      ->throwExceptionOnError();

          mysqli_stmt_execute
      ($stmt);
          $this
      ->throwExceptionOnError();

          mysqli_stmt_bind_result
      ($stmt, $rec_count);
          $this
      ->throwExceptionOnError();

          mysqli_stmt_fetch
      ($stmt);
          $this
      ->throwExceptionOnError();

          mysqli_stmt_free_result
      ($stmt);
          mysqli_close
      ($this->connection);

         
      return $rec_count;
      }


      /**
      * Returns $numItems rows starting from the $startIndex row from the
      * table.
      *
      * Add authorization or any logical checks for secure access to your data
      *
      *
      *
      * @return array
      */

      public function getCategory_paged($startIndex, $numItems) {

          $stmt
      = mysqli_prepare($this->connection, "SELECT * FROM $this->tablename LIMIT ?, ?");
          $this
      ->throwExceptionOnError();

          mysqli_stmt_bind_param
      ($stmt, 'ii', $startIndex, $numItems);
          mysqli_stmt_execute
      ($stmt);
          $this
      ->throwExceptionOnError();

          $rows
      = array();

          mysqli_stmt_bind_result
      ($stmt, $row->categoryId, $row->category, $row->description);

         
      while (mysqli_stmt_fetch($stmt)) {
            $rows
      [] = $row;
            $row
      = new stdClass();
            mysqli_stmt_bind_result
      ($stmt, $row->categoryId, $row->category, $row->description);
         
      }

          mysqli_stmt_free_result
      ($stmt);    
          mysqli_close
      ($this->connection);

         
      return $rows;
      }


      /**
      * Utility function to throw an exception if an error occurs
      * while running a mysql command.
      */

      private function throwExceptionOnError($link = null) {
         
      if($link == null) {
              $link
      = $this->connection;
         
      }
         
      if(mysqli_error($link)) {
              $msg
      = mysqli_errno($link) . ": " . mysqli_error($link);
             
      throw new Exception('MySQL Error - '. $msg);
         
      }      
      }

      }

      ?>

      In Flash Builder i have 2 spark List, the first one have all categorys and i want when "selectedItem" it populates the other one with the correspondent photos!! I have a query that works with the database but i cannot insert it into php. This is the query:

      SELECT TC.*, TP.photodescription , TP.photo FROM category TC inner join photos TP on TC.categoryId = TP.categoryId

      How can i do this!?

      Hope it's understandable, thanks for "listening"..

        • 1. Re: Flex & PHP - Insert Query in Php Service
          DonMitchinson Level 2

          This is more of a PHP question than Flex.

           

          What fields are being returned from your TC table? Can you change your query to specifically list them instead of TC.*?

           

          I can probably modify the generic getAllCategory to work

          1 person found this helpful
          • 2. Re: Flex & PHP - Insert Query in Php Service
            DonMitchinson Level 2

            Add this at top with other class variables:

             

            var $query = 'SELECT TC.*, TP.photodescription , TP.photo FROM category TC inner join photos TP on TC.categoryId = TP.categoryId';
            
            

             

            Add this function and see if it does:

             

            /**
             * Returns all the rows from the query.
             *
             * @return array
             */
            public function getAllPhotos() {
                
                $stmt = mysqli_prepare($this->connection, $this->query);        
                $this->throwExceptionOnError();
            
                mysqli_stmt_execute($stmt);
                $this->throwExceptionOnError();
            
                $rows = array();
            
                mysqli_stmt_bind_result($stmt, $row->photoID, $row->categoryID, $row->photodescription, $row->photo);
            
                while (mysqli_stmt_fetch($stmt)) {
                  $rows[] = $row;
                  $row = new stdClass();
                    mysqli_stmt_bind_result($stmt, $row->photoID, $row->categoryID, $row->photodescription, $row->photo);
                }
            
                mysqli_stmt_free_result($stmt);
                mysqli_close($this->connection);
            
                return $rows;
            }
            
            1 person found this helpful
            • 3. Re: Flex & PHP - Insert Query in Php Service
              Rpgccv Level 1

              Thanks a lot for the answer!!

               

              I think that is something like that that i need, but this one gives me an error:

              Warning: mysqli_stmt_bind_result() [function.mysqli-stmt-bind-result]: Number of bind variables doesn't match number of fields in prepared statement in D:\Programas\wamp\www\Experience-debug\services\CategoryService1.php on line 97

               

              Line 97 is this: mysqli_stmt_bind_result($stmt, $row->photoID, $row->categoryID, $row->photodescription, $row->photo);

               

              Do you understand why!?

              Hope you can help me,

              Thanks again..;-)

               

               


              • 4. Re: Flex & PHP - Insert Query in Php Service
                DonMitchinson Level 2

                That was just an example - you have to match your row fields to the ones in query.

                 

                I don't know what fields arein your TC table, but maybe try changing

                 

                var $query = 'SELECT TC.*, TP.photodescription , TP.photo FROM category TC inner join photos TP on TC.categoryId = TP.categoryId';
                

                to

                 

                var $query = 'SELECT TC.photoID, TC.categoryID, TP.photodescription , TP.photo FROM category TC inner join photos TP on TC.categoryId = TP.categoryId';

                1 person found this helpful
                • 5. Re: Flex & PHP - Insert Query in Php Service
                  Rpgccv Level 1

                  This is great, that's it!! Many thanks ;-)

                  Now i can get info from this function but i'm not sure how to manage it.

                  I have 2 spark List, the first one have all categorys and i want when "selectedItem", it populates the other one with the correspondent photos!!

                   

                   

                  This is my .mxml:

                   

                   

                  <fx:Script>

                          <![CDATA[

                              import mx.controls.Alert;

                              import mx.events.FlexEvent;

                             

                              import spark.events.IndexChangeEvent;

                             

                              protected function list_creationCompleteHandler(event:FlexEvent):void

                              {

                                  getAllCategoryResult.token = categoryService.getAllCategory();

                              }

                             

                              protected function list_changeHandler(event:IndexChangeEvent):void

                              {

                                  category = list.selectedItem;

                              }

                             

                          ]]>

                      </fx:Script>

                      <fx:Declarations>

                          <s:CallResponder id="getAllCategoryResult"/>

                          <categoryservice:CategoryService id="categoryService"

                                                           fault="Alert.show(event.fault.faultString + '\n' + event.fault.faultDetail)"

                                                           showBusyCursor="true"/>

                          <valueObjects:Category id="category"/>

                      </fx:Declarations>

                      <s:List id="list" x="94" y="143" creationComplete="list_creationCompleteHandler(event)" change="list_changeHandler(event)"

                              labelField="category">

                          <s:AsyncListView list="{getAllCategoryResult.lastResult}"/>

                      </s:List>

                      <s:List x="672" y="143"></s:List>

                   

                   

                   

                  What do i have to do now!?

                  Thanks again..

                  • 6. Re: Flex & PHP - Insert Query in Php Service
                    DonMitchinson Level 2

                    I think I understand but not sure.

                     

                    You don't want to really show all photos - that's what the second query is doing though

                     

                    What you're trying to accomplish is to just show photos with the selected "category" variable I don't see

                    that declared anywhere. Is that the same as categoryID field in the query?

                     

                    If that's true then you probably need the new function to look more like getCategoryByID

                    and call it getPhotosByID

                    1 person found this helpful
                    • 7. Re: Flex & PHP - Insert Query in Php Service
                      Rpgccv Level 1

                      My problem is that i don't know php,

                      but yes, that's it; what i want is to call the correspondent photos from the selected category.

                       

                      This are the two tables from the database:

                      1º  category  ---  categoryId / category / description

                      2º  photos  ---  photoId/ categoryId / photodescription / photo

                       

                      So i have to create a function getPhotosByCategoryId, right!?

                      I'm gonna try, thanks so much.. ;-)

                      • 8. Re: Flex & PHP - Insert Query in Php Service
                        DonMitchinson Level 2

                        Good luck - sounds  like you're on the right track.

                         

                        It also looks like you don't need the categoryID in the SELECT function, definitely not in the prepared statement

                        but it's not going to hurt you - just put the fields you want in there... probably

                        photoID, photodescription

                         

                        I imagine you'll want to open the photo using the photoID?

                        • 9. Re: Flex & PHP - Insert Query in Php Service
                          Rpgccv Level 1

                          Now i'm confused and i'm not being able to make the function!!

                           

                          "I imagine you'll want to open the photo using the photoID?"  

                          The "photoId"!?

                          I thought i need the categoryId (that's the only relation on the database)...... to, when select the category in the List it give me all the photos from that specific category!!

                          What am i'm thinking wrong!? :S

                          • 10. Re: Flex & PHP - Insert Query in Php Service
                            DonMitchinson Level 2

                            No you're not confused - you were right.

                             

                            I was just thinking ahead to what you were going to do when they clicked on a photo item in the list

                            Thought you might want to open the photo.

                             

                            Regardless, if you want to list the photos in the selected category you need to create the getPhotosByCategoryID function

                            • 11. Re: Flex & PHP - Insert Query in Php Service
                              Rpgccv Level 1

                              yes that's right you are correct, then i would want to open the photo!!

                               

                              I'm trying to do the getPhotosByCategoryID function, but i'm not sure how. I copied the getCategoryByID function and tried to change some fields like we did earlier, but i'm not getting there!!

                              Could you give me the correct tip again!?

                              Your help has been perfect and very useful, thanks again very much!!

                              • 12. Re: Flex & PHP - Insert Query in Php Service
                                DonMitchinson Level 2

                                This should probably be what your query looks like - I don't think you want to include the actual photo

                                in the array - but you can try if you want - just add it to the query - then again to the "..bind_result" function calls (two of them)

                                 

                                var $query = 'SELECT TP.photoID, TP.photodescription, FROM category TC INNER JOIN photos TP ON TC.categoryId = TP.categoryId WHERE categoryID = ?';
                                

                                 

                                Then use this function - merges code from both the single row/table and multiple row return functions:

                                From there you're going to have to figure out how to display the photo using the selected photoID - that's out of my realm

                                 

                                /**
                                 * 
                                 * @return stdClass
                                 */
                                public function getPhotosByCategoryID($categoryID) {
                                
                                    $stmt = mysqli_prepare($this->connection, $query);
                                    $this->throwExceptionOnError();
                                
                                    mysqli_stmt_bind_param($stmt, 'i', $categoryID);        
                                    $this->throwExceptionOnError();
                                
                                    $rows = array();
                                
                                    mysqli_stmt_bind_result($stmt, $row->photoID, $row->photodescription);
                                
                                    while (mysqli_stmt_fetch($stmt)) {
                                      $rows[] = $row;
                                      $row = new stdClass();
                                      mysqli_stmt_bind_result($stmt, $row->photoID, $row->photodescription);
                                    }
                                
                                    mysqli_stmt_free_result($stmt);
                                    mysqli_close($this->connection);
                                
                                    return $rows;
                                }
                                
                                • 13. Re: Flex & PHP - Insert Query in Php Service
                                  Rpgccv Level 1

                                  With some adjustments and your really good indications i finally achieve what i was looking for..  

                                   

                                  Thank you so much for all the enormous help and for all your time spent!! 

                                   

                                  Cheers  ;-)

                                  • 14. Re: Flex & PHP - Insert Query in Php Service
                                    DonMitchinson Level 2

                                    Glad it worked out for you.