put ids into array from mysql query

**Dormilich** · Aug 30 '10, 05:43 PM

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

**pradeepkr13** · Aug 31 '10, 12:03 PM

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

**wizardry** · Aug 31 '10, 01:13 PM

thanks for your response.

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

Code:

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

Code:

<!-- 
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** · Aug 31 '10, 04:15 PM

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.

Code:

//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** · Aug 31 '10, 06:22 PM

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** · Sep 1 '10, 11:20 AM

Lets concentrate on the first part only,

Take backup and Try this,

Code:

//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** · Sep 1 '10, 01:10 PM

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

Code:

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** · Sep 5 '10, 05:41 PM

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.

Code:

// 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** · Sep 5 '10, 07:13 PM

ok this is what i have from the second part.

Code:

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** · Sep 6 '10, 09:48 AM

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** · Sep 6 '10, 10:09 AM

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)

Code:

//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** · Sep 6 '10, 05:20 PM

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** · Sep 6 '10, 11:53 PM

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:

Code:

# 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** · Sep 7 '10, 11:36 AM

Code:

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

put ids into array from mysql query

put ids into array from mysql query

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment