put ids into array from mysql query

[wizardry]

hello -

i'm trying to pull this query from the ids that are displayed on the page from another query.

it runs but it limits the result sets only returning one record instead of 2 that are for the query.

the first query can have many id's and the 2nd query can have many to the first.

the only way i can get the id is from assigning a variable to the posted id record set.

my main objective is to create an array from the id's in the first query to pull the data for the second query.

could u provide an example of storing an array of ids and accessing them for the 2nd qry? i've googled, but found some similar nothing useful.

i would post code but limit is 20 lines.

thanks in advance for your help!

[Dormilich]

not sure if I understood it correctly, but here are some (hopefully) helpful links:
PDO - fetchAll()
MySQLi - fetch_all()

[pradeepkr13]

would be great if you can exemplify what exactly your need is.

[wizardry]

thanks for your response.

ok here is a moch of what im trying to accomplish.

//primary query
 
mysql_select_db($database_example, $example);
$qry_user = "select id, name, img from users"
$query_user = sprintf("%s LIMIT %d, %d", $query_user, $startRow_user, $maxRows_query_user);
$user = mysql_query($query_limit_user, $example) or die(mysql_error());
$row_user = mysql_fetch_assoc($user);
 
// sub query
 
mysql_select_db($database_notes, $notes);
$query_notes = "select notes, id, idfk, name, dates from notes where idfk="$NId" order by dates desc";
$query_limit_notes = sprintf("%s LIMIT %d, %d", $query_notes, $startRow_notes, $maxRows_notes);
$notes = mysql_query($query_limit_notes, $notes) or die(mysql_error());
$row_notes = mysql_fetch_assoc($notes);
 
 

ok here is my html moch of what im trying to accomplish.

<!-- 
this is the layout for the first query
 
-->
<table border="2" align="center" width="50%" cellpadding="5" cellspacing="5">
    <?php do { ?>     
    <tr>
    <td valign="top" align="center"><?php echo $row_user['name']; ?><br /><img src="<?php echo $row_user['img']; ?>" height="120" width="120"/>    
// i assign the id to a php variable to pull the second record query record set
 
$TNum = $row_user['Id'];
 
            //echo $TNum; // for debugging only
            ?></td>
            </tr>
 
 
// second layout for second query
 
<?php 
 
// create array of name ID[$i] for record sets
 
    $TNum_Id=$_POST['TNum'];
 
    for($i=0;$i<count($TNum_Id);$i++) {
 
    //echo $i;
 
    $NId=$TNum_Id[$i]; 
 
 
$result = $result = @mysql_query($query_notes, $notes) or die (mysql_error()) ;          
 
        $num = $totalRows_notes ; // get row count
 
        $thumbcols = 2 ; // column count
 
        // create the columns based on row count 5 columns per row
        $thumbrows = 1+ round($num / $thumbcols);
 
        // check record set for values
 
        if(!empty($num)) {
 
    } // closing of record count
 
//create function for table layout
    function display_table() {
 
        // create global variables
        global $num, $result, $thumbrows, $thumbcols ; 
 
        // for loop for row counter
        for ($r=1; $r<=$thumbrows; $r++) {
 
         echo '<tr>' ;
 
            // for loop for the columns
            for ($c=1; $c<=$thumbcols; $c++) {
 
            echo '<td align="center" valign="top">';    
 
            // get array from the query
 
            $row = @mysql_fetch_array($result) ;
 
            $Id = $row['idfk'] ;
            $Name = $row['name'] ;
            $notes = $row['notes'] ;
 
            // check to see if path is not null
 
            if (!empty($Name)) {
            echo "$Date"; echo "<br />";    
            echo "$Name"; echo "<br />" ; echo "$Id"; // for debugging
 
            } // closing path
 
                else {
                    echo '&nbsp;' ;
                } //closing space
                 echo '</td>' ;
            } // closing the rows loop
            echo '</tr>' ;
        } //closing the row loop
    } // closing the outer loop
 
    // display the table
         display_table() ; 
 
          }
      ?>
 
<?php     } while ($row_user = mysql_fetch_assoc($user));?>
            </table>
 
 

the way the db design is 1=>8 on user / notes

what im trying to do is grab the id from user store it in a variable to pull if any records that match the sub query, else just show the user record.

thanks in advance for your help!

[pradeepkr13]

Lets do it step by step,

First some changes in your primary query. Please take a backup of your file and put this and run it. It should give some output and exit. Copy that output and paste here (to this forum). Put your backup file in place again.

//primary query
 
mysql_select_db($database_example, $example);
$qry_user = "select id, name, img from users"
$query_user = sprintf("%s LIMIT %d, %d", $qry_user, $startRow_user, $maxRows_query_user);
echo  "SQL1 is: ".$query_user."<br>";
$user = mysql_query($query_limit_user, $example) or die("Failed to run example query ".mysql_error());
$row_user = mysql_fetch_assoc($user);
 
//debugging 1 start
echo '<pre>debugging 1 start';
while ($row_user_debugging = mysql_fetch_assoc($user)) {
    print_r($row_user_debugging);
}  
echo '</pre>';
//debugging 1 ends
 
 
// sub query
 
mysql_select_db($database_notes, $notes);
$query_notes = "select notes, id, idfk, name, dates from notes where idfk='$NId' order by dates desc";
$query_limit_notes = sprintf("%s LIMIT %d, %d", $query_notes, $startRow_notes, $maxRows_notes);
echo  "SQL2 is: ".$query_limit_notes."<br>";
$notes = mysql_query($query_limit_notes, $notes) or die("Failed to run notes query ".mysql_error());
$row_notes = mysql_fetch_assoc($notes);
 
//debugging 2 start
echo '<pre>debugging 2 start';
while ($row_user_debugging = mysql_fetch_assoc($user)) {
    print_r($row_user_debugging);
}  
echo '</pre>';
//debugging 2 ends
 
 
exit;
 

[wizardry]

thanks for your response.

here is the output:


SQL1 is: select id, name, img from users

debugging 1 start

SQL2 is: select notes, id, idfk, name, dates from notes where idfk='' order by dates desc

debugging 2 start


I see that the key is not being captured into the array. I've used a debug echo on that on the first script.

thanks in advance for your help!

[pradeepkr13]

Lets concentrate on the first part only,

Take backup and Try this,

//primary query
$db_selected = mysql_select_db($database_example, $example);
if (!$db_selected) {
    die ('Can not select DB : ' . mysql_error());
}
 
echo "Limits |$startRow_user|, |$maxRows_query_user| <br>";
$qry_user = "select id, name, img from users";
echo  "Partial SQL1 is: ".$query_user."<br>";
$query_user = sprintf("%s LIMIT %d, %d", $qry_user, $startRow_user, $maxRows_query_user);
 
echo  "Full SQL1 is: ".$query_user."<br>";
$user = mysql_query($query_limit_user, $example) or die("Failed to run example query ".mysql_error());
//$row_user = mysql_fetch_assoc($user);
 
//debugging 1 start
echo '<pre>debugging 1 start';
while ($row_user_debugging = mysql_fetch_assoc($user)) {
    print_r($row_user_debugging);
}  
echo '</pre>';
//debugging 1 ends
exit;
 

[wizardry]

Limits |0|, |10|
Partial SQL1 is: select id, name, img from users
Full SQL1 is: select id, name, img from users

debugging 1 startArray
(
    [Name] => example
    [Img] => ./example/demo/pictures/273930.jpg
    [Id] => 10
    )
Array
(
    [Name] => example
    [Img] => ./example/demo/pictures/273930.jpg
    [Id] => 9
    )
Array
(
    [Name] => example
    [Img] => ./example/demo/pictures/273930.jpg
    [Id] => 8
    )

[pradeepkr13]

Sorry for being too late,
I was occupied with some work.

Lets carry on,

Now please remove the exit command which I put in the first query (primary query). And put this debugging in place of second query. Dont forget to take a backup of your files.

// sub query
 
mysql_select_db($database_notes, $notes);
echo "Limits |$startRow_notes|, |$maxRows_notes| <br>";
$query_notes = "select notes, id, idfk, name, dates from notes where idfk="$NId" order by dates desc";
echo  "Partial SQL1 is: ".$query_notes."<br>";
$query_limit_notes = sprintf("%s LIMIT %d, %d", $query_notes, $startRow_notes, $maxRows_notes);
echo  "Full SQL1 is: ".$query_limit_notes."<br>";
$notes = mysql_query($query_limit_notes, $notes) or die(mysql_error());
//$row_notes = mysql_fetch_assoc($notes);
 
 //debugging 1 start
 echo '<pre>debugging 2 start';
 while ($row_notes_debugging = mysql_fetch_assoc($notes)) {
     print_r($row_notes_debugging);
 }  
 echo '</pre>';
 //debugging 2 ends
 exit; 

Give me the output.

[wizardry]

ok this is what i have from the second part.

 
Limits |0|, |10|
Partial SQL1 is: select id, name, img, dates from users order by Dates desc LIMIT 0, 10
Full SQL1 is: select id, name, img from users LIMIT 0, 10
 
debugging 1 startArray
(
    [name] => example
    [img] => ./example/demo/pictures/original/27393.jpg
    [Id] => 10
    [dates] => 2010-08-29 18:50:04
    )
Array
(
    [name] => example
    [img] => ./example/demo/pictures/original/27393.jpg
    [Id] => 9
    [dates] => 2010-08-29 18:45:46
    )
Array
(
    [name] => example
    [img] => ./example/demo/pictures/original/27393.jpg
    [Id] => 8
    [dates] => 2010-08-29 14:27:32
    )
Array
(
    [name] => example
    [img] => ./example/demo/pictures/original/27393.jpg
    [Id] => 7
    [dates] => 2010-08-24 15:03:50
)
Array
(
    [name] => example
    [img] => ./example/demo/pictures/original/27393.jpg
    [Id] => 6
    [dates] => 2010-08-24 14:59:17
)
Array
(
    [name] => example
    [img] => ./example/demo/pictures/original/27393.jpg
    [Id] => 3
    [dates] => 2010-08-24 14:31:51
  )
Array
(
    [name] => example
    [img] => ./example/demo/pictures/original/27393.jpg
    [Id] => 2
    [dates] => 2010-08-24 14:30:55
    )
Array
(
    [name] => example
    [img] => ./example/demo/pictures/original/27393.jpg
    [Id] => 1
    [dates] => 2010-08-24 12:04:28
)
 
 
Limits |0|, |10|
Partial SQL1 is: select notes, id, idfk, name, dates from notes order by dates desc
Full SQL1 is: select notes, id, idfk, name, dates from notes order by dates desc LIMIT 0, 10
 
debugging 2 startArray
(
    [notes] => testing the status update comment schema
    [dates] => 2010-08-29 23:03:36
    [idfk] => 10
 
)
Array
(
    [notes] => testing the status update comment schema
    [dates] => 2010-08-29 23:03:36
    [idfk] => 10
)
Array
(
    [notes] => testing the status update comment schema
    [dates] => 2010-08-29 23:03:36
    [idfk] => 9
)
Array
(
    [notes] => testing the status update comment schema
    [dates] => 2010-08-29 23:03:36
    [idfk] => 9
)
Array
(
    [notes] => testing the status update comment schema
    [dates] => 2010-08-29 23:03:36
    [idfk] => 7 
)
Array
(
    [notes] => testing the status update comment schema
    [dates] => 2010-08-29 23:03:36
    [idfk] => 3
)
Array
(
    [notes] => testing the status update comment schema
    [dates] => 2010-08-29 23:03:36
    [idfk] => 1
)
Array
(
    [notes] => testing the status update comment schema
    [dates] => 2010-08-29 23:03:36
    [idfk] => 1
)
 
 

thanks in advance for your help!

[pradeepkr13]

are you messing up with the SQL output? I am not getting exact debugg SQL command being executed. eg this SQL is lacking "where" clause
Partial SQL1 is: select notes, id, idfk, name, dates from notes order by dates desc

though I am sure it should have pasted it.
May you wanted to keep it secure(is it?).

Well lets move on, I see that you get 8 rows from first query result and 8 rows from second query.

Now, let me know how do you want to show these result on the screen? I would create code as per your representation.

[pradeepkr13]

I think I got what you need. See the output it gives, that should be the one you are willing for.

Try this (take backup before doing all this)

//primary query
 
$all_ids = array(); 
$str_ids = "";
mysql_select_db($database_example, $example);
$qry_user = "select id, name, img from users";
$query_user = sprintf("%s LIMIT %d, %d", $query_user, $startRow_user, $maxRows_query_user);
$user = mysql_query($query_limit_user, $example) or die(mysql_error());
//$row_user = mysql_fetch_assoc($user);
while ($row_user = mysql_fetch_assoc($user)) {
  $all_ids[] = $row_user['id']; //this is the array you want, 
  //but you dont need to use this much memory resources by creating array, you can use simple string to do this
  $str_ids .= $row_user['id'].','; //add a comma
}  
//remove last comma eg from 2,4,5,6,7,
$str_ids = (substr($str_ids,-1) == ',') ? substr($str_ids, 0, -1) : $str_ids;
 
//now create the second query with these ids
$query_notes = "select notes, id, idfk, name, dates from notes where idfk in ($str_ids) order by dates desc";
$query_limit_notes = sprintf("%s LIMIT %d, %d", $query_notes, $startRow_notes, $maxRows_notes);
$notes = mysql_query($query_limit_notes) or die(mysql_error());
//$row_notes = mysql_fetch_assoc($notes);
 
 //debugging 1 start
 echo '<pre>debugging 2 start';
 while ($row_notes = mysql_fetch_assoc($notes)) {
     print_r($row_notes_debugging);
 }  
 echo '</pre>';
 //debugging 2 ends
 exit; 

[wizardry]

thanks for your responses.

that was my error. but it should be as you have it where fkid = $id.

i had to remove it because it was not being feed into the string to query the second qry. i removed it to show that it does work for debugging.

thanks for your help! I just got up need to enjoy a couple cups of java, then i will post my results!

[wizardry]

ok that does do what i want it to do, sorry for the late response. trying to incorporate this query method into my html table layout in php.

what im trying to do is group them by fkid with the user id in php.

example:

# debugging 1 startArray
# (
#     [name] => example
#     [img] => ./example/demo/pictures/original/27393.jpg
#     [Id] => 10
#     [dates] => 2010-08-29 18:50:04
#     )
# debugging 2 startArray
# (
#     [notes] => testing the notes
#     [dates] => 2010-08-29 23:03:36
#     [idfk] => 10
#  
# )
# Array
# (
#     [notes] => testing the notes
#     [dates] => 2010-08-29 23:03:36
#     [idfk] => 10
# )
 
 

as in the example the parent query will be first then the child query will be right below it. then the next parent and child if any.

could you show me an example on how to display the results using this method. if needed i can post my current method.

thanks in advance for your help.

[pradeepkr13]

first query
while loop(row)
{
   print here row
   second query
   while loop(row_child)
   {
     print here row_child
   }
}

[Dormilich]

@pradeep: that’s not very efficient (though it works). less overhead is used when working with prepared statements:
- get all IDs with first query (1st query result array)
- make a prepared statement for the second query
- execute/fetch it for each of the IDs from the first query (result array)

[pradeepkr13]

Agree.

Even more efficient is what I gave before this.
Get all Ids with first SQL into array.
Create second query and use all ids from first in this query. Store into multidimensional array2.

Hence only Two DB querires.

Now, use loop to get things from array.

[wizardry]

thanks for your responses. I will post both my method and your method latter.

[wizardry]

ok i was able to get it working, however its not setting the $str_ids in this loop.

i've echo $str_ids after the - comma string and the comma is not being removed.

here is the output:

10,11,12,5,6,7,8,1,2
 
 
 

any insight is helpful, thanks in advance for your help!


here is my code:

<?php 
$all_ids = array();
$str_ids = "";
 
// first parent query
$maxRows_sourceType = 10;
$pageNum_sourceType = 0;
if (isset($_GET['pageNum_sourceType'])) {
  $pageNum_sourceType = $_GET['pageNum_sourceType'];
}
$startRow_sourceType = $pageNum_sourceType * $maxRows_sourceType;
 
mysql_select_db($database_Comments, $Comments);
$query_sourceType = "SELECT c.Name, h.Path, h.Default, h.Id as hId, j.Id as Id, j.type, j.Dates, k.comment FROM aswebinfo as c right join asmanyalbums as e on ( c.UIdFk=e.UserId ) right join asalbums as f on ( e.AlbumId=f.Id ) right join astitle as g on ( f.Id=g.AlbumId ) right join asdata as h on ( g.Id=TitleId ) right join asstatusupdate as j on ( c.UIdFk=j.UIdFk) right join asstatusdata as k on (k.SFk=j.Id) WHERE f.Default='Y' and h.Default='Y' and c.UIdFk = any (select FriendId from asfriends where UIdFk0=1) order by Dates desc";
$query_limit_sourceType = sprintf("%s LIMIT %d, %d", $query_sourceType, $startRow_sourceType, $maxRows_sourceType);
$sourceType = mysql_query($query_limit_sourceType, $Comments) or die(mysql_error());
$row_sourceType = mysql_fetch_assoc($sourceType);
 
if (isset($_GET['totalRows_sourceType'])) {
  $totalRows_sourceType = $_GET['totalRows_sourceType'];
} else {
  $all_sourceType = mysql_query($query_sourceType);
  $totalRows_sourceType = mysql_num_rows($all_sourceType);
}
$totalPages_sourceType = ceil($totalRows_sourceType/$maxRows_sourceType)-1;
 
// add the array
while ($row_source = mysql_fetch_assoc($sourceType)) {
 
    $all_ids[] = $row_source['Id'];
 
    $str_ids .= $row_source['Id'].',';
 
 }
 
        // remove the array
     $str_ids = (substr($str_ids,-1) == ',') ? substr($str_ids, 0, -1) : $str_ids;
 
//second child query
    $maxRows_sourceComments = 10;
$pageNum_sourceComments = 0;
if (isset($_GET['pageNum_sourceComments'])) {
  $pageNum_sourceComments = $_GET['pageNum_sourceComments'];
}
$startRow_sourceComments = $pageNum_sourceComments * $maxRows_sourceComments;
 
mysql_select_db($database_Comments, $Comments);
$query_sourceComments = "select a.memo, a.date as Date, b.SFk, c.Name, g.Path  from ascomments as a  right join asmanystatusupdate as b  on a.Id=b.CFk, aswebinfo as c right join asmanyalbums as d on ( c.UIdFk=d.UserId ) right join asalbums as e on ( d.AlbumId=e.Id ) right join astitle as f on ( e.Id=f.AlbumId ) right join asdata as g on ( f.Id=g.TitleId ) where c.UIdFk=any(select friendid from asfriends where UIdFk0='1') and e.Default='Y' and g.Default='Y' and b.SFk in ($str_ids) order by date desc";
$query_limit_sourceComments = sprintf("%s LIMIT %d, %d", $query_sourceComments, $startRow_sourceComments, $maxRows_sourceComments);
$sourceComments = mysql_query($query_limit_sourceComments, $Comments) or die(mysql_error());
$row_sourceComments = mysql_fetch_assoc($sourceComments);
 
if (isset($_GET['totalRows_sourceComments'])) {
  $totalRows_sourceComments = $_GET['totalRows_sourceComments'];
} else {
  $all_sourceComments = mysql_query($query_sourceComments);
  $totalRows_sourceComments = mysql_num_rows($all_sourceComments);
}
$totalPages_sourceComments = ceil($totalRows_sourceComments/$maxRows_sourceComments)-1;
 
 
 
$resultComments = @mysql_query($query_sourceComments, $Comments);
$numComments = @mysql_num_rows($resultComments);
 
 
$result = @mysql_query($query_sourceType, $Comments);
 
$num = @mysql_num_rows($result);
 
// column count for parent
    $thumbcols = 1;
 
        // column count for parent query
    $thumbrows = 1+ round($num / $thumbcols);
 
            // column count for child query    $thumbrowsComments = 1+ round($numComments/$thumbcols);
 
            // header
            print '<br />';
            print '<table align="center" width="500" border="2" cellpadding="0" cellspacing="0">';
 
            if (!empty($num)) {
                print '<tr><td colspan="3" align="center"><strong>Returned Num of Record Sets: ' .$num. ' and comments count' .$numComments. '</strong></td></tr>';    
 
            }
 
    // table layout for parent query        function display_table() {
 
// global variables
global $num, $result, $thumbrows, $thumbcols, $resultComments, $numComments, $thumbrowsComments ;
 
// row count for parent
 for ($r=1; $r<=$thumbrows; $r++) {
 
     // print table row
         print '<tr>';
 
//format the columns
for ($c=1; $c<=$thumbcols; $c++) {
 
print '<td align="center" valign="top">';
 
    $row = @mysql_fetch_array($result);
 
    $Id = $row['Id'];
    $Path = $row['Path'];
    $Name = $row['Name'];
    $Type = $row['Type'];
        $Dates = $row['Dates'];
    $Comment = $row['Comment'];
 
// test if not empty show record sets            
    if (!empty($Id)) {
 
                            // print output from parent query
 
                                    echo "$Name";
                                    echo "<br />";
                                    echo "&nbsp;";
                            echo '<img src="' ."$Path". '" height="120" width="120" />'; 
                                    echo "&nbsp;";
                                    echo "<br />";
 
                                    // grab type dates comment and format for there column
 
                                    echo '<td valign="top" align="center">'; 
                                    echo "$Type"; echo '&nbsp;&nbsp;'; echo "$Dates"; echo '<br />';echo '<br />';echo '<br />';
                                    echo "$Comment"; echo '</td>';
                                    echo '<td>'; echo "$Id"; echo '</td>';
                                    echo '<td>'; echo "$str_ids"; echo '</td>';
            } // closing the $id loop
 
                            // row count for second query
for($i=1;$i<=$thumbrowsComments;$i++){                           // print new row
                print '<tr>';
 
// column count for second query
        for ($g=1; $g<=$thumbcols; $g++) {
                            print '<td align="center" valign="top">';
 
                            $com = @mysql_fetch_array($resultComments);
 
                            $Memo = $com['memo'];
                            $Dates0 = $com['Date'];
                            $SFk = $com['SFk'];
                            $Name0 = $com['Name'];
                            $Path0 = $com['Path'];
 
 
    if (!empty($Memo)) {
echo "$numComments";
 
                            echo "$Name0";
                            echo "<br />";
                            echo "&nbsp;";
                            echo '<img src="' ."$Path0". '" height="120" width="120" />';
                            echo "&nbsp;";
                            echo "<br />";
 
                            // grab type comments and format the column
 
                                    echo '<td valign="top" align="center">';
                                    echo "$Dates0";
                                    echo "<br />";
                                    echo "$Memo";
                                    echo '</td>';
                                    echo '<td>'; echo "$SFk"; echo '</td>';
                            } // closing the $sfk loop
 
                                                else {
                                                        print '&nbsp;&nbsp;';
                                                    }
                            print '</td>'; //closing the table data
                        } //closing the rows
                            print '</tr>';
             } // closing the outter loop
            //} //closing fk id loop
                            print '</td>'; //closing the table data
                } //closing the rows
                            print '</tr>';    
            } // closing the outter loop
 
    } // closing the main loop
 
                // call the main table
            display_table() ;
                print '</table>';
 
 
?>