i am getting only one result during search,

Question

I have many records with same expertise and whenever i search then it gives only one result While the values of $exp is array . suppose $exp=array(); I have tried my code is given below i used foreach()

Submit

<?php if(isset($_POST['submit'])){

echo $exp=$_POST['expertise'];
$sq=mysql_fetch_assoc(mysql_query("select distinct(requirement_id) from requirement where label_value='$exp'"));

foreach($sq as $ii){
echo $query1 = "SELECT u.`users_id`, u.`name`, u.`email`, u.`phone`, re.`label_name`, re.`label_value`, re.`requirement_id`, re.`date` FROM users u INNER JOIN requirement re ON u.`users_id`=re.`users_id` WHERE  re.`requirement_id`='$ii'";

$query=mysql_query($query1);

$resultArr = array(); //to store query result
while($row=mysql_fetch_assoc($query))
{
    $resultArr[] = $row;
} 
//print_r($resultArr);
?>
<table class="table table-hover">
<tbody>
<?php
$tempUserID = "";
$tempEmail = "";
$tempPhone = "";
$tempReqID = 0; 
for($i=0;$i<count($resultArr);$i++)
{
    //if user id is blank, assign temporary values we need only for one time.
    if($tempUserID=="")
    {
        $tempUserID = $resultArr[$i]['users_id'];
        $tempEmail = $resultArr[$i]['email'];
        $tempPhone = $resultArr[$i]['phone'];
         $tempReqID = $resultArr[$i]['requirement_id'];
        //printing first row
        ?>
        <tr>
        <td>
            <div class="row" style="background: #00ACFF; ">
                <p style="padding: 10px;margin: 0;color: white;">
                    <i class="fa fa-user"></i>
                    <strong> <?=$resultArr[$i]['name']?> </strong>
                    <span style="font-size:85%;"><?=$resultArr[$i]['users_id']?>000<?=$tempReqID?></span>
                    <span class="pull-right">
                        <i class="fa fa-clock-o"></i> <?=$resultArr[$i]['date']?>
                    </span>
                </p>
            </div>
            <div class="row" style="background: #EDFEAF; min-height:80px;">
                *Looking For<br>->
        <?php
} 
    //ok
    if($tempUserID == $resultArr[$i]['users_id'] &&  $tempReqID==$resultArr[$i]['requirement_id'])
    {
        //printing label_name and label_value if users_id is equal to the tempuserid
        ?>

                <br>
                 <?=$resultArr[$i]['label_name']?>: <?=$resultArr[$i]['label_value']?>
                <br>

        <?php
    } 
    else
    {
        //if users_id is not equal to the previous one, we will close the first row(i.e.<tr>) and start a new one
        ?>
        </div>
        <div class="row" style="background: #00ACFF; ">
                <p style="padding: 10px;margin: 0;color: white;">

                    <i class="fa fa-envelope"></i>&nbsp;<?php echo $email =  substr_replace($tempEmail,"xxxxxxxxxx",3,10);?>
                    <!--<i class="fa fa-envelope"></i> <?php //$tempEmail?>-->
                    <i class="fa fa-mobile" style="margin-left:20px"></i>
                    <strong><?php echo $var1 =substr_replace($tempPhone, str_repeat("X", 6), 2, 6);?> </strong>

                  <span class="pull-right">
                  <a class="btn btn-default btn-xs" href="#">
                      View @&nbsp;&nbsp;&nbsp;
                      <i class="fa fa-circle-thin"></i> 12pt
                  </a>
                  </span>
                </p>
            </div>
        </td>
    </tr>
        <?php
        //since the users_id is not equal to the previous row, which means that data about new user is available, we will assign new values to temporary variables and start a new table row.
        $tempUserID = $resultArr[$i]['users_id'];
        $tempEmail = $resultArr[$i]['email'];
        $tempPhone = $resultArr[$i]['phone'];
        $tempReqID = $resultArr[$i]['requirement_id'];
        ?>
        <tr>
        <td>
            <div class="row" style="background: #00ACFF; ">
                <p style="padding: 10px;margin: 0;color: white;">
                    <i class="fa fa-user"></i>
                    <strong> <?=$resultArr[$i]['name']?> </strong>
                    <span style="font-size:85%;"><?=$resultArr[$i]['users_id']?>000<?=$tempReqID?></span>
                    <span class="pull-right">
                        <i class="fa fa-clock-o"></i> <?=$resultArr[$i]['date']?>
                    </span>
                </p>
            </div>
           <!--the edited part -->
             <div class="row" style="background: #EDFEAF; min-height:80px;">
                *Looking For<br>->
                 <br>
                 <?=$resultArr[$i]['label_name']?>: <?=$resultArr[$i]['label_value']?>
                <br>
        <?php
    }
} 
?>
<!--we will close the table row if current row is the last one in the result-->
<div class="row" style="background: #00ACFF; ">
                <p style="padding: 10px;margin: 0;color: white;">

                    <i class="fa fa-envelope"></i> <?php echo $email =  substr_replace($tempEmail,"xxxxxxxxxx",3,10);?>
                    <i class="fa fa-mobile" style="margin-left:20px"></i>
                    <strong> <?php echo $var1 =substr_replace($tempPhone, str_repeat("X", 6), 2, 6); ?></strong>

                  <span class="pull-right">
                  <a class="btn btn-default btn-xs" href="#">
                      View @&nbsp;&nbsp;&nbsp;
                      <i class="fa fa-circle-thin"></i> 12pt
                  </a>
                  </span>
                </p>
            </div>
        </td>
    </tr>





        </div><!--/col-9-->
        </tbody>
</table>    

<?php } } ?>

**Stop** using deprecated `mysql_*` API. Use `mysqli_*` or `PDO` — Jens, Mar 15 '16 at 08:53
@KA_lin- This time i am getting 1 record for 1 requirement id like :- http://screenshot.net/7kxz4a2. But i wanted there 3 recoreds of three requirements like- http://screenshot.net/7wg7kbk — Divakarcool, Mar 15 '16 at 09:06
@KA_lin- suppose i am tring to search dentist and records of dentist are 3 in database as you seen in db (label_value) then it should produce 3 results but i am getting only once . i think i getting prob in foreach() — Divakarcool, Mar 15 '16 at 09:11

score 0 · Answer 1 · edited May 23 '17 at 12:15

First off, don't use mysql_*, see this post for more info.

Apart from that, you can't paste user data directly into a query, that's unsafe, imagine someone entering something like "1; DROP TABLE requirements".

To answer your question, you can't paste an array directly into a query so $exp is not an array (unless you get errors right now).

If you actually have an array as $exp, use an foreach-loop to rewrite your array to a string so you get something like

select distinct(requirement_id) from requirement where label_value IN(1,2,3)

score 0 · Answer 2 · edited Mar 15 '16 at 10:21

The problem with mysql_fetch_assoc which you have used . The mysql_fetch_assoc will return an associative array that corresponds to the fetched row and moves the internal data pointer ahead.

since you are not iterating that will return associate array of the current pointer .

You can use as below if you still need to use as foreach or directly use while

$result = mysql_query("select distinct(requirement_id) from requirement     where label_value='$exp'");
while($row = mysql_fetch_assoc($result))// this will iterate the pointer 
{
    $sq[] = $row['requirement_id'];
}

i am getting only one result during search,

2 Answers2