PHP MySQLi Query within Prepared Statement

Question

I'm in the process of updating from MySQL to MySQLi and have come across an issue I'm not sure how to handle with MySQLi/prepared statements.

In my previous code, I had a query within a query:

while ($record = mysql_fetch_array($result)) 
{
    //CLEAR VARIABLES
    $REMINDER_TIME = '';
    $REMINDER_USER_ID = '';

    //STORE DATA
    $REMINDER_TIME = $record['REMINDER_TIME'];
    $REMINDER_USER_ID = $record['USER_ID'];

    //LOCALIZE TIMES    
    $REMINDER_TIME = new DateTime($REMINDER_TIME);
    $REMINDER_TIME->setTimeZone(new DateTimeZone($user_timezone));

    //GET USER NAME
    $result1 = mysql_query("SELECT FIRST_NAME, LAST_NAME, EMAIL FROM TABLE_users WHERE USER_ID='$REMINDER_USER_ID'");

    while ($record1 = mysql_fetch_array($result1)) 
    {
        //CLEAR VARIABLES
        $REMINDER_USER_FIRST = '';
        $REMINDER_USER_LAST = '';
        $REMINDER_USER_EMAIL = '';

        //STORE DATA
        $REMINDER_USER_FIRST = $record1['FIRST_NAME'];
        $REMINDER_USER_LAST = $record1['LAST_NAME'];
        $REMINDER_USER_EMAIL = $record1['EMAIL'];
    }

    $tableRow .= '<tr>
                 <td class="tall">'.$REMINDER_TIME->format('F j, Y').'<br />'.$REMINDER_TIME->format('g:ia T').'</a></td>
                 <td class="tall">'.$REMINDER_USER_FIRST.' '.$REMINDER_USER_LAST.'<br />('.$REMINDER_USER_EMAIL.')</td>
                 </tr>';
}

However, with my prepared statements, I'm not sure how to run a query within a query:

$result = $stmt -> get_result();

while ($row = $result -> fetch_array(MYSQLI_ASSOC))
{
    //CLEAR VARIABLES
    $REMINDER_TIME = '';
    $REMINDER_USER_ID = '';

    //STORE DATA
    $REMINDER_TIME = $row['REMINDER_TIME'];
    $REMINDER_USER_ID = $row['USER_ID'];

    //LOCALIZE TIMES    
    $REMINDER_TIME = new DateTime($REMINDER_TIME);
    $REMINDER_TIME->setTimeZone(new DateTimeZone($user_timezone));

    //_____QUERY WOULD GO HERE_____

    $tableRow .=    '<tr>
    <td class="tall">'.$REMINDER_TIME->format('F j, Y').'<br />'.$REMINDER_TIME->format('g:ia T').'</a></td>
    <td class="tall">'.$REMINDER_USER_FIRST.' '.$REMINDER_USER_LAST.'<br />('.$REMINDER_USER_EMAIL.')</td>
    </tr>';
}

$stmt->close();

Is it possible to do this with prepared statements? I basically need to hit the database to get data in a secondary query based on values received during the first query. Thank you!

I think your problem is you think you're doing a query in a query. But you're not. You're just running a query and then you're using PHP to loop through the results. You can just do a new query while looping through the results. The looping is done in PHP not in MySQL. MySQL doesn't matter how many query you're doing while looping through the results since it has nothing to do with MySQL. — Charlotte Dunois
– Charlotte Dunois, Commented Aug 12, 2014 at 20:47
OK, that makes sense. Should I issue the $stmt -> close() right after $result = $stmt -> get_result(); ? — Jason
– Jason, Commented Aug 12, 2014 at 21:02
Well, I've never really used the close function (I'm using PDO anyway). But you'd kill your results if you don't fetch them before. The comments of mysqli_stmt_close are stating you should rather use $stmt->reset(). ch2.php.net/manual/en/mysqli-stmt.reset.php — Charlotte Dunois
– Charlotte Dunois, Commented Aug 12, 2014 at 21:06
Can you tell me why you're using MySQLi and not directly PDO? (PDO has a cleaner and better integration of Prepared Statements, fyi) — Charlotte Dunois
– Charlotte Dunois, Commented Aug 12, 2014 at 21:07
Can you inclue in your post the first sql query ? It is a really bad idea to do a query inside a loop. You probably could rewrite this code and do only one query to retrieve all informations you need (with a join query for example) — Imad Moqaddem
– Imad Moqaddem, Commented Aug 12, 2014 at 22:21

Imad Moqaddem · Accepted Answer · 2014-08-14 12:29:00Z

1

Executing queries inside a loop is a bad practice and should be avoided, because of performance issues.

Given the code and query you posted, I suggest you to modify your first sql statement:

SELECT a.REMINDER_TIME, a.USER_ID, b.FIRST_NAME, b.LAST_NAME, b.EMAIL
FROM TABLE_logs_reminders a
LEFT JOIN table_users b on a.user_id = b.user_id
WHERE REQUEST_ID=?
ORDER BY a.REMINDER_TIME

This will fetch the data you need, in one single request, avoiding several calls to your DBMS. Then, in your php code, you can access the user columns without a new query:

//_____QUERY WOULD GO HERE_____
// No need for a query
$REMINDER_USER_FIRST = $record1['FIRST_NAME'];
$REMINDER_USER_LAST = $record1['LAST_NAME'];
$REMINDER_USER_EMAIL = $record1['EMAIL'];

There are some good tutorials about joins you should read.

edited Aug 14, 2014 at 12:29

answered Aug 13, 2014 at 0:22

Imad Moqaddem

1,4939 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jason Over a year ago

Thanks for your help. I tried this query but SQL is saying there is a syntax error near LEFT JOIN. Here is the query:

SELECT a.REMINDER_TIME, a.USER_ID, b.FIRST_NAME, b.LAST_NAME, b.EMAIL FROM TABLE_logs_reminders a WHERE REQUEST_ID=? ORDER BY a.REMINDER_TIME LEFT JOIN TABLE_users b ON a.USER_ID = b.USER_ID;

Imad Moqaddem Over a year ago

I just edited my answer. The order by clause must be after the join.

Jason Over a year ago

Thanks for the quick reply! Unfortunately I'm still getting a syntax error. Strange.

Jason Over a year ago

OK - I think I fixed it. The WHERE clause also had to go at the end.

Imad Moqaddem Over a year ago

yes ! I edited the answer. I copy / pasted your original query and completely forget to move these clauses...

Collectives™ on Stack Overflow

PHP MySQLi Query within Prepared Statement

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related