Creating array from SELECT results

PHP
1

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:

<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.


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.

2

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:


$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.

3

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!

4

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!

5

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:

SELECT
      id
    , date_time
FROM
    mass_schedule
WHERE
    id >= 56
AND
    id <= 59

For getting the results into an array:


while ($row = $result->fetch_object()) {
$masses[] = $row;
}
6

Sounds as if you can take advantage of mysql’s IN clause:


// 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

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!


// 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.