+ Post New Thread
Results 1 to 5 of 5
Web Development Thread, PHP and MySQL in Coding and Web Development; me again with another php question cos i know you all love me we have webhelpdesk set up using a ...
  1. #1

    MK-2's Avatar
    Join Date
    Oct 2006
    Location
    Nottingham
    Posts
    3,237
    Thank Post
    149
    Thanked 581 Times in 307 Posts
    Blog Entries
    8
    Rep Power
    199

    PHP and MySQL

    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
    PHP Code:
    $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 
    "<b>$user_name</b><br>$jobname<br>$jobdesc<br><br><hr><br>";

    $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?
    Last edited by MK-2; 28th June 2011 at 10:34 AM.

  2. #2

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,799
    Thank Post
    272
    Thanked 1,134 Times in 1,030 Posts
    Rep Power
    349
    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);

  3. Thanks to glennda from:

    MK-2 (28th June 2011)

  4. #3

    MK-2's Avatar
    Join Date
    Oct 2006
    Location
    Nottingham
    Posts
    3,237
    Thank Post
    149
    Thanked 581 Times in 307 Posts
    Blog Entries
    8
    Rep Power
    199
    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!
    Last edited by MK-2; 28th June 2011 at 10:55 AM.

  5. #4
    Marci's Avatar
    Join Date
    Jun 2008
    Location
    Wakefield, West Yorkshire
    Posts
    888
    Thank Post
    84
    Thanked 234 Times in 193 Posts
    Rep Power
    82
    PHP Code:
    <?php 
    $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 
    '<table>';

    do    {        
         echo 
    '<tr><td>'.$row_results['user_name'].'</td><td>'.$row_results['job_name'].'</td><td>'.$row_results['job_desc'].'</td></tr>'
        }
    while (
    $row_results=mysql_fetch_assoc($runquery));
     
    echo 
    '</table>';
    echo 
    $total_records.' Support Tickets in total';
    ?>
    Last edited by Marci; 28th June 2011 at 11:38 AM.

  6. Thanks to Marci from:

    MK-2 (28th June 2011)

  7. #5

    MK-2's Avatar
    Join Date
    Oct 2006
    Location
    Nottingham
    Posts
    3,237
    Thank Post
    149
    Thanked 581 Times in 307 Posts
    Blog Entries
    8
    Rep Power
    199
    Quote Originally Posted by Marci View Post
    PHP Code:
    <?php 
    $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 
    '<table>';

    do    {        
         echo 
    '<tr><td>'.$row_results['user_name'].'</td><td>'.$row_results['job_name'].'</td><td>'.$row_results['job_desc'].'</td></tr>'
        }
    while (
    $row_results=mysql_fetch_assoc($runquery));
     
    echo 
    '</table>';
    echo 
    $total_records.' Support Tickets in total';
    ?>
    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

SHARE:
+ Post New Thread

Similar Threads

  1. PHP/MySQL
    By Silvor in forum Coding
    Replies: 2
    Last Post: 12th May 2010, 02:52 PM
  2. Best Site to learn php and mysql?
    By My220x in forum Coding
    Replies: 11
    Last Post: 23rd April 2008, 08:58 AM
  3. PHP MYSQL Hit counter
    By Nij.UK in forum Web Development
    Replies: 9
    Last Post: 15th November 2006, 09:11 AM
  4. Php/MySQL Web Design
    By Adam in forum How do you do....it?
    Replies: 8
    Last Post: 18th July 2005, 04:13 PM
  5. php MySql
    By iking in forum Books and Manuals
    Replies: 0
    Last Post: 11th July 2005, 03:35 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •