trying to get a four column array to align correctly in a tbale 1

[tippytarka]

can someone tell me where i'm going wrong with this....

i'm trying to loop my query results into a table and i get a very weird result. i guess its something to do with how i express the array ....i think

when i run the query in mysql i get this, which is what i'm trying to achieve....

jobname  | jobtitle  | tel       | email
-------------------------------------------------
creative  | 555       | 499     | 2313
-------------------------------------------------
Design   | 5575     | 5503    | 10370
-------------------------------------------------
photo     | 32        | 18        | 416

but when i run the query using this php script....

// open table
echo '<table width="555" height="38" border="0" cellpadding="0" cellspacing="1">';

// table header
echo '<tr bgcolor="#FF943C" class="style60 style4">';
echo '<td colspan="2" height="31" align="center">JOB TITLE</td>';
echo '<td width="92" height="31" align="center">WITH TEL</td>';
echo '<td width="92" height="31" align="center">WITH EMAIL</td>';
echo '<td width="92" height="31" bgcolor="#FF6600" align="center">QUANTITY</td>';
echo '</tr>';
 						
// begin for loop and mysql query
for ($i = 0; $i < count($job_title_id); $i++) { 
$query = "SELECT 
		p1_job_title_code.job_title_01 AS JOB_TITLE,
		SUM(CASE WHEN p1_list_creative.telephone = ' ' THEN 0 ELSE 1 END) AS WITH_TEL,
		SUM(CASE WHEN p1_list_creative.email = ' ' THEN 0 ELSE 1 END) AS WITH_EMAIL,
		COUNT(p1_job_title_code.job_title_id) AS QUANTITY 
		FROM p1_list_creative, p1_job_title_code
		WHERE p1_job_title_code.job_title_id = '".$job_title_id[$i]."'
		AND p1_list_creative.country_id = '".$country_id."'
		AND p1_list_creative.job_title_id = '".$job_title_id[$i]."'
		GROUP BY p1_job_title_code.job_title_id";
$query_result = mysql_query($query);

$table = mysql_result($query_result,0);
				
// open table row
echo '<tr bgcolor="#FFBC79" class="style60">';			
											
// column 1 JOB TITLE 
echo '<td height="31" colspan="2" align="center" class="style60">'.$table[0].'</td>';

// column 2 WITH TEL  
echo '<td height="31" align="center" class="style60">'.$table[1].'</td>';

// column 3 WITH EMAIL 
echo '<td height="31" align="center" class="style60">'.$table[3].'</td>';
						
// column 4 QUANTITY
echo '<td height="31" align="center" class="style60">'.$table[4].'</td>';
				
// close looped table row
echo '</tr>';
				
// end for loop
}

// end table
echo '</table>';

here's what i'm getting.....

jobname  | jobtitle  | tel       | email
-------------------------------------------------
c            | r           | a         | t
-------------------------------------------------
d            | e          | i          | g
-------------------------------------------------
p            | h          | t          | o

cheers!

 

[Coogan]

You may want to start by looking at your query. It looks like your query is only taking the job name.

Your code...

// column 1 JOB TITLE 
echo '<td height="31" colspan="2" align="center" class="style60">'.$table[0].'</td>';

// column 2 WITH TEL  
echo '<td height="31" align="center" class="style60">'.$table[1].'</td>';

// column 3 WITH EMAIL 
echo '<td height="31" align="center" class="style60">'.$table[3].'</td>';
                        
// column 4 QUANTITY
echo '<td height="31" align="center" class="style60">'.$table[4].'</td>';

... is then taking the first, second, fourth and fifth letter of the job name and displaying it in the table.

Sorry i cant help any further but I am a newbie to PHP and still learning.

Hope this helps a little.

Thanks

Martin

 

[Westbury]

You need to tell php to put he results into an array. mysql_query returns a resource not an array.
Try something like:

$table2 = mysql_fetch_array($table, MYSQL_NUM);
.
.
.
echo '<td height="31" colspan="2" align="center" class="style60">'.$table2[0].'</td>';

 

[tippytarka]

i tried your suggestion but the result was an empty column??

see code change....

for ($i = 0; $i < count($job_title_id); $i++) { 
$query = "SELECT 
		p1_job_title_code.job_title_01 AS JOB_TITLE,
		SUM(CASE WHEN p1_list_creative.telephone = ' ' THEN 0 ELSE 1 END) AS WITH_TEL,
		SUM(CASE WHEN p1_list_creative.email = ' ' THEN 0 ELSE 1 END) AS WITH_EMAIL,
		COUNT(p1_job_title_code.job_title_id) AS QUANTITY 
		FROM p1_list_creative, p1_job_title_code
		WHERE p1_job_title_code.job_title_id = '".$job_title_id[$i]."'
		AND p1_list_creative.country_id = '".$country_id."'
		AND p1_list_creative.job_title_id = '".$job_title_id[$i]."'
		GROUP BY p1_job_title_code.job_title_id";
$query_result = mysql_query($query);

$table = mysql_result($query_result,0,0);
$table2 = mysql_fetch_array($table, MYSQL_NUM);
				
// open table row
echo '<tr bgcolor="#FFBC79" class="style60">';			
											
// column 1 JOB TITLE 
echo '<td height="31" colspan="2" align="center" class="style60">'.$table2[0].'</td>';

 

[tippytarka]

westbury, actually your suggestion worked like this....

$table = mysql_fetch_row($query_result);

thanks!