Why is my query only giving one result

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
lloydie-t
Forum Commoner
Posts: 88
Joined: Thu Jun 27, 2002 3:41 am
Location: UK

Why is my query only giving one result

Post by lloydie-t »

I know this is going to be something stupid, but I have gone brain dead. I have the following PHP script which shoiuld give me an array of names but I only get one. Can you help?

Code: Select all

<?php
$connection = @mysql_connect($host, $user, $pass) or die ("Unable to connect to database");
mysql_select_db($db) or die ("Unable to select database: $db ");
$query = "SELECT DISTINCT cal_group_id
FROM cal_user_group
WHERE cal_user_id = '$cal_user_id'";
$result = mysql_query ($query);
        while ($r = mysql_fetch_array($result)) {
$cal_group_id = $r['cal_group_id'];
        } 
          						
$connection = @mysql_connect($host, $user, $pass) or die ("Unable to connect to database");
mysql_select_db($db) or die ("Unable to select database: $db ");
$query = "SELECT DISTINCT cal_user_id
FROM cal_user_group
WHERE cal_group_id IN ($cal_group_id)";
$result = mysql_query ($query);
        while ($r = mysql_fetch_array($result)) {
       $cal_group_user_id = $r['cal_user_id'] ;
        }
             
$connection = @mysql_connect($host, $user, $pass) or die ("Unable to connect to database");
mysql_select_db($db) or die ("Unable to select database: $db ");
$query = "SELECT DISTINCT user_id, forename, surname 
FROM users 
WHERE user_id IN ($cal_group_user_id)";
$result = mysql_query ($query);
        while ($r = mysql_fetch_array($result)) {
        echo "<option value="$r['user_id']">$r[forename] $r[surname]</option>";
        }        						
     
?>
I have manually tried the queries in Mysql and I get the array's, but not in PHP.
User avatar
delorian
Forum Contributor
Posts: 223
Joined: Sun May 04, 2003 5:20 pm
Location: Olsztyn, Poland

Post by delorian »

Is there any error message. Try to print those queries in you browser before you execute them. You can also try to use print_r($r) to see what you've got. As for me I can't find any real problem in code you provided.

BTW: Use single quotes in array indexes like $r['forename']
lloydie-t
Forum Commoner
Posts: 88
Joined: Thu Jun 27, 2002 3:41 am
Location: UK

Post by lloydie-t »

Delorian, There is no error message and I have already tried print_r and it just returns a single result
User avatar
delorian
Forum Contributor
Posts: 223
Joined: Sun May 04, 2003 5:20 pm
Location: Olsztyn, Poland

Post by delorian »

What about printing the queries. Are they ok :?: I can't find any error here in your code, only "cosmetic" stuff.
User avatar
delorian
Forum Contributor
Posts: 223
Joined: Sun May 04, 2003 5:20 pm
Location: Olsztyn, Poland

Post by delorian »

Oh, wait:

Code: Select all

$query = "SELECT DISTINCT user_id, forename, surname 
FROM users 
WHERE user_id IN ($cal_group_user_id)";
Shouldn't it return only one row where the user_id is equal to $cal_group_user_id. The $cal_group_user_id has the last value of cal_user_id from cal_user_group table. The same with $cal_group_id.
lloydie-t
Forum Commoner
Posts: 88
Joined: Thu Jun 27, 2002 3:41 am
Location: UK

Post by lloydie-t »

Now I am confused. When I do a loop on $r['cal_group_id'] and then do print_r I still onl get one result
User avatar
delorian
Forum Contributor
Posts: 223
Joined: Sun May 04, 2003 5:20 pm
Location: Olsztyn, Poland

Post by delorian »

Code: Select all

while ($r = mysql_fetch_array($result)) { 
$cal_group_id = $r['cal_group_id']; 
        }
Yes, because $cal_group_id has the last value from the array in that while loop.
lloydie-t
Forum Commoner
Posts: 88
Joined: Thu Jun 27, 2002 3:41 am
Location: UK

Post by lloydie-t »

Anyway I have done a print. Results

Code: Select all

1 //$r&#1111;'cal_group_id'];
2

1 //$r&#1111;'cal_user_id'] 
3

3 Darrin Butler //$r&#1111;'user_id']>$r&#1111;forename] $r&#1111;surname]
So you are right. But how do I get the array into the query
User avatar
delorian
Forum Contributor
Posts: 223
Joined: Sun May 04, 2003 5:20 pm
Location: Olsztyn, Poland

Post by delorian »

Code: Select all

$query .= "SELECT DISTINCT cal_user_id FROM cal_user_group ";
$query .= "WHERE cal_group_id IN (";

for($i=count($cal_group_id)-1;$i>=0;$i--) {
  $query .= $cal_group_id[$i];
  
  if($i!=0) { $query .= ","; } // separate comma in IN (sdf,ad,ads,ad)
}

$query .= ")";
Maybe it will work, I did not test it, but it should. :D
lloydie-t
Forum Commoner
Posts: 88
Joined: Thu Jun 27, 2002 3:41 am
Location: UK

Post by lloydie-t »

OK. I tried and failed. I am getting no output using that script.

It is probably because I do not understand what I have to do afterwards to print $cal_user_id.

Is " $query .= $cal_group_id[$i];" correct?
I tried changing it to "$query .= $cal_user_id[$i];"

Please more help
User avatar
delorian
Forum Contributor
Posts: 223
Joined: Sun May 04, 2003 5:20 pm
Location: Olsztyn, Poland

Post by delorian »

I understand your script in that way:

Code: Select all

<?php
 
$connection = @mysql_connect($host, $user, $pass) or die ("Unable to connect to database"); 
mysql_select_db($db) or die ("Unable to select database: $db "); 
$query = "SELECT DISTINCT cal_group_id 
FROM cal_user_group 
WHERE cal_user_id = '$cal_user_id'";
I the script above you're quering a cal_group_id which is probably ONE value, cause I think the one user which ID is in $cal_user_id has only ONE cal_group_id, yes :?:
So why do you think is an array, and making a while loop :?:

Code: Select all

$result = mysql_query ($query); 
        while ($r = mysql_fetch_array($result)) { 
$cal_group_id = $r['cal_group_id']; 
        }

Then you don't need to connect to database one more time, what for :?:

Code: Select all

/*                    
$connection = @mysql_connect($host, $user, $pass) or die ("Unable to connect to database"); 
mysql_select_db($db) or die ("Unable to select database: $db "); 
*/

Code: Select all

$query = "SELECT DISTINCT cal_user_id 
FROM cal_user_group 
WHERE cal_group_id IN ($cal_group_id)";
Then you're quering a cal_user_id (without duplicates) from cal_user_group table where the group ID is IN the list of values in $cal_group_id - which as we know is porbably one value.
So you get the result of users with the same group ID, right :?: And want to put them into a table $cal_group_user_id

Code: Select all

$result = mysql_query ($query); 
       $i =0;
        while ($r = mysql_fetch_array($result)) { 
      //  $cal_group_user_id = $r['cal_user_id'] ; - it's wrong, it should be
        $cal_goup_user_id[$i++] = $r['cal_user_id'];

        }

Code: Select all

/* 
$connection = @mysql_connect($host, $user, $pass) or die ("Unable to connect to database"); 
mysql_select_db($db) or die ("Unable to select database: $db "); 
*/
Next, you choose the users, their ID, forneme na surname from users where their ID is IN the list of values which are in the $cal_group_user_id. So here you need my script which should work correctly, if $cal_group_user_id is an array.

Code: Select all

$query = "SELECT DISTINCT user_id, forename, surname 
FROM users 
WHERE user_id IN ($cal_group_user_id)";
and then you put in on the screen:

Code: Select all

$result = mysql_query ($query); 
        while ($r = mysql_fetch_array($result)) { 
        echo "<option value="$r['user_id']">$r[forename] $r[surname]</option>"; 
        }                           
      
?>
If I'm wrong please write the table definition or something more. It would be easier for me to help you then.
Post Reply