嵌套MySQLI查询

Question

I am trying to pull a number from one table inside a database, and then use that number to process a query on another table in the same database.

The code doesn't spit out any errors - it just doesn't return a string! I am trying to understand mysqli and the whole array structure, but I'm having difficulty figuring out why this isn't working. I believe I am trying to successfully turned the original array into a string for use in the second query, which I also translate into a string for the echo. It's just that for some reason it's not printing anything! If I take out the nested loop then it prints the active_event number just fine. I'm at a loss!

<?php
$DBServer = 'localhost';
$DBUser   = 'user';
$DBPass   = 'pass';
$DBName   = 'database';
$conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName);
if ($conn->connect_error) {
trigger_error('Database connection failed: '  . $conn->connect_error, E_USER_ERROR);
}
$get_active_event = mysqli_query($conn, "SELECT active_event FROM asp_config");
while($active_event = @mysql_fetch_assoc($get_active_event)){
$get_event_name = mysqli_query($conn, "SELECT * FROM asp_events WHERE id = {$active_event['active_event']}"); echo $get_event_name->fetch_object()->event_name;}
$conn->close();
?>

Thanks!

-Philip

Answer 1

I suggest to change the logic of your piece of code modifying you db schema in a more efficient way.

I'd fetch the results in a single query joining the two tables asp_config and asp_events or, even better, if possible get rid of asp_config and add a column is_activeor something like this to asp_events table.

Then you just have to cycle with while-loop without the second query because all you need to know is in the first results set. Be careful to use the error suppression (@) you need to know if there is an error and handle it. Suppress without knowing it's a bad pratice