SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Enthusiast kewlistantikewl's Avatar
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Help pulling a query from MySQL with PHP

    Ok so i'm trying to pull a simple query from mysql database and its not returning anything. Can anyone help me??

    Here is the code i'm using
    PHP Code:
    <?php
    #MySQL Authentication Information
    $host="localhost";
    $user="user";
    $pass="password";
        
    open_type();

    function 
    open_type(){
    global 
    $host,$user,$pass;
        
    $connect mysql_connect("$host","$user","$pass") or die ("Could not connect");

    mysql_select_db('database') or die ("Cannot Select DB");
                        
    $query="SELECT * FROM users WHERE userid='ACCOUNTS\firstname.lastname'";

    $result mysql_query($query) or die("Query Failed");
                                        
    while (
    $line mysql_fetch_array($result)) {
    $name=$line['fullname'];
    $position=$line['position'];                
    }
    mysql_free_result($result);
    mysql_close($connect);
    ?>    
                

    <html>
    <head><title></title></head>

    <body>
    The approver is <?php echo("$name")?>. and their position is <?php echo("$position")?>
    </body>

    </html>
    <?php
    }
    ?>

    Any help would be greatly appreciated
    Lost:PHP coding skills if found please call 1-800-Need-PHP

  2. #2
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do $name and $position have any scope outside of the function open_type() ?

    Mike

  3. #3
    SitePoint Enthusiast kewlistantikewl's Avatar
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no they will only be used in the function open_type section.
    Lost:PHP coding skills if found please call 1-800-Need-PHP

  4. #4
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    userid = 'ACCOUNTS\firstname.lastname'

    This looks like a very strange userid to me. What type of field is userid?
    What exactly is the string (or integer) that you are looking for?

  5. #5
    SitePoint Enthusiast kewlistantikewl's Avatar
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The userid field is a field from the DB that holds the user account information.

    I'm trying to fix a problem in a system that wasn't created by me so all i know is that the field is a copy of their system logon id.

    ie. logon info = firstname.lastname
    System = ACCOUNTS

    it is stored in the Active Directory as ACCOUNTS\firstname.lastname

    does that help?
    Lost:PHP coding skills if found please call 1-800-Need-PHP

  6. #6
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kewlistantikewl
    no they will only be used in the function open_type section.
    I knew the answer, I was posing it as a question to the original poster.

    One way round this is to declare the variables where you have the database variables created and include them in the globals line.

    Regarding the odd SQL. Can you post the relevant database field names and types with a couple of typical record entries (not real names and passwords).

    Regards,

    Mike

  7. #7
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When you say 'stored in the Active Directory' what do you mean?

    To help you out, remove the WHERE clause from the SQL statement, and just print out the userid(s) returned. This will at least show you the data that is held in the database.

    The way userid is currently setup, it will only ever look for a userid of 'ACCOUNTS\firstname.lastname'. I assume that you actually want to search the user table for information for a given firstname and lastname, e.g. if my name was joe public, then you would want to search for 'ACCOUNTS\joe.public'. Is that correct?

    Also, the backslash should be escaped, as in userid = 'ACCOUNTS\\firstname.lastname'

  8. #8
    SitePoint Enthusiast kewlistantikewl's Avatar
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MikeBigg
    I knew the answer, I was posing it as a question to the original poster.
    Heheheh I am the original poster

    Here's an example pulled from the DB

    userid fullname position phone email
    ACCOUNTS\john.smith Smith,John Helpdesk 9999 jsmith@email.com
    ACCOUNTS\john.jones Jones,John Manager 1111 jjones@email.com
    Lost:PHP coding skills if found please call 1-800-Need-PHP

  9. #9
    SitePoint Enthusiast kewlistantikewl's Avatar
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by swdev
    When you say 'stored in the Active Directory' what do you mean?
    Its a copy of the company's active directory that is all i know.

    To help you out, remove the WHERE clause from the SQL statement, and just print out the userid(s) returned. This will at least show you the data that is held in the database.
    in doing this it only pulled the first record how do i pull all of them?

    (sorry its been awhile since i've programmed in PHP.)

    The way userid is currently setup, it will only ever look for a userid of 'ACCOUNTS\firstname.lastname'. I assume that you actually want to search the user table for information for a given firstname and lastname, e.g. if my name was joe public, then you would want to search for 'ACCOUNTS\joe.public'. Is that correct?
    Yes that is correct. any advice on how to do that?

    Also, the backslash should be escaped, as in userid = 'ACCOUNTS\\firstname.lastname'
    thanks i had forgotten that one. but it still doesn't work
    Lost:PHP coding skills if found please call 1-800-Need-PHP

  10. #10
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Where are you going to get the firstname and lastname that you want to look up from? do they come from a form? or via the URL?

  11. #11
    SitePoint Enthusiast kewlistantikewl's Avatar
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well as of right now its only going to be one name but what i'd like to do is have it query something like this


    $query="SELECT * FROM users WHERE userid = {$server['Auth_user']}"

    and of course have this above it

    if (isset($_SERVER['AUTH_USER'])) { // Current user
    $user = addslashes($_SERVER['AUTH_USER']);
    } else {
    $user = "unknown";
    }

    With the appropriate login info.

    That way if their Auth_user is = to the user id in the DB it gives them a certain page ie. an admin page as opposed to the basic user page.

    make sense?
    Lost:PHP coding skills if found please call 1-800-Need-PHP

  12. #12
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This should work for the specific user case:

    PHP Code:
    <?php 
    #MySQL Authentication Information 
    $host="localhost"
    $user="user"
    $pass="password"

    $name 'default';
    $position 'default';
         
    open_type(); 

    function 
    open_type(){ 
    global 
    $host,$user,$pass$name$position 
         
    $connect mysql_connect("$host","$user","$pass") or die ("Could not connect"); 

    mysql_select_db('database') or die ("Cannot Select DB"); 
                         
    $query="SELECT * FROM users WHERE userid='ACCOUNTS\john.smith'"

    $result mysql_query($query) or die("Query Failed"); 
                                         
    while (
    $line mysql_fetch_array($result)) { 
    $name=$line['fullname']; 
    $position=$line['position'];                 

    mysql_free_result($result); 
    mysql_close($connect); 
    ?>     
                 

    <html> 
    <head><title></title></head> 

    <body> 
    The approver is <?php echo("$name and their position is $position")?> 
    </body> 

    </html> 
    <?php 

    ?>
    Mike

  13. #13
    SitePoint Enthusiast kewlistantikewl's Avatar
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey mike,

    It doesn't work now i get "The approver is default and their position is default "

    I need that to show the user's name and position
    Lost:PHP coding skills if found please call 1-800-Need-PHP

  14. #14
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK. I think that is progress

    Silly question: did you change the john.smith in the query string to a real user that is in the database?

    Also, do you have access to something like MySQL-Front so you can test the sql before putting it into the script?

    Also, can you put the following lines between the $result = line and the while line:

    PHP Code:
    $name 'abcd' ;
    $position '1234' 
    this may show us something.

    Mike

  15. #15
    SitePoint Enthusiast kewlistantikewl's Avatar
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Silly question: did you change the john.smith in the query string to a real user that is in the database?
    I've used a real name and it doesn't work

    Also, do you have access to something like MySQL-Front so you can test the sql before putting it into the script?

    I've tested the script and it won't pull the ACCOUNTS\User.name it queries the DB but pulls nothing i think i may have something to do with the slash

    Also, can you put the following lines between the $result = line and the while line:


    PHP Code:
    $name = 'abcd' ;
    $position = '1234' ;
    I get this "The approver is abcd and their position is 1234 "
    Lost:PHP coding skills if found please call 1-800-Need-PHP

  16. #16
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To see if it is the slash, try using a wildcard character in there:

    ACCOUNTS%john.smith

    Also, what about mySQL-Front? Have you got it?

    Mike

  17. #17
    SitePoint Enthusiast kewlistantikewl's Avatar
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To see if it is the slash, try using a wildcard character in there:

    ACCOUNTS%john.smith
    I tried that and it still doesn't work

    Also, what about mySQL-Front? Have you got it?
    The only thing i have is the sql feature in PHPmyadmin.

    I'm using that
    Lost:PHP coding skills if found please call 1-800-Need-PHP

  18. #18
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When you put the query into phpmyadmin, what results do you get?

    There is no point putting your sql into the php script until you can get the data you want out of the database in phpmyadmin.

    If you develop on a windows PC, it is worth getting hold of mySQL-Front or mySQL-CC. It will give you a much easier and more familiar interface to your database. I couldn't work without it!

    Mike

  19. #19
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't have $_SERVER['AUTH_USER'] defined on my system, so I can't tell what its format is.

    However, your SQL query should look something like this

    PHP Code:
       
       $system_name 
    'ACCOUNTS';  // change as required
       
    $first_name 'john';  // change / get from wherever
       
    $last_name 'smith'// change / get from wherever
       
       
    $sql 'SELECT * FROM users WHERE userid = \'' $system_name '\\' $first_name '.' $last_name '\'';
       echo 
    'executing query ' $sql// <== diagnostics - see what is being sent to the database
       
    $result mysql_query($sql) or die('Failed to execute ' $sql ' dur to ' mysql_error()); 
    See if this helps. At the least, you should be able to see what you are sending to the database, and work from there.

  20. #20
    SitePoint Enthusiast kewlistantikewl's Avatar
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MikeBigg
    When you put the query into phpmyadmin, what results do you get?

    There is no point putting your sql into the php script until you can get the data you want out of the database in phpmyadmin.

    If you develop on a windows PC, it is worth getting hold of mySQL-Front or mySQL-CC. It will give you a much easier and more familiar interface to your database. I couldn't work without it!

    Mike
    this is what i get
    Your SQL-query has been executed successfully (Query took 0.0004 sec)

    SQL-query : [Edit] [Explain SQL] [Create PHP Code]
    SELECT *
    FROM users
    WHERE userid = 'ACCOUNTS%john.doe' LIMIT 0 , 30


    But i returns nothing.
    Lost:PHP coding skills if found please call 1-800-Need-PHP

  21. #21
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use that exact same query, but remove the WHERE clause (e.g SELECT userid FROM users). This should return you all the data fom the user table.
    That will then tell you the exact format that the data is stored in the database.
    post the results

  22. #22
    SitePoint Enthusiast kewlistantikewl's Avatar
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks swdev but i did the query as

    Code:
    SELECT * 
    FROM users
    WHERE userid = 'ACCOUNTS\\\john.doe'
    and it returned the john doe entry so now that i've got that working how do i get it so it populates a drop down list with all the users in a certain position

    ie.

    PHP Code:
    <?php
    function open_type(){
    global 
    $host,$user,$pass,$name,$position;
        
    #Grab All Employees
    $connect mysql_connect("$host","$user","$pass") or die ("Could not connect");
    mysql_select_db('halzilla') or die ("Cannot Select DB");

    $query="SELECT * FROM users WHERE position='Manager'";
    $result mysql_query($query) or die("Query Failed");

    while (
    $line mysql_fetch_array($result)) {
    $name=$line['fullname'];
    $position=$line['position'];

    $approvers_names.="<option>$name";
    }
            
    mysql_free_result($result);
    mysql_close($connect);        
    ?>    
                

    <html>
    <head><title></title></head>

    <body>
    The approver is <?php echo("$name and their position is $position")?> 
    <select name="completed_by"><option>Select Name<?php echo "$approvers_name?></select>

    </body>

    </html>
    <?php

    ?>
    Lost:PHP coding skills if found please call 1-800-Need-PHP


Bookmarks

Posting Permissions

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