Place Query Results into Array then Implode?

Question

Basically I pull an Id from table1, use that id to find a site id in table2, then need to use the site ids in an array, implode, and query table3 for site names. I cannot implode the array correctly first I got an error, then used a while loop.

With the while loop the output simply says: Array

$mysqli = mysqli_connect("server", "login", "pass", "db");
$sql = "SELECT MarketID FROM marketdates WHERE Date = '2010-04-04 00:00:00' AND VenueID = '2'";
$result = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
$dates_id = mysqli_fetch_assoc ( $result );
$comma_separated = implode(",", $dates_id);
echo $comma_separated; //This Returns 79, which is correct.

$sql = "SELECT SIteID FROM bookings WHERE BSH_ID = '1' AND MarketID = '$comma_separated'";
$result = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
// This is where my problems start
$SIteID = array();

while ($newArray = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
  $SIteID[] = $newArray[SIteID];
}

$locationList = implode(",",$SIteID);

?>

Basically what I need to do is correctly move the query results to an array that I can implode and use in a 3rd query to pull names from table3.

Answer 1

Isn't it possible to do this in the mysql query?

SELECT SIteID FROM bookings WHERE BSH_ID = '1' AND MarketID IN (SELECT MarketID FROM marketdates WHERE Date = '2010-04-04 00:00:00' AND VenueID = '2')

Answer 2

Or you could use your way, in that case you just need to use the same IN statement for mysql.

$sql = "SELECT SIteID FROM bookings WHERE BSH_ID = '1' AND MarketID IN (".$comma_separated.")";

Answer 3

Probably you're getting a type mismatch error in SQL. Your second sql query should be:

$sql = "SELECT SIteID FROM bookings WHERE BSH_ID = '1' AND MarketID IN ($comma_separated)";

Answer 4

I'd be surprised if this is what you mean:

... AND MarketID = '$comma_separated'

This is saying that the MarketID is stored in the database as a comma separated list. Probably you mean this:

... AND MarketID IN ($comma_separated)

Also, you don't need to make multiple queries, you can use a JOIN:

SELECT SIteID
FROM marketdates
JOIN bookings
ON marketdates.MarketID = bookings.MarketID
WHERE bookings.BSH_ID = '1'
  AND marketdates.Date = '2010-04-04 00:00:00'
  AND marketdates.VenueID = '2'

Similarly you can join your table3 with this instead to get your final result in one query instead of three.

Answer 5

The others already wrote about the SQL query. But make also sure that you quote array indexes correctly. So instead of

$SIteID[] = $newArray[SIteID];

do this

$SIteID[] = $newArray['SIteID'];