SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict MikesBarto2002's Avatar
    Join Date
    May 2006
    Location
    New York City
    Posts
    317
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Creating array from SELECT results

    I am working on creating a form where you can select multiple dates to delete from the database.

    I have a series of checkboxes that collect which dates will be chosen to delete. This bit of code is used in a previous function to display the form, which is why you see the $value[1] in the code:

    Code:
    <input type="checkbox" name="select_masses[]" value="' . $value[1] . '" />
    I am then trying to use the following function to create an array of those selected. The reason that I want to create an array is because I want to use another function to ask the user if they are sure that they want to delete the dates they selected. The code that I am using is below. The $select_masses variable is the array of id's of the dates that the user chose.

    PHP Code:
    function Find_multiple_delete_masses() {

            global 
    $mysqli;
            
    $select_masses = array();
            
    $select_masses $_POST['select_masses'];
            
    $this->schedule = array();
            
            
    $query "SELECT id, date_time FROM mass_schedule WHERE";
            foreach (
    $select_masses as $value) {
                
    $query .= " id=" $value " AND";
            }
            
    $query substr($query,0,-4);
            
            
    $result $mysqli->query($query);     
            if (
    $result) {
                while (
    $row $result->fetch_object()) {
                    if (isset(
    $row)) {
                        
    array_push($this->schedule,$row->id,$row->date_time);
                    }
                }
                
    $result->close();
            }
            
            return 
    $this->schedule;
            
        } 
    I am a noob to MYSQLI, and I can't seem to figure out why this isn't working. Any help would be much appreciated. I do know that the $query works fine, as I tested it in MySQL and it worked fine.
    James Web Development | New York, NY
    Design, Develop, Deliver

  2. #2
    SitePoint Addict sdleihssirhc's Avatar
    Join Date
    Feb 2009
    Posts
    387
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What's going wrong here? If the query works fine, then something is breaking in that if-while-if nest. Can you figure out which part it is?

    Also, on a completely unrelated note, you could build the query using implode instead of foreach:

    PHP Code:
    $query "SELECT id, date_time FROM mass_schedule WHERE id=".
        
    implode(' AND id='$select_masses); 
    It wasn't your original problem, and there won't be any speed benefits*; I just find that more readable, and couldn't stop myself from pointing it out.

    *Even if implode is faster/slower than foreach, the difference is going to be completely negligible.
    I'm the web overlord for Graphic Business Systems

  3. #3
    SitePoint Addict MikesBarto2002's Avatar
    Join Date
    May 2006
    Location
    New York City
    Posts
    317
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm really not sure which part of the if when if isn't working. That's the main reason I posted. I am a complete noob to mysqli, so I am at a bit of a loss. If you have any other ideas, I would appreciate any advice.

    And thanks for the implode thought. Never thought to do that for some reason, but that is much easier to read, even if it doesn't do much for speed. I am hoping that this is something that someone will partner up with me in the furture, so easy to read is a must. Thanks!
    James Web Development | New York, NY
    Design, Develop, Deliver

  4. #4
    SitePoint Addict MikesBarto2002's Avatar
    Join Date
    May 2006
    Location
    New York City
    Posts
    317
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question UPDATE: Creating array from SELECT results

    So I tested out the query that I created in MySQL:

    SELECT id, date_time FROM mass_schedule WHERE id=56 AND id=57 AND id=59

    When I tested it out, it is now telling me:

    MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0002 sec )

    ... which is strange because my table is set up in the following way:

    | id | date_time |
    --------------------------------
    | 56 | 2011-10-23 09:15:00 |
    | 57 | 2011-10-23 09:15:00 |
    | 59 | 2011-10-23 09:15:00 |

    Am I not supposed to use date_time as a column header? No clue what's happening here!
    James Web Development | New York, NY
    Design, Develop, Deliver

  5. #5
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,000
    Mentioned
    101 Post(s)
    Tagged
    0 Thread(s)
    Your query is returning 0 rows as each row must have an id of 56 AND and id of 57 AND an an id of 58 any row of your table can't have all three ids at the same time.

    You need to change the WHERE clause of your query to:

    Code:
    SELECT
          id
        , date_time
    FROM
        mass_schedule
    WHERE
        id >= 56
    AND
        id <= 59
    For getting the results into an array:

    PHP Code:
    while ($row $result->fetch_object()) {
    $masses[] = $row;

    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Sounds as if you can take advantage of mysql's IN clause:

    PHP Code:
    // spoofiing your inputs for now...
    $select_masses = array(56,57,59);  // works if one is missing, 58

    // join all elements to make a comma separated string
    $in join($select_masses',');

    // insert the string into your sql query
    $query "SELECT id, date_time FROM mass_schedule WHERE id in ($in)";
            
    // prove it works or not, paste the result to mysql and see if you have matching data
    echo $query;
    // gives:
    //SELECT id, date_time FROM mass_schedule WHERE id IN (56,57,59)

    // comment out the echo, and move on ... 

  7. #7
    SitePoint Addict MikesBarto2002's Avatar
    Join Date
    May 2006
    Location
    New York City
    Posts
    317
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Thanks for all the advice, and I finally got it going with the following code. I used a combination of SpacePhoenix and Cups solutions, which works beautifully!

    PHP Code:
    // Get multiple masses that needs to be deleted from database
        
    function Find_multiple_delete_masses() {

            global 
    $mysqli;
            
    $this->schedule = array();
            
    $select_masses = array();
            
    $select_masses $_POST['select_masses'];
            
            
    $in join($select_masses','); 

            
    $query "SELECT id, date_time FROM mass_schedule WHERE id IN (" $in ")";
            
            
    $result $mysqli->query($query);     
            if (
    $result) {
                while (
    $row $result->fetch_object()) {
                    
    $this->schedule[] = $row;
                }
                
    $result->close();
            }
            
            return 
    $this->schedule;
            
        } 
    Your query is returning 0 rows as each row must have an id of 56 AND and id of 57 AND an an id of 58 any row of your table can't have all three ids at the same time.
    Wow, that's something I should have noticed! I can't believe I missed that! But thanks for pointing it out.
    James Web Development | New York, NY
    Design, Develop, Deliver


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •