How to get multiple value out of while loop to use in query outside the loop?

Question

I'm trying to get multiple values outside the while loop to use it in the query outside the while loop also. Now I can just get one value at a time. The values that I'm trying to get is this one $ids=$row["curriculum_id"];

This is my code, any help would be highly appreciated.

  <?php

    if(isset($_POST['Submit']) AND $_POST['Submit'] == 'Submit')
    {

   $coursneededdate =$_POST['needed']; 
$coursneededdate =mysqli_real_escape_string($mysqli,$coursneededdate);
$gradunder=$_POST['gradunder']; 
$gradunder =mysqli_real_escape_string ($mysqli,$gradunder);
$avalablesemster= $_POST['avalable'];
$avalablesemster =mysqli_real_escape_string($mysqli,$avalablesemster);

    $sql = "SELECT * FROM curriculum where '".$coursneededdate."' between  startdate and enddate";
    $result = $mysqli->query($sql);

    if ($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
              $ids=$row["curriculum_id"];

        }


    }
    }

    $result2 ="SELECT * FROM  curriculumcourses  INNER JOIN courses ON curriculumcourses.course_id = courses.course_id where curriculum_id='$ids' and semester_ava='$avalablesemster'";

    $result3 = $mysqli->query($result2);
    if ($result3->num_rows > 0) {
        while($row = $result3->fetch_assoc()) {
     echo "<pre>";
    print_r($row);


        }

        }

    ?>

score 1 · Accepted Answer · edited May 23 '17 at 12:06

This is a rough version (untested). This code was injectable, I've put in the mysqli_real_escape_string, http://php.net/manual/en/mysqli.real-escape-string.php. Consider using prepared statements in the future, How can I prevent SQL injection in PHP?.

<?php
if(isset($_POST['Submit']) AND $_POST['Submit'] == 'Submit') {
    $coursneededdate = isset($_POST['needed']) ? $mysqli->real_escape_string($_POST['needed']) : '';
    $gradunder=isset($_POST['gradunder']) ? $_POST['gradunder'] : ''; //this isn't used?
    $avalablesemster= isset($_POST['avalable']) ? $mysqli->real_escape_string($_POST['avalable']) : '';
    $sql = "SELECT * FROM curriculum where '".$coursneededdate."' between  startdate and enddate";
    $result = $mysqli->query($sql);
    if ($result->num_rows > 0) {
        $ids = '';
        while($row = $result->fetch_assoc()) {
            $ids .= $mysqli->real_escape_string($row["curriculum_id"]) . ', ';
        }
        $ids = rtrim($ids, ', ');
    }
}
$result2 ="SELECT * FROM  curriculumcourses  INNER JOIN courses ON curriculumcourses.course_id = courses.course_id where curriculum_id in($ids) and semester_ava='$avalablesemster'";
$result3 = $mysqli->query($result2);
if ($result3->num_rows > 0) {
    while($row = $result3->fetch_assoc()) {
        echo "<pre>";
        print_r($row);
        echo "</pre>";
    }
}
?>

This code is concatenating all your curriculum ids into a comma delimited listed in the ids variable. This variable is then used with the SQL function in which will search for all ids in that list. Here's 2 docs on that topic https://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in http://www.tutorialspoint.com/mysql/mysql-in-clause.htm.

Thank you very much @chris85 I appreciate your help. I was editing my post to use real_escape_string. About this one $gradunder I will start to try to use it to filter data depending in its value. — Hussain Almalki, Apr 18 '15 at 01:31
Okay, be sure to escape that as well. I wasn't sure if that was used somewhere so I didn't escape it. — chris85, Apr 18 '15 at 01:32

score 1 · Answer 2 · answered Apr 18 '15 at 01:24

You can replace both queries for just one:

SELECT * FROM  curriculumcourses  
    NATURAL JOIN courses  
    WHERE  semester_ava='$avalablesemster' 
    AND curriculum_id IN (
        SELECT curriculum_id 
        FROM curriculum 
        WHERE '".$coursneededdate."' BETWEEN startdate AND enddate)

And then directly fetch the result from it.

This solutions is faster because the DBMS optimize the queries. Also, the option of query -> PHP -> query -> PHP it's more difficult to debug, read and maintain (in addition to be slower).

How to get multiple value out of while loop to use in query outside the loop?

2 Answers2