Query within a query ... array help too

nadir_s

I am making a table from the first query. However, within each row, I want to run another query to list additional details pulled from the row that's being called.

The code below will list an applicant name and their IP address. I also want it to list all other names that had posted with the same IP address (this is where I do the second query for that).

The array part for it is driving me up the wall .. not sure if I even have the second query setup correctly. Any pointers would be much appreciated!

<?php

include("../_includes/connect.php"); 

$query  = "SELECT DATEDIFF(CURDATE(), apps.birthday) AS daysOld, apps.id, apps.applicant, apps.uuid, DATEDIFF(CURDATE(), apps.date_added) AS submitdate, apps.birthday, apps.role, apps.q_meta, apps.q_exp, apps.q_char, apps.q_goals, apps.q_ie, apps.group_name, apps.ip, apps.status, apps.lastupdated, apps.community FROM apps WHERE YEARWEEK(apps.date_added) = YEARWEEK(CURRENT_DATE) AND apps.status != 'PENDING' ORDER BY apps.id DESC";

$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
while($nt=mysql_fetch_array($result)){

// Trying to run a query within this query to list all other applicant names submitted with the same ip address.
// Below is where I am attempting to run the second query to retrieve names ... i put results into an arrow and try to implode, but doesn't work! I don't get any errors, just the applicant's name twice
	$alts_results = mysql_query("SELECT apps.applicant FROM apps WHERE apps.ip = '$nt[ip]'") or die('FAIL!');
	$altlist = mysql_fetch_array($alts_results);
	$alts = implode(",", $altlist);

echo stripslashes("<tr valign=\"top\" id=\"$nt[id]\"><td valign=\"top\"><b>$nt[applicant]</b><br>[ip]<br>Also applied with under these names: $alts</td></tr>");
}

mysql_close();
?>

johanafm

You aren't going at this in a good way. There is no reason to have a separate query. Get all the data you need in one query. I'm guessing that for each applicant matching your criteria you want to find all other applicants with the same ip. If so

SELECT ..., apps.applicant, ..., apps.ip, ..., ip_related.applicant AS ipr_applicant FROM apps
FROM apps
LEFT JOIN apps AS ip_related ON ip_related.ip = apps.ip AND ip_related.id != apps.id
WHERE ...
ORDER BY apps.id DESC";

Then, as you foreach over the result, just keep track on what applicant you are handling

$last_app = null;
$ipr_apps = null;
while($nt = mysql_fetch_array()) {
	// just started on the next applicant.
	if ($last_app != $nt['id']) {
		// This corresponds to what happened in your former outermost loop

	// You also need to
	if ($ipr_apps) {
		$altlist = implode(', ' . $ipr_apps);
		// etc
	}
	$ipr_apps = array();
	$last_app = $nt['id'];
}

// this corresponds to the results of your nested query
$ipr_apps[] = $nt['ipr_applicant'];
}

To help out while debugging, I'd start by turning on all error reporting. Furthermore, it's often helpful to see what a query really looks like, so

	$alts_query = "SELECT apps.applicant FROM apps WHERE apps.ip = '$nt[ip]'";
	echo $alts_query;
	$alts_result = mysql_query($alts_query) OR die('FAIL');

And you most definitely want to show yourself any actual error. While 'FAIL' may accurately describe the situation, it is somewhat lacking in precision.

$alts_result = mysql_query($alts_query);
if (!$alts_result) {
	// Now you have a whole lot better information to go by...
	error_log(mysql_errno() . ': ' . mysql_error());
	die('FAIL');
}

When the above is ok, why not try some more output

print_r($altlist);

And the actual problem in your code is that you don't go over all your related applicants, but only use the first...

	$altlist = array();
	while($alt_row = mysql_fetch_array($alts_results);
		$altlist[] = $alt_row['applicant'];
	$alts = implode(",", $altlist);

nadir_s

Hi Johanafm,

Thanks much for your help thus far! It's much appreciated 🙂 The debugging part is good practice; I'll start incorporating that as I go along.

Is the second part of your post (everything related to alt_query) dependent on the first? I incorporated the second part of your post first to see if I can fix the array issue (I was having this array problem in another page). However, this time, I get no results ...

<?php

include("../_includes/connect.php"); 

$query  = "SELECT DATEDIFF(CURDATE(), apps.birthday) AS daysOld, apps.id, apps.applicant, apps.uuid, DATEDIFF(CURDATE(), apps.date_added) AS submitdate, apps.birthday, apps.role, apps.q_meta, apps.q_exp, apps.q_char, apps.q_goals, apps.q_ie, apps.group_name, apps.ip, apps.status, apps.lastupdated, apps.community FROM apps WHERE YEARWEEK(apps.date_added) = YEARWEEK(CURRENT_DATE) AND apps.status != 'PENDING' ORDER BY apps.id DESC";

$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
while($nt=mysql_fetch_array($result)){

// Trying to run a query within this query to list all other applicant names submitted with the same ip address.
// Below is where I am attempting to run the second query to retrieve names ... i put results into an arrow and try to implode, but doesn't work! I don't get any errors, just the applicant's name twice
	$alts_query = "SELECT apps.applicant FROM apps WHERE apps.ip = '$nt[ip]'";
    echo $alts_query; // debug results
    $alts_result = mysql_query($alts_query);
		if (!$alts_result) {
    	// Now you have a whole lot better information to go by...
    	error_log(mysql_errno() . ': ' . mysql_error());
    	die('FAIL');
		} 

$altlist = array();

while($alt_row = mysql_fetch_array($alts_result));
    $altlist[] = $alt_row['applicant'];
$alts = implode(",", $altlist);
print_r($altlist); // debug results


echo stripslashes("<tr valign=\"top\" id=\"$nt[id]\"><td valign=\"top\"><b>$nt[applicant]</b><br>[ip]<br>Also applied with under these names: $alts</td></tr>");
}

mysql_close();
?>

The debug dumps I'm getting look like this:

SELECT apps.applicant FROM apps WHERE apps.ip = '24.1.101.92'Array ( [0] => ) SELECT apps.applicant FROM apps WHERE apps.ip = '93.35.51.47'Array ( [0] => ) SELECT apps.applicant FROM apps WHERE apps.ip = '76.20.72.128'Array ( [0] => ) SELECT apps.applicant FROM apps WHERE apps.ip = '76.21.107.54'Array ( [0] => ) SELECT apps.applicant FROM apps WHERE apps.ip = '76.21.107.54'Array ( [0] => ) SELECT apps.applicant FROM apps WHERE apps.ip = '76.21.107.54'Array ( [0] => ) SELECT apps.applicant FROM apps WHERE apps.ip = '171.71.137.37'Array ( [0] => )

I will clone this file and start working on rewriting my initial query with the first part of your post's guidance.

Again, thank you so much!

nadir_s

OH snap ... I adjusted the while statement to wrap the line beneath in brackets to look like this and I think it works now!

while($alt_row = mysql_fetch_array($alts_result)){
        $altlist[] = $alt_row['applicant'];}
    $alts = implode(",", $altlist);
	print_r($altlist);