Help pulling a query from MySQL with PHP

**kewlistantikewl** · Dec 22, 2004 10:29

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

**MikeBigg** · Dec 22, 2004 10:50

Do $name and $position have any scope outside of the function open_type() ?

Mike

**kewlistantikewl** · Dec 22, 2004 10:54

no they will only be used in the function open_type section.

**swdev** · Dec 22, 2004 10:56

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?

**kewlistantikewl** · Dec 22, 2004 11:00

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?

**MikeBigg** · Dec 22, 2004 11:05

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

**swdev** · Dec 22, 2004 11:09

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'

**kewlistantikewl** · Dec 22, 2004 11:13

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

**kewlistantikewl** · Dec 22, 2004 11:18

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

**swdev** · Dec 22, 2004 11:23

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?

**kewlistantikewl** · Dec 22, 2004 11:28

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?

**MikeBigg** · Dec 22, 2004 11:35

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

**kewlistantikewl** · Dec 22, 2004 11:40

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

**MikeBigg** · Dec 22, 2004 11:50

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

**kewlistantikewl** · Dec 22, 2004 11:57

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 "

**MikeBigg** · Dec 22, 2004 12:06

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

**kewlistantikewl** · Dec 22, 2004 12:08

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

**MikeBigg** · Dec 22, 2004 12:16

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

**swdev** · Dec 22, 2004 12:23

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.

**kewlistantikewl** · Dec 22, 2004 13:14

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.

**swdev** · Dec 22, 2004 13:28

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

**kewlistantikewl** · Dec 22, 2004 13:37

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

} 

?>

User Tag List

Thread: Help pulling a query from MySQL with PHP

Thread Tools

Display

Help pulling a query from MySQL with PHP

Bookmarks

Bookmarks

Posting Permissions