We help IT Professionals succeed at work.
Troubleshooting Question

Query Not Looping

David Schure
David Schure asked
on
68 Views
Last Modified: 2020-10-19
I need to Loop through the records and grab three of them.  This query is not looping or returning any records.  It was before I added the while...
// Get content from the database 
   $query = $db->query("SELECT therapist_id,therapist_name,photo,details FROM tbl_answers_therapist
   JOIN tbl_answers USING(country,state)
   WHERE tbl_answers.client_id = {$_GET['id']} AND tbl_answers_therapist.status = 'Yes'
   LIMIT 3");
    if($query->num_rows > 0){ 
        while ($cmsData = $query->fetch_assoc()); 
        echo '<p>'.$cmsData['therapist_id'].'</p>';
      echo '<p>'.$cmsData['therapist_name'].'</p>';
      echo '<img src="/resources/images/Therapist/', $cmsData['photo'], '" height="100px" width="100px" border="2">';  
      echo '<p>'.$cmsData['details'].'</p>';
      mysqli_free_result($query);

  mysqli_close($db); 
    }else{ 
        echo 'Content not found....'; 
    } 
}else{ 
    echo 'Content not found....'; 
} 
Comment
Watch Question

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
please post sample data for tbl_answers_therapist  and tbl_answers
and what are you passing $_GET['id']? 
Dan CraciunIT Consultant
CERTIFIED EXPERT

Commented:
You have some issues in your code. Please try like this:

if($query->num_rows > 0){ 
    while ($cmsData = $query->fetch_assoc()) {
        echo '<p>'.$cmsData['therapist_id'].'</p>';
        echo '<p>'.$cmsData['therapist_name'].'</p>';
        echo '<img src="/resources/images/Therapist/' . $cmsData['photo'] . '" height="100px" width="100px" border="2">';  
        echo '<p>'.$cmsData['details'].'</p>';
    }
} else { 
    echo 'Content not found....'; 
}
mysqli_free_result($query);
mysqli_close($db); 

HTH,
Dan

Author

Commented:
table answers therapist
table answers
right now I am just passing it as a hard code. later it will be a variable

Author

Commented:
Hi Dan..that comes up blank..

Author

Commented:
The page for context...
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

if(!empty($_GET['id'])){ 
    // Database configuration 
    $dbHost = 'localhost'; 
    $dbUsername = ''; 
    $dbPassword = ''; 
    $dbName = ''; 
     
    // Create connection and select database  
    $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 
     
    if ($db->connect_error) { 
        die("Unable to connect database: " . $db->connect_error); 
    } 
    // Get content from the database 
   $query = $db->query("SELECT therapist_id,therapist_name,photo,details FROM tbl_answers_therapist
   JOIN tbl_answers USING(country,state)
   WHERE tbl_answers.client_id = {$_GET['id']} AND tbl_answers_therapist.status = 'Yes'
   LIMIT 3");
   if($query->num_rows > 0){ 
    while ($cmsData = $query->fetch_assoc()) {
        echo '<p>'.$cmsData['therapist_id'].'</p>';
        echo '<p>'.$cmsData['therapist_name'].'</p>';
        echo '<img src="/resources/images/Therapist/' . $cmsData['photo'] . '" height="100px" width="100px" border="2">';  
        echo '<p>'.$cmsData['details'].'</p>';
    }
} else { 
    echo 'Content not found....'; 
}
mysqli_free_result($query);
mysqli_close($db); 
    //if($query->num_rows > 0){ 
       // while ($cmsData = $query->fetch_assoc()); 
       // echo '<p>'.$cmsData['therapist_id'].'</p>';
      //echo '<p>'.$cmsData['therapist_name'].'</p>';
      //echo '<img src="/resources/images/Therapist/', $cmsData['photo'], '" height="100px" width="100px" border="2">';  
      ///echo '<p>'.$cmsData['details'].'</p>';
      //mysqli_free_result($query);

  //mysqli_close($db); 
    }else{ 
        echo 'Content not found....'; 
    } 
}else{ 
    echo 'Content not found....'; 
} 
?>


Dan CraciunIT Consultant
CERTIFIED EXPERT

Commented:
Please try this:
 $query = $db->query("SELECT therapist_id,therapist_name,photo,details FROM tbl_answers_therapist
   JOIN tbl_answers USING(country,state)
   WHERE tbl_answers.client_id = {$_GET['id']} AND tbl_answers_therapist.status = 'Yes'
   LIMIT 3");
   echo '<p>Number of results: ' . $query->num_rows . '</p>'
   if($query->num_rows > 0){
It should output how many results you got from your query. If it's 0, then blank is normal.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
I guess your query logic is wrong...

based on data, I see this query should be used

SELECT *
  FROM tbl_answers_therapist t
  JOIN tbl_answers a on a.country=t.country and a.state=t.state and a.answer_id=t.answer_id
 WHERE a.client_id = 1 AND t.status = 'Yes'
 LIMIT 3;

Author

Commented:
Still blank

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
also, add this

echo $_GET['id']

to see whats the value of ID
maybe you dont get anything...

Author

Commented:
The query is returning only one record, the correct answer is 2 records.  That's the issue.  It should loop. I think.

Author

Commented:
https://arise.plus/MODALHELPER.html
Try it here. You will see one result
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
based on your data and query, it should return 1 not 2!
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
tha data you posted above does not show status column...
is is "Yes" for those 2 records? Maybe one of them is "No"

Author

Commented:

Author

Commented:
The status is yes for both.

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
sorry, I could not get your date schema...
please post full columns and data...

also, you are checking one country_id here
based on your data, query returns correct values...

add more data, update your data and make sure you have correct values
then run the queries...

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
can you echo

$query->num_rows 

before loop... is it 1 or 3?

Author

Commented:
I am trying to keep it simple.  Query is based on Country, State and Status.  That's it.  I really think that it is not looping through the records thus not displaying them.  Please correct me if I am wrong.  It should be two rows returning.

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
can you run these queries and post the values

select * from tbl_answers a

select * from tbl_answers_therapist t

select * from tbl_answers_therapist t where a.client_id = 316

it looks like just data issue to me... 

Author

Commented:

Author

Commented:

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
ok, what happens if you do not check number of rows but just loop

if($query->num_rows > 0){     while ($cmsData = $query->fetch_assoc()) {
        echo '<p>'.$cmsData['therapist_id'].'</p>';
        echo '<p>'.$cmsData['therapist_name'].'</p>';
        echo '<img src="/resources/images/Therapist/' . $cmsData['photo'] . '" height="100px" width="100px" border="2">';  
        echo '<p>'.$cmsData['details'].'</p>';
    }
} else {     echo 'Content not found....'; } 
>>>
while ($cmsData = $query->fetch_assoc()) {
        echo '<p>'.$cmsData['therapist_id'].'</p>';
        echo '<p>'.$cmsData['therapist_name'].'</p>';
        echo '<img src="/resources/images/Therapist/' . $cmsData['photo'] . '" height="100px" width="100px" border="2">';  
        echo '<p>'.$cmsData['details'].'</p>';
    }

Author

Commented:

Author

Commented:
Question.  Is the query set up tp loop?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
can you try this code...

$query = "SELECT ...";
$result = mysqli_query($query);

if (mysqli_num_rows($result) == 0) {
   echo "Content not found...."
} else {
   while ($cmsData = mysqli_fetch_assoc($result)) {
      echo '<p>'.$cmsData['therapist_id'].'</p>';
      echo '<p>'.$cmsData['therapist_name'].'</p>';
      echo '<img src="/resources/images/Therapist/' . $cmsData['photo'] . '" height="100px" width="100px" border="2">';  
      echo '<p>'.$cmsData['details'].'</p>';
    }
}
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
I got above sample from here

mysqli_fetch_assoc

https://www.macs.hw.ac.uk/~hwloidl/docs/PHP/function.mysqli-fetch-assoc.html

I guess num_rows fetches all records to get the result...
so, loop only finds the last record... my guess...

Author

Commented:
Just tried it.  Blank.
Dan CraciunIT Consultant
CERTIFIED EXPERT

Commented:
Can you post a link? Looks like it may be a local issue, on your server.
Dan CraciunIT Consultant
CERTIFIED EXPERT

Commented:
NVM, found it.

So, for ID 316, you expected more than 1 result?

Author

Commented:
https://arise.plus/MODALHELPER.html
yes there are two records that meet that criteria
Dan CraciunIT Consultant
CERTIFIED EXPERT

Commented:

Author

Commented:
the problem I think is that it's not looping.  I either get one record or no records depending on the code I run but the correct answer is two
Dan CraciunIT Consultant
CERTIFIED EXPERT

Commented:
If the loop was not working, then you would get NO result.
The problem is that your query returns 1 result. You can check that by printing $query->num_rows.

Author

Commented:
but why am I getting two results when I run the query alone?  confused on this.
Dan CraciunIT Consultant
CERTIFIED EXPERT

Commented:
You're sure you're running the query against the same tables?
When I replace 316 with 1 or 3 I get "Content not found..."

Author

Commented:
there are no records for 1 or three.  316 is the client I have set up for this.

Author

Commented:
i should get the same results whether I run the query inside or outside of the webpage

Author

Commented:
I am convinced that the answer i
lies in this part.  The SELECT works with any variation o the WHERE part.
if($query->num_rows > 0){ 
        $cmsData = $query->fetch_assoc(); 
        echo '<p>'.$cmsData['therapist_id'].'</p>';
      echo '<p>'.$cmsData['therapist_name'].'</p>';
      echo '<img src="/resources/images/Therapist/', $cmsData['photo'], '" height="100px" width="100px" border="2">';  
      echo '<p>'.$cmsData['details'].'</p>'; 
    }else{ 
        echo 'Content not found....'; 
    }
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Hey David,

The code you've just posted will only ever retrieve one record, because you're only calling fetch_assoc() once. You did seem to have the code set up to loop correctly earlier on, but it looks like you may have changed it.

I would suggest you take a step back on this and do some very simple debugging. Something along these lines:

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

require_once 'db.php';

$sql = <<< EOT
    SELECT therapist_id, therapist_name, photo, details FROM tbl_answers_therapist
    JOIN tbl_answers USING(country,state)
    WHERE tbl_answers.client_id = 316 AND tbl_answers_therapist.status = 'Yes'
    LIMIT 3
EOT;

$query = $db->query($sql);

$data= $query->fetch_all();

var_dump($data);

Open in new window

You may need to edit it to include your own DB connection, but it's a simple query/dump. It will show you exactly what data you're getting back from your query without all the other white noise.

Author

Commented:
Working on it.  Chris.  This is PDO? Correct?
Warning: Use of undefined constant DB_NAME - assumed 'DB_NAME' (this will throw an Error in a future version of PHP) in /home/audiodigz/public_html/THERAPIST/includes/pdo_connection.php on line 14

Warning: Use of undefined constant DB_SERVER - assumed 'DB_SERVER' (this will throw an Error in a future version of PHP) in /home/audiodigz/public_html/THERAPIST/includes/pdo_connection.php on line 14

Warning: Use of undefined constant DB_USER - assumed 'DB_USER' (this will throw an Error in a future version of PHP) in /home/audiodigz/public_html/THERAPIST/includes/pdo_connection.php on line 15

Warning: Use of undefined constant DB_PASS - assumed 'DB_PASS' (this will throw an Error in a future version of PHP) in /home/audiodigz/public_html/THERAPIST/includes/pdo_connection.php on line 15

Fatal error: Uncaught PDOException: SQLSTATE[HY000] [2005] Unknown MySQL server host 'DB_SERVER' (0) in /home/audiodigz/public_html/THERAPIST/includes/pdo_connection.php:15 Stack trace: #0 /home/audiodigz/public_html/THERAPIST/includes/pdo_connection.php(15): PDO->__construct('mysql:dbname=DB...', 'DB_USER', 'DB_PASS') #1 /home/audiodigz/public_html/getContent.php(5): require_once('/home/audiodigz...') #2 {main} thrown in /home/audiodigz/public_html/THERAPIST/includes/pdo_connection.php on line 15

Author

Commented:
Also the entire site was moved from audiodigz.com to arise.plus so I do not now why this is coming up?
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK. That's an issue with your DB connection. Also, you seem to be importing a PDO connection here, but all of your code in this question so far has been MySQLi  - I'm guessing that you're including the wrong DB connection file. You must have the right one somewhere otherwise NONE of the code you've previously posted in this question would work at all

Author

Commented:
Fatal error: Uncaught Error: Call to undefined method mysqli_result::fetch_all() in /home/audiodigz/public_html/getContent.php:28 Stack trace: #0 {main} thrown in /home/audiodigz/public_html/getContent.php on line 28 

Author

Commented:
THis is what I have...
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

if(!empty($_GET['id'])){ 
    // Database configuration 
    $dbHost = 'localhost'; 
    $dbUsername = ''; 
    $dbPassword = ''; 
    $dbName = ''; 
     
    // Create connection and select database  
    $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 
     
    if ($db->connect_error) { 
        die("Unable to connect database: " . $db->connect_error); 
    } 
    $sql = <<< EOT
    SELECT therapist_id, therapist_name, photo, details FROM tbl_answers_therapist
    JOIN tbl_answers USING(country,state)
    WHERE tbl_answers.client_id = 316 AND tbl_answers_therapist.status = 'Yes'
    LIMIT 3
EOT;

$query = $db->query($sql);

$data= $query->fetch_all();

var_dump($data);
   // Get content from the database 
   //$query = $db->query("SELECT therapist_id,therapist_name,photo,details FROM //tbl_answers_therapist
   //JOIN tbl_answers USING(country,state)
   //WHERE tbl_answers.client_id = {$_GET['id']} AND tbl_answers_therapist.status = 'Yes'
   //LIMIT 3");
   //if($query->num_rows > 0){ 
        //$cmsData = $query->fetch_assoc(); 
        //echo '<p>'.$cmsData['therapist_id'].'</p>';
      //echo '<p>'.$cmsData['therapist_name'].'</p>';
      //echo '<img src="/resources/images/Therapist/', $cmsData['photo'], '" height="100px" //width="100px" border="2">';  
      //echo '<p>'.$cmsData['details'].'</p>'; 
    //}else{ 
        //echo 'Content not found....'; 
    //}
 
}else{ 
    echo 'Content not found....'; 
} 
?>
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
For your own sanity - clean up your code - remove EVERYTHING that isn't related to debugging this problem - get rid of the GET check, get rid of all the commented code, get rid of the else statements - clean code is easy code !!

Author

Commented:
Okay...here we go
array(4) { ["therapist_id"]=> string(1) "1" ["therapist_name"]=> string(16) "Dr. Larry Nogood" ["photo"]=> string(8) "Man1.png" ["details"]=> string(61) "I am a good therapist 1. Hire me. You won't be disappointed." } array(4) { ["therapist_id"]=> string(1) "3" ["therapist_name"]=> string(14) "Dr. Scott Ford" ["photo"]=> string(11) "Woman10.png" ["details"]=> string(61) "I am a good therapist 3. Hire me. You won't be disappointed." }

Author

Commented:
Okay so we are getting two records!

Now I need to get rid of the cmsData part

Author

Commented:
while ($data = $query->fetch_assoc()) {
    //var_dump($data);
   echo '<p>'.$cmsData['therapist_id'].'</p>';
   echo '<p>'.$cmsData['therapist_name'].'</p>';
   echo '<img src="/resources/images/Therapist/', $cmsData['photo'], '" height="100px" width="100px" border="2">';  
   echo '<p>'.$cmsData['details'].'</p>'; 

Author

Commented:
CHRIS!  YOU DID IT AGAIN!!!!!!

Author

Commented:
Thank you so much Chris I appreciate you.
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK. Perfect. So now you know you have 2 records, so build your HTML back into your loop:

$sql = <<< EOT
    SELECT therapist_id, therapist_name, photo, details FROM tbl_answers_therapist
    JOIN tbl_answers USING(country,state)
    WHERE tbl_answers.client_id = 316 AND tbl_answers_therapist.status = 'Yes'
    LIMIT 3
EOT;

$query = $db->query($sql);

while ($data = $query->fetch_assoc()) {
    printf("<p>%s</p>", $data['therapist_id']);
    printf("<p>%s</p>", $data['therapist_name']);
    printf("<img src='/resources/images/Therapist/%s' height='100px' width='100px'>", $data['photo']);
    printf("<p>%s</p>", $data['details']);
}

Open in new window

Give that a go to check it's working as expected
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Ah - we cross posted. Good effort. Glad you got it sorted.

Often, when things aren't going as expected, it pays to strip your code back to basics and build up from there - checking code off in bite sized chunks. It gives you much clearer picture of what's working and what's not.

Author

Commented:
Thank you Chris...
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
You're welcome David :)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions