[php] PHP and MySQL

[MK-2]

me again with another php question cos i know you all love me

 

we have webhelpdesk set up using a mysql db. i've just had a look through and it stores the helpdesk tickets under the job_ticket table. in that table it has the job name/job description/date submitted etc which I hope i could pull out quite easily with some php.

the problem is, it doesn't list the username in there, it lists the user ID, which is then a part of the client table along with their username.

 

now i know to get all the jobs from job_ticket i could just grab all the rows, loop them in php and set variables for ticket_name/ticket_desc etc. i know i could also do something like:

SELECT *

FROM `helpdesk`.`client`

WHERE `CLIENT_ID` = xxxx

but then how do i pair the two, so as it goes through the loop getting the job details, it then checks the user id, finds the username from it and carries on?

 

would it be something like

$query="SELECT * FROM job_ticket";
$result=mysql_query($query);
$num=mysql_numrows($result);
$i = 0;
while ($i < $num) {
$jobname=mysql_result($result,$i,"job_name");
$jobdesc=mysql_result($result,$i,"job_desc");
$userid = mysql_result($result,$i,"user_id");

$query2 = "SELECT * FROM `helpdesk`.`client` WHERE `CLIENT_ID` = $userid";
$result2 = mysql_query($query2);

$user_name = mysql_result($result2,$i,"user_name");

echo "$user_name
$jobname
$jobdesc


";

$i++;
}

 

bearing in mind i've never done php in depth before this week so this is all sort of cobbled together from examples and what i assume would be correct. would what i've posted above work? my one thought is that in the $user_name variable i've set it to use $i but $i was used for the initial query, not the username query, so would it still work or would i have to change something?

Edited June 28, 2011 by MK-2

[glennda]

You would want to nest the query's

 

see here MySQL :: MySQL 5.0 Reference Manual :: 12.2.9 Subquery Syntax

this is the example on that site

 

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

[MK-2]

hmm my problem at the mo is doing SELECT * FROM job_ticket even in phpmyadmin shows "Showing rows 0 - 0 ( ~1 total 1, Query took 0.0005 sec)"

 

there is 1 job ticket in there, but its saying rows 0-0 so when $i=0 and while $i< $num it quits, as $=0 and $num=0 according to that

 

or its me being a complete....you know what.....when i typed in the php on the forum i used job_desc and job_name just as an example, its not actually stored as that, yet i left it in, so of course it found bugger all!

Edited June 28, 2011 by MK-2

[Marci]

$query = "SELECT t1.job_name, t1.job_desc, t2.user_name FROM job_ticket AS t1 INNER JOIN helpdesk.client AS t2 ON t1.userid = t2.userid";
$runquery = mysql_query($query, $connection) or die(mysql_error());
$row_results = mysql_fetch_assoc($runquery);
$total_records = mysql_num_rows($runquery);

echo '</pre><table>';

do	{        
	echo ''.$row_results['user_name'].''.$row_results['job_name'].''.$row_results['job_desc'].''; 
}
while ($row_results=mysql_fetch_assoc($runquery));

echo '</table>';<br>echo $total_records.' Support Tickets in total';<br

Edited June 28, 2011 by Marci

[MK-2]

$query = "SELECT t1.job_name, t1.job_desc, t2.user_name FROM job_ticket AS t1 INNER JOIN helpdesk.client AS t2 ON t1.userid = t2.userid";
$runquery = mysql_query($query, $connection) or die(mysql_error());
$row_results = mysql_fetch_assoc($runquery);
$total_records = mysql_num_rows($runquery);

echo '</pre><table>';

do	{        
	echo ''.$row_results['user_name'].''.$row_results['job_name'].''.$row_results['job_desc'].''; 
}
while ($row_results=mysql_fetch_assoc($runquery));

echo '</table>';<br>echo $total_records.' Support Tickets in total';<br

 

thanks for the pointers (and to glennda too). firstly im just pleased that the initial one i posted sort of worked, considering i made it up from examples and what i thought would work

 

i want to mess about with it some more, but at least i know it is possible