CODEWITHSUNDEEP
phpmysql
Hussain Almalki
Hussain Almalki

Reputation: 177

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

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);


        }

        }

    ?>

Upvotes: 0

Views: 819

Answers (2)

&#193;lvaro Reneses
&#193;lvaro Reneses

Reputation: 701

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

Upvotes: 1

chris85
chris85

Reputation: 23892

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.

Upvotes: 1

Related Questions